HiveSQL实战——大厂面试真题

一、字节跳动

最高峰同时直播人数

https://blog.csdn.net/SHWAITME/article/details/135918264
0 问题描述

   有如下数据记录直播平台主播上播及下播时间，根据该数据盘算出平台最高峰同时直播人数。

1. +----------+----------------------+----------------------+
2. | user_id  |      start_time      |       end_time       |
3. +----------+----------------------+----------------------+
4. | 1        | 2024-04-29 01:00:00  | 2024-04-29 02:01:05  |
5. | 2        | 2024-04-29 01:05:00  | 2024-04-29 02:03:18  |
6. | 3        | 2024-04-29 02:00:00  | 2024-04-29 04:03:22  |
7. | 4        | 2024-04-29 03:15:07  | 2024-04-29 04:33:21  |
8. | 5        | 2024-04-29 03:34:16  | 2024-04-29 06:10:45  |
9. | 6        | 2024-04-29 05:22:00  | 2024-04-29 07:01:08  |
10. | 7        | 2024-04-29 06:11:03  | 2024-04-29 09:26:05  |
11. | 3        | 2024-04-29 08:00:00  | 2024-04-29 12:34:27  |
12. | 1        | 2024-04-29 11:00:00  | 2024-04-29 16:03:18  |
13. | 8        | 2024-04-29 15:00:00  | 2024-04-29 17:01:05  |
14. +----------+----------------------+----------------------+

复制代码

1 数据准备

1. CREATE TABLE IF NOT EXISTS t1_livestream_log (
2.     user_id INT, -- 主播ID
3.     start_time STRING, -- 开始时间
4.     end_time STRING -- 结束时间
5. );
8. insert overwrite table  t1_livestream_log values
9. (1,'2024-04-29 01:00:00','2024-04-29 02:01:05'),
10. (2,'2024-04-29 01:05:00','2024-04-29 02:03:18'),
11. (3,'2024-04-29 02:00:00','2024-04-29 04:03:22'),
12. (4,'2024-04-29 03:15:07','2024-04-29 04:33:21'),
13. (5,'2024-04-29 03:34:16','2024-04-29 06:10:45'),
14. (6,'2024-04-29 05:22:00','2024-04-29 07:01:08'),
15. (7,'2024-04-29 06:11:03','2024-04-29 09:26:05'),
16. (3,'2024-04-29 08:00:00','2024-04-29 12:34:27'),
17. (1,'2024-04-29 11:00:00','2024-04-29 16:03:18'),
18. (8,'2024-04-29 15:00:00','2024-04-29 17:01:05');

复制代码

2 数据分析

1. with t1 as(
2.         select
3.             user_id,
4.             start_time as action_time,
5.             --开播记录，标记 1
6.             1 as change_cnt
7.         from t1_livestream_log
8.         union all
9.         select
10.             user_id,
11.             end_time as action_time,
12.              --下播记录，标记 1
13.             -1 as change_cnt
14.         from t1_livestream_log
15.     )
16. select
17.      max(online_cnt) as max_online_cnt
18. from
19.    (select
20.       user_id,
21.       action_time,
22.       change_cnt,
23.       sum(change_cnt)over(order by action_time asc) as online_cnt
24.    from t1)t2;

复制代码

思路分析：

• step1: 首先对原始数据进行处置惩罚，生成主播上下播的日志数据，同时增长人数变化字段，主播上播为1，主播下播-1;
  
• step2:对操纵日志按照操纵时间进行累积求和
  
• step3:求取累计求和中的最大值，即为当天最高峰同时直播人数
  

3 小结

    该题的关键点在于：对每个用户进入/退出直播间的行为进行打标签，再利用sum()over  + max聚合函数盘算终极的数值。
==========================*****==========================
股票波峰波谷

https://blog.csdn.net/SHWAITME/article/details/135902998
0 问题描述

    有如下数据，记录天天每只股票的收盘价格，请盘算 每只股票的波峰和波谷的日期和价格；

1. 波峰：当天的股票价格大于前一天和后一天
2. 波谷：当天的股票价格小于前一天和后一天

复制代码

1 数据准备

1. create table t3_stock_test(
2. ts_code string comment '股票代码',
3. trade_date string comment '交易日期',
4. close float comment '收盘价'
5. );
7. insert overwrite table `t3_stock_test` values
8. ('000001.SZ','20220104',16.66),
9. ('000002.SZ','20220104',20.49),
10. ('000001.SZ','20220105',17.15),
11. ('000002.SZ','20220105',21.17),
12. ('000001.SZ','20220106',17.12),
13. ('000002.SZ','20220106',21.05),
14. ('000001.SZ','20220107',17.2),
15. ('000002.SZ','20220107',21.89),
16. ('000001.SZ','20220110',17.19),
17. ('000002.SZ','20220110',22.16),
18. ('000001.SZ','20220111',17.41),
19. ('000002.SZ','20220111',22.3),
20. ('000001.SZ','20220112',17),
21. ('000002.SZ','20220112',22.05),
22. ('000001.SZ','20220113',16.98),
23. ('000002.SZ','20220113',21.53),
24. ('000001.SZ','20220114',16.33),
25. ('000002.SZ','20220114',20.7),
26. ('000001.SZ','20220117',16.22),
27. ('000002.SZ','20220117',20.87);

复制代码

2 数据分析

      利用lag函数和lead函数，对每支股票分组，开窗盘算出天天股票记录的前一天和后一天记录中的价格。

1. select
2.     ts_code,
3.     trade_date,
4.     close,
5.     point_type
6. from
7.     (
8.     select
9.         ts_code,
10.         trade_date,
11.         close,
12.         lastday_close,
13.         nextday_close,
14.         case when  close > lastday_close and close > nextday_close then '波峰'
15.             when  close < lastday_close and close < nextday_close then '波谷'
16.             else '其他' end as `point_type`
17.     from
18.         (
19.         select
20.             ts_code,
21.             trade_date,
22.             close,
23.             lag(close,1)over(partition by ts_code order by trade_date ) as lastday_close,
24.             lead(close,1)over(partition by ts_code order by trade_date ) as nextday_close
25.         from t3_stock_test
26.         ) t1
27.     ) t2
28. where t2.point_type in('波峰','波谷')

复制代码

3 小结

   lead和lag函数一般用于盘算当前行与上一行，大概当前行与下一行之间的差值。

1. -- 用于统计窗口内往上第n行。参数1为列名，参数2为往上第n行（可选，默认为1），参数3为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
3. lag(col,n,DEFAULT) over(partition by  xx order by xx)
5. -- 用于统计窗口内往下第n行。参数1为列名，参数2为往下第n行（可选，默认为1），参数3为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
7. lead(col,n,DEFAULT) over(partition by  xx order by xx)

复制代码

==========================*****==========================
归并日期重叠的活动

0 问题描述

1. 已知有表记录了每个大厅的活动开始日期和结束日期，每个大厅可以有多个活动。请编写一个SQL查询合并在同一个大厅举行的所有重叠的活动，如果两个活动至少有一天相同，那他们就是重叠的
3. 样例数据
4. +----------+-------------+-------------+
5. | hall_id  | start_date  |  end_date   |
6. +----------+-------------+-------------+
7. | 1        | 2023-01-13  | 2023-01-20  |
8. | 1        | 2023-01-14  | 2023-01-17  |
9. | 1        | 2023-01-14  | 2023-01-16  |
10. | 1        | 2023-01-18  | 2023-01-25  |
11. | 1        | 2023-01-20  | 2023-01-26  |
12. | 2        | 2022-12-09  | 2022-12-23  |
13. | 2        | 2022-12-13  | 2022-12-17  |
14. | 2        | 2022-12-20  | 2022-12-24  |
15. | 2        | 2022-12-25  | 2022-12-30  |
16. | 3        | 2022-12-01  | 2023-01-30  |
17. +----------+-------------+-------------+
19. 结果
20. +----------+-------------+-------------+
21. | hall_id  | start_date  |  end_date   |
22. +----------+-------------+-------------+
23. | 1        | 2023-01-13  | 2023-01-26  |
24. | 2        | 2022-12-09  | 2022-12-24  |
25. | 2        | 2022-12-25  | 2022-12-30  |
26. | 3        | 2022-12-01  | 2023-01-30  |
27. +----------+-------------+-------------+
28. 解释：
29.   大厅 1:
30.      两个活动["2823-01-13"，"2023-01-20"]和[“2023-01-14"，"2023-01-17"]重叠，需求将它们合并到一个活动中[“2023-01-13"，"2023-01-20"]。

复制代码

1 数据准备

1. CREATE TABLE IF NOT EXISTS t4_hall_event (
2.     hall_id STRING, --大厅ID
3.     start_date STRING, -- 营销活动开始日期
4.     end_date STRING -- 营销活动结束日期
5. );
6. --数据插入
7. insert overwrite table  t4_hall_event values
8. ('1','2023-01-13','2023-01-20'),
9. ('1','2023-01-14','2023-01-17'),
10. ('1','2023-01-14','2023-01-16'),
11. ('1','2023-01-18','2023-01-25'),
12. ('1','2023-01-20','2023-01-26'),
13. ('2','2022-12-09','2022-12-23'),
14. ('2','2022-12-13','2022-12-17'),
15. ('2','2022-12-20','2022-12-24'),
16. ('2','2022-12-25','2022-12-30'),
17. ('3','2022-12-01','2023-01-30');

复制代码

2 数据分析

1. select
2.        hall_id,
3.        min(start_date) as start_date,
4.        max(end_date)   as end_date
5. from (select
6.              hall_id,
7.              start_date,
8.              end_date,
9.              max_end_date,
10.              is_merge,
11.              sum(is_merge) over (partition by hall_id order by start_date  ) as group_id
12.       from (select
13.                    hall_id,
14.                    start_date,
15.                    end_date,
16.                    max_end_date,
17.                    if(start_date <= max_end_date, 0, 1) as is_merge
18.             --0:日期重叠，需要合并，1:日期没重叠，不用合并
19.             from (select
20.                          hall_id,
21.                          start_date,
22.                          end_date,
23. -- step1: 1.使用max()函数开窗，获得截止到当前行之前的活动最后日期
24.                          max(end_date)
25.                              over (partition by hall_id order by start_date   rows between unbounded preceding and 1 preceding) as max_end_date
26.                   from t4_hall_event) t1
27.            ) t2
28.      ) t3
29. group by hall_id, group_id --注意这里的分组，有group_id

复制代码

思路分析： 

• step1: 利用max()函数开窗，获得制止到当前行之前的活动末了日期
  
• step2:  对当前行的start_date 和制止到上一行的最大end_date进行比较，如果当前行的start_date 小于等于制止到前一行最大end_date 代表有交叉，可以归并，否则代表不可归并。 if(start_date <= max_end_date, 0, 1) as is_merge
  
• step3：一连问题，利用sum()over()进行分组
  
• step4：对hall_id+group_id分组，取每个组内的start_day 的最小值作为活动开始日期，end_day的最大值作为活动结束日期，得到终极结果。
   
  

 题意转换： 统计每个大厅开展的营销活动总天数（日期有重叠的地方需要去重）
HiveSQL题——炸裂函数(explode/posexplode)_hive explode-CSDN博客

1. select
2.        hall_id,
3.        sum( datediff(end_date,new_start_date)+ 1) as day_cnt
4. from (select hall_id,
5.              start_date,
6.              end_date,
7.              max_end_date,
8.              new_start_date
9.       from (select hall_id,
10.                    start_date,
11.                    end_date,
12.                    max_end_date,
13.             if(max_end_date is null, start_date,
14.                          if(start_date > max_end_date, start_date, date_add(max_end_date, 1))) new_start_date
15.             from (select hall_id,
16.                          start_date,
17.                          end_date,
18.                          max(end_date)
19.                              over (partition by hall_id order by
20.      start_date ,end_date  rows between unbounded preceding and 1 preceding) as max_end_date
21.                   from t4_hall_event) t1) t2
22.      where new_start_date <= end_date) t3
23. group by hall_id;

复制代码

3 小结

    处置惩罚的关键思路：当营销活动的上一个日期区间A 与 当前活动的日期区间B出现重叠（日期交叉，有重复数据）时，需要将区间B的起始时间改成区间A的结束时间。（注意：修改之后需要保证B区间的结束时间> 开始时间）。
==========================*****==========================
查询迩来一笔有用订单

0 问题描述

1. 现有订单表t5_order，包含订单ID，订单时间,下单用户，当前订单是否有效
3. +---------+----------------------+----------+-----------+
4. | ord_id  |       ord_time       | user_id  | is_valid  |
5. +---------+----------------------+----------+-----------+
6. | 1       | 2023-12-11 12:01:03  | a        | 1         |
7. | 2       | 2023-12-11 12:02:06  | a        | 0         |
8. | 3       | 2023-12-11 12:03:15  | a        | 0         |
9. | 4       | 2023-12-11 12:04:20  | a        | 1         |
10. | 5       | 2023-12-11 12:05:03  | a        | 1         |
11. | 6       | 2023-12-11 12:01:02  | b        | 1         |
12. | 7       | 2023-12-11 12:03:03  | b        | 0         |
13. | 8       | 2023-12-11 12:04:01  | b        | 1         |
14. | 9       | 2023-12-11 12:07:03  | b        | 1         |
15. +---------+----------------------+----------+-----------+
17. 请查询出每笔订单的上一笔有效订单,期望查询结果如下：
19. +---------+----------------------+----------+-----------+--------------------+
20. | ord_id  |       ord_time       | user_id  | is_valid  | last_valid_ord_id  |
21. +---------+----------------------+----------+-----------+--------------------+
22. | 1       | 2023-12-11 12:01:03  | a        | 1         | NULL               |
23. | 2       | 2023-12-11 12:02:06  | a        | 0         | 1                  |
24. | 3       | 2023-12-11 12:03:15  | a        | 0         | 1                  |
25. | 4       | 2023-12-11 12:04:20  | a        | 1         | 1                  |
26. | 5       | 2023-12-11 12:05:03  | a        | 1         | 4                  |
27. | 6       | 2023-12-11 12:01:02  | b        | 1         | NULL               |
28. | 7       | 2023-12-11 12:03:03  | b        | 0         | 6                  |
29. | 8       | 2023-12-11 12:04:01  | b        | 1         | 6                  |
30. | 9       | 2023-12-11 12:07:03  | b        | 1         | 8                  |
31. +---------+----------------------+----------+-----------+--------------------+

复制代码

1 数据准备

1. create table t5_order
2. (
3. ord_id bigint COMMENT '订单ID',
4. ord_time string COMMENT '订单时间',
5. user_id string COMMENT '用户',
6. is_valid bigint COMMENT '订单是否有效'
7. );
8. -- 数据插入
9. insert overwrite table t5_order values
10.     (1,'2023-12-11 12:01:03','a',1),
11.     (2,'2023-12-11 12:02:06','a',0),
12.     (3,'2023-12-11 12:03:15','a',0),
13.     (4,'2023-12-11 12:04:20','a',1),
14.     (5,'2023-12-11 12:05:03','a',1),
15.     (6,'2023-12-11 12:01:02','b',1),
16.     (7,'2023-12-11 12:03:03','b',0),
17.     (8,'2023-12-11 12:04:01','b',1),
18.     (9,'2023-12-11 12:07:03','b',1);

复制代码

2 数据分析

1. with tmp as
2.    (select
3.       ord_id,
4.          ord_time,
5.          user_id,
6.          is_valid,
7.         lag (ord_id,1,null) over(partition by user_id order by ord_time) last_valid_ord_id
8.     from t5_order
9.     where is_valid = 1)
11. select
12.      ord_id,
13.      ord_time,
14.      user_id,
15.      is_valid,
16.      last_valid_ord_id
17. from
18.    (
19.    select
20.         t5.*,
21.         tmp.last_valid_ord_id,
22.         row_number()over(partition by t5.ord_id,t5.user_id  order by tmp.ord_time ) rn
23.    from t5_order t5
24.    left join tmp
25.    on   t5.user_id = tmp.user_id
26.    where tmp.ord_time >= t5.ord_time
27.    )t6
28. where rn = 1;

复制代码

思路分析： 

• step1: 查询出有用订单，以及 每笔有用订单的上一单有用订单
  
• step2:  原始的明细数据与step1步的有用订单表按照用户user_id进行关联，筛选条件：有用订单表的订单时间大于等于原始订单表
  
• step3：利用row_number，对原始订单记录表的user_id、ord_id进行分组，对有用订单表的时间排序
  
• step4：筛选rn=1 的记录
  

3 小结

略
==========================*****==========================
共同利用ip用户检测

0 问题描述

1. 现有用户登录日志表，记录了每个用户登录的IP地址，请查询共同使用过3个及以上IP的用户对；
2. +----------+-----------------+----------------------+
3. | user_id  |       ip        |      time_stamp      |
4. +----------+-----------------+----------------------+
5. | 2        | 223.104.41.101  | 2023-08-24 07:00:00  |
6. | 4        | 223.104.41.122  | 2023-08-24 10:00:00  |
7. | 5        | 223.104.41.126  | 2023-08-24 11:00:00  |
8. | 4        | 223.104.41.126  | 2023-08-24 13:00:00  |
9. | 1        | 223.104.41.101  | 2023-08-24 16:00:00  |
10. | 3        | 223.104.41.101  | 2023-08-24 16:02:00  |
11. | 2        | 223.104.41.104  | 2023-08-24 16:30:00  |
12. | 1        | 223.104.41.121  | 2023-08-24 17:00:00  |
13. | 2        | 223.104.41.122  | 2023-08-24 17:05:00  |
14. | 3        | 223.104.41.103  | 2023-08-24 18:11:00  |
15. | 2        | 223.104.41.103  | 2023-08-24 19:00:00  |
16. | 1        | 223.104.41.104  | 2023-08-24 19:00:00  |
17. | 3        | 223.104.41.122  | 2023-08-24 19:07:00  |
18. | 1        | 223.104.41.122  | 2023-08-24 21:00:00  |
19. +----------+-----------------+----------------------+

复制代码

1 数据准备

1. CREATE TABLE t6_login_log (
2.    user_id bigint COMMENT '用户ID',
3.    ip string COMMENT '用户登录ip地址',
4.    time_stamp string COMMENT '登录时间'
5. ) COMMENT '用户登录记录表';
7. -- 插入数据
8. insert overwrite table t6_login_log
9. values
10.    (1,'223.104.41.101','2023-08-24 16:00:00'),
11.    (1,'223.104.41.121','2023-08-24 17:00:00'),
12.    (1,'223.104.41.104','2023-08-24 19:00:00'),
13.    (1,'223.104.41.122','2023-08-24 21:00:00'),
14.    (1,'223.104.41.122','2023-08-24 22:00:00'),
15.    (2,'223.104.41.101','2023-08-24 07:00:00'),
16.    (2,'223.104.41.103','2023-08-24 19:00:00'),
17.    (2,'223.104.41.104','2023-08-24 16:30:00'),
18.    (2,'223.104.41.122','2023-08-24 17:05:00'),
19.    (3,'223.104.41.103','2023-08-24 18:11:00'),
20.    (3,'223.104.41.122','2023-08-24 19:07:00'),
21.    (3,'223.104.41.101','2023-08-24 16:02:00'),
22.    (4,'223.104.41.126','2023-08-24 13:00:00'),
23.    (5,'223.104.41.126','2023-08-24 11:00:00'),
24.    (4,'223.104.41.122','2023-08-24 10:00:00');

复制代码

2 数据分析

1. -- step1: 针对用户登录记录，按照用户ID和登录IP去重
2. with tmp as
3. (
4.     select
5.          user_id,
6.          ip
7.     from t6_login_log
8.     group by user_id,ip
9.     )
11. select
12.      t2.user_id,
13.      t1.user_id
14. from tmp t1
15. join tmp t2
16. -- 自关联，会导致使用相同IP的用户，出现1-2、2-1、1-1、2-2记录（1,2 代表user_id），显然只需要保留 1-2的记录（2-1跟1-2意思一样，保留一个即可；但是1-1，2-2直接过滤掉）
17. on t1.ip = t2.ip
18. -- step2:  通过IP地址进行自关联，去重，剔除相同用户
19. where t2.user_id >  t1.user_id
20. group by t2.user_id,t1.user_id
21. having count(t1.ip) >= 3;

复制代码

思路分析： 

• step1: 针对用户登录记录，按照用户ID和登录IP去重
  
• step2:  通过IP地址进行自关联，去重，剔除雷同用户
  
• step3：group by + having : 得到共同利用过3个以上IP的用户对
  

3 小结

略
==========================*****==========================
二、百度

归并用户欣赏行为

0 问题描述

1. 有一份用户访问记录表，记录用户id和访问时间，如果用户访问时间间隔小于60s则认为时一次浏览，请合并用户的浏览行为。
3. +----------+--------------+
4. | user_id  | access_time  |
5. +----------+--------------+
6. | 1        | 1736337600   |
7. | 1        | 1736337660   |
8. | 2        | 1736337670   |
9. | 1        | 1736337710   |
10. | 3        | 1736337715   |
11. | 2        | 1736337750   |
12. | 1        | 1736337760   |
13. | 3        | 1736337820   |
14. | 2        | 1736337850   |
15. | 1        | 1736337910   |
16. +----------+--------------+

复制代码

1 数据准备

1. CREATE TABLE t2_user_access_log (
2.   user_id bigint COMMENT '用户ID',
3.   access_time bigint COMMENT '访问时间'
4. ) COMMENT '用户访问记录表';
6. --插入数据
7. insert overwrite table t2_user_access_log
8. values
9.     (1,1736337600),
10.     (1,1736337660),
11.     (2,1736337670),
12.     (1,1736337710),
13.     (3,1736337715),
14.     (2,1736337750),
15.     (1,1736337760),
16.     (3,1736337820),
17.     (2,1736337850),
18.     (1,1736337910);

复制代码

2 数据分析

1. select
2.         user_id,
3.         access_time,
4.         last_access_time,
5.         sum(is_new_group)over(partition by user_id order by access_time) as group_id
6. from
7. (
8.    select
9.         user_id,
10.         access_time,
11.         last_access_time,
12.         -- 1736337710 (时间戳 10位，单位是秒)
13.         if(access_time - last_access_time >= 60,1,0)  as is_new_group
14.     from
15.    (
16.      select
17.          user_id,
18.           access_time,
19.           lag(access_time,1,null) over(partition by user_id order by access_time) as last_access_time
20.      from t2_user_access_log
21.    )t1
22. )t2

复制代码

思路分析：

• step1: 借助lag开窗函数，对每个用户分区，对访问时间排序，盘算出时间差（用户访问时间间隔）
  
• step2:   对访问时间间隔进行判断，如果大于等于60秒，则是新的访问
  
• step3：sum() over (partition by order by )累加盘算,得到归并结果
  

3 小结

  对上下两行数据进行比较，一般采用lag 、 lead这种开窗函数。
==========================*****==========================
一连签到领金币

0 问题描述

1. 有用户签到记录表，t4_coin_signin,记录用户当天是否完成签到,请计算出每个用户的每个月获得的金币数量；
2. 签到领金币规则如下：
4. （1）用户签到获得1金币；
5. （2）如果用户连续签到3天则第三天获得2金币，如果用户连续签到7天则第7天获得5金币；
6. （3）连续签到7天后连续天数重置，每月签到天数重置；
9. 样例数据
11. +----------+--------------+----------+
12. | user_id  | signin_date  | is_sign  |
13. +----------+--------------+----------+
14. | 001      | 2024-01-01   | 1        |
15. | 001      | 2024-01-02   | 1        |
16. | 001      | 2024-01-03   | 1        |
17. | 001      | 2024-01-04   | 0        |
18. | 001      | 2024-01-05   | 1        |
19. | 001      | 2024-01-06   | 1        |
20. | 001      | 2024-01-07   | 1        |
21. | 001      | 2024-01-08   | 1        |
22. | 001      | 2024-01-09   | 1        |
23. | 001      | 2024-01-10   | 1        |
24. | 001      | 2024-01-11   | 1        |
25. | 001      | 2024-01-12   | 1        |
26. | 001      | 2024-01-13   | 1        |
27. | 001      | 2024-01-14   | 1        |
28. | 001      | 2024-01-15   | 1        |
29. | 001      | 2024-01-16   | 1        |
30. | 001      | 2024-01-17   | 1        |
31. | 001      | 2024-01-18   | 1        |
32. | 001      | 2024-01-19   | 1        |
33. | 001      | 2024-01-20   | 0        |
34. | 001      | 2024-01-21   | 1        |
35. | 001      | 2024-01-22   | 1        |
36. | 001      | 2024-01-23   | 1        |
37. | 001      | 2024-01-24   | 0        |
38. | 001      | 2024-01-25   | 1        |
39. | 001      | 2024-01-26   | 1        |
40. | 001      | 2024-01-27   | 1        |
41. | 001      | 2024-01-28   | 1        |
42. | 001      | 2024-01-29   | 0        |
43. | 001      | 2024-01-30   | 1        |
44. | 001      | 2024-01-31   | 1        |
45. | 001      | 2024-02-01   | 1        |
46. | 001      | 2024-02-02   | 1        |
47. | 001      | 2024-02-03   | 1        |
48. | 001      | 2024-02-04   | 1        |
49. | 001      | 2024-02-05   | 1        |
50. | 001      | 2024-02-06   | 1        |
51. | 001      | 2024-02-07   | 1        |
52. | 001      | 2024-02-08   | 1        |
53. | 001      | 2024-02-09   | 1        |
54. | 001      | 2024-02-10   | 1        |
55. +----------+--------------+----------+

复制代码

1 数据准备

1. CREATE TABLE t4_coin_signin
2. (
3.     user_id     string COMMENT '用户ID',
4.     signin_date string COMMENT '日期',
5.     is_sign     bigint COMMENT '是否签到 1-签到，0-未签到'
6. ) COMMENT '签到领金币记录表';
8.      -- 插入数据
9. insert overwrite tablet4_coin_signin
10. values ('001', '2024-01-01', 1),
11.        ('001', '2024-01-02', 1),
12.        ('001', '2024-01-03', 1),
13.        ('001', '2024-01-04', 0),
14.        ('001', '2024-01-05', 1),
15.        ('001', '2024-01-06', 1),
16.        ('001', '2024-01-07', 1),
17.        ('001', '2024-01-08', 1),
18.        ('001', '2024-01-09', 1),
19.        ('001', '2024-01-10', 1),
20.        ('001', '2024-01-11', 1),
21.        ('001', '2024-01-12', 1),
22.        ('001', '2024-01-13', 1),
23.        ('001', '2024-01-14', 1),
24.        ('001', '2024-01-15', 1),
25.        ('001', '2024-01-16', 1),
26.        ('001', '2024-01-17', 1),
27.        ('001', '2024-01-18', 1),
28.        ('001', '2024-01-19', 1),
29.        ('001', '2024-01-20', 0),
30.        ('001', '2024-01-21', 1),
31.        ('001', '2024-01-22', 1),
32.        ('001', '2024-01-23', 1),
33.        ('001', '2024-01-24', 0),
34.        ('001', '2024-01-25', 1),
35.        ('001', '2024-01-26', 1),
36.        ('001', '2024-01-27', 1),
37.        ('001', '2024-01-28', 1),
38.        ('001', '2024-01-29', 0),
39.        ('001', '2024-01-30', 1),
40.        ('001', '2024-01-31', 1),
41.        ('001', '2024-02-01', 1),
42.        ('001', '2024-02-02', 1),
43.        ('001', '2024-02-03', 1),
44.        ('001', '2024-02-04', 1),
45.        ('001', '2024-02-05', 1),
46.        ('001', '2024-02-06', 1),
47.        ('001', '2024-02-07', 1),
48.        ('001', '2024-02-08', 1),
49.        ('001', '2024-02-09', 1),
50.        ('001', '2024-02-10', 1);

复制代码

2 数据分析

1. select
2.      user_id,
3.      signin_month,
4.      sum(coin_num) as month_coin_num
5. from
6. (
7.    select
8.            user_id,
9.            signin_month,
10.            signin_group,
11.            conn_sign_days,
12.            new2_conn_sign_days,
13.            -- case when,根据new2_conn_sign_days中签到第几天，得出每天应该得到多少金币。 计算每天得到的金币数量
14.            case when new2_conn_sign_days = 3 then 2
15.                 when conn_sign_days = 7 then 5
16.                    else 1 end as coin_num
17.     from
18.    (
19.       select
20.            user_id,
21.            signin_month,
22.            signin_group,
23.            conn_sign_days,
24.            -- mod取模函数，处理7天重置问题,得到参与活动的实际连续第几天签到；
25.            mod(conn_sign_days,7) as new1_conn_sign_days,
27. -- 使用mod函数，对conn_sign_days进行处理，每7天重置一次，其中0代表第7天，需要特殊处理一下。
28.            if(mod(conn_sign_days,7)  = 0,7,mod(conn_sign_days,7)) as new2_conn_sign_days
29.       from
30.       (
31.          select
32.                 user_id,
33.                 signin_date,
34.                 signin_month,
35.                 signin_group,
36.                 -- 根据按照用户、月份、signin_group 进行分组，按照日期排序，使用count(signin_date) 计算出:每个用户每月的，截止到当前的连续签到天数。
37.                 count(signin_date)over(partition by user_id,signin_month,signin_group  order by signin_date) as conn_sign_days
38.          from
39.           (
40.               select
41.                    user_id,
42.                    signin_date,
43.                    is_sign,
44.                    substr(signin_date,1,7)  as signin_month,
45.                    --  根据用户、月份进行分组,按照日期排序，得到一个用户连续签到的分组 signin_group
46.                    sum(if(is_sign = 1,0,1)) over(partition by user_id,substr(signin_date,1,7) order by signin_date)  as signin_group
47.               from t4_coin_signin
48.           )t1
49.          -- 筛选出：用户签到状态的数据
50.          where is_sign = 1
51.        )t2
52.     )t3
53. )t4
54. group by user_id,
55.           signin_month;

复制代码

思路分析：

• step1: 判断用户是否一连签到：根据用户、月份进行分组,按照日期排序，得到一个用户一连签到的分组 
  
• step2: 根据按照用户、月份、signin_group 进行分组，按照日期排序，利用count(signin_date) 盘算出:每个用户每月的，制止到当前的一连签到天数。（用户当月实际是第几天一连签到，需要注意where的筛选条件：用户是签到状态）
  
• step3：一连签到7天后一连天数重置。利用mod函数，对conn_sign_days进行处置惩罚，每7天重置一次，此中0代表第7天，需要特殊处置惩罚一下。
  
• step4：case when,根据new2_conn_sign_days中签到第几天，得出天天应该得到多少金币。 盘算出每个用户天天得到的金币数量。
  
• step5：分组聚合得到每个用户每月得到的金币数
  

3 小结

 略
==========================*****==========================
三、阿里

用户行为轨迹

0 问题描述

1. 现有一份用户在各个地铁站进出的时间表和一份用户商场扫码行为表，其中商场扫码会存在多次，可以取最新的数据。请查询并返回出用户整个行为轨迹列表.
3. 样例数据
4. t_subway_station
6. +----------+-------------+----------------------+----------------------+
7. | user_id  | station_id  |       in_time        |       out_time       |
8. +----------+-------------+----------------------+----------------------+
9. | 001      | 1           | 2024-07-01 09:01:01  | NULL                 |
10. | 001      | 2           | NULL                 | 2024-07-01 09:21:08  |
11. | 001      | 3           | 2024-07-01 11:01:41  | NULL                 |
12. | 001      | 4           | NULL                 | 2024-07-01 11:23:12  |
13. | 001      | 4           | 2024-07-01 15:33:29  | NULL                 |
14. | 001      | 1           | NULL                 | 2024-07-01 15:45:41  |
15. +----------+-------------+----------------------+----------------------+
18. t_market
19. +----------+------------+----------------------+
20. | user_id  | market_id  |      check_time      |
21. +----------+------------+----------------------+
22. | 001      | 1001       | 2024-07-01 10:23:04  |
23. | 001      | 1001       | 2024-07-01 10:25:38  |
24. | 001      | 1002       | 2024-07-01 10:45:01  |
25. | 001      | 1004       | 2024-07-01 13:56:27  |
26. | 001      | 1003       | 2024-07-01 14:37:24  |
27. +----------+------------+----------------------+
29. 期望结果
30. +----------+----------------------------------+
31. | user_id  |            place_list            |
32. +----------+----------------------------------+
33. | 001      | 1,2,1001,1002,3,4,1004,1003,4,1  |
34. +----------+----------------------------------+

复制代码

1 数据准备

1. --建表语句
2. CREATE TABLE t1_subway_station (
3.   user_id string,
4.   station_id string,
5.   in_time string,
6.   out_time string
7. );
8. --插入数据
9. insert overwrite  table  t1_subway_station
10. values
11.    ('001','1','2024-07-01 09:01:01',null),
12.    ('001','2',null,'2024-07-01 09:21:08'),
13.    ('001','3','2024-07-01 11:01:41',null),
14.    ('001','4',null,'2024-07-01 11:23:12'),
15.    ('001','4','2024-07-01 15:33:29',null),
16.    ('001','1',null,'2024-07-01 15:45:41');
18. --建表语句
19. CREATE TABLE t1_market (
20.   user_id string,
21.   market_id string,
22.   check_time string
23. );
24. --插入数据
25. insert overwrite  table t1_market
26. values
27. ('001','1001','2024-07-01 10:23:04'),
28. ('001','1001','2024-07-01 10:25:38'),
29. ('001','1002','2024-07-01 10:45:01'),
30. ('001','1004','2024-07-01 13:56:27'),
31. ('001','1003','2024-07-01 14:37:24');

复制代码

2 数据分析

1. select
2.       user_id,
3.      -- 利用concat_ws函数拼接字符串,使用正则表达式regexp_replace将结果时间进行剔除，得到最终结果。
4.      --  \d表示匹配数字字符,因此，\d{4}可以匹配任意四个数字字符。
5.       regexp_replace(concat_ws(',',sort1_str) ,'\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}','') as place_list
6. from
7. (
8.     select
9.            user_id,
10.      -- 使用collect_list对con_str进行拼接，并使用sort_array进行排序，得到顺序的时间点位信息。
11.            sort_array(collect_list(concat(check_time,place))) as  sort1_str
12.     from
13.     (
14.         select
15.                user_id,
16.                station_id                  as place,
17.                coalesce(in_time, out_time) as check_time
18.         from t1_subway_station
19.         group by  user_id,station_id,coalesce(in_time, out_time)
20.         union all
21.         --商场时间
22.         select
23.                user_id,
24.                place,
25.                check_time
26.         from (
27.                  select user_id,
28.                         market_id       as place,
29.         -- 根据题目要求，取出每个商场最新的时间即可。
30.                         max(check_time) as check_time
31.                  from t1_market
32.                  group by user_id, market_id) t
33.     )t1
34.     group by user_id
35. )t2

复制代码

思路分析：

• step1: 地铁站进出数据和商场数据进行union all操纵。
  
• step2：需要根据时间保证结果有序，所以需要先对check_time 与place进行拼接。利用concat进行拼接
  
• step3：利用collect_list对con_str进行拼接，并利用sort_array进行排序，得到顺序的时间点位信息。
  
• step4：利用正则表达式将结果时间进行剔除，得到终极结果。
  

3 小结

HiveSQL题——collect_set()/collect_list()聚合函数_collect set-CSDN博客
上述用到的Hive SQL函数：
(1) collect_set()、collect_list()
     collect_set()函数与collect_list()函数属于高级聚合函数（行转列），将分组中的某列转换成一个数组返回，常与concat_ws()函数连用实现字段拼接效果。

• collect_list：收集并形成list聚集，结果不去重
  
• collect_set：收集并形成set聚集，结果去重
  

(2) sort_array : 数组排序函数

•  语法：sort_array(array, [asc|desc]) ： 按照指定的排序规则对数组进行排序，并返回一个排好序的新数组。  第一个参数：array为需要排序的数组；第二个参数：asc为可选参数，如果设置为true则按升序排序；desc为可选参数，如果设置为true，则按降序排序。如果既不设置asc也不设置desc，则按升序排序
  
•  举例：select sort_array(array(2, 5, 3, 1)) as sorted_array;  ====》  [1,2,3,5]
  

(3) concat_ws(带分隔符的字符串毗连函数)

• 语法：concat_ws(string SEP, string A ,string B.......)
  
• 返回值：string
  
• 说明：返回输入字符串毗连后的结果,SEP表现各个字符串的分隔符
  
• 举例：select  concat_ws('|','ad','cv','op') ;---> ad|cv|op 
  

(4) lpad / rpad：左/右补足函数

• 语法: lpad(string str, int len, string pad) 、rpad(string str, int len, string pad)
  
• 举例：select lpad('abc',7,'td'); ---> tdtdabc  ；  select rpad('abc',7,'td'); ---> abctdtd
  

==========================*****==========================
四、meta

盘算每个用户的受欢迎程度

0 问题描述

1. 有好友关系表t1_friend，记录了user1_id,user2_id的好友关系对。现定义用户受欢迎程度=用户拥有的朋友总数/平台上的用户总数,请计算出每个用户的受欢迎程度。
3. +-----------+-----------+
4. | user1_id  | user2_id  |
5. +-----------+-----------+
6. | 1         | 2         |
7. | 1         | 3         |
8. | 1         | 4         |
9. | 1         | 5         |
10. | 2         | 3         |
11. | 2         | 4         |
12. | 3         | 4         |
13. | 4         | 5         |
14. | 5         | 6         |
15. | 5         | 7         |
16. | 7         | 8         |
17. | 9         | 10        |
18. +-----------+-----------+

复制代码

1 数据准备

1. --建表语句
2. CREATE TABLE t1_friend(
3.   user1_id bigint COMMENT '用户1ID',
4.   user2_id bigint COMMENT '用户2ID'
5. ) COMMENT '好友关系表';
7. -- 插入数据
8. insert overwrite  table t1_friend
9. values
10.   (1,2),
11.   (1,3),
12.   (1,4),
13.   (1,5),
14.   (2,3),
15.   (2,4),
16.   (3,4),
17.   (4,5),
18.   (5,6),
19.   (5,7),
20.   (7,8),
21.   (9,10)

复制代码

2 数据分析

1. with tmp as
2.          (select
3.               user1_id as  user1_id,
4.               user2_id as  user2_id
5.          from t1_friend
6.          union all
7.          select
8.               user2_id as  user1_id,
9.               user1_id as  user2_id
10.          from t1_friend)
12. select
13.      user1_id,
14.      friend_cnt / total_cnt as res
15. from (
16.      select
17.             user1_id,
18.             count(user2_id) as friend_cnt,
19.             count( user1_id) over () as total_cnt
20.     from tmp
21.     group by user1_id
22.      )t1;

复制代码

思路分析：

• step1: user1_id,user2_id互换，然后进行union all。题中数据user1_id,user2_id为互为好友关系，形成 【关系对】，即1与2是好友关系，则1-2,2-1记录只会存在一条，为方便盘算，需要有两条记录。所以将user2_id与user1_id 互换，然后与原表进行union all；
  
• step2：按照user1_id分组，统计user2_id的个数，即user1_id 的好友数据；再利用开窗盘算出所有的用户数；
  
• step3：某个用户的好友数除以总用户数，盘算终极结果；
  

3 小结

  略
==========================*****==========================
五、腾讯

向用户保举好友喜欢的音乐

0 问题描述

1. 现有三张表分别为：
3. 用户关注表t1_follow(user_id,follower_id)记录用户ID及其关注的人ID
4. +----------+--------------+
5. | user_id  | follower_id  |
6. +----------+--------------+
7. | 1        | 2            |
8. | 1        | 4            |
9. | 1        | 5            |
10. +----------+--------------+
12. 用户喜欢的音乐t1_music_likes(user_id,music_id)
13. +----------+-----------+
14. | user_id  | music_id  |
15. +----------+-----------+
16. | 1        | 10        |
17. | 2        | 20        |
18. | 2        | 30        |
19. | 3        | 20        |
20. | 3        | 30        |
21. | 4        | 40        |
22. | 4        | 50        |
23. +----------+-----------+
25. 音乐名字表t1_music(music_id,music_name)
26. +-----------+-------------+
27. | music_id  | music_name  |
28. +-----------+-------------+
29. | 10        | a           |
30. | 20        | b           |
31. | 30        | c           |
32. | 40        | d           |
33. | 50        | e           |
34. +-----------+-------------+
36. 需求：给用户1推荐他关注的用户喜欢的音乐名称

复制代码

1 数据准备

1. --建表语句
2. CREATE TABLE t1_follow (
3.   user_id bigint COMMENT '用户ID',
4.   follower_id bigint COMMENT '其关注的用户ID'
5. ) COMMENT '用户关注表';
6.      
7. -- 插入数据
8. insert overwrite table  t1_follow
9. values
10.    (1,2),
11.    (1,4),
12.    (1,5);
14. -- 建表语句
15. CREATE TABLE t1_music_likes (
16.   user_id bigint COMMENT '用户ID',
17.   music_id bigint COMMENT '音乐ID'
18. ) COMMENT '用户喜欢音乐ID';
20. --插入语句
21. insert overwrite table t1_music_likes
22. values
23.   (1,10),
24.   (2,20),
25.   (2,30),
26.   (3,20),
27.   (3,30),
28.   (4,40),
29.   (4,50);
30.      
31.      
32. --建表语句
33. CREATE TABLE t1_music (
34.    music_id bigint COMMENT '音乐ID',
35.    music_name string COMMENT '音乐名称'
36. ) COMMENT '音乐名字表';
37.      
38. -- 插入语句
39. insert overwrite table t1_music
40. values
41.    (10,'a'),
42.    (20,'b'),
43.    (30,'c'),
44.    (40,'d'),
45.    (50,'e');

复制代码

2 数据分析

1. select
2.      t4.user_id,
3.      concat_ws(',', collect_set(t4.music_name)) as push_music
4. from
5.     (
6.         select
7.              t1.user_id,
8.              t1.follower_id,
9.              t2.music_id,
10.              t3.music_name
11.         from t1_follow t1
12.         left join t1_music_likes t2
13.          on t1.follower_id  = t2.user_id
14.          left join t1_music t3
15.         on  t2.music_id = t3.music_id
16.          where t1.user_id = 1
17.     )t4
18. group by t1.user_id;

复制代码

思路分析：

• step1: 根据用户关注表和用户喜欢的音乐表进行关联，查询出每个用户喜欢的音乐ID；再关联音乐名字表，关联出对应的音乐名称；
  
• step2：行转列并对重复的音乐名称去重，得到终极结果；行转列利用聚合函数collect_set()函数，然后利用concat_ws转成字符串
  

3 小结

略
==========================*****==========================
一连登岸超过N天的用户

0 问题描述

1. 现有用户登录日志表 t5_login_log,包含用户ID(user_id),登录日期(login_date)。
2. 数据已经按照用户日期去重，请计算 连续登录超过4天的用户ID。
4. 样例数据
5. +----------+-------------+
6. | user_id  | login_date  |
7. +----------+-------------+
8. | 0001     | 20220101    |
9. | 0001     | 20220102    |
10. | 0001     | 20220103    |
11. | 0001     | 20220104    |
12. | 0001     | 20220105    |
13. | 0001     | 20220107    |
14. | 0001     | 20220108    |
15. | 0001     | 20220109    |
16. | 0002     | 20220101    |
17. | 0002     | 20220102    |
18. | 0002     | 20220103    |
19. | 0002     | 20220107    |
20. | 0002     | 20220108    |
21. | 0003     | 20220107    |
22. | 0003     | 20220108    |
23. | 0003     | 20220109    |
24. +----------+-------------+

复制代码

1 数据准备

1. -- 建表语句
2. create table t5_login_log
3. (
4.    user_id string comment '用户ID',
5.    login_date string comment '登录日期'
6. );
8. --数据插入语句
9. insert overwrite table  t5_login_log values
10.    ('0001','20220101'),
11.    ('0001','20220102'),
12.    ('0001','20220103'),
13.    ('0001','20220104'),
14.    ('0001','20220105'),
15.    ('0001','20220107'),
16.    ('0001','20220108'),
17.    ('0001','20220109'),
18.    ('0002','20220101'),
19.    ('0002','20220102'),
20.    ('0002','20220103'),
21.    ('0002','20220107'),
22.    ('0002','20220108'),
23.    ('0003','20220107'),
24.    ('0003','20220108'),
25.    ('0003','20220109');

复制代码

2 数据分析

2. select
3.      user_id
4. from
5.   (
6.       select
7.            user_id,
8.            new_login_date,
9.            rn,
10.            date_sub(new_login_date,rn) as diff
11.       from
12.           (
13.             select
14.                 user_id,
15.                 login_date,
16.                 unix_timestamp(login_date, 'yyyyMMdd') as login_ts,
17.                 from_unixtime( unix_timestamp(login_date, 'yyyyMMdd'),'yyyy-MM-dd') as new_login_date,
18.                 row_number() over(partition by user_id order by login_date ) as rn
19.             from t5_login_log
20.           )t1
21.   )t2
22. group by  user_id,diff
23. having count(1) >=4;

复制代码

思路分析：

• step1: 处置惩罚日期格式；
  
• step2：row_number()开窗，盘算每个用户每个登录日期的排名；
  
• step3：盘算用户登录日期与排名的差值，差值一样的，代表一连登岸；
  
• step4：按照用户和差值分组，得出一连登录大于等于4天的用户；
  

3 小结

  上述代码用到的日期函数
(1) from_unixtime(时间戳转日期函数)

• 语法：from_unixtime(bigint unixtime , string format)
  
• 返回值：输入bigint的时间戳， 输出string格式化的时间
  
• 说明：转化 unix时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式，默认的format是yyyy-MM-dd HH:mm:ss，可以指定别的。
  
• 举例：select from_unixtime(1323308943,'yyyyMMdd')  --> 20111208 
  

(2) unix_timestamp(日期转 时间戳函数)

• 语法：unix_timestamp(string date)  、unix_timestamp(string date,string pattern)
  
• 返回值：bigint
  
• 说明：将格式为"yyyy-MM-dd HH:mm:ss"的日期 转换成 unix的时间戳。如果转换失败，则返回值为0；
  
• 注意：输入时间必须是到秒级的时间，否则转换失败返回NULL
  

==========================*****==========================
微信活动步数在好友中的排名

0 问题描述

1. 有两个表，朋友关系表t6_user_friend，用户步数表t6_user_steps。
2. 朋友关系表包含两个字段，用户id，用户好友的id；
3. 用户步数表包含两个字段，用户id，用户的步数
5. 求出：用户在好友中的排名。
7. 朋友关系表t6_user_friend
8. +----------+------------+
9. | user_id  | friend_id  |
10. +----------+------------+
11. | 1        | 2          |
12. | 1        | 3          |
13. | 2        | 1          |
14. | 2        | 3          |
15. | 2        | 4          |
16. | 2        | 5          |
17. | 3        | 1          |
18. | 3        | 4          |
19. | 3        | 5          |
20. | 4        | 2          |
21. | 4        | 3          |
22. | 4        | 5          |
23. | 5        | 2          |
24. | 5        | 3          |
25. | 5        | 4          |
26. +----------+------------+
28. 用户步数表t6_user_steps
29. +---------------------+-------------------+
30. | t6_user_steps.user_id  | t6_user_steps.steps  |
31. +---------------------+-------------------+
32. | 1                   | 100               |
33. | 2                   | 95                |
34. | 3                   | 90                |
35. | 4                   | 80                |
36. | 5                   | 10                |
37. +---------------------+-------------------+

复制代码

1 数据准备

2. create table t6_user_friend
3. (
4.     user_id   INT,
5.     friend_id INT
6. );
8. -- 插入数据
9. insert overwrite table  t6_user_friend
10. values (1, 2),
11.        (1, 3),
12.        (2, 1),
13.        (2, 3),
14.        (2, 4),
15.        (2, 5),
16.        (3, 1),
17.        (3, 4),
18.        (3, 5),
19.        (4, 2),
20.        (4, 3),
21.        (4, 5),
22.        (5, 2),
23.        (5, 3),
24.        (5, 4);
26. create table t6_user_steps
27. (
28.     user_id INT,
29.     steps   INT
30. );
32. insert overwrite table  t6_user_steps
33. values (1, 100),
34.        (2, 95),
35.        (3, 90),
36.        (4, 80),
37.        (5, 10);

复制代码

2 数据分析

1. select
2.      user_id,
3.      rn
4. from
5.     (
6.       select
7.             user_id,
8.             friend_id,
9.             steps,
10.             row_number() over (partition by user_id order by steps desc) as rn
11.      from
12.         (
13.             --好友步数
14.             select
15.                  t1.user_id,
16.                  t1.friend_id,
17.                  t2.steps
18.             from t6_user_friend t1
19.             join t6_user_steps t2
20.             on t1.friend_id = t2.user_id
21.             union all
22.             -- 自己步数
23.             select
24.                  user_id,
25.                  user_id as friend_id,
26.                  steps
27.            from t6_user_steps
28.        )tmp1
29.    )tmp2
30. where user_id = friend_id

复制代码

思路分析：

• step1: 需要求出 本身在好友中的排名，由于好友关系表中只有“好友”，所以需要加入本身的数据；
  
• step2：按照用户分组，给每个用户的“好友”进行排名；
  
• step3：筛选出本身名次的那一行数据；
  

3 小结

略
==========================*****==========================
六、高频大数据面试SQL

每年结果都有所提拔的学生

0 问题描述

1. 一张学生成绩表(student_scores)，有year-学年，subject-课程，student-学生，score-分数这四个字段，请完成如下问题：
3. 问题1：每年每门学科排名第一的学生
5. 问题2：每年总成绩都有所提升的学生
7. 数据内容如下
8. +-------+----------+----------+--------+
9. | year  | subject  | student  | score  |
10. +-------+----------+----------+--------+
11. | 2018  | 语文       | A        | 84     |
12. | 2018  | 数学       | A        | 59     |
13. | 2018  | 英语       | A        | 30     |
14. | 2018  | 语文       | B        | 44     |
15. | 2018  | 数学       | B        | 76     |
16. | 2018  | 英语       | B        | 68     |
17. | 2019  | 语文       | A        | 51     |
18. | 2019  | 数学       | A        | 94     |
19. | 2019  | 英语       | A        | 71     |
20. | 2019  | 语文       | B        | 87     |
21. | 2019  | 数学       | B        | 44     |
22. | 2019  | 英语       | B        | 38     |
23. | 2020  | 语文       | A        | 91     |
24. | 2020  | 数学       | A        | 50     |
25. | 2020  | 英语       | A        | 89     |
26. | 2020  | 语文       | B        | 81     |
27. | 2020  | 数学       | B        | 84     |
28. | 2020  | 英语       | B        | 98     |
29. +-------+----------+----------+--------+

复制代码

1 数据准备

1. CREATE TABLE t1_student_scores
2. (
3.     year STRING,
4.     subject STRING,
5.     student STRING,
6.     score INT );
7. -- 数据插入语句
8. insert overwrite  table t1_student_scores
9.       VALUES
10.     (2018, '语文', 'A', 84),
11.     (2018, '数学', 'A', 59),
12.     (2018, '英语', 'A', 30),
13.     (2018, '语文', 'B', 44),
14.     (2018, '数学', 'B', 76),
15.     (2018, '英语', 'B', 68),
16.     (2019, '语文', 'A', 51),
17.     (2019, '数学', 'A', 94),
18.     (2019, '英语', 'A', 71),
19.     (2019, '语文', 'B', 87),
20.     (2019, '数学', 'B', 44),
21.     (2019, '英语', 'B', 38),
22.     (2020, '语文', 'A', 91),
23.     (2020, '数学', 'A', 50),
24.     (2020, '英语', 'A', 89),
25.     (2020, '语文', 'B', 81),
26.     (2020, '数学', 'B', 84),
27.     (2020, '英语', 'B', 98);

复制代码

2 数据分析

   问题1：每年每门学科排名第一的学生

1. --计算排名第一的人
2. select year,
3.        subject,
4.        first_student
5. from (select year,
6.              subject,
7.              first_value(student) over (partition by year,subject order by score desc) as first_student
8.       from t1_student_scores) t
9. group by year, subject, first_student;

复制代码

思路分析：

• step1: 按照年份、学科分组，按照分数排序，盘算出雷同年份，雷同学科排名第一的人
  
• step2：去重，盘算出终极结果。
  

   问题2：每年总结果都有所提拔的学生

1. select
2.       student
3. from
4. (
5.   select
6.          year,
7.          student,
8.          total_score,
9.          last_year_score,
10.          if(total_score >last_year_score,1,0 ) as improve_flag
11.   
12.   from
13.   (
14.      select
15.           year,
16.           student,
17.           total_score,
18.           lag(total_score,1,0) over(partition by student order by year ) last_year_score
19.      from
20.      (
21.          select
22.               year,
23.               student,
24.               sum(score) as total_score
25.          from t1_student_scores
26.          group by year,student
27.      )t1
28.    )t2
29.    where last_year_score is not null
30. )t3
31.   group by student
32.    having count(1) = sum(improve_flag);

复制代码

思路分析：

• step1: 盘算每年每个学生的总结果；
  
• step2：团结lag开窗函数函数，在本行添加上一学年结果；
  
• step3：where last_year_score is not null，去掉lag()结果字段为空数据，并判断是否有进步
  
• step4：根据学生分组，having count(1) = sum(improve_flag) 来获取每年进步的学生；
  

3 小结

略
==========================*****==========================
一连点击三次用户（**）

0 问题描述

1. 有用户点击日志记录表 t2_click_log,  user_id(用户ID), click_time(点击时间)， 请查询出连续点击三次的用户数，连续点击三次：指点击记录中同一用户连续点击，中间无其他用户点击；
3. +----------+--------------+
4. | user_id  | click_time |
5. +----------+--------------+
6. | 1        | 1736337600   |
7. | 2        | 1736337670   |
8. | 1        | 1736337710   |
9. | 1        | 1736337715   |
10. | 1        | 1736337750   |
11. | 2        | 1736337760   |
12. | 3        | 1736337820   |
13. | 3        | 1736337840   |
14. | 3        | 1736337850   |
15. | 3        | 1736337910   |
16. | 4        | 1736337915   |
17. +----------+--------------+

复制代码

1 数据准备

1. --建表语句
2. CREATE TABLE t2_click_log (
3.   user_id BIGINT,
4.   click_time BIGINT
5. );
7. --插入数据
8. insert overwrite table t2_click_log
9. (1,1736337600),
10. (2,1736337670),
11. (1,1736337710),
12. (1,1736337715),
13. (1,1736337750),
14. (2,1736337760),
15. (3,1736337820),
16. (3,1736337840),
17. (3,1736337850),
18. (3,1736337910),
19. (4,1736337915)

复制代码

2 数据分析

方式一：双重排序差值法（一连N天登岸）

1. select
2.      count(distinct user_id) as num
3. from
4.   (
5.     select
6.          user_id,
7.          diff,
8.          count(1) as cnt
9.     from
10.     (
11.          select
12.              user_id,
13.              rn1 - rn2 as diff
14.          from (select user_id,
15.                      click_time,
16.                      row_number() over (order by click_time asc) as rn1,
17.                      row_number() over (partition by user_id order by click_time asc) as rn2
18.               from t2_click_log) t1
19.     )t2
20.          group by  user_id,diff
21.          having cnt >= 3
22.   )t3;

复制代码

思路分析：

• step1: 与一连登录天数类似。先按照点击时间(click_time) 进行全排序，再按照用户ID(user_id)分组，按照点击时间排序；
  
• step2：对两次排序盘算差值diff ，按照用户user_id 和差值diff 进行分组。对于雷同用户，差值一样代表 一连；
  
• step3：同一分组的数量>=3的user_id 则是一连点击三次用户
  

3 小结

略
==========================*****==========================
去掉最大最小值的部门均匀薪水

0 问题描述

1. 有员工薪资表t3_salary,包含员工ID(emp_id)，部门ID(depart_id)，薪水(salary),请计算去除最高最低薪资后的平均薪水；（每个部门员工数不少于3人）
2. +---------+------------+-----------+
3. | emp_id  | depart_id  |  salary   |
4. +---------+------------+-----------+
5. | 1001    | 1          | 5000.00   |
6. | 1002    | 1          | 10000.00  |
7. | 1003    | 1          | 20000.00  |
8. | 1004    | 1          | 30000.00  |
9. | 1005    | 1          | 6000.00   |
10. | 1006    | 1          | 10000.00  |
11. | 1007    | 1          | 11000.00  |
12. | 1008    | 2          | 3000.00   |
13. | 1009    | 2          | 7000.00   |
14. | 1010    | 2          | 9000.00   |
15. | 1011    | 2          | 30000.00  |
16. +---------+------------+-----------+

复制代码

1 数据准备

1. CREATE TABLE t3_salary (
2.   emp_id bigint,
3.   depart_id bigint,
4.   salary decimal(16,2)
5. );
6. --插入数据
7. insert overwrite table t3_salary
8. values
9.     (1001,1,5000.00),
10.     (1002,1,10000.00),
11.     (1003,1,20000.00),
12.     (1004,1,30000.00),
13.     (1005,1,6000.00),
14.     (1006,1,10000.00),
15.     (1007,1,11000.00),
16.     (1008,2,3000.00),
17.     (1009,2,7000.00),
18.     (1010,2,9000.00),
19.     (1011,2,30000.00);

复制代码

2 数据分析

1. select
2.      depart_id ,
3.      avg(salary)  as avg_salary
4. from
5.   (
6.     select
7.          emp_id    ,
8.          depart_id ,
9.          salary    ,
10.          row_number()over(partition by depart_id order by salary ) as rn1,
11.          row_number()over(partition by depart_id order by salary desc) as rn2
12.     from t3_salary
13.   )t1
14. where rn1 > 1 and  rn2 > 1
15. group by depart_id

复制代码

思路分析：

• step1: 进行两次开窗，排序往复掉 部门内的最高最低薪资
  
• step2：按照部门分组进行分组取均匀即可；
  

3 小结

   需注意：本题的前置条件：每个部门员工数不少于3人，且部门的最高和最低薪资仅一人，所以直接利用row_number开窗函数就能处置惩罚。
==========================*****==========================
当前活泼用户一连活泼天数

0 问题描述

1. 有用户登录日志表，包含日期、用户ID，当天是否登录，请查询出当天活跃的用户当前连续活跃天数；
3. +-------------+----------+-----------+
4. | login_date  | user_id  | is_login  |
5. +-------------+----------+-----------+
6. | 2023-08-01  | 1        | 1         |
7. | 2023-08-01  | 2        | 1         |
8. | 2023-08-01  | 3        | 1         |
9. | 2023-08-01  | 4        | 0         |
10. | 2023-08-02  | 1        | 1         |
11. | 2023-08-02  | 2        | 0         |
12. | 2023-08-02  | 3        | 1         |
13. | 2023-08-02  | 4        | 1         |
14. | 2023-08-03  | 1        | 1         |
15. | 2023-08-03  | 2        | 1         |
16. | 2023-08-03  | 3        | 0         |
17. | 2023-08-03  | 4        | 1         |
18. +-------------+----------+-----------+

复制代码

1 数据准备

1. --建表语句
2. CREATE TABLE t4_login_log (
3.   login_date string COMMENT '日期',
4.   user_id bigint COMMENT '用户ID',
5.   is_login bigint COMMENT '是否登录'
6. ) COMMENT '用户签到记录表';
8. --插入数据
9. insert overwrite table  t4_login_log
10. values
11.   ('2023-08-01',1,1),
12.   ('2023-08-01',2,1),
13.   ('2023-08-01',3,1),
14.   ('2023-08-01',4,0),
15.   ('2023-08-02',1,1),
16.   ('2023-08-02',2,0),
17.   ('2023-08-02',3,1),
18.   ('2023-08-02',4,1),
19.   ('2023-08-03',1,1),
20.   ('2023-08-03',2,1),
21.   ('2023-08-03',3,0),
22.   ('2023-08-03',4,1);

复制代码

2 数据分析

1. select
2.        t1.user_id,
3.        count(1) as login_days
4. from t4_login_log t1
5. left join
6.   (
7.     select
8.          user_id,
9.          max(login_date)as latest_unlogin_date
10.     from t4_login_log
11.     where is_login = 0
12.     group by user_id
13.   )t2
14. on t1.user_id = t2.user_id
15.   where t1.login_date > coalesce(t2.latest_unlogin_date,'1970-01-01')
16.      group by t1.user_id;

复制代码

思路分析：

• step1：找到所有用户末了未登录日期；
  
• step2：筛选出末了未登录日期的数据，（如果不存在未登录数据，则代表该用户不停一连登录）
  
• step3：对筛选结果进行分组统计；
  

3 小结

==========================*****==========================
贩卖额一连3天增长的商户

0 问题描述

1. 有一张订单记录表 t5_order 包含 订单ID(order_id),商户ID(shop_id),订单时间(order_time)和订单金额(order_amt),请查询出过去至少存在3天销售额连续增长的商户
3. +-----------+----------+----------------------+------------+
4. | order_id  | shop_id  |      order_time      | order_amt  |
5. +-----------+----------+----------------------+------------+
6. | 1         | 1001     | 2023-08-21 09:01:00  | 9.99       |
7. | 2         | 1001     | 2023-08-22 10:00:00  | 19.99      |
8. | 3         | 1001     | 2023-08-22 13:00:00  | 8.88       |
9. | 4         | 1001     | 2023-08-23 08:00:00  | 29.99      |
10. | 5         | 1001     | 2023-08-23 09:00:00  | 19.99      |
11. | 6         | 1001     | 2023-08-24 11:00:00  | 99.99      |
12. | 7         | 1001     | 2023-08-25 15:00:00  | 1.99       |
13. | 8         | 1001     | 2023-08-26 16:00:00  | 2.99       |
14. | 9         | 1001     | 2023-08-26 17:00:00  | 95.99      |
15. | 10        | 1002     | 2023-08-21 09:00:00  | 9.99       |
16. | 11        | 1002     | 2023-08-22 11:00:00  | 1.99       |
17. | 12        | 1002     | 2023-08-22 11:01:00  | 19.99      |
18. | 13        | 1002     | 2023-08-22 12:05:00  | 14.99      |
19. | 14        | 1002     | 2023-08-22 13:00:00  | 6.99       |
20. | 15        | 1002     | 2023-08-23 14:00:00  | 99.99      |
21. | 16        | 1002     | 2023-08-24 13:00:00  | 19.99      |
22. | 17        | 1002     | 2023-08-25 09:00:00  | 19.99      |
23. | 18        | 1002     | 2023-08-25 11:00:00  | 5.99       |
24. | 19        | 1002     | 2023-08-25 13:00:00  | 6.99       |
25. | 20        | 1002     | 2023-08-25 13:07:00  | 7.0        |
26. | 21        | 1002     | 2023-08-25 15:00:00  | 10.0       |
27. | 22        | 1002     | 2023-08-26 07:00:00  | 9.99       |
28. | 23        | 1003     | 2023-08-21 13:07:00  | 7.0        |
29. | 24        | 1003     | 2023-08-22 15:00:00  | 8.0        |
30. | 25        | 1003     | 2023-08-23 07:00:00  | 9.99       |
31. | 26        | 1003     | 2023-08-25 13:07:00  | 10.0       |
32. | 27        | 1003     | 2023-08-26 15:00:00  | 11.0       |
33. +-----------+----------+----------------------+------------+

复制代码

1 数据准备

1. --建表语句
2. CREATE TABLE t5_order (
3. order_id bigint COMMENT '订单ID',
4. shop_id bigint COMMENT '商户ID',
5. order_time string COMMENT '订单时间',
6. order_amt double COMMENT '订单金额'
7. ) COMMENT '订单记录表';
8. -- 插入数据
10. insert overwrite table  t5_order
11. values
12.    (1,1001,'2023-08-21 09:01:00',9.99),
13.    (2,1001,'2023-08-22 10:00:00',19.99),
14.    (3,1001,'2023-08-22 13:00:00',8.88),
15.    (4,1001,'2023-08-23 08:00:00',29.99),
16.    (5,1001,'2023-08-23 09:00:00',19.99),
17.    (6,1001,'2023-08-24 11:00:00',99.99),
18.    (7,1001,'2023-08-25 15:00:00',1.99),
19.    (8,1001,'2023-08-26 16:00:00',2.99),
20.    (9,1001,'2023-08-26 17:00:00',95.99),
21.    (10,1002,'2023-08-21 09:00:00',9.99),
22.    (11,1002,'2023-08-22 11:00:00',1.99),
23.    (12,1002,'2023-08-22 11:01:00',19.99),
24.    (13,1002,'2023-08-22 12:05:00',14.99),
25.    (14,1002,'2023-08-22 13:00:00',6.99),
26.    (15,1002,'2023-08-23 14:00:00',99.99),
27.    (16,1002,'2023-08-24 13:00:00',19.99),
28.    (17,1002,'2023-08-25 09:00:00',19.99),
29.    (18,1002,'2023-08-25 11:00:00',5.99),
30.    (19,1002,'2023-08-25 13:00:00',6.99),
31.    (20,1002,'2023-08-25 13:07:00',7.00),
32.    (21,1002,'2023-08-25 15:00:00',10.00),
33.    (22,1002,'2023-08-26 07:00:00',9.99),
34.    (23,1003,'2023-08-21 13:07:00',7.00),
35.    (24,1003,'2023-08-22 15:00:00',8.00),
36.    (25,1003,'2023-08-23 07:00:00',9.99),
37.    (26,1003,'2023-08-25 13:07:00',10.00),
38.    (27,1003,'2023-08-26 15:00:00',11.00);

复制代码

2 数据分析

1. with tmp1 as
2.    (
3.      select
4.         shop_id,
5.         to_date(order_time) as order_date,
6.         sum(order_amt) as order_amt
7.      from t5_order
8.      group by shop_id,to_date(order_time)
9.    ),
10. tmp2 as
11.   (
12.     select
13.         shop_id,
14.         order_date,
15.         order_amt,
16.         order_amt - lag(order_amt,1,null) over (partition by shop_id order by order_date) as order_amt_diff
17.     from tmp1
18.   )
20. select
21.      shop_id
22. from
23.   (
24.        select
25.              shop_id,
26.              order_date,
27.              date_sub(order_date,row_number() over(partition by shop_id order by order_date)) as diff2
28.        from tmp2
29.        where order_amt_diff >0
30.   )tmp3
31. group by shop_id,diff2
32.   having count(1) >=3;

复制代码

思路分析：

• step1：盘算出每个商户的天天贩卖额；
  
• step2：针对每个商户，盘算当日与上一日的贩卖差额，筛选出贩卖额增长的记录；
  
• step3：date_sub+row_number 盘算出差值diff2，差值雷同代表 贩卖额一连增长；
  
• step4：根据商户，差值diff2分组，得到贩卖额一连3天增长的商户；
  

3 小结

==========================*****==========================
不合格课程数大于2的学生的均匀结果及其排名

0 问题描述

1. 有张 学生每科科目成绩表，求不及格课程数大于2的学生的平均成绩及其成绩平均值后所在的排名。
3. +------+------+--------+
4. | sid  | cid  | score  |
5. +------+------+--------+
6. | 1    | 1    | 90     |
7. | 1    | 2    | 50     |
8. | 1    | 3    | 72     |
9. | 2    | 1    | 40     |
10. | 2    | 2    | 50     |
11. | 2    | 3    | 22     |
12. | 3    | 1    | 30     |
13. | 3    | 2    | 50     |
14. | 3    | 3    | 52     |
15. | 4    | 1    | 90     |
16. | 4    | 2    | 90     |
17. | 4    | 3    | 72     |
18. +------+------+--------+

复制代码

1 数据准备

1. --建表语句
2. CREATE TABLE t6_scores (
3. sid bigint COMMENT '学生ID',
4. cid bigint COMMENT '课程ID',
5. score bigint COMMENT '得分'
6. ) COMMENT '用户课程分数';
7. -- 插入数据
8. insert overwrite table t6_scores
9. values
10.   (1,1,90),
11.   (1,2,50),
12.   (1,3,72),
13.   (2,1,40),
14.   (2,2,50),
15.   (2,3,22),
16.   (3,1,30),
17.   (3,2,50),
18.   (3,3,52),
19.   (4,1,90),
20.   (4,2,90),
21.   (4,3,72)

复制代码

2 数据分析

1. select
2.        sid,
3.        avg_score,
4.        rn
5. from
6.     (
7.         select
8.              sid,
9.              avg_score,
10.              fail_num,
11.              dense_rank() over(order by avg_score desc) as rn
12.         from
13.         (
14.           select
15.                sid,
16.                avg(score) as avg_score,
17.                sum(case when score <60 then 1 else 0 end ) as fail_num
18.           from t6_scores
19.           group by sid
20.         )t1
21.     )t2
22.   where fail_num > 2;

复制代码

思路分析：

• step1：盘算出每个学生的均匀结果、不合格的科目数；
  
• step2：根据均匀结果盘算排名；
  

3 小结

==========================*****==========================
盘算越日留存率

0 问题描述

1. 有一张 用户登录记录表，已经按照用户日期进行去重处理。以用户登录的最早日期作为当日的新增用户日期，请计算次日留存率是多少。
3. 指标定义：
5. 次日留存用户：基准日新增的用户在第二天又登录（活跃）；
7. 次日留存率：t+1日留存用户数/t日新增用户；
10. +----------+-------------+
11. | user_id  | login_date  |
12. +----------+-------------+
13. | aaa      | 2023-12-01  |
14. | bbb      | 2023-12-01  |
15. | bbb      | 2023-12-02  |
16. | ccc      | 2023-12-02  |
17. | bbb      | 2023-12-03  |
18. | ccc      | 2023-12-03  |
19. | ddd      | 2023-12-03  |
20. | ccc      | 2023-12-04  |
21. | ddd      | 2023-12-04  |
22. +----------+-------------+

复制代码

1 数据准备

1. create table t7_login
2. (
3. user_id string COMMENT '用户ID',
4. login_date string COMMENT '登录日期'
5. ) COMMENT '用户登录记录表';
7. insert overwrite table t7_login
8. values
9.   ('aaa','2023-12-01'),
10.   ('bbb','2023-12-01'),
11.   ('bbb','2023-12-02'),
12.   ('ccc','2023-12-02'),
13.   ('bbb','2023-12-03'),
14.   ('ccc','2023-12-03'),
15.   ('ddd','2023-12-03'),
16.   ('ccc','2023-12-04'),
17.   ('ddd','2023-12-04');

复制代码

2 数据分析

1. select
2.      first_day,
3.      concat(per * 100,'%') as next_act_per
4. from
5.    (
6.        select
7.              first_day,
8.             if(count(case when date_diff = 0 then user_id end) = 0, 0,
9.                    count(case when date_diff = 1 then user_id end) / count(case when date_diff = 0 then user_id end))  per
10.       from
11.        (
12.           select
13.               user_id,
14.               min(login_date)over(partition by user_id order by login_date) as first_day,
15.               datediff(login_date,min(login_date)over(partition by user_id order by login_date) ) as date_diff
16.           from t7_login
17.        )t1
18.       group by first_day
19.    )t2;

复制代码

思路分析：

• step1：利用开窗函数盘算出用户的最小登录时间作为新增日期first_day， 再盘算当天日期和新增日期的时间差；
  
• step2：根据first_day进行分组，date_diff=0的为当天新增用户，date_diff=1的为越日登录的用户；
  
• step3：用越日留存数/新增用户数据即为留存率，因为新增可能为0，需要先判断；
  

3 小结

==========================*****==========================
按照顺序进行行转列拼接

0 问题描述

1. 已知有表中含有两列数据id，val,数据内容如下，请按照id的大小将val进行拼接。
3. +-----+------+
4. | id  | val  |
5. +-----+------+
6. | 1   | 20   |
7. | 2   | 10   |
8. | 8   | 120  |
9. | 9   | 30   |
10. | 11  | 50   |
11. | 22  | 40   |
12. +-----+------+

复制代码

1 数据准备

1. --建表语句
2. create table t8_concat_ordered
3. (
4. id bigint COMMENT '用户ID',
5. val string COMMENT '登录日期'
6. ) COMMENT '用户登录记录表';
7. --插入数据
8. insert overwrite table  t8_concat_ordered
9. values
10.   (1,'20'),
11.   (2,'10'),
12.   (8,'120'),
13.   (9,'30'),
14.   (11,'50'),
15.   (22,'40')

复制代码

2 数据分析

  collect_list 拼接字符串是无序的，因此即使按照顺序将原始数据排好，也不能保证结果有序。所以可以将id和val 进行拼接，这样对整个字符串进行排序就会按照id的顺序排序。
 别的需要注意，id是数字类型，直接拼接会导致按照字符顺序，即11在2前面，为办理这个问题，需要左补零。总结：利用字符串拼接以后，利用sort_array()函数，保证结果有序，再转化成字符串，末了将拼接上的id替换掉。
  collect_list不保证有序，用 lpad、concat_ws、 sort_array、regexp_replace 等函数替换。
HiveSQL题——collect_set()/collect_list()聚合函数_collect set-CSDN博客

1. select regexp_replace(
2.    concat_ws(',' , sort_array( collect_list( concat_ws(':',lpad(id,5,0),val)))),'\\d+\:',''
3.    )
4. from t8_concat_ordered;

复制代码

  

思路分析：

• step1：将ID进行左补0保证所有数据结果位数雷同，然后与和val进行拼接； concat_ws(':', lpad(id, 5, 0), val)
  
• step2：将数据进行聚合，并将结果进行排序；sort_array(collect_list(concat_ws(':',lpad(id,5,0),val)))
  
• step3：将结果进行字符串替换，将补的ID去掉，得到终极结果；
  

3 小结

==========================*****==========================
所有考试科目标结果都大于对应学科均匀结果的学生

0 问题描述

1. 有学生每科科目成绩，找出所有科目成绩都大于对应学科的平均成绩的学生
3. +------+------+--------+
4. | sid  | cid  | score  |
5. +------+------+--------+
6. | 1    | 1    | 90     |
7. | 1    | 2    | 50     |
8. | 1    | 3    | 72     |
9. | 2    | 1    | 40     |
10. | 2    | 2    | 50     |
11. | 2    | 3    | 22     |
12. | 3    | 1    | 30     |
13. | 3    | 2    | 50     |
14. | 3    | 3    | 52     |
15. | 4    | 1    | 90     |
16. | 4    | 2    | 90     |
17. | 4    | 3    | 72     |
18. +------+------+--------+

复制代码

1 数据准备

1. CREATE TABLE t9_scores (
2.   sid bigint COMMENT '学生ID',
3.   cid bigint COMMENT '课程ID',
4.   score bigint COMMENT '得分'
5. ) COMMENT '用户课程分数';
6. -- 插入数据
7. insert overwrite table  t9_scores
8. values
9.    (1,1,90),
10.    (1,2,50),
11.    (1,3,72),
12.    (2,1,40),
13.    (2,2,50),
14.    (2,3,22),
15.    (3,1,30),
16.    (3,2,50),
17.    (3,3,52),
18.    (4,1,90),
19.    (4,2,90),
20.    (4,3,72)

复制代码

2 数据分析

1. select
2.      sid
3. from (select
4.              sid,
5.              cid,
6.              score,
7.              avg_score,
8.              if(score > avg_score, 0, 1) as flag
9.       from (select
10.                    sid,
11.                    cid,
12.                    score,
13.                    avg(score) over (partition by cid) as avg_score
14.             from t9_scores) t1) t2
15. group by sid
16. having sum(flag) = 0;

复制代码

3 小结

==========================*****==========================
用户行为路径分析

0 问题描述

1. 有一张用户操作行为记录表 t10_act_log 包含用户ID(user_id),操作编号(op_id),操作时间(op_time)
3. 要求:
4. 1.统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻;
5. 2.统计每天用户行为序列为A-B-D的用户数;其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)
7. +----------+--------+----------------------+
8. | user_id  | op_id  |       op_time        |
9. +----------+--------+----------------------+
10. | 1        | A      | 2023-10-18 12:01:03  |
11. | 2        | A      | 2023-10-18 12:01:04  |
12. | 3        | A      | 2023-10-18 12:01:05  |
13. | 1        | B      | 2023-10-18 12:03:03  |
14. | 1        | A      | 2023-10-18 12:04:03  |
15. | 1        | C      | 2023-10-18 12:06:03  |
16. | 1        | D      | 2023-10-18 12:11:03  |
17. | 2        | A      | 2023-10-18 12:07:04  |
18. | 3        | C      | 2023-10-18 12:02:05  |
19. | 2        | C      | 2023-10-18 12:09:03  |
20. | 2        | A      | 2023-10-18 12:10:03  |
21. | 4        | A      | 2023-10-18 12:01:03  |
22. | 4        | C      | 2023-10-18 12:11:05  |
23. | 4        | D      | 2023-10-18 12:15:05  |
24. | 1        | A      | 2023-10-19 12:01:03  |
25. | 2        | A      | 2023-10-19 12:01:04  |
26. | 3        | A      | 2023-10-19 12:01:05  |
27. | 1        | B      | 2023-10-19 12:03:03  |
28. | 1        | A      | 2023-10-19 12:04:03  |
29. | 1        | C      | 2023-10-19 12:06:03  |
30. | 2        | A      | 2023-10-19 12:07:04  |
31. | 3        | B      | 2023-10-19 12:08:05  |
32. | 3        | E      | 2023-10-19 12:09:05  |
33. | 3        | D      | 2023-10-19 12:11:05  |
34. | 2        | C      | 2023-10-19 12:09:03  |
35. | 4        | E      | 2023-10-19 12:05:03  |
36. | 4        | B      | 2023-10-19 12:06:03  |
37. | 4        | E      | 2023-10-19 12:07:03  |
38. | 2        | A      | 2023-10-19 12:10:03  |
39. +----------+--------+----------------------+

复制代码

1 数据准备

1. create table t10_act_log(
2.     user_id bigint  comment'用户ID',
3.     op_id   string  comment'操作编号',
4.     op_time string  comment'操作时间'
5.   );
7. insert overwrite table  t10_act_log
8. values
9.    (1, 'A', '2023-10-18 12:01:03'),
10.    (2, 'A', '2023-10-18 12:01:04'),
11.    (3, 'A', '2023-10-18 12:01:05'),
12.    (1, 'B', '2023-10-18 12:03:03'),
13.    (1, 'A', '2023-10-18 12:04:03'),
14.    (1, 'C', '2023-10-18 12:06:03'),
15.    (1, 'D', '2023-10-18 12:11:03'),
16.    (2, 'A', '2023-10-18 12:07:04'),
17.    (3, 'C', '2023-10-18 12:02:05'),
18.    (2, 'C', '2023-10-18 12:09:03'),
19.    (2, 'A', '2023-10-18 12:10:03'),
20.    (4, 'A', '2023-10-18 12:01:03'),
21.    (4, 'C', '2023-10-18 12:11:05'),
22.    (4, 'D', '2023-10-18 12:15:05'),
23.    (1, 'A', '2023-10-19 12:01:03'),
24.    (2, 'A', '2023-10-19 12:01:04'),
25.    (3, 'A', '2023-10-19 12:01:05'),
26.    (1, 'B', '2023-10-19 12:03:03'),
27.    (1, 'A', '2023-10-19 12:04:03'),
28.    (1, 'C', '2023-10-19 12:06:03'),
29.    (2, 'A', '2023-10-19 12:07:04'),
30.    (3, 'B', '2023-10-19 12:08:05'),
31.    (3, 'E', '2023-10-19 12:09:05'),
32.    (3, 'D', '2023-10-19 12:11:05'),
33.    (2, 'C', '2023-10-19 12:09:03'),
34.    (4, 'E', '2023-10-19 12:05:03'),
35.    (4, 'B', '2023-10-19 12:06:03'),
36.    (4, 'E', '2023-10-19 12:07:03'),
37.    (2, 'A', '2023-10-19 12:10:03');
38.    

复制代码

2 数据分析

问题一：统计天天符合以下条件的用户数：A操纵之后是B操纵，AB操纵必须相邻;

2. select
3.      dt,
4.      count(1) as cnt
5. from(
6.        select
7.              user_id,
8.              dt,
9.              op_sort2
10.        from
11.           (
12.              select
13.                    user_id,
14.                    dt,
15.                    regexp_replace(op_sort1,
16.                                     '(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\|)', '') as op_sort2
17.               from
18.                  (
19.                      select
20.                           user_id,
21.                           dt,
22.                           sort_array(collect_list(op_str)) as sort1,
23.                           concat_ws(',', sort_array(collect_list(op_str))) as op_sort1
24.                      from
25.                       (
26.                            select
27.                              user_id ,
28.                              op_id    ,
29.                              op_time  ,
30.                              to_date(op_time)  as dt,
31.                              concat_ws('|',op_time, op_id) as op_str
32.                            from t10_act_log
33.                       )t1
34.                      group by user_id, dt
35.                  )t2
36.           )t3
37.       where op_sort2 like '%A,B%'
38.      )t4
39. group by dt;

复制代码

思路分析：
   collect_list拼接字符串是无序的，即便按照顺序将原始数据排好，也不能保证结果有序

• step1：拼接op_time和op_id, 然后根据用户和日期进行分组，collect_list聚合出天天用户的行为，利用sort_array保证拼接后的字符串有序。
  concat_ws(',', sort_array(collect_list(op_str))) as op_sort
  
• step2：regexp_replace进行字符串替换，去掉op_time及添加的| 
  
• step3：利用like查询包含'A,B'的记录；
  
• step4：按照日期分组，盘算天天符合条件的用户数量
  

问题二：统计天天用户行为序列为A-B-D的用户数 ;  此中:A-B之间可以有任何其他欣赏记录(如C,E等),B-D之间除了C记录可以有任何其他欣赏记录(如A,E等)

2. select
3.      dt,
4.      count(1) as cnt
5. from(
6.        select
7.              user_id,
8.              dt,
9.              op_sort2
10.        from
11.           (
12.              select
13.                    user_id,
14.                    dt,
15.                    regexp_replace(op_sort1,
16.                                     '(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\|)', '') as op_sort2
17.               from
18.                  (
19.                      select
20.                           user_id,
21.                           dt,
22.                           sort_array(collect_list(op_str)) as sort1,
23.                           concat_ws(',', sort_array(collect_list(op_str))) as op_sort1
24.                      from
25.                       (
26.                            select
27.                              user_id ,
28.                              op_id    ,
29.                              op_time  ,
30.                              to_date(op_time)  as dt,
31.                              concat_ws('|',op_time, op_id) as op_str
32.                            from t10_act_log
33.                       )t1
34.                      group by user_id, dt
35.                  )t2
36.           )t3
37.        where op_sort2 like '%A%B%D%' and op_sort2 not like '%A%B%C%D%'
38.      )t4
39. group by dt;

复制代码

3 小结

• 按照顺序拼接字符串；
  
• ​包含'A,B';
  
• 包含’A%B%D'并且不能是‘A%B%C%D’
  

=================*****==========================
查询前2大和前2小用户并有序拼接

0 问题描述

1. 有一张 用户账户表，包含年份，用户id和值,请按照年份分组，取出值前两小和前两大对应的用户id。
2. 注意：需要保持值最小和最大的用户id排首位。
4. 样例数据
5. +-------+----------+--------+
6. | year  | user_id  | value  |
7. +-------+----------+--------+
8. | 2022  | A        | 30     |
9. | 2022  | B        | 10     |
10. | 2022  | C        | 20     |
11. | 2023  | A        | 40     |
12. | 2023  | B        | 50     |
13. | 2023  | C        | 20     |
14. | 2023  | D        | 30     |
15. +-------+----------+--------+
17. 期望结果
18. +-------+-----------------+-----------------+
19. | year  | max2_user_list  | min2_user_list  |
20. +-------+-----------------+-----------------+
21. | 2022  | A,C             | B,C             |
22. | 2023  | B,A             | C,D             |
23. +-------+-----------------+-----------------+

复制代码

1 数据准备

1. --建表语句
2. create table if not exists t12_amount
3. (
4.     year    string comment '年份',
5.     user_id string comment '用户id',
6.     value   bigint comment '数值'
7. );
9. --插入数据
10. insert  overwrite table t12_amount
11. values ('2022', 'A', 30),
12.        ('2022', 'B', 10),
13.        ('2022', 'C', 20),
14.        ('2023', 'A', 40),
15.        ('2023', 'B', 50),
16.        ('2023', 'C', 20),
17.        ('2023', 'D', 30)

复制代码

2 数据分析

1. select
2.      concat(max1_user_id,',',max2_user_id) as max_user_list,
3.      concat(min1_user_id,',',min2_user_id) as min_user_id
4. from
5.   (
6.       select
7.             year,
8.            max(if(rn1 = 1,user_id,null)) as max1_user_id,
9.            max(if(rn1 = 2,user_id,null)) as max2_user_id,
10.            max(if(rn2 = 1,user_id,null)) as min1_user_id,
11.            max(if(rn2 = 2,user_id,null)) as min2_user_id
12.        from
13.       (
14.           select
15.                year,
16.                user_id,
17.                value,
18.                row_number()over(partition by year order by value desc) as rn1,
19.                row_number()over(partition by year order by value ) as rn2
20.           from t12_amount
21.       )t1
22.       group by year
23.   )t2

复制代码

思路分析：
   collect_list拼接字符串是无序的，即便按照顺序将原始数据排好，也不能保证结果有序

• step1：利用row_number函数根据年份分组，对value排序得到 值前两小和前两大
  
• step2：按照顺序拼接，得到终极结果
  

3 小结

==========================*****==========================
查询每个学科第三名的学生的学科结果总结果及总排名

0 问题描述

1. 有一张学生成绩表，包含学生姓名、学科、成绩三个字段，请用一条SQL查询出每个学科排名第三名的学生，他的学科成绩、总成绩、以及总排名。
3. 样例数据
4. +----------+----------+--------+
5. | student  | subject  | score  |
6. +----------+----------+--------+
7. | 张三       | 语文       | 95     |
8. | 李四       | 语文       | 90     |
9. | 王五       | 语文       | 88     |
10. | 赵六       | 语文       | 77     |
11. | 张三       | 数学       | 80     |
12. | 李四       | 数学       | 90     |
13. | 王五       | 数学       | 92     |
14. | 赵六       | 数学       | 84     |
15. | 张三       | 英语       | 82     |
16. | 李四       | 英语       | 93     |
17. | 王五       | 英语       | 88     |
18. | 赵六       | 英语       | 68     |
19. +----------+----------+--------+

复制代码

1 数据准备

1. --建表语句
2. create table if not exists t13_student_score
3. (
4.     student string,
5.     subject string,
6.     score   bigint
7. );
9. --插入数据
11. insert overwrite table t13_student_score
12. values ('张三', '语文', 95),
13.        ('李四', '语文', 90),
14.        ('王五', '语文', 88),
15.        ('赵六', '语文', 77),
16.        ('张三', '数学', 80),
17.        ('李四', '数学', 90),
18.        ('王五', '数学', 92),
19.        ('赵六', '数学', 84),
20.        ('张三', '英语', 82),
21.        ('李四', '英语', 93),
22.        ('王五', '英语', 88),
23.        ('赵六', '英语', 68);

复制代码

2 数据分析

2. select
3.       student,
4.       subject,
5.       score,
6.       total_score,
7.       total_rn
8. from
9.    (
10.       select
11.            student,
12.            subject,
13.            score  ,
14.            subject_rn,
15.            total_score,
16.            row_number() over(partition by subject order by total_score desc) as total_rn
17.       from
18.          (
19.           select
20.                student,
21.                subject,
22.                score  ,
23.                row_number() over (partition by subject order by score desc) as subject_rn,
24.                sum(score) over (partition by student) as total_score
25.           from t13_student_score
26.          )t1
27.    )t2
28.    where subject_rn = 3;

复制代码

思路分析：

• step1：每个学科内的结果排名、每个学生总结果;
  
• step2：利用row_number()函数，根据学科分组，按照总分排序来盘算学生总排名；
  
• step3：限定subject_rn = 3得到每个学科排名第三的同学记录；
  

3 小结

==========================*****==========================
各用户最长的一连登录天数-可间断

0 问题描述

1. 现有各用户的登录记录表t14_login_events如下，表中每行数据表达的信息是一个用户何时登录了平台。
2. 现要求统计【各用户最长的连续登录天数，间断一天也算作连续】，例如：一个用户在1,3,5,6登录，则视为连续6天登录。
4. 样例数据
5. +----------+----------------------+
6. | user_id  |    login_datetime    |
7. +----------+----------------------+
8. | 100      | 2021-12-01 19:00:00  |
9. | 100      | 2021-12-01 19:30:00  |
10. | 100      | 2021-12-02 21:01:00  |
11. | 100      | 2021-12-03 11:01:00  |
12. | 101      | 2021-12-01 19:05:00  |
13. | 101      | 2021-12-01 21:05:00  |
14. | 101      | 2021-12-03 21:05:00  |
15. | 101      | 2021-12-05 15:05:00  |
16. | 101      | 2021-12-06 19:05:00  |
17. | 102      | 2021-12-01 19:55:00  |
18. | 102      | 2021-12-01 21:05:00  |
19. | 102      | 2021-12-02 21:57:00  |
20. | 102      | 2021-12-03 19:10:00  |
21. | 104      | 2021-12-04 21:57:00  |
22. | 104      | 2021-12-02 22:57:00  |
23. | 105      | 2021-12-01 10:01:00  |
24. +----------+----------------------+
25. 期望结果
26. +----------+---------------+
27. | user_id  | max_log_days  |
28. +----------+---------------+
29. | 100      | 3             |
30. | 101      | 6             |
31. | 102      | 3             |
32. | 104      | 3             |
33. | 105      | 1             |
34. +----------+---------------+

复制代码

1 数据准备

1. create table if not exists t14_login_events
2. (
3.     user_id        int comment '用户id',
4.     login_datetime string comment '登录时间'
5. )
6.     comment '直播间访问记录';
7. --数据插入
8. insert  overwrite table t14_login_events
9. values (100, '2021-12-01 19:00:00'),
10.        (100, '2021-12-01 19:30:00'),
11.        (100, '2021-12-02 21:01:00'),
12.        (100, '2021-12-03 11:01:00'),
13.        (101, '2021-12-01 19:05:00'),
14.        (101, '2021-12-01 21:05:00'),
15.        (101, '2021-12-03 21:05:00'),
16.        (101, '2021-12-05 15:05:00'),
17.        (101, '2021-12-06 19:05:00'),
18.        (102, '2021-12-01 19:55:00'),
19.        (102, '2021-12-01 21:05:00'),
20.        (102, '2021-12-02 21:57:00'),
21.        (102, '2021-12-03 19:10:00'),
22.        (104, '2021-12-04 21:57:00'),
23.        (104, '2021-12-02 22:57:00'),
24.        (105, '2021-12-01 10:01:00');

复制代码

2 数据分析

1. select
2.      user_id,
3.      max(log_days) as max_log_days
4. from
5.    (
6.       select
7.            user_id,
8.            group_id,
9.            datediff(max(login_date),min(login_date)) +1 as log_days
10.       from
11.           (
12.             select
13.                   user_id,
14.                   login_date,
15.                   lag_log_date,
16.                   date_diff,
17.                   sum(if(date_diff <=2 ,0,1) )over(partition by  user_id order by login_date) as group_id
18.             from
19.                (
20.                  select
21.                       user_id,
22.                       login_date,
23.                       lag(login_date,1,null) over (partition by user_id order by login_date ) as lag_log_date,
24.                       datediff(login_date,lag(login_date,1,'1970-01-01') over (partition by user_id order by login_date)) as date_diff
25.                  from
26.                      (
27.                          select
28.                               user_id,
29.                               to_date(login_datetime)  as login_date
30.                          from t14_login_events
31.                          group by user_id,to_date(login_datetime)
32.                      )t1
33.                )t2
34.           )t3
35.         group by user_id,
36.              group_id
37.     )t4
38. group by user_id;

复制代码

思路分析：

• step1：利用to_date函数，得到登岸日期，去重处置惩罚;
  
• step2：根据用户分组，利用lag函数获得当前行的上一行数据中的日期，利用datediff函数判断日期当期日期与上一行日期的时间差；
  
• step3：根据date_diff结果判断 是否一连，如果date_diff <= 2，认为一连 ，赋值0，不一连则赋值为1；
  
• step4：按照用户和group_id 分组，盘算每次一连登岸的天数，再根据用户分组盘算最大一连天数
  

3 小结

==========================*****==========================
奖金瓜分问题

0 问题描述

1. 在活动大促中，有玩游戏瓜分奖金环节。现有奖金池为 10000元，代表奖金池中的初始额度。用户的分数信息如下表。表中的数据代表每一个用户和其对应的得分，user_id 和 score 都不会有重复值。瓜分奖金的规则如下：按照 score 从高到低依次瓜分，每个人都能分走当前奖金池里面剩余奖金的一半，当奖金池里面剩余的奖金少于 250 时（不含），则停止瓜分奖金。 现在需要查询出所有分到奖金的 user_id 和其对应的奖金。
3. 样例数据
4. +----------+--------+
5. | user_id  | score  |
6. +----------+--------+
7. | 100      | 60     |
8. | 101      | 45     |
9. | 102      | 45     |
10. | 103      | 35     |
11. | 104      | 30     |
12. | 105      | 25     |
13. | 106      | 15     |
14. | 107      | 10     |
15. +----------+--------+

复制代码

1 数据准备

1. --建表语句
2. create table if not exists t15_user_score
3. (
4.     user_id string,
5.     score   bigint
6. );
8. --插入数据
9. insert overwrite table  t15_user_score
10. values
11.    ('100',60),
12.    ('101',45),
13.    ('102',45),
14.    ('103',35),
15.    ('104',30),
16.    ('105',25),
17.    ('106',15),
18.    ('107',10)

复制代码

2 数据分析

1. select
2.      user_id,
3.      score,
4.      prize1
5. from
6.    (
7.       select
8.             user_id,
9.             score,
10.             power(0.5, rn) * 10000 as prize1,
11.             power(0.5, rn - 1) * 10000 as prize2
12.        from
13.            (
14.               select
15.                    user_id,
16.                    score,
17.                    row_number() over(order by score desc) as rn
18.                from t15_user_score
19.            )t1
20.    )t2
21. where prize2 >=250;

复制代码

思路分析：

• step1：利用row_number开窗，得到用户排名rn;
  
• step2：每个人得到当前奖池的1/2，排名rn的用户得到的为 （1/2 ）^ rn * 10000，奖池剩余的也是（1/2 ）^ rn * 10000；利用hive中的幂运算函数power，例如power(2,3) = 2^ 3 =8
  
• step3：限定瓜分条件，题目中要求当奖金池内里剩余的奖金少于 250 时（不含），则制止瓜分奖金
  

3 小结


参考文章：
https://www.dwsql.com/interview

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。