文章详情页_大数据SQL-截止目前登陆用户数及登陆用户列表

已知有用户登录记录表，包含登录日期和登录用户ID，请查询出截止到最新日期累积登录用户数、所有用户累积登录次数、登陆用户列表。

样例数据

+-------------+----------+
|  log_date   | user_id  |
+-------------+----------+
| 2024-01-01  | a        |
| 2024-01-02  | a        |
| 2024-01-02  | b        |
| 2024-01-03  | b        |
| 2024-01-04  | c        |
| 2024-01-05  | b        |
| 2024-01-05  | c        |
| 2024-01-05  | d        |
| 2024-01-05  | e        |
| 2024-01-06  | f        |
+-------------+----------+

期望结果

+-------------+-----------+-----------+---------------------+
|  log_date   | user_cnt  | login_cnt | user_list           |
+-------------+-----------+-----------+---------------------+
| 2024-01-01  | 1         | 1         | [a]                 |
| 2024-01-02  | 2         | 3         | [a, b]              |
| 2024-01-03  | 2         | 4         | [a, b]              |
| 2024-01-04  | 3         | 5         | [a, b, c]           |
| 2024-01-05  | 5         | 9         | [a, b, c, d, e]     |
| 2024-01-06  | 6         | 10        | [a, b, c, d, e, f]  |
+-------------+-----------+-----------+---------------------+

分析

统计截止到当前行的登录用户数，考察的是聚合函数开窗函数；查询用户列表考察的数据对数据的聚合、数组去重、数组排序等操作。属于深度考察开窗函数、数组操作等知识内容。

关于 COLLECT_SET 函数的详细用法，详见 MaxCompute 官方文档。

SQL

数据聚合

使用开窗函数 count() over(order by ...) 完成对截止到当前行的数据统计；
使用复杂函数 collect_list 将 user_id 列值聚合为一个数组。
执行 SQL

select 
    log_date
    ,user_id
    ,count(user_id) over (order by log_date)        as login_cnt
    ,collect_list(user_id) over (order by log_date) as user_list    
from t_user_login;

返回结果

数据去重聚合

使用复杂函数 collect_set 将 user_id 列值聚合为一个无重复元素的数组；
使用复杂函数 sort_array 对 ARRAY 数组中的元素进行排序。
执行 SQL

select 
    log_date
    ,user_id
    ,count(user_id) over (order by log_date)                    as login_cnt
    ,sort_array(collect_set(user_id) over (order by log_date))  as user_list
from t_user_login;

返回结果

根据 log_date 分组，得到最后结果

从上面数据可知：同一 log_date 下，user_list 列的值是一样的； user_list 列中的元素个数即为截至当前的登录用户数；user_list 列的最大值即为截至当前的累积用户登录次数。故使用 group by 分组即可得到最终结果。

执行 SQL

select 
    log_date
    ,max(user_cnt)  as user_cnt
    ,max(login_cnt) as login_cnt
    ,max(user_list) as user_list
from (
    select 
        log_date
        ,user_id
        ,size(sort_array(collect_set(user_id) over (order by log_date)))    as user_cnt
        ,count(user_id) over (order by log_date)                            as login_cnt
        ,sort_array(collect_set(user_id) over (order by log_date))          as user_list
    from t_user_login
)
group by log_date;

返回结果

建表语句

-- drop table t_user_login;
create table if not exists t_user_login (
    log_date    string,
    user_id     string
)
comment '用户登录记录表';

示例数据

-- 样例数据
insert into t_user_login (log_date, user_id) values
    ('2024-01-01','a'),
    ('2024-01-02','a'),
    ('2024-01-02','b'),
    ('2024-01-03','b'),
    ('2024-01-04','c'),
    ('2024-01-05','b'),
    ('2024-01-05','c'),
    ('2024-01-05','d'),
    ('2024-01-05','e'),
    ('2024-01-06','f');

参考资料

滴滴大数据面试SQL-截止目前登陆用户数及登陆用户列表

原创文章，转载请注明出处：http://www.opcoder.cn/article/75/