row_number分组函数
count(distinct user_id) 等同于 count(*) over(partition by user_id)
-- count(*) over(partition by params['page_car_series'],params['car_series_name']) 等同于 count(distinct user_id) -- count(distinct user_id) over() 等同于没有任何分组的全部用户 -- max() min() 也是一样的 select '${date}' as date, params['page_car_series'] as series_id, params['car_series_name'] as series_n count(distinct user_id) as uv, count(*) over(partition by params['page_car_series'],params['car_series_name']) as uv2, count(distinct user_id) over() as total_uv fro***utocar.autocar_mds_event_detail where date = '${date}' and event = 'page_enter' and params['page_id'] = 'page_car_series' group by params['page_car_series'], params['car_series_name']
-- OLAP函数只能根据分组里面的分组字段进行新的聚合 -- max() over() 、row_number() over() 都是根据分组获得的8条记录进行新的聚合函数 -- partition 必须基于现在存在的字段进行分组 select date, user_uid, count(*) as impr_cnt, count(*) over() as record_cnt, -- 记录数(后续所有函数 操作的原记录数) count(*) over(partition by date) as user_cnt, -- 日期分组的用户数 count(*) over(partition by user_uid) as date_cnt, -- 用户分组的用户日期数 max(date) over(partition by user_uid) as max_date, -- 用户分组的用户的最大日期 min(date) over(partition by user_uid) as min_date, -- 用户分组的用户的最小日期 row_number() over(partition by date) as rank -- 日期分组,用户排序 fro***utocar.autocar_common_article_impr_self where date >= '20191115' and date <= '20191118' and page_id = 'page_category' and sub_tab = 'motor_car' and user_uid in ('4296541257082451','663707435675239','52033783688') group by date,user_uid