row_number() over() 问题

-- row_number() over() 函数必须根据已经存在的列进行分组和排序，不会更改整个查询的总行数
-- 如果没有分组group by 则直接根据相对应的列进行分组和另外的列进行排序
-- 如果进行分组，则可以使用聚合函数，对对应的列分组，对另外的聚合函数进行排序

-- 如果没有分组直接根据列进行分组和排序，可以使用partition by 和 distribute by
-- order 是全局排序，可以单独使用，sort by 必须和distribute by 一起使用
select
    params['car_series_id'] as series_id,
    params['car_series_name'] as series_name,
    row_number() over(distribute by params['car_series_name'] sort by params['car_series_id'] asc) as rank_id,
    row_number() over(partition by params['car_series_name'] order by params['car_series_id'] asc) as rank_id2,
    row_number() over(partition by params['car_series_name']) as rank_id3, -- 单分组 如果排序？
    row_number() over(distribute by params['car_series_name']) as rank_id4,-- 但分组 如何排序？
    row_number() over(order by params['car_series_id'] asc) as rank_id5,   -- 单排序 整体排序
    row_number() over(sort by params['car_series_id'] asc) as rank_id6     -- 单sort 如果排序
from
    dm_autocar.autocar_mds_event_detail
where
    date = '${date}'
    -- date > '${date-12M}' and date <= '${date}'
    and event = 'page_enter'
    and params['page_id'] = 'page_car_series'







select
    params['car_series_id'] as series_id,
    params['car_series_name'] as series_name,
    count(distinct user_id) as enter_uv,
    row_number() over(order by count(distinct user_id) desc) as rank_id
from
    dm_autocar.autocar_mds_event_detail
where
    date = '${date}'
    -- date > '${date-12M}' and date <= '${date}'
    and event = 'page_enter'
    and params['page_id'] = 'page_car_series'
group by
    params['car_series_id'],
    params['car_series_name']