SQL 从入门到精通

用户累计访问次数

user_id	visit_date	visit_count
1	2025-01-01	5
1	2025-01-02	6
1	2025-01-03	4
2	2025-01-01	3
2	2025-01-02	1
3	2025-01-01	9
计算每个用户每月的访问累计次数：

WITH visit_data(user_id,visit_date,visit_count) as (
    SELECT 1,'2025-01-01',5 UNION ALL
    SELECT 1,'2025-01-02',6 UNION ALL
    SELECT 1,'2025-01-03',4 UNION ALL
    SELECT 2,'2025-01-01',3 UNION ALL
    SELECT 2,'2025-01-02',1 UNION ALL
    SELECT 3,'2025-01-01',9
)
-- 不使用 ORDER BY 子句
SELECT user_id, date_format(visit_date, 'yyyy-MM'), cum_visit_count
FROM (
        SELECT *, SUM(visit_count) over (partition by user_id, date_format(visit_date, 'yyyy-MM')) as cum_visit_count
        FROM visit_data
    ) t1
group by
    user_id,
    date_format(visit_date, 'yyyy-MM'),
    cum_visit_count
 
-- 使用 ORDER BY 子句
SELECT user_id, date_format(visit_date, 'yyyy-MM'), MAX(cum_visit_count) as cum_visit_count
FROM (
        SELECT *, SUM(visit_count) over (partition by user_id, date_format(visit_date, 'yyyy-MM') ORDER BY visit_date ) as cum_visit_count
        FROM visit_data
    ) t1
GROUP BY
    user_id,
    date_format(visit_date, 'yyyy-MM')

要注意SUM 开窗函数中的 order by 子句，加上 ORDER BY，则会按日期顺序进行累加。否则的话，会按分区进行累加，即将分区中的所有数据都累加起来。

+----------+----------+------------------+
| user_id  |   _c1    | cum_visit_count  |
+----------+----------+------------------+
| 1        | 2025-01  | 15               |
| 2        | 2025-01  | 4                |
| 3        | 2025-01  | 9                |
+----------+----------+------------------+

同时在线人数

核心要点是

计算出需要计算哪些小时：通过每个用户的登录时间和退出时间，计算出用户所在的小时数
展开每个用户的小时数：将每个用户的小时数展开为多行，每个小时占一行
统计每个小时的在线人数：对展开后的数据集进行分组统计，每个小时的人数就是同时在线的人数

with login_log (user_id,login_time,logout_time) as (
    SELECT 1,'2025-10-10 12:00:00','2025-10-10 13:35:00' UNION ALL
    SELECT 1,'2025-10-10 12:30:00','2025-10-10 12:40:00' UNION ALL
    SELECT 2,'2025-10-10 12:30:00','2025-10-10 14:35:00' UNION ALL
    SELECT 3,'2025-10-10 12:35:00','2025-10-10 15:40:00' UNION ALL
    SELECT 4,'2025-10-10 12:38:00','2025-10-10 12:40:00'
),
t_tmp as (
SELECT
    user_id,
    hour(login_time) as login_hour,
    hour(logout_time) as logout_hour,
    split (
        space(
            hour(logout_time) - hour(login_time)
        ),
        ' '
    ) as hour_list
FROM login_log
)
SELECT hour, COUNT(DISTINCT user_id) as user_count
FROM (
        SELECT user_id, login_hour + hour_pos as hour
        FROM
            t_tmp lateral view posexplode (hour_list) t_ex as hour_pos, hour
    ) t1
GROUP BY
    hour

查询恶意买家

📝 题目要求

问题描述： 下面是某电商网站的订单数据，包括 order_id (订单ID), user_id (用户ID), order_status (订单状态) 和 operate_time (操作时间) 四个字段。我们需要找出所有恶意购买的用户。

恶意购买的定义： 同一个用户，在任意半小时内（含），取消订单次数 >= 3次的，就被视为恶意买家。例如：在样例数据中，用户 c 就是恶意买家。

WITH order_data AS (
    SELECT 1101 AS order_id, 'a' AS user_id, '已支付' AS order_status, CAST('2023-01-01 10:00:00' AS TIMESTAMP) AS operate_time UNION ALL
    SELECT 1102, 'a', '已取消', '2023-01-01 10:10:00' UNION ALL
    SELECT 1103, 'a', '待支付', '2023-01-01 10:20:00' UNION ALL
    SELECT 1104, 'b', '已取消', '2023-01-01 10:30:00' UNION ALL
    SELECT 1105, 'a', '待确认', '2023-01-01 10:50:00' UNION ALL
    SELECT 1106, 'a', '已取消', '2023-01-01 11:00:00' UNION ALL
    SELECT 1107, 'b', '已取消', '2023-01-01 11:40:00' UNION ALL
    SELECT 1108, 'b', '已取消', '2023-01-01 11:50:00' UNION ALL
    SELECT 1109, 'b', '已支付', '2023-01-01 12:00:00' UNION ALL
    SELECT 1110, 'b', '已取消', '2023-01-01 12:11:00' UNION ALL
    SELECT 1111, 'c', '已取消', '2023-01-01 12:20:00' UNION ALL
    SELECT 1112, 'c', '已取消', '2023-01-01 12:30:00' UNION ALL
    SELECT 1113, 'c', '已取消', '2023-01-01 12:55:00' UNION ALL
    SELECT 1114, 'c', '已取消', '2023-01-01 13:00:00'
)

为了精准锁定在 30 分钟内触发至少 3 次取消行为的用户，我们采取“滑动观察窗口”的策略：

特征清洗：首先筛选出所有 order_status = '已取消' 的记录，排除无关操作的干扰。
建立观察窗口：利用窗口函数 LAG(operate_time, 2)，让每一条取消记录去“回看”该用户此前第 2 次取消的时间点。
- 原理：如果当前记录与前第 2 条记录（共计 3 条）的时间差 $\le 30$ 分钟，则意味着在该时间段内发生了 3 次取消。
计算时间跨度：将当前操作时间与获取到的“前第 2 次时间”求差。若该差值存在（非 Null）且小于等于 1800 秒（30 分钟），则触发恶意行为预警。
结果去重：最后按 user_id 分组提取，得到最终的恶意用户名单。

SELECT user_id
FROM (
        SELECT
            *, lag(operate_time, 2) OVER (
                PARTITION BY
                    user_id
                ORDER BY operate_time
            ) as pre_2_cnacel, unix_timestamp(operate_time) - unix_timestamp(
                lag(operate_time, 2) OVER (
                    PARTITION BY
                        user_id
                    ORDER BY operate_time
                )
            ) as time_diff
        FROM order_data
        WHERE
            order_status = '已取消'
    ) t1
WHERE
    pre_2_cnacel is not null
    AND time_diff <= 1800
GROUP BY
    user_id

打折日期交叉问题

📝 题目要求

问题描述：给定各平台的商品促销数据，包含 brand（品牌）、stt（打折开始日期）和 edt（打折结束日期）三个字段。

核心任务：计算每个品牌总的打折销售天数。

难点：需要处理日期交叉问题。
示例：vivo 品牌，第一次活动是 06-05 到 06-15，第二次是 06-09 到 06-21。其中 9号到 15号为重复天数，只统计一次。因此 vivo 总打折天数为 06-05 到 06-21，共计 17 天。

WITH promotion_data(brand, stt, edt) AS (
    SELECT 'oppo', '2021-06-05', '2021-06-09' UNION ALL
    SELECT 'oppo', '2021-06-11', '2021-06-21' UNION ALL
    SELECT 'vivo', '2021-06-05', '2021-06-15' UNION ALL
    SELECT 'vivo', '2021-06-09', '2021-06-21' UNION ALL
    SELECT 'redmi', '2021-06-05', '2021-06-21' UNION ALL
    SELECT 'redmi', '2021-06-09', '2021-06-15' UNION ALL
    SELECT 'redmi', '2021-06-17', '2021-06-26' UNION ALL
    SELECT 'huawei', '2021-06-05', '2021-06-26' UNION ALL
    SELECT 'huawei', '2021-06-09', '2021-06-15' UNION ALL
    SELECT 'huawei', '2021-06-17', '2021-06-21'
)

本题的解法需要用到 “最大结束日期法”，核心的内容是弄明白窗口函数的窗口范围是如何定义的。

当前行的最大结束时间（不包括本行）可以使用ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING来定义范围。

按照开始时间进行排序，找到每组中到当前行最大的结束日期
如果为 NULL 的话，说明当前行就是最小的开始时间，即第一行，所以使用结束时间减去开始时间
如果当前的最大结束时间小于本数据的开始时间，说明中间有间隔，那么这条数据也可以当做一个新开始的数据，所以也是使用结束时间减去开始时间
如果当前的最大结束时间小于本数据的结束时间，则将本数据的结束时间减去之前的最大开始时间即可

SELECT brand, sum(day_diff)
FROM (
        SELECT
            *, CASE
                WHEN (cur_max_edt is null)
                or (cur_max_edt < stt) THEN datediff(edt, stt)
                when cur_max_edt < edt then datediff(edt, cur_max_edt)
                ELSE 0
            END as day_diff
        FROM (
                SELECT *, MAX(edt) OVER (
                        PARTITION BY
                            brand
                        ORDER BY
                            stt ROWS BETWEEN UNBOUNDED PRECEDING
                            AND 1 PRECEDING
                    ) as cur_max_edt
                FROM promotion_data
            ) t1
    ) t2
GROUP BY
    brand

+---------+------+
|  brand  | _c1  |
+---------+------+
| huawei  | 21   |
| oppo    | 14   |
| redmi   | 21   |
| vivo    | 16   |
+---------+------+