走进大数据丨一条让我虎躯一震的SQL

超、凢脫俗 2023-06-19 11:05 40阅读 0赞

有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）

日期    用户    年龄

2019-02-11,test\_1,23

2019-02-11,test\_2,19

2019-02-11,test\_3,39

2019-02-11,test\_1,23

2019-02-11,test\_3,39

2019-02-11,test\_1,23

2019-02-12,test\_2,19

2019-02-13,test\_1,23

2019-02-15,test\_2,19

2019-02-16,test\_2,19

CREATE TABLE user_age (
    dt string,
    user_id string, age INT ) 
    ROW format delimited FIELDS TERMINATED BY ',';

1）按照日期以及用户分组，按照日期排序并给出排名

select
       dt,
       user_id,
       min(age) age,
       rank() over(partition by user_id order by dt) rk
    from
    
    
       user_age
    group by
    
    
       dt,user_id;t1

2）计算日期及排名的差值

select
    
    
       user_id,
    
    
       age,
    
    
    date_sub(dt,rk) flag
    
    
    from
    
    
       t1;t2

3）过滤出差值大于等于2的，即为连续两天活跃的用户

select
    
    
       user_id,
    
    
    min(age) age
    
    
    from
    
    
       t2
    
    
    group by
    
    
       user_id,flag
    
    
    having
    
    
    count(*)>=2;t3

4）对数据进行去重处理（一个用户可以在两个不同的时间点连续登录），例如：a用户在1月10号1月11号以及1月20号和1月21号4天登录。

select
    
    
       user_id,
    
    
    min(age) age
    
    
    from
    
    
       t3
    
    
    group by
    
    
       user_id;t4

5）计算活跃用户（两天连续有访问）的人数以及平均年龄

select
    
    
    count(*) ct,
    
    
    cast(sum(age)/count(*) as decimal(10,2))
    
    
    from t4;

6）对全量数据集进行按照用户去重

select
    
    
       user_id,
    
    
    min(age) age
    
    
    from
    
    
       user_age
    
    
    group by
    
    
       user_id;t5

7）计算所有用户的数量以及平均年龄

select
    
    
    count(*) user_count,
    
    
    cast((sum(age)/count(*)) as decimal(10,1))
    
    
    from
    
    
       t5;

8）将第5步以及第7步两个数据集进行union all操作

select
    
    
    0 user_total_count,
    
    
    0 user_total_avg_age,
    
    
    count(*) twice_count,
    
    
    cast(sum(age)/count(*) as decimal(10,2)) twice_count_avg_age
    
    
    from
    
    
    (
    
    
    select
    
    
       user_id,
    
    
    min(age) age
    
    
    from
    
    
       (select
    
    
       user_id,
    
    
    min(age) age
    
    
    from
    
    
       (
    
    
    select
    
    
       user_id,
    
    
       age,
    
    
    date_sub(dt,rk) flag
    
    
    from
    
    
       (
    
    
    select
    
    
           dt,
    
    
           user_id,
    
    
    min(age) age,
    
    
    rank() over(partition by user_id order by dt) rk
    
    
    from
    
    
           user_age
    
    
    group by
    
    
           dt,user_id
    
    
        )t1
    
    
        )t2
    
    
    group by
    
    
       user_id,flag
    
    
    having
    
    
    count(*)>=2)t3
    
    
    group by
    
    
       user_id
    
    
    )t4
    
    
    
    
    
    
    union all
    
    
    
    
    
    
    select
    
    
    count(*) user_total_count,
    
    
    cast((sum(age)/count(*)) as decimal(10,1)),
    
    
    0 twice_count,
    
    
    0 twice_count_avg_age
    
    
    from
    
    
       (
    
    
    select
    
    
             user_id,
    
    
    min(age) age
    
    
    from
    
    
             user_age
    
    
    group by
    
    
             user_id
    
    
       )t5;t6

9）计算最终结果

select
    
    
    sum(user_total_count),
    
    
    sum(user_total_avg_age),
    
    
    sum(twice_count),
    
    
    sum(twice_count_avg_age)
    
    
    from
    
    
    (select
    
    
    0 user_total_count,
    
    
    0 user_total_avg_age,
    
    
    count(*) twice_count,
    
    
    cast(sum(age)/count(*) as decimal(10,2)) twice_count_avg_age
    
    
    from
    
    
    (
    
    
    select
    
    
       user_id,
    
    
    min(age) age
    
    
    from
    
    
       (select
    
    
       user_id,
    
    
    min(age) age
    
    
    from
    
    
       (
    
    
    select
    
    
       user_id,
    
    
       age,
    
    
    date_sub(dt,rk) flag
    
    
    from
    
    
       (
    
    
    select
    
    
           dt,
    
    
           user_id,
    
    
    min(age) age,
    
    
    rank() over(partition by user_id order by dt) rk
    
    
    from
    
    
           user_age
    
    
    group by
    
    
           dt,user_id
    
    
        )t1
    
    
        )t2
    
    
    group by
    
    
       user_id,flag
    
    
    having
    
    
    count(*)>=2)t3
    
    
    group by
    
    
       user_id
    
    
    )t4
    
    
    
    
    
    
    union all
    
    
    
    
    
    
    select
    
    
    count(*) user_total_count,
    
    
    cast((sum(age)/count(*)) as decimal(10,1)),
    
    
    0 twice_count,
    
    
    0 twice_count_avg_age
    
    
    from
    
    
       (
    
    
    select
    
    
             user_id,
    
    
    min(age) age
    
    
    from
    
    
             user_age
    
    
    group by
    
    
             user_id
    
    
       )t5)t6;

![format_png][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9sR2x2b0tIblJVSjA2Qk1oaEIyRG05REptTG5JZmdtU2RMYmttTmw3M0FoSVgwajlxMURORVlFSFJ0MW54TFZma0F5RkxVSUdwcTY3ajV3aHJiNjBidy82NDA?x-oss-process=image/format,png