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 >= &#39;20191115&#39; and date <= &#39;20191118&#39;
    and page_id = &#39;page_category&#39;
    and sub_tab = &#39;motor_car&#39;
    and user_uid in (&#39;4296541257082451&#39;,&#39;663707435675239&#39;,&#39;52033783688&#39;)
group by date,user_uid