SQL练习-时间间隔类题目-CSDN博客

本文链接：https://blog.csdn.net/Zekisofdope/article/details/148044390

时间间隔类题目

解题思路

利用ROW_NUMBER() 进行排序，按照时间进行升序排序
这里有两种方法
- 利用错位相减法，即进行自连接使得a.rn = b.rn - 1
- 利用LAG函数，获得上一个时间
根据题目要求求出对应指标

数据源表

CREATE TABLE continues_time (
 sign_date DATE,
 user_id INT,
 continues_time TIME
);
INSERT INTO continues_time (sign_date, user_id, continues_time) 
VALUES
('2023-08-01', 1001, '09:00:00.0'),
('2023-08-01', 1002, '10:30:00.0'),
('2023-08-01', 1003, '11:45:00.0'), 
('2023-08-01', 1004, '14:20:00.0'),
('2023-08-02', 1001, '08:39:00.0'), 
('2023-08-02', 1002, '11:15:00.0'),
('2023-08-02', 1003, '13:49:00.0'),
('2023-08-02', 1004, '15:50:00.0'),
('2023-08-03', 1001, '10:00:00.0'),
('2023-08-03', 1002, '12:30:00.0'),
('2023-08-03', 1003, '14:10:00.0'),
('2023-08-03', 1004, '16:45:00.0'),
('2023-08-04', 1001, '09:45:00.0'),
('2023-08-04', 1002, '12:15:00.0'),
('2023-08-04', 1003, '14:30:00.0'),
('2023-08-04', 1004, '16:20:00.0'),
('2023-08-05', 1001, '18:00:00.0'), 
('2023-08-05', 1002, '12:40:00.0');

第一题：求每个用户相邻登录时间间隔小于1500分钟的次数

注意，求时间间隔的话，我们需要使用UNIX时间戳来相减得到时间间隔

第一种：错位相减

先对用户进行分组，对时间进行排序，然后用Left Join匹配两张表

(
  SELECT
    user_id,
    unix_timestamp(concat(sign_date, ' ', continues_time)) AS datetime_value,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY concat(sign_date, ' ', continues_time)) AS num 
  FROM continues_time
) a
LEFT JOIN (
  SELECT
    user_id,
    unix_timestamp(concat(sign_date, ' ', continues_time)) AS datetime_value,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY concat(sign_date, ' ', continues_time)) AS num 
  FROM continues_time
) b ON a.user_id = b.user_id AND a.num = b.num + 1

通过这样就能匹配上两张表了，再利用相减得到间隔少于1500分钟的数量

完整代码为

SELECT
	 a.user_id,
	count(a.user_id) cnt
FROM
 (
	 SELECT
		 user_id,
		 unix_timestamp(concat( sign_date, ' ', continues_time )) DATETIME,
		 (ROW_NUMBER() over (PARTITION BY user_id ORDER BY concat( sign_date, ' ', continues_time ))) num 
	 FROM continues_time 
 ) a
 LEFT JOIN (
	 SELECT
		 user_id,
		 unix_timestamp(concat( sign_date, ' ', continues_time )) DATETIME,
		 (ROW_NUMBER() over (PARTITION BY user_id ORDER BY concat( sign_date, ' ', continues_time ))) num 
	 FROM continues_time 
 ) b ON a.user_id = b.user_id 
 AND a.num = (b.num - 1)
where (b.DATETIME-a.DATETIME)/60 <1500
group by a.user_id

第二种：LAG函数

SELECT
    user_id,
    COUNT(*) AS login_count
FROM (
    SELECT
        user_id,
        -- 转换为时间戳
        UNIX_TIMESTAMP(STR_TO_DATE(concat(sign_date,' ',continues_time), '%Y-%m-%d %H:%i:%s')) AS current_time,
        -- 获取上一条的日期时间
        LAG(UNIX_TIMESTAMP(STR_TO_DATE(concat(sign_date,' ',continues_time), '%Y-%m-%d %H:%i:%s'))) 
            OVER(PARTITION BY user_id ORDER BY concat(sign_date,' ',continues_time)) AS prev_time
    FROM continues_time
) t
-- 保证前一条不为空
WHERE prev_time IS NOT NULL 
  AND (current_time - prev_time)/60 < 1500
GROUP BY user_id

补充一下LAG函数的使用方法，主要原因也是突然有点忘记参数有什么了

LAG() OVER()函数
是 SQL 中的一个窗口函数，访问行的前一行的数据，通常用于分析和比较。
基本语法：
LAG(expression [,offset [,default]]) OVER (
[PARTITION BY partition_expression, … ]
ORDER BY sort_expression [ASC | DESC], … )
语法解释：
expression：检索的列或表达式
offset（可选）：指定从当前行向前查看的行数，默认为 1。
default（可选）：如果没有前一行（即在分区的第一行），则返回的默认值。
PARTITION BY：将数据分成多个窗口或分区。
ORDER BY：定义窗口内记录的排序顺序。这对于 LAG()这样的函数非常重要，因
为它依赖于知道哪一行是“之前”的行。

第二题：计算每个用户间隔连续登录天数（间隔一天也算连续）

要注意，同一个用户有多个连续登录的时间也都要进行统计

-- 第一步：准备基础数据（去重处理）
-- 作用：获取每个用户每天的登录记录（同一天多次登录只计一次）
WITH ranked_logins AS (
  SELECT 
    user_id,
    login_date,
    -- 计算当前登录日期与前一次登录日期的间隔天数
    -- LAG函数获取按用户分组、按日期排序后的上一条记录
    DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) AS day_gap
  FROM (
    -- 子查询：对原始数据去重（消除同用户同一天多次登录的记录,比如上午登录下午也登陆的情况）
    SELECT DISTINCT user_id, DATE(login_datetime) AS login_date 
    FROM login_events
  ) t
),

-- 第二步：标记连续登录分组
-- 作用：当日期间隔>2天时创建新分组（表示连续登录中断,即group_id + 1）
grouped_logins AS (
  SELECT
    user_id,
    login_date,
    -- 当日期间隔>2天时累加分组ID（创建新组）
    -- SUM OVER实现类似"断点累加"的效果
    SUM(CASE WHEN day_gap > 2 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
  FROM ranked_logins
)

-- 第三步：计算最终结果
-- 作用：统计每个连续登录分组的起止日期和持续天数
SELECT
  user_id,
  -- 每组的最早日期作为连续登录开始日期
  MIN(login_date) AS start_date,
  -- 计算持续天数：(最晚日期-最早日期)+1
  -- 例如5月1日到5月3日是3天（包含首尾）
  DATEDIFF(MAX(login_date), MIN(login_date)) + 1 AS continuous_days
FROM grouped_logins
-- 按用户和分组ID聚合
GROUP BY user_id, group_id
-- 按用户ID和开始日期排序
ORDER BY user_id, start_date;