HiveSQL实战——大厂面试真题

已于 2024-08-17 21:09:14 修改
阅读量359 收藏 4
点赞数 12
分类专栏： 小镇做题家 文章标签： 大数据
于 2024-08-14 06:53:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/SHWAITME/article/details/141179664
版权

一、字节跳动

最高峰同时直播人数

https://blog.csdn.net/SHWAITME/article/details/135918264

0 问题描述

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

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


insert overwrite table  t1_livestream_log values
(1,'2024-04-29 01:00:00','2024-04-29 02:01:05'),
(2,'2024-04-29 01:05:00','2024-04-29 02:03:18'),
(3,'2024-04-29 02:00:00','2024-04-29 04:03:22'),
(4,'2024-04-29 03:15:07','2024-04-29 04:33:21'),
(5,'2024-04-29 03:34:16','2024-04-29 06:10:45'),
(6,'2024-04-29 05:22:00','2024-04-29 07:01:08'),
(7,'2024-04-29 06:11:03','2024-04-29 09:26:05'),
(3,'2024-04-29 08:00:00','2024-04-29 12:34:27'),
(1,'2024-04-29 11:00:00','2024-04-29 16:03:18'),
(8,'2024-04-29 15:00:00','2024-04-29 17:01:05');
2 数据分析
with t1 as(
        select
            user_id,
            start_time as action_time,
            --开播记录，标记 1
            1 as change_cnt
        from t1_livestream_log
        union all
        select
            user_id,
            end_time as action_time,
             --下播记录，标记 1
            -1 as change_cnt
        from t1_livestream_log
    )
select
     max(online_cnt) as max_online_cnt
from
   (select
      user_id,
      action_time,
      change_cnt,
      sum(change_cnt)over(order by action_time asc) as online_cnt
   from t1)t2;

思路分析：

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

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

==========================*****==========================

股票波峰波谷

https://blog.csdn.net/SHWAITME/article/details/135902998

0 问题描述

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

波峰：当天的股票价格大于前一天和后一天
波谷：当天的股票价格小于前一天和后一天
1 数据准备
create table t3_stock_test(
ts_code string comment '股票代码',
trade_date string comment '交易日期',
close float comment '收盘价'
);
 
insert overwrite table `t3_stock_test` values
('000001.SZ','20220104',16.66),
('000002.SZ','20220104',20.49),
('000001.SZ','20220105',17.15),
('000002.SZ','20220105',21.17),
('000001.SZ','20220106',17.12),
('000002.SZ','20220106',21.05),
('000001.SZ','20220107',17.2),
('000002.SZ','20220107',21.89),
('000001.SZ','20220110',17.19),
('000002.SZ','20220110',22.16),
('000001.SZ','20220111',17.41),
('000002.SZ','20220111',22.3),
('000001.SZ','20220112',17),
('000002.SZ','20220112',22.05),
('000001.SZ','20220113',16.98),
('000002.SZ','20220113',21.53),
('000001.SZ','20220114',16.33),
('000002.SZ','20220114',20.7),
('000001.SZ','20220117',16.22),
('000002.SZ','20220117',20.87);
2 数据分析

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

select 
    ts_code,
    trade_date,
    close,
    point_type
from 
    (
    select
        ts_code,
        trade_date,
        close,
        lastday_close,
        nextday_close,
        case when  close > lastday_close and close > nextday_close then '波峰'
            when  close < lastday_close and close < nextday_close then '波谷'
            else '其他' end as `point_type`
    from
        (
        select
            ts_code,
            trade_date,
            close,
            lag(close,1)over(partition by ts_code order by trade_date ) as lastday_close,
            lead(close,1)over(partition by ts_code order by trade_date ) as nextday_close
        from t3_stock_test
        ) t1
    ) t2
where t2.point_type in('波峰','波谷')
3 小结

   lead和lag函数一般用于计算当前行与上一行，或者当前行与下一行之间的差值。

-- 用于统计窗口内往上第n行。参数1为列名，参数2为往上第n行（可选，默认为1），参数3为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

lag(col,n,DEFAULT) over(partition by  xx order by xx) 

-- 用于统计窗口内往下第n行。参数1为列名，参数2为往下第n行（可选，默认为1），参数3为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

lead(col,n,DEFAULT) over(partition by  xx order by xx)

==========================*****==========================

合并日期重叠的活动

0 问题描述
已知有表记录了每个大厅的活动开始日期和结束日期，每个大厅可以有多个活动。请编写一个SQL查询合并在同一个大厅举行的所有重叠的活动，如果两个活动至少有一天相同，那他们就是重叠的

样例数据
+----------+-------------+-------------+
| hall_id  | start_date  |  end_date   |
+----------+-------------+-------------+
| 1        | 2023-01-13  | 2023-01-20  |
| 1        | 2023-01-14  | 2023-01-17  |
| 1        | 2023-01-14  | 2023-01-16  |
| 1        | 2023-01-18  | 2023-01-25  |
| 1        | 2023-01-20  | 2023-01-26  |
| 2        | 2022-12-09  | 2022-12-23  |
| 2        | 2022-12-13  | 2022-12-17  |
| 2        | 2022-12-20  | 2022-12-24  |
| 2        | 2022-12-25  | 2022-12-30  |
| 3        | 2022-12-01  | 2023-01-30  |
+----------+-------------+-------------+

结果
+----------+-------------+-------------+
| hall_id  | start_date  |  end_date   |
+----------+-------------+-------------+
| 1        | 2023-01-13  | 2023-01-26  |
| 2        | 2022-12-09  | 2022-12-24  |
| 2        | 2022-12-25  | 2022-12-30  |
| 3        | 2022-12-01  | 2023-01-30  |
+----------+-------------+-------------+
解释：
  大厅 1:
     两个活动["2823-01-13"，"2023-01-20"]和[“2023-01-14"，"2023-01-17"]重叠，需求将它们合并到一个活动中[“2023-01-13"，"2023-01-20"]。
1 数据准备
CREATE TABLE IF NOT EXISTS t4_hall_event (
    hall_id STRING, --大厅ID
    start_date STRING, -- 营销活动开始日期
    end_date STRING -- 营销活动结束日期
);
--数据插入
insert overwrite table  t4_hall_event values
('1','2023-01-13','2023-01-20'),
('1','2023-01-14','2023-01-17'),
('1','2023-01-14','2023-01-16'),
('1','2023-01-18','2023-01-25'),
('1','2023-01-20','2023-01-26'),
('2','2022-12-09','2022-12-23'),
('2','2022-12-13','2022-12-17'),
('2','2022-12-20','2022-12-24'),
('2','2022-12-25','2022-12-30'),
('3','2022-12-01','2023-01-30');
2 数据分析
select
       hall_id,
       min(start_date) as start_date,
       max(end_date)   as end_date
from (select
             hall_id,
             start_date,
             end_date,
             max_end_date,
             is_merge,
             sum(is_merge) over (partition by hall_id order by start_date  ) as group_id
      from (select
                   hall_id,
                   start_date,
                   end_date,
                   max_end_date,
                   if(start_date <= max_end_date, 0, 1) as is_merge
            --0:日期重叠，需要合并，1:日期没重叠，不用合并
            from (select
                         hall_id,
                         start_date,
                         end_date,
-- step1: 1.使用max()函数开窗，获得截止到当前行之前的活动最后日期
                         max(end_date)
                             over (partition by hall_id order by start_date   rows between unbounded preceding and 1 preceding) as max_end_date
                  from t4_hall_event) t1
           ) t2
     ) t3
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博客

select
       hall_id,
       sum( datediff(end_date,new_start_date)+ 1) as day_cnt
from (select hall_id,
             start_date,
             end_date,
             max_end_date,
             new_start_date
      from (select hall_id,
                   start_date,
                   end_date,
                   max_end_date,
            if(max_end_date is null, start_date,
                         if(start_date > max_end_date, start_date, date_add(max_end_date, 1))) new_start_date
            from (select hall_id,
                         start_date,
                         end_date,
                         max(end_date)
                             over (partition by hall_id order by
     start_date ,end_date  rows between unbounded preceding and 1 preceding) as max_end_date
                  from t4_hall_event) t1) t2
     where new_start_date <= end_date) t3
group by hall_id;
3 小结

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

==========================*****==========================

查询最近一笔有效订单

0 问题描述
现有订单表t5_order，包含订单ID，订单时间,下单用户，当前订单是否有效

+---------+----------------------+----------+-----------+
| ord_id  |       ord_time       | user_id  | is_valid  |
+---------+----------------------+----------+-----------+
| 1       | 2023-12-11 12:01:03  | a        | 1         |
| 2       | 2023-12-11 12:02:06  | a        | 0         |
| 3       | 2023-12-11 12:03:15  | a        | 0         |
| 4       | 2023-12-11 12:04:20  | a        | 1         |
| 5       | 2023-12-11 12:05:03  | a        | 1         |
| 6       | 2023-12-11 12:01:02  | b        | 1         |
| 7       | 2023-12-11 12:03:03  | b        | 0         |
| 8       | 2023-12-11 12:04:01  | b        | 1         |
| 9       | 2023-12-11 12:07:03  | b        | 1         |
+---------+----------------------+----------+-----------+

请查询出每笔订单的上一笔有效订单,期望查询结果如下：

+---------+----------------------+----------+-----------+--------------------+
| ord_id  |       ord_time       | user_id  | is_valid  | last_valid_ord_id  |
+---------+----------------------+----------+-----------+--------------------+
| 1       | 2023-12-11 12:01:03  | a        | 1         | NULL               |
| 2       | 2023-12-11 12:02:06  | a        | 0         | 1                  |
| 3       | 2023-12-11 12:03:15  | a        | 0         | 1                  |
| 4       | 2023-12-11 12:04:20  | a        | 1         | 1                  |
| 5       | 2023-12-11 12:05:03  | a        | 1         | 4                  |
| 6       | 2023-12-11 12:01:02  | b        | 1         | NULL               |
| 7       | 2023-12-11 12:03:03  | b        | 0         | 6                  |
| 8       | 2023-12-11 12:04:01  | b        | 1         | 6                  |
| 9       | 2023-12-11 12:07:03  | b        | 1         | 8                  |
+---------+----------------------+----------+-----------+--------------------+
1 数据准备
create table t5_order
(
ord_id bigint COMMENT '订单ID',
ord_time string COMMENT '订单时间',
user_id string COMMENT '用户',
is_valid bigint COMMENT '订单是否有效'
);
-- 数据插入
insert overwrite table t5_order values
    (1,'2023-12-11 12:01:03','a',1),
    (2,'2023-12-11 12:02:06','a',0),
    (3,'2023-12-11 12:03:15','a',0),
    (4,'2023-12-11 12:04:20','a',1),
    (5,'2023-12-11 12:05:03','a',1),
    (6,'2023-12-11 12:01:02','b',1),
    (7,'2023-12-11 12:03:03','b',0),
    (8,'2023-12-11 12:04:01','b',1),
    (9,'2023-12-11 12:07:03','b',1);
2 数据分析
with tmp as
   (select
      ord_id,
         ord_time,
         user_id,
         is_valid,
        lag (ord_id,1,null) over(partition by user_id order by ord_time) last_valid_ord_id
    from t5_order
    where is_valid = 1)

select
     ord_id,
     ord_time,
     user_id,
     is_valid,
     last_valid_ord_id
from
   (
   select
        t5.*,
        tmp.last_valid_ord_id,
        row_number()over(partition by t5.ord_id,t5.user_id  order by tmp.ord_time ) rn
   from t5_order t5
   left join tmp
   on   t5.user_id = tmp.user_id
   where tmp.ord_time >= t5.ord_time
   )t6
where rn = 1;

思路分析： 

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

略

==========================*****==========================

共同使用ip用户检测

0 问题描述
现有用户登录日志表，记录了每个用户登录的IP地址，请查询共同使用过3个及以上IP的用户对；
+----------+-----------------+----------------------+
| user_id  |       ip        |      time_stamp      |
+----------+-----------------+----------------------+
| 2        | 223.104.41.101  | 2023-08-24 07:00:00  |
| 4        | 223.104.41.122  | 2023-08-24 10:00:00  |
| 5        | 223.104.41.126  | 2023-08-24 11:00:00  |
| 4        | 223.104.41.126  | 2023-08-24 13:00:00  |
| 1        | 223.104.41.101  | 2023-08-24 16:00:00  |
| 3        | 223.104.41.101  | 2023-08-24 16:02:00  |
| 2        | 223.104.41.104  | 2023-08-24 16:30:00  |
| 1        | 223.104.41.121  | 2023-08-24 17:00:00  |
| 2        | 223.104.41.122  | 2023-08-24 17:05:00  |
| 3        | 223.104.41.103  | 2023-08-24 18:11:00  |
| 2        | 223.104.41.103  | 2023-08-24 19:00:00  |
| 1        | 223.104.41.104  | 2023-08-24 19:00:00  |
| 3        | 223.104.41.122  | 2023-08-24 19:07:00  |
| 1        | 223.104.41.122  | 2023-08-24 21:00:00  |
+----------+-----------------+----------------------+
1 数据准备
CREATE TABLE t6_login_log (
   user_id bigint COMMENT '用户ID',
   ip string COMMENT '用户登录ip地址',
   time_stamp string COMMENT '登录时间'
) COMMENT '用户登录记录表';

 -- 插入数据
insert overwrite table t6_login_log
values
   (1,'223.104.41.101','2023-08-24 16:00:00'),
   (1,'223.104.41.121','2023-08-24 17:00:00'),
   (1,'223.104.41.104','2023-08-24 19:00:00'),
   (1,'223.104.41.122','2023-08-24 21:00:00'),
   (1,'223.104.41.122','2023-08-24 22:00:00'),
   (2,'223.104.41.101','2023-08-24 07:00:00'),
   (2,'223.104.41.103','2023-08-24 19:00:00'),
   (2,'223.104.41.104','2023-08-24 16:30:00'),
   (2,'223.104.41.122','2023-08-24 17:05:00'),
   (3,'223.104.41.103','2023-08-24 18:11:00'),
   (3,'223.104.41.122','2023-08-24 19:07:00'),
   (3,'223.104.41.101','2023-08-24 16:02:00'),
   (4,'223.104.41.126','2023-08-24 13:00:00'),
   (5,'223.104.41.126','2023-08-24 11:00:00'),
   (4,'223.104.41.122','2023-08-24 10:00:00');
2 数据分析
-- step1: 针对用户登录记录，按照用户ID和登录IP去重
with tmp as
 (
    select
         user_id,
         ip
    from t6_login_log
    group by user_id,ip
    )

select
     t2.user_id,
     t1.user_id
from tmp t1
join tmp t2
-- 自关联，会导致使用相同IP的用户，出现1-2、2-1、1-1、2-2记录（1,2 代表user_id），显然只需要保留 1-2的记录（2-1跟1-2意思一样，保留一个即可；但是1-1，2-2直接过滤掉）
on t1.ip = t2.ip
-- step2:  通过IP地址进行自关联，去重，剔除相同用户
where t2.user_id >  t1.user_id
group by t2.user_id,t1.user_id
having count(t1.ip) >= 3;

思路分析： 

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

略

==========================*****==========================

二、百度

合并用户浏览行为

0 问题描述
有一份用户访问记录表，记录用户id和访问时间，如果用户访问时间间隔小于60s则认为时一次浏览，请合并用户的浏览行为。

+----------+--------------+
| user_id  | access_time  |
+----------+--------------+
| 1        | 1736337600   |
| 1        | 1736337660   |
| 2        | 1736337670   |
| 1        | 1736337710   |
| 3        | 1736337715   |
| 2        | 1736337750   |
| 1        | 1736337760   |
| 3        | 1736337820   |
| 2        | 1736337850   |
| 1        | 1736337910   |
+----------+--------------+
1 数据准备
CREATE TABLE t2_user_access_log (
  user_id bigint COMMENT '用户ID',
  access_time bigint COMMENT '访问时间'
) COMMENT '用户访问记录表';

--插入数据
insert overwrite table t2_user_access_log
values
    (1,1736337600),
    (1,1736337660),
    (2,1736337670),
    (1,1736337710),
    (3,1736337715),
    (2,1736337750),
    (1,1736337760),
    (3,1736337820),
    (2,1736337850),
    (1,1736337910);
2 数据分析
select
        user_id,
        access_time,
        last_access_time,
        sum(is_new_group)over(partition by user_id order by access_time) as group_id
 from
(
   select
        user_id,
        access_time,
        last_access_time,
        -- 1736337710 (时间戳 10位，单位是秒)
        if(access_time - last_access_time >= 60,1,0)  as is_new_group
    from
   (
     select
         user_id,
          access_time,
          lag(access_time,1,null) over(partition by user_id order by access_time) as last_access_time
     from t2_user_access_log
   )t1
)t2

思路分析：

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

  对上下两行数据进行比较，一般采用lag 、 lead这种开窗函数。

==========================*****==========================

连续签到领金币

0 问题描述
有用户签到记录表，t4_coin_signin,记录用户当天是否完成签到,请计算出每个用户的每个月获得的金币数量；
签到领金币规则如下：

（1）用户签到获得1金币；
（2）如果用户连续签到3天则第三天获得2金币，如果用户连续签到7天则第7天获得5金币；
（3）连续签到7天后连续天数重置，每月签到天数重置；


样例数据

+----------+--------------+----------+
| user_id  | signin_date  | is_sign  |
+----------+--------------+----------+
| 001      | 2024-01-01   | 1        |
| 001      | 2024-01-02   | 1        |
| 001      | 2024-01-03   | 1        |
| 001      | 2024-01-04   | 0        |
| 001      | 2024-01-05   | 1        |
| 001      | 2024-01-06   | 1        |
| 001      | 2024-01-07   | 1        |
| 001      | 2024-01-08   | 1        |
| 001      | 2024-01-09   | 1        |
| 001      | 2024-01-10   | 1        |
| 001      | 2024-01-11   | 1        |
| 001      | 2024-01-12   | 1        |
| 001      | 2024-01-13   | 1        |
| 001      | 2024-01-14   | 1        |
| 001      | 2024-01-15   | 1        |
| 001      | 2024-01-16   | 1        |
| 001      | 2024-01-17   | 1        |
| 001      | 2024-01-18   | 1        |
| 001      | 2024-01-19   | 1        |
| 001      | 2024-01-20   | 0        |
| 001      | 2024-01-21   | 1        |
| 001      | 2024-01-22   | 1        |
| 001      | 2024-01-23   | 1        |
| 001      | 2024-01-24   | 0        |
| 001      | 2024-01-25   | 1        |
| 001      | 2024-01-26   | 1        |
| 001      | 2024-01-27   | 1        |
| 001      | 2024-01-28   | 1        |
| 001      | 2024-01-29   | 0        |
| 001      | 2024-01-30   | 1        |
| 001      | 2024-01-31   | 1        |
| 001      | 2024-02-01   | 1        |
| 001      | 2024-02-02   | 1        |
| 001      | 2024-02-03   | 1        |
| 001      | 2024-02-04   | 1        |
| 001      | 2024-02-05   | 1        |
| 001      | 2024-02-06   | 1        |
| 001      | 2024-02-07   | 1        |
| 001      | 2024-02-08   | 1        |
| 001      | 2024-02-09   | 1        |
| 001      | 2024-02-10   | 1        |
+----------+--------------+----------+
1 数据准备
CREATE TABLE t4_coin_signin
(
    user_id     string COMMENT '用户ID',
    signin_date string COMMENT '日期',
    is_sign     bigint COMMENT '是否签到 1-签到，0-未签到'
) COMMENT '签到领金币记录表';

     -- 插入数据
insert overwrite tablet4_coin_signin
values ('001', '2024-01-01', 1),
       ('001', '2024-01-02', 1),
       ('001', '2024-01-03', 1),
       ('001', '2024-01-04', 0),
       ('001', '2024-01-05', 1),
       ('001', '2024-01-06', 1),
       ('001', '2024-01-07', 1),
       ('001', '2024-01-08', 1),
       ('001', '2024-01-09', 1),
       ('001', '2024-01-10', 1),
       ('001', '2024-01-11', 1),
       ('001', '2024-01-12', 1),
       ('001', '2024-01-13', 1),
       ('001', '2024-01-14', 1),
       ('001', '2024-01-15', 1),
       ('001', '2024-01-16', 1),
       ('001', '2024-01-17', 1),
       ('001', '2024-01-18', 1),
       ('001', '2024-01-19', 1),
       ('001', '2024-01-20', 0),
       ('001', '2024-01-21', 1),
       ('001', '2024-01-22', 1),
       ('001', '2024-01-23', 1),
       ('001', '2024-01-24', 0),
       ('001', '2024-01-25', 1),
       ('001', '2024-01-26', 1),
       ('001', '2024-01-27', 1),
       ('001', '2024-01-28', 1),
       ('001', '2024-01-29', 0),
       ('001', '2024-01-30', 1),
       ('001', '2024-01-31', 1),
       ('001', '2024-02-01', 1),
       ('001', '2024-02-02', 1),
       ('001', '2024-02-03', 1),
       ('001', '2024-02-04', 1),
       ('001', '2024-02-05', 1),
       ('001', '2024-02-06', 1),
       ('001', '2024-02-07', 1),
       ('001', '2024-02-08', 1),
       ('001', '2024-02-09', 1),
       ('001', '2024-02-10', 1);
2 数据分析
select
     user_id,
     signin_month,
     sum(coin_num) as month_coin_num
from
(
   select
           user_id,
           signin_month,
           signin_group,
           conn_sign_days,
           new2_conn_sign_days,
           -- case when,根据new2_conn_sign_days中签到第几天，得出每天应该得到多少金币。 计算每天得到的金币数量
           case when new2_conn_sign_days = 3 then 2
                when conn_sign_days = 7 then 5
                   else 1 end as coin_num
    from
   (
      select
           user_id,
           signin_month,
           signin_group,
           conn_sign_days,
           -- mod取模函数，处理7天重置问题,得到参与活动的实际连续第几天签到；
           mod(conn_sign_days,7) as new1_conn_sign_days,

 -- 使用mod函数，对conn_sign_days进行处理，每7天重置一次，其中0代表第7天，需要特殊处理一下。
           if(mod(conn_sign_days,7)  = 0,7,mod(conn_sign_days,7)) as new2_conn_sign_days
      from
      (
         select
                user_id,
                signin_date,
                signin_month,
                signin_group,
                -- 根据按照用户、月份、signin_group 进行分组，按照日期排序，使用count(signin_date) 计算出:每个用户每月的，截止到当前的连续签到天数。
                count(signin_date)over(partition by user_id,signin_month,signin_group  order by signin_date) as conn_sign_days
         from
          (
              select
                   user_id,
                   signin_date,
                   is_sign,
                   substr(signin_date,1,7)  as signin_month,
                   --  根据用户、月份进行分组,按照日期排序，得到一个用户连续签到的分组 signin_group
                   sum(if(is_sign = 1,0,1)) over(partition by user_id,substr(signin_date,1,7) order by signin_date)  as signin_group
              from t4_coin_signin
          )t1
         -- 筛选出：用户签到状态的数据
         where is_sign = 1
       )t2
    )t3
)t4
 group by user_id,
          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 问题描述
现有一份用户在各个地铁站进出的时间表和一份用户商场扫码行为表，其中商场扫码会存在多次，可以取最新的数据。请查询并返回出用户整个行为轨迹列表.

样例数据
t_subway_station

+----------+-------------+----------------------+----------------------+
| user_id  | station_id  |       in_time        |       out_time       |
+----------+-------------+----------------------+----------------------+
| 001      | 1           | 2024-07-01 09:01:01  | NULL                 |
| 001      | 2           | NULL                 | 2024-07-01 09:21:08  |
| 001      | 3           | 2024-07-01 11:01:41  | NULL                 |
| 001      | 4           | NULL                 | 2024-07-01 11:23:12  |
| 001      | 4           | 2024-07-01 15:33:29  | NULL                 |
| 001      | 1           | NULL                 | 2024-07-01 15:45:41  |
+----------+-------------+----------------------+----------------------+


t_market
+----------+------------+----------------------+
| user_id  | market_id  |      check_time      |
+----------+------------+----------------------+
| 001      | 1001       | 2024-07-01 10:23:04  |
| 001      | 1001       | 2024-07-01 10:25:38  |
| 001      | 1002       | 2024-07-01 10:45:01  |
| 001      | 1004       | 2024-07-01 13:56:27  |
| 001      | 1003       | 2024-07-01 14:37:24  |
+----------+------------+----------------------+

期望结果
+----------+----------------------------------+
| user_id  |            place_list            |
+----------+----------------------------------+
| 001      | 1,2,1001,1002,3,4,1004,1003,4,1  |
+----------+----------------------------------+
1 数据准备
--建表语句
CREATE TABLE t1_subway_station (
  user_id string,
  station_id string,
  in_time string,
  out_time string
);
--插入数据
insert overwrite  table  t1_subway_station
values
   ('001','1','2024-07-01 09:01:01',null),
   ('001','2',null,'2024-07-01 09:21:08'),
   ('001','3','2024-07-01 11:01:41',null),
   ('001','4',null,'2024-07-01 11:23:12'),
   ('001','4','2024-07-01 15:33:29',null),
   ('001','1',null,'2024-07-01 15:45:41');

--建表语句
CREATE TABLE t1_market (
  user_id string,
  market_id string,
  check_time string
);
--插入数据
insert overwrite  table t1_market
values
('001','1001','2024-07-01 10:23:04'),
('001','1001','2024-07-01 10:25:38'),
('001','1002','2024-07-01 10:45:01'),
('001','1004','2024-07-01 13:56:27'),
('001','1003','2024-07-01 14:37:24');
2 数据分析
select
      user_id,
     -- 利用concat_ws函数拼接字符串,使用正则表达式regexp_replace将结果时间进行剔除，得到最终结果。
     --  \d表示匹配数字字符,因此，\d{4}可以匹配任意四个数字字符。
      regexp_replace(concat_ws(',',sort1_str) ,'\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}','') as place_list
from
(
    select
           user_id,
     -- 使用collect_list对con_str进行拼接，并使用sort_array进行排序，得到顺序的时间点位信息。
           sort_array(collect_list(concat(check_time,place))) as  sort1_str
    from
    (
        select
               user_id,
               station_id                  as place,
               coalesce(in_time, out_time) as check_time
        from t1_subway_station
        group by  user_id,station_id,coalesce(in_time, out_time)
        union all
        --商场时间
        select
               user_id,
               place,
               check_time
        from (
                 select user_id,
                        market_id       as place,
        -- 根据题目要求，取出每个商场最新的时间即可。
                        max(check_time) as check_time
                 from t1_market
                 group by user_id, market_id) t
    )t1
    group by user_id
)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 问题描述
有好友关系表t1_friend，记录了user1_id,user2_id的好友关系对。现定义用户受欢迎程度=用户拥有的朋友总数/平台上的用户总数,请计算出每个用户的受欢迎程度。

+-----------+-----------+
| user1_id  | user2_id  |
+-----------+-----------+
| 1         | 2         |
| 1         | 3         |
| 1         | 4         |
| 1         | 5         |
| 2         | 3         |
| 2         | 4         |
| 3         | 4         |
| 4         | 5         |
| 5         | 6         |
| 5         | 7         |
| 7         | 8         |
| 9         | 10        |
+-----------+-----------+
1 数据准备
--建表语句
CREATE TABLE t1_friend(
  user1_id bigint COMMENT '用户1ID',
  user2_id bigint COMMENT '用户2ID'
) COMMENT '好友关系表';

-- 插入数据
insert overwrite  table t1_friend
values
  (1,2),
  (1,3),
  (1,4),
  (1,5),
  (2,3),
  (2,4),
  (3,4),
  (4,5),
  (5,6),
  (5,7),
  (7,8),
  (9,10)
2 数据分析
with tmp as
         (select
              user1_id as  user1_id,
              user2_id as  user2_id
         from t1_friend
         union all
         select
              user2_id as  user1_id,
              user1_id as  user2_id
         from t1_friend)

select
     user1_id,
     friend_cnt / total_cnt as res
from (
     select 
            user1_id,
            count(user2_id) as friend_cnt,
            count( user1_id) over () as total_cnt
    from tmp
    group by user1_id
     )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 问题描述
现有三张表分别为：

用户关注表t1_follow(user_id,follower_id)记录用户ID及其关注的人ID
+----------+--------------+
| user_id  | follower_id  |
+----------+--------------+
| 1        | 2            |
| 1        | 4            |
| 1        | 5            |
+----------+--------------+

用户喜欢的音乐t1_music_likes(user_id,music_id)
+----------+-----------+
| user_id  | music_id  |
+----------+-----------+
| 1        | 10        |
| 2        | 20        |
| 2        | 30        |
| 3        | 20        |
| 3        | 30        |
| 4        | 40        |
| 4        | 50        |
+----------+-----------+

音乐名字表t1_music(music_id,music_name)
+-----------+-------------+
| music_id  | music_name  |
+-----------+-------------+
| 10        | a           |
| 20        | b           |
| 30        | c           |
| 40        | d           |
| 50        | e           |
+-----------+-------------+

需求：给用户1推荐他关注的用户喜欢的音乐名称
1 数据准备
--建表语句
CREATE TABLE t1_follow (
  user_id bigint COMMENT '用户ID',
  follower_id bigint COMMENT '其关注的用户ID'
) COMMENT '用户关注表';
     
-- 插入数据
insert overwrite table  t1_follow
values
   (1,2),
   (1,4),
   (1,5);

-- 建表语句
CREATE TABLE t1_music_likes (
  user_id bigint COMMENT '用户ID',
  music_id bigint COMMENT '音乐ID'
) COMMENT '用户喜欢音乐ID';

--插入语句
insert overwrite table t1_music_likes
values
  (1,10),
  (2,20),
  (2,30),
  (3,20),
  (3,30),
  (4,40),
  (4,50);
     
     
--建表语句
CREATE TABLE t1_music (
   music_id bigint COMMENT '音乐ID',
   music_name string COMMENT '音乐名称'
) COMMENT '音乐名字表';
     
-- 插入语句
insert overwrite table t1_music
values
   (10,'a'),
   (20,'b'),
   (30,'c'),
   (40,'d'),
   (50,'e');
2 数据分析
select
     t4.user_id,
     concat_ws(',', collect_set(t4.music_name)) as push_music
from
    (
        select
             t1.user_id,
             t1.follower_id,
             t2.music_id,
             t3.music_name
        from t1_follow t1
        left join t1_music_likes t2
         on t1.follower_id  = t2.user_id
         left join t1_music t3
        on  t2.music_id = t3.music_id
         where t1.user_id = 1
    )t4
group by t1.user_id;

思路分析：

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

略

==========================*****==========================

连续登陆超过N天的用户

0 问题描述
现有用户登录日志表 t5_login_log,包含用户ID(user_id),登录日期(login_date)。
数据已经按照用户日期去重，请计算 连续登录超过4天的用户ID。

样例数据
+----------+-------------+
| user_id  | login_date  |
+----------+-------------+
| 0001     | 20220101    |
| 0001     | 20220102    |
| 0001     | 20220103    |
| 0001     | 20220104    |
| 0001     | 20220105    |
| 0001     | 20220107    |
| 0001     | 20220108    |
| 0001     | 20220109    |
| 0002     | 20220101    |
| 0002     | 20220102    |
| 0002     | 20220103    |
| 0002     | 20220107    |
| 0002     | 20220108    |
| 0003     | 20220107    |
| 0003     | 20220108    |
| 0003     | 20220109    |
+----------+-------------+
1 数据准备
-- 建表语句
create table t5_login_log
(
   user_id string comment '用户ID',
   login_date string comment '登录日期'
);

--数据插入语句
insert overwrite table  t5_login_log values
   ('0001','20220101'),
   ('0001','20220102'),
   ('0001','20220103'),
   ('0001','20220104'),
   ('0001','20220105'),
   ('0001','20220107'),
   ('0001','20220108'),
   ('0001','20220109'),
   ('0002','20220101'),
   ('0002','20220102'),
   ('0002','20220103'),
   ('0002','20220107'),
   ('0002','20220108'),
   ('0003','20220107'),
   ('0003','20220108'),
   ('0003','20220109');
2 数据分析

select
     user_id
from
  (
      select
           user_id,
           new_login_date,
           rn,
           date_sub(new_login_date,rn) as diff
      from
          (
            select
                user_id,
                login_date,
                unix_timestamp(login_date, 'yyyyMMdd') as login_ts,
                from_unixtime( unix_timestamp(login_date, 'yyyyMMdd'),'yyyy-MM-dd') as new_login_date,
                row_number() over(partition by user_id order by login_date ) as rn
            from t5_login_log
          )t1
  )t2
group by  user_id,diff
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 问题描述
有两个表，朋友关系表t6_user_friend，用户步数表t6_user_steps。
朋友关系表包含两个字段，用户id，用户好友的id；
用户步数表包含两个字段，用户id，用户的步数

求出：用户在好友中的排名。

朋友关系表t6_user_friend
+----------+------------+
| user_id  | friend_id  |
+----------+------------+
| 1        | 2          |
| 1        | 3          |
| 2        | 1          |
| 2        | 3          |
| 2        | 4          |
| 2        | 5          |
| 3        | 1          |
| 3        | 4          |
| 3        | 5          |
| 4        | 2          |
| 4        | 3          |
| 4        | 5          |
| 5        | 2          |
| 5        | 3          |
| 5        | 4          |
+----------+------------+

用户步数表t6_user_steps
+---------------------+-------------------+
| t6_user_steps.user_id  | t6_user_steps.steps  |
+---------------------+-------------------+
| 1                   | 100               |
| 2                   | 95                |
| 3                   | 90                |
| 4                   | 80                |
| 5                   | 10                |
+---------------------+-------------------+
1 数据准备

create table t6_user_friend
(
    user_id   INT,
    friend_id INT
);

-- 插入数据
insert overwrite table  t6_user_friend
values (1, 2),
       (1, 3),
       (2, 1),
       (2, 3),
       (2, 4),
       (2, 5),
       (3, 1),
       (3, 4),
       (3, 5),
       (4, 2),
       (4, 3),
       (4, 5),
       (5, 2),
       (5, 3),
       (5, 4);

create table t6_user_steps
(
    user_id INT,
    steps   INT
);

insert overwrite table  t6_user_steps
values (1, 100),
       (2, 95),
       (3, 90),
       (4, 80),
       (5, 10);
2 数据分析
select
     user_id,
     rn
from
    (
      select
            user_id,
            friend_id,
            steps,
            row_number() over (partition by user_id order by steps desc) as rn
     from
        (
            --好友步数
            select
                 t1.user_id,
                 t1.friend_id,
                 t2.steps
            from t6_user_friend t1
            join t6_user_steps t2
            on t1.friend_id = t2.user_id
            union all
            -- 自己步数
            select
                 user_id,
                 user_id as friend_id,
                 steps
           from t6_user_steps
       )tmp1
   )tmp2
where user_id = friend_id

思路分析：

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

略

==========================*****==========================

六、高频大数据面试SQL

每年成绩都有所提升的学生

0 问题描述
一张学生成绩表(student_scores)，有year-学年，subject-课程，student-学生，score-分数这四个字段，请完成如下问题：

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

问题2：每年总成绩都有所提升的学生

数据内容如下
+-------+----------+----------+--------+
| year  | subject  | student  | score  |
+-------+----------+----------+--------+
| 2018  | 语文       | A        | 84     |
| 2018  | 数学       | A        | 59     |
| 2018  | 英语       | A        | 30     |
| 2018  | 语文       | B        | 44     |
| 2018  | 数学       | B        | 76     |
| 2018  | 英语       | B        | 68     |
| 2019  | 语文       | A        | 51     |
| 2019  | 数学       | A        | 94     |
| 2019  | 英语       | A        | 71     |
| 2019  | 语文       | B        | 87     |
| 2019  | 数学       | B        | 44     |
| 2019  | 英语       | B        | 38     |
| 2020  | 语文       | A        | 91     |
| 2020  | 数学       | A        | 50     |
| 2020  | 英语       | A        | 89     |
| 2020  | 语文       | B        | 81     |
| 2020  | 数学       | B        | 84     |
| 2020  | 英语       | B        | 98     |
+-------+----------+----------+--------+
1 数据准备
CREATE TABLE t1_student_scores
(
    year STRING,
    subject STRING,
    student STRING,
    score INT );
-- 数据插入语句
insert overwrite  table t1_student_scores
      VALUES
    (2018, '语文', 'A', 84),
    (2018, '数学', 'A', 59),
    (2018, '英语', 'A', 30),
    (2018, '语文', 'B', 44),
    (2018, '数学', 'B', 76),
    (2018, '英语', 'B', 68),
    (2019, '语文', 'A', 51),
    (2019, '数学', 'A', 94),
    (2019, '英语', 'A', 71),
    (2019, '语文', 'B', 87),
    (2019, '数学', 'B', 44),
    (2019, '英语', 'B', 38),
    (2020, '语文', 'A', 91),
    (2020, '数学', 'A', 50),
    (2020, '英语', 'A', 89),
    (2020, '语文', 'B', 81),
    (2020, '数学', 'B', 84),
    (2020, '英语', 'B', 98);
2 数据分析

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

 --计算排名第一的人
select year,
       subject,
       first_student
from (select year,
             subject,
             first_value(student) over (partition by year,subject order by score desc) as first_student
      from t1_student_scores) t
group by year, subject, first_student;

思路分析：

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

   问题2：每年总成绩都有所提升的学生

select
      student
from
(
  select
         year,
         student,
         total_score,
         last_year_score,
         if(total_score >last_year_score,1,0 ) as improve_flag
  
  from
  (
     select
          year,
          student,
          total_score,
          lag(total_score,1,0) over(partition by student order by year ) last_year_score
     from
     (
         select
              year,
              student,
              sum(score) as total_score
         from t1_student_scores
         group by year,student
     )t1
   )t2
   where last_year_score is not null
)t3
  group by student
   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 问题描述
有用户点击日志记录表 t2_click_log,  user_id(用户ID), click_time(点击时间)， 请查询出连续点击三次的用户数，连续点击三次：指点击记录中同一用户连续点击，中间无其他用户点击；

+----------+--------------+
| user_id  | click_time |
+----------+--------------+
| 1        | 1736337600   |
| 2        | 1736337670   |
| 1        | 1736337710   |
| 1        | 1736337715   |
| 1        | 1736337750   |
| 2        | 1736337760   |
| 3        | 1736337820   |
| 3        | 1736337840   |
| 3        | 1736337850   |
| 3        | 1736337910   |
| 4        | 1736337915   |
+----------+--------------+
1 数据准备
--建表语句
CREATE TABLE t2_click_log (
  user_id BIGINT,
  click_time BIGINT
);

--插入数据
insert overwrite table t2_click_log 
(1,1736337600),
(2,1736337670),
(1,1736337710),
(1,1736337715),
(1,1736337750),
(2,1736337760),
(3,1736337820),
(3,1736337840),
(3,1736337850),
(3,1736337910),
(4,1736337915)
2 数据分析

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

select
     count(distinct user_id) as num
 from
  (
    select
         user_id,
         diff,
         count(1) as cnt
    from
    (
         select
             user_id,
             rn1 - rn2 as diff
         from (select user_id,
                     click_time,
                     row_number() over (order by click_time asc) as rn1,
                     row_number() over (partition by user_id order by click_time asc) as rn2
              from t2_click_log) t1
    )t2
         group by  user_id,diff
         having cnt >= 3
  )t3;

思路分析：

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

略

==========================*****==========================

去掉最大最小值的部门平均薪水

0 问题描述
有员工薪资表t3_salary,包含员工ID(emp_id)，部门ID(depart_id)，薪水(salary),请计算去除最高最低薪资后的平均薪水；（每个部门员工数不少于3人）
+---------+------------+-----------+
| emp_id  | depart_id  |  salary   |
+---------+------------+-----------+
| 1001    | 1          | 5000.00   |
| 1002    | 1          | 10000.00  |
| 1003    | 1          | 20000.00  |
| 1004    | 1          | 30000.00  |
| 1005    | 1          | 6000.00   |
| 1006    | 1          | 10000.00  |
| 1007    | 1          | 11000.00  |
| 1008    | 2          | 3000.00   |
| 1009    | 2          | 7000.00   |
| 1010    | 2          | 9000.00   |
| 1011    | 2          | 30000.00  |
+---------+------------+-----------+
1 数据准备
CREATE TABLE t3_salary (
  emp_id bigint,
  depart_id bigint,
  salary decimal(16,2)
);
--插入数据
insert overwrite table t3_salary
values
    (1001,1,5000.00),
    (1002,1,10000.00),
    (1003,1,20000.00),
    (1004,1,30000.00),
    (1005,1,6000.00),
    (1006,1,10000.00),
    (1007,1,11000.00),
    (1008,2,3000.00),
    (1009,2,7000.00),
    (1010,2,9000.00),
    (1011,2,30000.00);
2 数据分析
select
     depart_id ,
     avg(salary)  as avg_salary
from
  (
    select
         emp_id    ,
         depart_id ,
         salary    ,
         row_number()over(partition by depart_id order by salary ) as rn1,
         row_number()over(partition by depart_id order by salary desc) as rn2
    from t3_salary
  )t1
 where rn1 > 1 and  rn2 > 1
group by depart_id

思路分析：

  • step1: 进行两次开窗，排序来去掉 部门内的最高最低薪资
  • step2：按照部门分组进行分组取平均即可；
3 小结

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

==========================*****==========================

当前活跃用户连续活跃天数

0 问题描述
有用户登录日志表，包含日期、用户ID，当天是否登录，请查询出当天活跃的用户当前连续活跃天数；

+-------------+----------+-----------+
| login_date  | user_id  | is_login  |
+-------------+----------+-----------+
| 2023-08-01  | 1        | 1         |
| 2023-08-01  | 2        | 1         |
| 2023-08-01  | 3        | 1         |
| 2023-08-01  | 4        | 0         |
| 2023-08-02  | 1        | 1         |
| 2023-08-02  | 2        | 0         |
| 2023-08-02  | 3        | 1         |
| 2023-08-02  | 4        | 1         |
| 2023-08-03  | 1        | 1         |
| 2023-08-03  | 2        | 1         |
| 2023-08-03  | 3        | 0         |
| 2023-08-03  | 4        | 1         |
+-------------+----------+-----------+
1 数据准备
--建表语句
CREATE TABLE t4_login_log (
  login_date string COMMENT '日期',
  user_id bigint COMMENT '用户ID',
  is_login bigint COMMENT '是否登录'
) COMMENT '用户签到记录表';

--插入数据
insert overwrite table  t4_login_log
values
  ('2023-08-01',1,1),
  ('2023-08-01',2,1),
  ('2023-08-01',3,1),
  ('2023-08-01',4,0),
  ('2023-08-02',1,1),
  ('2023-08-02',2,0),
  ('2023-08-02',3,1),
  ('2023-08-02',4,1),
  ('2023-08-03',1,1),
  ('2023-08-03',2,1),
  ('2023-08-03',3,0),
  ('2023-08-03',4,1);
2 数据分析
select
       t1.user_id,
       count(1) as login_days
from t4_login_log t1
 left join
  (
    select
         user_id,
         max(login_date)as latest_unlogin_date
    from t4_login_log
    where is_login = 0
    group by user_id
  )t2
 on t1.user_id = t2.user_id
  where t1.login_date > coalesce(t2.latest_unlogin_date,'1970-01-01')
     group by t1.user_id;

思路分析：

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

==========================*****==========================

销售额连续3天增长的商户

0 问题描述
有一张订单记录表 t5_order 包含 订单ID(order_id),商户ID(shop_id),订单时间(order_time)和订单金额(order_amt),请查询出过去至少存在3天销售额连续增长的商户

+-----------+----------+----------------------+------------+
| order_id  | shop_id  |      order_time      | order_amt  |
+-----------+----------+----------------------+------------+
| 1         | 1001     | 2023-08-21 09:01:00  | 9.99       |
| 2         | 1001     | 2023-08-22 10:00:00  | 19.99      |
| 3         | 1001     | 2023-08-22 13:00:00  | 8.88       |
| 4         | 1001     | 2023-08-23 08:00:00  | 29.99      |
| 5         | 1001     | 2023-08-23 09:00:00  | 19.99      |
| 6         | 1001     | 2023-08-24 11:00:00  | 99.99      |
| 7         | 1001     | 2023-08-25 15:00:00  | 1.99       |
| 8         | 1001     | 2023-08-26 16:00:00  | 2.99       |
| 9         | 1001     | 2023-08-26 17:00:00  | 95.99      |
| 10        | 1002     | 2023-08-21 09:00:00  | 9.99       |
| 11        | 1002     | 2023-08-22 11:00:00  | 1.99       |
| 12        | 1002     | 2023-08-22 11:01:00  | 19.99      |
| 13        | 1002     | 2023-08-22 12:05:00  | 14.99      |
| 14        | 1002     | 2023-08-22 13:00:00  | 6.99       |
| 15        | 1002     | 2023-08-23 14:00:00  | 99.99      |
| 16        | 1002     | 2023-08-24 13:00:00  | 19.99      |
| 17        | 1002     | 2023-08-25 09:00:00  | 19.99      |
| 18        | 1002     | 2023-08-25 11:00:00  | 5.99       |
| 19        | 1002     | 2023-08-25 13:00:00  | 6.99       |
| 20        | 1002     | 2023-08-25 13:07:00  | 7.0        |
| 21        | 1002     | 2023-08-25 15:00:00  | 10.0       |
| 22        | 1002     | 2023-08-26 07:00:00  | 9.99       |
| 23        | 1003     | 2023-08-21 13:07:00  | 7.0        |
| 24        | 1003     | 2023-08-22 15:00:00  | 8.0        |
| 25        | 1003     | 2023-08-23 07:00:00  | 9.99       |
| 26        | 1003     | 2023-08-25 13:07:00  | 10.0       |
| 27        | 1003     | 2023-08-26 15:00:00  | 11.0       |
+-----------+----------+----------------------+------------+
1 数据准备
--建表语句
CREATE TABLE t5_order (
order_id bigint COMMENT '订单ID',
shop_id bigint COMMENT '商户ID',
order_time string COMMENT '订单时间',
order_amt double COMMENT '订单金额'
) COMMENT '订单记录表';
-- 插入数据

insert overwrite table  t5_order
values
   (1,1001,'2023-08-21 09:01:00',9.99),
   (2,1001,'2023-08-22 10:00:00',19.99),
   (3,1001,'2023-08-22 13:00:00',8.88),
   (4,1001,'2023-08-23 08:00:00',29.99),
   (5,1001,'2023-08-23 09:00:00',19.99),
   (6,1001,'2023-08-24 11:00:00',99.99),
   (7,1001,'2023-08-25 15:00:00',1.99),
   (8,1001,'2023-08-26 16:00:00',2.99),
   (9,1001,'2023-08-26 17:00:00',95.99),
   (10,1002,'2023-08-21 09:00:00',9.99),
   (11,1002,'2023-08-22 11:00:00',1.99),
   (12,1002,'2023-08-22 11:01:00',19.99),
   (13,1002,'2023-08-22 12:05:00',14.99),
   (14,1002,'2023-08-22 13:00:00',6.99),
   (15,1002,'2023-08-23 14:00:00',99.99),
   (16,1002,'2023-08-24 13:00:00',19.99),
   (17,1002,'2023-08-25 09:00:00',19.99),
   (18,1002,'2023-08-25 11:00:00',5.99),
   (19,1002,'2023-08-25 13:00:00',6.99),
   (20,1002,'2023-08-25 13:07:00',7.00),
   (21,1002,'2023-08-25 15:00:00',10.00),
   (22,1002,'2023-08-26 07:00:00',9.99),
   (23,1003,'2023-08-21 13:07:00',7.00),
   (24,1003,'2023-08-22 15:00:00',8.00),
   (25,1003,'2023-08-23 07:00:00',9.99),
   (26,1003,'2023-08-25 13:07:00',10.00),
   (27,1003,'2023-08-26 15:00:00',11.00);
2 数据分析
with tmp1 as
   (
     select
        shop_id,
        to_date(order_time) as order_date,
        sum(order_amt) as order_amt
     from t5_order
     group by shop_id,to_date(order_time)
   ),
tmp2 as
  (
    select
        shop_id,
        order_date,
        order_amt,
        order_amt - lag(order_amt,1,null) over (partition by shop_id order by order_date) as order_amt_diff
    from tmp1
  )

select
     shop_id
from
  (
       select
             shop_id,
             order_date,
             date_sub(order_date,row_number() over(partition by shop_id order by order_date)) as diff2
       from tmp2
       where order_amt_diff >0
  )tmp3
 group by shop_id,diff2
  having count(1) >=3;

思路分析：

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

==========================*****==========================

不及格课程数大于2的学生的平均成绩及其排名

0 问题描述
有张 学生每科科目成绩表，求不及格课程数大于2的学生的平均成绩及其成绩平均值后所在的排名。

+------+------+--------+
| sid  | cid  | score  |
+------+------+--------+
| 1    | 1    | 90     |
| 1    | 2    | 50     |
| 1    | 3    | 72     |
| 2    | 1    | 40     |
| 2    | 2    | 50     |
| 2    | 3    | 22     |
| 3    | 1    | 30     |
| 3    | 2    | 50     |
| 3    | 3    | 52     |
| 4    | 1    | 90     |
| 4    | 2    | 90     |
| 4    | 3    | 72     |
+------+------+--------+
1 数据准备
--建表语句
CREATE TABLE t6_scores (
sid bigint COMMENT '学生ID',
cid bigint COMMENT '课程ID',
score bigint COMMENT '得分'
) COMMENT '用户课程分数';
-- 插入数据
insert overwrite table t6_scores
values
  (1,1,90),
  (1,2,50),
  (1,3,72),
  (2,1,40),
  (2,2,50),
  (2,3,22),
  (3,1,30),
  (3,2,50),
  (3,3,52),
  (4,1,90),
  (4,2,90),
  (4,3,72)
2 数据分析
select
       sid,
       avg_score,
       rn
from
    (
        select
             sid,
             avg_score,
             fail_num,
             dense_rank() over(order by avg_score desc) as rn
        from
        (
          select
               sid,
               avg(score) as avg_score,
               sum(case when score <60 then 1 else 0 end ) as fail_num
          from t6_scores
          group by sid
        )t1
    )t2
  where fail_num > 2;

思路分析：

  • step1：计算出每个学生的平均成绩、不及格的科目数；
  • step2：根据平均成绩计算排名；
3 小结

==========================*****==========================

计算次日留存率

0 问题描述
有一张 用户登录记录表，已经按照用户日期进行去重处理。以用户登录的最早日期作为当日的新增用户日期，请计算次日留存率是多少。

指标定义：

次日留存用户：基准日新增的用户在第二天又登录（活跃）；

次日留存率：t+1日留存用户数/t日新增用户；


+----------+-------------+
| user_id  | login_date  |
+----------+-------------+
| aaa      | 2023-12-01  |
| bbb      | 2023-12-01  |
| bbb      | 2023-12-02  |
| ccc      | 2023-12-02  |
| bbb      | 2023-12-03  |
| ccc      | 2023-12-03  |
| ddd      | 2023-12-03  |
| ccc      | 2023-12-04  |
| ddd      | 2023-12-04  |
+----------+-------------+
1 数据准备
create table t7_login
(
user_id string COMMENT '用户ID',
login_date string COMMENT '登录日期'
) COMMENT '用户登录记录表';

insert overwrite table t7_login
values
  ('aaa','2023-12-01'),
  ('bbb','2023-12-01'),
  ('bbb','2023-12-02'),
  ('ccc','2023-12-02'),
  ('bbb','2023-12-03'),
  ('ccc','2023-12-03'),
  ('ddd','2023-12-03'),
  ('ccc','2023-12-04'),
  ('ddd','2023-12-04');
2 数据分析
select
     first_day,
     concat(per * 100,'%') as next_act_per
from
   (
       select
             first_day,
            if(count(case when date_diff = 0 then user_id end) = 0, 0,
                   count(case when date_diff = 1 then user_id end) / count(case when date_diff = 0 then user_id end))  per
      from
       (
          select
              user_id,
              min(login_date)over(partition by user_id order by login_date) as first_day,
              datediff(login_date,min(login_date)over(partition by user_id order by login_date) ) as date_diff
          from t7_login
       )t1
      group by first_day
   )t2;

思路分析：

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

==========================*****==========================

按照顺序进行行转列拼接

0 问题描述
已知有表中含有两列数据id，val,数据内容如下，请按照id的大小将val进行拼接。

+-----+------+
| id  | val  |
+-----+------+
| 1   | 20   |
| 2   | 10   |
| 8   | 120  |
| 9   | 30   |
| 11  | 50   |
| 22  | 40   |
+-----+------+
1 数据准备
--建表语句
create table t8_concat_ordered
(
id bigint COMMENT '用户ID',
val string COMMENT '登录日期'
) COMMENT '用户登录记录表';
--插入数据
insert overwrite table  t8_concat_ordered
values
  (1,'20'),
  (2,'10'),
  (8,'120'),
  (9,'30'),
  (11,'50'),
  (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博客

select regexp_replace(
   concat_ws(',' , sort_array( collect_list( concat_ws(':',lpad(id,5,0),val)))),'\\d+\:',''
   )
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 问题描述
有学生每科科目成绩，找出所有科目成绩都大于对应学科的平均成绩的学生

+------+------+--------+
| sid  | cid  | score  |
+------+------+--------+
| 1    | 1    | 90     |
| 1    | 2    | 50     |
| 1    | 3    | 72     |
| 2    | 1    | 40     |
| 2    | 2    | 50     |
| 2    | 3    | 22     |
| 3    | 1    | 30     |
| 3    | 2    | 50     |
| 3    | 3    | 52     |
| 4    | 1    | 90     |
| 4    | 2    | 90     |
| 4    | 3    | 72     |
+------+------+--------+
1 数据准备
CREATE TABLE t9_scores (
  sid bigint COMMENT '学生ID',
  cid bigint COMMENT '课程ID',
  score bigint COMMENT '得分'
) COMMENT '用户课程分数';
-- 插入数据
insert overwrite table  t9_scores
values
   (1,1,90),
   (1,2,50),
   (1,3,72),
   (2,1,40),
   (2,2,50),
   (2,3,22),
   (3,1,30),
   (3,2,50),
   (3,3,52),
   (4,1,90),
   (4,2,90),
   (4,3,72)
2 数据分析
select 
     sid
from (select
             sid,
             cid,
             score,
             avg_score,
             if(score > avg_score, 0, 1) as flag
      from (select 
                   sid,
                   cid,
                   score,
                   avg(score) over (partition by cid) as avg_score
            from t9_scores) t1) t2
group by sid
having sum(flag) = 0;
3 小结

==========================*****==========================

用户行为路径分析

0 问题描述
有一张用户操作行为记录表 t10_act_log 包含用户ID(user_id),操作编号(op_id),操作时间(op_time)

要求:
 1.统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻;
 2.统计每天用户行为序列为A-B-D的用户数;其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)

+----------+--------+----------------------+
| user_id  | op_id  |       op_time        |
+----------+--------+----------------------+
| 1        | A      | 2023-10-18 12:01:03  |
| 2        | A      | 2023-10-18 12:01:04  |
| 3        | A      | 2023-10-18 12:01:05  |
| 1        | B      | 2023-10-18 12:03:03  |
| 1        | A      | 2023-10-18 12:04:03  |
| 1        | C      | 2023-10-18 12:06:03  |
| 1        | D      | 2023-10-18 12:11:03  |
| 2        | A      | 2023-10-18 12:07:04  |
| 3        | C      | 2023-10-18 12:02:05  |
| 2        | C      | 2023-10-18 12:09:03  |
| 2        | A      | 2023-10-18 12:10:03  |
| 4        | A      | 2023-10-18 12:01:03  |
| 4        | C      | 2023-10-18 12:11:05  |
| 4        | D      | 2023-10-18 12:15:05  |
| 1        | A      | 2023-10-19 12:01:03  |
| 2        | A      | 2023-10-19 12:01:04  |
| 3        | A      | 2023-10-19 12:01:05  |
| 1        | B      | 2023-10-19 12:03:03  |
| 1        | A      | 2023-10-19 12:04:03  |
| 1        | C      | 2023-10-19 12:06:03  |
| 2        | A      | 2023-10-19 12:07:04  |
| 3        | B      | 2023-10-19 12:08:05  |
| 3        | E      | 2023-10-19 12:09:05  |
| 3        | D      | 2023-10-19 12:11:05  |
| 2        | C      | 2023-10-19 12:09:03  |
| 4        | E      | 2023-10-19 12:05:03  |
| 4        | B      | 2023-10-19 12:06:03  |
| 4        | E      | 2023-10-19 12:07:03  |
| 2        | A      | 2023-10-19 12:10:03  |
+----------+--------+----------------------+
1 数据准备
create table t10_act_log(
    user_id bigint  comment'用户ID',
    op_id   string  comment'操作编号',
    op_time string  comment'操作时间'
  );

insert overwrite table  t10_act_log
values
   (1, 'A', '2023-10-18 12:01:03'),
   (2, 'A', '2023-10-18 12:01:04'),
   (3, 'A', '2023-10-18 12:01:05'),
   (1, 'B', '2023-10-18 12:03:03'),
   (1, 'A', '2023-10-18 12:04:03'),
   (1, 'C', '2023-10-18 12:06:03'),
   (1, 'D', '2023-10-18 12:11:03'),
   (2, 'A', '2023-10-18 12:07:04'),
   (3, 'C', '2023-10-18 12:02:05'),
   (2, 'C', '2023-10-18 12:09:03'),
   (2, 'A', '2023-10-18 12:10:03'),
   (4, 'A', '2023-10-18 12:01:03'),
   (4, 'C', '2023-10-18 12:11:05'),
   (4, 'D', '2023-10-18 12:15:05'),
   (1, 'A', '2023-10-19 12:01:03'),
   (2, 'A', '2023-10-19 12:01:04'),
   (3, 'A', '2023-10-19 12:01:05'),
   (1, 'B', '2023-10-19 12:03:03'),
   (1, 'A', '2023-10-19 12:04:03'),
   (1, 'C', '2023-10-19 12:06:03'),
   (2, 'A', '2023-10-19 12:07:04'),
   (3, 'B', '2023-10-19 12:08:05'),
   (3, 'E', '2023-10-19 12:09:05'),
   (3, 'D', '2023-10-19 12:11:05'),
   (2, 'C', '2023-10-19 12:09:03'),
   (4, 'E', '2023-10-19 12:05:03'),
   (4, 'B', '2023-10-19 12:06:03'),
   (4, 'E', '2023-10-19 12:07:03'),
   (2, 'A', '2023-10-19 12:10:03');
2 数据分析

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


select
     dt,
     count(1) as cnt
from(
       select
             user_id,
             dt,
             op_sort2
       from
          (
             select
                   user_id,
                   dt,
                   regexp_replace(op_sort1,
                                    '(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\|)', '') as op_sort2
              from
                 (
                     select
                          user_id,
                          dt,
                          sort_array(collect_list(op_str)) as sort1,
                          concat_ws(',', sort_array(collect_list(op_str))) as op_sort1
                     from
                      (
                           select
                             user_id ,
                             op_id    ,
                             op_time  ,
                             to_date(op_time)  as dt,
                             concat_ws('|',op_time, op_id) as op_str
                           from t10_act_log
                      )t1
                     group by user_id, dt
                 )t2
          )t3
      where op_sort2 like '%A,B%'
     )t4
 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等)


select
     dt,
     count(1) as cnt
from(
       select
             user_id,
             dt,
             op_sort2
       from
          (
             select
                   user_id,
                   dt,
                   regexp_replace(op_sort1,
                                    '(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\|)', '') as op_sort2
              from
                 (
                     select
                          user_id,
                          dt,
                          sort_array(collect_list(op_str)) as sort1,
                          concat_ws(',', sort_array(collect_list(op_str))) as op_sort1
                     from
                      (
                           select
                             user_id ,
                             op_id    ,
                             op_time  ,
                             to_date(op_time)  as dt,
                             concat_ws('|',op_time, op_id) as op_str
                           from t10_act_log
                      )t1
                     group by user_id, dt
                 )t2
          )t3
       where op_sort2 like '%A%B%D%' and op_sort2 not like '%A%B%C%D%'
     )t4
 group by dt;
3 小结
  1. 按照顺序拼接字符串；
  2. ​包含'A,B';
  3. 包含’A%B%D'并且不能是‘A%B%C%D’

=================*****==========================

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

0 问题描述
有一张 用户账户表，包含年份，用户id和值,请按照年份分组，取出值前两小和前两大对应的用户id。
注意：需要保持值最小和最大的用户id排首位。

样例数据
+-------+----------+--------+
| year  | user_id  | value  |
+-------+----------+--------+
| 2022  | A        | 30     |
| 2022  | B        | 10     |
| 2022  | C        | 20     |
| 2023  | A        | 40     |
| 2023  | B        | 50     |
| 2023  | C        | 20     |
| 2023  | D        | 30     |
+-------+----------+--------+

期望结果
+-------+-----------------+-----------------+
| year  | max2_user_list  | min2_user_list  |
+-------+-----------------+-----------------+
| 2022  | A,C             | B,C             |
| 2023  | B,A             | C,D             |
+-------+-----------------+-----------------+
1 数据准备
--建表语句
create table if not exists t12_amount
(
    year    string comment '年份',
    user_id string comment '用户id',
    value   bigint comment '数值'
);

--插入数据
insert  overwrite table t12_amount
values ('2022', 'A', 30),
       ('2022', 'B', 10),
       ('2022', 'C', 20),
       ('2023', 'A', 40),
       ('2023', 'B', 50),
       ('2023', 'C', 20),
       ('2023', 'D', 30)
2 数据分析
select
     concat(max1_user_id,',',max2_user_id) as max_user_list,
     concat(min1_user_id,',',min2_user_id) as min_user_id
from
  (
      select
            year,
           max(if(rn1 = 1,user_id,null)) as max1_user_id,
           max(if(rn1 = 2,user_id,null)) as max2_user_id,
           max(if(rn2 = 1,user_id,null)) as min1_user_id,
           max(if(rn2 = 2,user_id,null)) as min2_user_id
       from
      (
          select
               year,
               user_id,
               value,
               row_number()over(partition by year order by value desc) as rn1,
               row_number()over(partition by year order by value ) as rn2
          from t12_amount
      )t1
      group by year
  )t2

思路分析：

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

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

==========================*****==========================

查询每个学科第三名的学生的学科成绩总成绩及总排名

0 问题描述
有一张学生成绩表，包含学生姓名、学科、成绩三个字段，请用一条SQL查询出每个学科排名第三名的学生，他的学科成绩、总成绩、以及总排名。

样例数据
+----------+----------+--------+
| student  | subject  | score  |
+----------+----------+--------+
| 张三       | 语文       | 95     |
| 李四       | 语文       | 90     |
| 王五       | 语文       | 88     |
| 赵六       | 语文       | 77     |
| 张三       | 数学       | 80     |
| 李四       | 数学       | 90     |
| 王五       | 数学       | 92     |
| 赵六       | 数学       | 84     |
| 张三       | 英语       | 82     |
| 李四       | 英语       | 93     |
| 王五       | 英语       | 88     |
| 赵六       | 英语       | 68     |
+----------+----------+--------+
1 数据准备
--建表语句
create table if not exists t13_student_score
(
    student string,
    subject string,
    score   bigint
);
 
--插入数据
 
insert overwrite table t13_student_score
values ('张三', '语文', 95),
       ('李四', '语文', 90),
       ('王五', '语文', 88),
       ('赵六', '语文', 77),
       ('张三', '数学', 80),
       ('李四', '数学', 90),
       ('王五', '数学', 92),
       ('赵六', '数学', 84),
       ('张三', '英语', 82),
       ('李四', '英语', 93),
       ('王五', '英语', 88),
       ('赵六', '英语', 68);
2 数据分析

select
      student,
      subject,
      score,
      total_score,
      total_rn
from
   (
      select
           student,
           subject,
           score  ,
           subject_rn,
           total_score,
           row_number() over(partition by subject order by total_score desc) as total_rn
      from
         (
          select
               student,
               subject,
               score  ,
               row_number() over (partition by subject order by score desc) as subject_rn,
               sum(score) over (partition by student) as total_score
          from t13_student_score
         )t1
   )t2
   where subject_rn = 3;

思路分析：

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

==========================*****==========================

各用户最长的连续登录天数-可间断

0 问题描述
现有各用户的登录记录表t14_login_events如下，表中每行数据表达的信息是一个用户何时登录了平台。 
现要求统计【各用户最长的连续登录天数，间断一天也算作连续】，例如：一个用户在1,3,5,6登录，则视为连续6天登录。

样例数据
+----------+----------------------+
| user_id  |    login_datetime    |
+----------+----------------------+
| 100      | 2021-12-01 19:00:00  |
| 100      | 2021-12-01 19:30:00  |
| 100      | 2021-12-02 21:01:00  |
| 100      | 2021-12-03 11:01:00  |
| 101      | 2021-12-01 19:05:00  |
| 101      | 2021-12-01 21:05:00  |
| 101      | 2021-12-03 21:05:00  |
| 101      | 2021-12-05 15:05:00  |
| 101      | 2021-12-06 19:05:00  |
| 102      | 2021-12-01 19:55:00  |
| 102      | 2021-12-01 21:05:00  |
| 102      | 2021-12-02 21:57:00  |
| 102      | 2021-12-03 19:10:00  |
| 104      | 2021-12-04 21:57:00  |
| 104      | 2021-12-02 22:57:00  |
| 105      | 2021-12-01 10:01:00  |
+----------+----------------------+
期望结果
+----------+---------------+
| user_id  | max_log_days  |
+----------+---------------+
| 100      | 3             |
| 101      | 6             |
| 102      | 3             |
| 104      | 3             |
| 105      | 1             |
+----------+---------------+
1 数据准备
create table if not exists t14_login_events
(
    user_id        int comment '用户id',
    login_datetime string comment '登录时间'
)
    comment '直播间访问记录';
--数据插入
insert  overwrite table t14_login_events
values (100, '2021-12-01 19:00:00'),
       (100, '2021-12-01 19:30:00'),
       (100, '2021-12-02 21:01:00'),
       (100, '2021-12-03 11:01:00'),
       (101, '2021-12-01 19:05:00'),
       (101, '2021-12-01 21:05:00'),
       (101, '2021-12-03 21:05:00'),
       (101, '2021-12-05 15:05:00'),
       (101, '2021-12-06 19:05:00'),
       (102, '2021-12-01 19:55:00'),
       (102, '2021-12-01 21:05:00'),
       (102, '2021-12-02 21:57:00'),
       (102, '2021-12-03 19:10:00'),
       (104, '2021-12-04 21:57:00'),
       (104, '2021-12-02 22:57:00'),
       (105, '2021-12-01 10:01:00');
2 数据分析
select
     user_id,
     max(log_days) as max_log_days
from
   (
      select
           user_id,
           group_id,
           datediff(max(login_date),min(login_date)) +1 as log_days
      from
          (
            select
                  user_id,
                  login_date,
                  lag_log_date,
                  date_diff,
                  sum(if(date_diff <=2 ,0,1) )over(partition by  user_id order by login_date) as group_id
            from
               (
                 select
                      user_id,
                      login_date,
                      lag(login_date,1,null) over (partition by user_id order by login_date ) as lag_log_date,
                      datediff(login_date,lag(login_date,1,'1970-01-01') over (partition by user_id order by login_date)) as date_diff
                 from
                     (
                         select
                              user_id,
                              to_date(login_datetime)  as login_date
                         from t14_login_events
                         group by user_id,to_date(login_datetime)
                     )t1
               )t2
          )t3
        group by user_id,
             group_id
    )t4
group by user_id;

思路分析：

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

==========================*****==========================

奖金瓜分问题

0 问题描述
在活动大促中，有玩游戏瓜分奖金环节。现有奖金池为 10000元，代表奖金池中的初始额度。用户的分数信息如下表。表中的数据代表每一个用户和其对应的得分，user_id 和 score 都不会有重复值。瓜分奖金的规则如下：按照 score 从高到低依次瓜分，每个人都能分走当前奖金池里面剩余奖金的一半，当奖金池里面剩余的奖金少于 250 时（不含），则停止瓜分奖金。 现在需要查询出所有分到奖金的 user_id 和其对应的奖金。

样例数据
+----------+--------+
| user_id  | score  |
+----------+--------+
| 100      | 60     |
| 101      | 45     |
| 102      | 45     |
| 103      | 35     |
| 104      | 30     |
| 105      | 25     |
| 106      | 15     |
| 107      | 10     |
+----------+--------+
1 数据准备
--建表语句
create table if not exists t15_user_score
(
    user_id string,
    score   bigint
);

--插入数据
insert overwrite table  t15_user_score
values
   ('100',60),
   ('101',45),
   ('102',45),
   ('103',35),
   ('104',30),
   ('105',25),
   ('106',15),
   ('107',10)
2 数据分析
select
     user_id,
     score,
     prize1
from
   (
      select
            user_id,
            score,
            power(0.5, rn) * 10000 as prize1,
            power(0.5, rn - 1) * 10000 as prize2
       from
           (
              select
                   user_id,
                   score,
                   row_number() over(order by score desc) as rn
               from t15_user_score
           )t1
   )t2
 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

吵吵叭火
关注
  • 12
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
2024年大数据最新一共81个，开源大数据处理工具汇总（上）(1)，2024年最新2024大数据开发大厂面试真题
2401_84573481的博客
05-05 864
它是一个网络社交云计算方面理想的数据库。它最初由Facebook开发，用于储存收件箱等简单格式数据，集GoogleBigTable的数据模型与Amazon Dynamo的完全分布式的架构于一身Facebook于2008将 Cassandra 开源，此后，由于Cassandra良好的可扩放性，被Digg、Twitter等知名Web 2.0网站所采纳，成为了一种流行的分布式结构化数据存储方案。为提高搜索广告有效点击率的问题而开发的一个平台，通过统计分析用户对广告的点击率，排除相关度低的广告，提升点击率。
2024年最新大数据进阶之路——Spark SQL小结_spark sql进阶(1)
2401_84692226的博客
05-02 908
Python崛起并且风靡，因为优点多、应用领域广、被大牛们认可。学习 Python 门槛很低，但它的晋级路线很多，通过它你能进入机器学习、数据挖掘、大数据，CS等更加高级的领域。Python可以做网络应用，可以做科学计算，数据分析，可以做网络爬虫，可以做机器学习、自然语言处理、可以写游戏、可以做桌面应用…Python可以做的很多，你需要学好基础，再选择明确的方向。这里给大家分享一份全套的 Python 学习资料，给那些想学习 Python 的小伙伴们一点帮助！
24 个必须掌握的数据库面试问题！_24个必须掌握的 数据库 面试题
m0_60607562的博客
04-21 976
🍅 硬核资料：关注即可领取PPT模板、简历模板、行业经典书籍PDF。🍅 技术互助：技术群大佬指点迷津，你的问题可能不是问题，求资源在群里喊一声。🍅 面试题库：由技术群里的小伙伴们共同投稿，热乎的大厂面试真题，持续更新中。🍅 知识体系：含编程语言、算法、大数据生态圈组件（Mysql、Hive、Spark、Flink）、数据仓库、Python、前端等等。网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。
初中级前端程序员面试中小型公司会问哪些问题?
weixin_51689029的博客
05-11 1518
初中级前端程序员面试中小型公司会问哪些问题?不同的公司面试内容也不尽相同，有的面试过程很轻松，有的面试官是个架构师level 挺高不会问八股文，给出了几个现实中的场景，然后转换成代码的逻辑去让实现。 因此在面试过程中要做好准备，随机应变。面试题一览： 1、一般请求后端接口，你都怎么弄？ 2、你的后端要给你什么样的信息，你才能请求成功呢？ 3、请求参数有什么格式？ 4、如何给后端传递一个文件？ 5、你如何理解前端工程化? 6、要买个电脑，找 A 借 1k，找 B 借 2K，找 C 借 3K，拿着六千块钱买电脑
2023最新版大厂面试真题
11-25
"2023最新版大厂面试真题"是一个集合了各大互联网公司面试常见问题的资源，旨在帮助求职者了解并熟悉这些公司的面试流程和考察重点。这份资料涵盖了百度、腾讯、阿里、华为、京东、美团、拼多多、携程、蚂蚁金服、...
2020互联网一线大厂面试真题附含答案.zip
09-26
"2020互联网一线大厂面试真题附含答案.zip"这个压缩包文件包含了多个准备大厂面试的重要资源，如面试真题目录、高频面试题以及多份大厂面试的详细题目。这些资料对于求职者来说，是提升面试技能、了解企业需求的关键...
大厂面试真题大厂面试真题
08-14
大厂面试真题大厂面试真题 本文总结了大厂面试真题中的一些重要知识点，涵盖了.NET 和 C# 基础知识、递归算法、访问修饰符、overload 和 override 区别、委托和事件、冒泡排序算法、面向对象编程、通用语言规范、...
2021Java大厂面试题——大厂真题之字节跳动-Java初级.pdf
01-29
### 2021 Java大厂面试题解析 #### 一、锁的位置及使用方法 在Java编程语言中，为了确保多线程环境下的数据一致性，开发者常常需要使用同步机制来控制对共享资源的访问。Java提供了两种主要的加锁方式：`...
2021Java大厂面试题——大厂真题之携程-Java高级.pdf
01-29
### 2021年Java大厂面试题详解——大厂真题之携程-Java高级 #### 一、JVM加载Class文件的原理机制 在深入理解Java虚拟机(JVM)如何加载Class文件之前，我们需要明确一点：Java的所有类都需要通过类加载器加载到JVM...
Spark MLlib 特征工程(上)
08-14 2959
特征选择的动机，在于提取与预测标的关联度更高的特征，从而精简模型尺寸、提升模型泛化能力。特征选择可以从两方面入手，业务出发的专家经验和基于数据的统计分析。归一化的目的，在于去掉不同特征之间量纲的影响，避免量纲不一致而导致的梯度下降震荡、模型收敛效率低下等问题。归一化的具体做法，是把不同特征都缩放到同一个值域。在这方面，Spark MLlib 提供了多种归一化方法供开发者选择。
软考高级第四版备考---第39天（数据管理）
u013136284的专栏
08-12 951
5.2数据安全管理：数据安全管理是在数据安全标准与策略的指导下，通过对数据访问的授权、分类分级的控制、监控数据的访问等进行数据安全的管理工作，满足数据安全的业务需要和监管需求，实现组织内部对数据生存周期的数据安全管理。6.4数据质量提升：数据质量提升是对数据质量分析的结果，制定、实施数据质量改进方案，包括错误数据更正、业务流程优化、应用系统问题修复等，并制定数据质量问题的预防方案，确保数据质量改进的成果得到有效保持。
ElasticSearch文档数据关联关系处理
最新发布
qq_44027353的博客
08-15 787
再更新操作时，对象类型和嵌套对象nested方式有一个问题，因为根对象和嵌套对象本质上它们还是存在一个文档中的，每次更新时就可以需要重新索引整个文档。ES提供了父子关联关系，通过维护parent/child的关系，分离它们，使父文档和子文档是两个独立的文档，更新其中一个文档不会影响另一个文档。创建子文档时，必须通过routing指定父文档id，保证父子文档在一个shard中，提高join查询性能。user字段如果保存的是一个对象数组，在搜索时添加两个查询条件，数组中两个对象分别满足一个条件。
谷粒商城实战笔记-175~177-商城业务-检索服务-检索查询接口开发
epitomizelu的专栏
08-13 770
搜索页面搭建完成之后，点击搜索按钮，发送参数给后台服务，后台服务根据参数从Elasticsearch中查询符合条件的数据，返回给前端。
SparkSQL
m0_73745224的博客
08-13 1016
sparkSQL操作各种数据
重塑商业生态：技术驱动下的批发供应系统开发与革新
qq_27313987的博客
08-13 408
在当今这个快速变化的商业环境中，批发供应系统作为连接生产商、分销商与零售商的关键纽带，其效率与智能化水平直接影响着整个供应链的运作效率与市场竞争力。随着信息技术的飞速发展，特别是大数据、云计算、人工智能（AI）以及物联网（IoT）等前沿技术的深度融合，批发供应系统正经历着一场前所未有的技术革新，为传统商业模式带来了颠覆性的变化。
大数据系列之：Flink Doris Connector，实时同步数据到Doris数据库
zhengzaifeidelushang的博客
08-13 941
可以通过Flink操作（读取、插入、修改、删除）支持存储在Doris中的数据。本文介绍了如何通过Datastream和Flink操作Doris。
基于车联网大数据平台的用户驾驶习惯行为画像分析
DolphinDB智臾科技
08-15 618
车联网系统中，利用大数据对驾驶行为进行研究，有助于了解驾驶员的特征，并提供优化建议。本文从用户驾驶行程中的速度偏好、驾驶风格、熟练度三个方面描述用户画像，并介绍如何使用 DolphinDB 基于 K-means 算法的聚类模型生成用户画像与个性化标签，完成数据存储、处理、特征提取、模型构建及预测等全过程。
Binance 如何使用 Quickwit 构建 100PB 日志服务(Quickwit 博客)
o__cc的博客
08-15 507
三年前，我们开源了 Quickwit，一个面向大规模数据集的分布式搜索引擎。我们的目标很宏大：创建一种全新的全文搜索引擎，其成本效率比 Elasticsearch 高十倍，配置和管理显著更简单，并且能够扩展到 PB 级别的数据。 https://quickwit.io/blog/quickwit-first-release 虽然我们知道 Quickwit 的潜力，但我们通常测试的数据量不超...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值