SQL：阅读记录各时段阅读时长统计

发表于 分类于 ， ， 阅读次数： Valine：
本文字数： 5.5k 阅读时长 ≈ 5 分钟

背景介绍

  最近做了一个阅读记录的系统，打算统计一下各个时段的阅读时长展示在报表上，所以思考如何仅通过SQL实现数据统计。
  此处我将一天24小时分为24个时段，如[12:00:00,13:00:00)的左闭右开区间为一个时段，记为12:00:00。

阅读记录表

表名：dwd_book_read_record
表结构

字段名 数据类型 备注
id int 自增id
book_name varchar 书名（与书单书名对齐）
page_start float 阅读开始的页码（使用浮点数兼容百分比进度，不小于0）
page_end float 阅读结束的页码（使用浮点数兼容百分比进度，不大于图书总页码）
datetime_start datetime 阅读开始的时间
datetime_end datetime 阅读结束的时间（取消或结束的时间）
read_time int 阅读时长（单位分钟，不小于0，不大于开始、结束时间之差，可人为指定）
status varchar 阅读状态（reading、cancel、end，全表只能有一条reading记录）
is_finish int 是否读完（1：读完，0：未读完。当结束页码等于图书总页码时为1）

数据样例

id book_name page_start page_end datetime_start datetime_end read_time status is_finish
1 西线无战事 0 12 2022-10-13 13:28:26 2022-10-13 13:51:26 23 end 0
6 只差一个谎言 36 83 2022-10-08 18:46:22 2022-10-08 19:22:22 36 end 0
71 夜晚的潜水艇 35 52 2022-01-27 23:59:38 2022-01-28 00:28:38 29 end 0

思路分析

  我个人的阅读时长通常不会超过三小时，由于阅读记录中只记录了阅读的开始时间和结束时间，即使是阅读时长小于一个小时，由于开始时间的不同也可能导致两个时间点分别位于不同的时段内，所以直接根据一条记录的两个时间点并不能直接得到各个时段的阅读时长。需要将该记录覆盖的时间范围切割成多条记录，分别计算对应各个时段的阅读时长。

数据膨胀
  我这里采用的方法是通过非等值连接将阅读记录所覆盖到的时段左连接上去，将一条记录膨胀为多条，额外增加一个对应时段的字段。
  首先是要计算开始时间和结束时间所对应的时段，直接使用时间格式化函数即刻：

1
DATE_FORMAT(datetime_start,'%H:00:00') AS time_start_stage

  接下来为了实现膨胀需要一张包含24个时段的辅助表，该表可以提前准备好，我这里采用临时生成的方式。

time_stage
00:00:00
01:00:00
…
23:00:00

  最后通过非等值左连接的方式即可对原始记录进行膨胀：

1
t2.time_start_stage <= t1.time_stage AND  t2.time_end_stage >= t1.time_stage

  这里还涉及一个零点问题不适用上面的判断条件，需要单独判断处理，之后会进行讨论。

分段计算

  现在对与一条记录的开始时间，结束时间以及时段分别会产生四种情况，如图1所示，最多可以将所覆盖的时间范围划分为三段进行处理。

图1：时段划分

1
2
3
4
5
6
7
8
9
CASE 
    WHEN time_start_stage=time_end_stage 
        THEN TIME_TO_SEC(SUBTIME(time_end,time_start)) -- 开始、结束时间位于相同时段
    WHEN time_stage<time_start 
        THEN 3600-TIME_TO_SEC(SUBTIME(time_start,time_stage)) -- 求第一个时段阅读时间
    WHEN time_start_stage<time_stage AND time_stage<time_end_stage
        THEN 3600 -- 求中间时段阅读时间
    ELSE TIME_TO_SEC(SUBTIME(time_end,time_stage)) -- 求末尾时段阅读时间
END AS read_time_sec

零点问题

  由于我喜欢在睡觉前读书，所以会存在跨越零点的阅读记录，该类记录不管是非等值连接也好，还是分段计算也好，都不适用于上面的通用判断条件，需要单独写处理条件。

图2：涉及零点问题的时段划分

  对于跨零点数据的非等值连接条件为：

1
2
3
time_start_stage>time_end_stage 
AND (t2.time_start_stage <= t1.time_stage 
    OR t2.time_end_stage >= t1.time_stage)

  对于跨零点数据的分段计算接条件为：

1
2
3
4
5
6
7
8
9
10
11
12
13
WHEN time_end_stage<time_start_stage THEN ( -- 单独处理跨零点记录
    CASE 
    WHEN time_stage<time_start 
        AND time_stage>time_end_stage 
        THEN 3600-TIME_TO_SEC(SUBTIME(time_start,time_stage)) -- 求第一个时段阅读时间
    WHEN time_stage<'23:59:59' 
        AND time_stage>time_start_stage 
        THEN 3600 -- 求零点前的中间时段阅读时间
    WHEN time_stage<time_end_stage 
        THEN 3600 -- 求零点后的中间时段阅读时间
    ELSE TIME_TO_SEC(SUBTIME(time_end,time_stage)) -- 求末尾时段阅读时间
    END 
)

注意每一个条件的先后顺序

完整的代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
with t1 AS ( -- 手动构建时段辅助表
  SELECT '00:00:00' AS time_stage
  UNION 
  SELECT '01:00:00'
  UNION 
  SELECT '02:00:00'
  UNION 
  SELECT '03:00:00'
  UNION 
  SELECT '04:00:00'
  UNION 
  SELECT '05:00:00'
  UNION 
  SELECT '06:00:00'
  UNION 
  SELECT '07:00:00'
  UNION 
  SELECT '08:00:00'
  UNION 
  SELECT '09:00:00'
  UNION 
  SELECT '10:00:00'
  UNION 
  SELECT '11:00:00'
  UNION 
  SELECT '12:00:00'
  UNION 
  SELECT '13:00:00'
  UNION 
  SELECT '14:00:00'
  UNION 
  SELECT '15:00:00'
  UNION 
  SELECT '16:00:00'
  UNION 
  SELECT '17:00:00'
  UNION 
  SELECT '18:00:00'
  UNION 
  SELECT '19:00:00'
  UNION 
  SELECT '20:00:00'
  UNION 
  SELECT '21:00:00'
  UNION 
  SELECT '22:00:00'
  UNION 
  SELECT '23:00:00'
)
SELECT 
    time_start,
    time_end,
    time_start_stage,
    time_end_stage,
    time_stage,
    CASE 
        WHEN time_end_stage<time_start_stage THEN ( -- 单独处理跨零点记录
          CASE 
            WHEN time_stage<time_start 
                AND time_stage>time_end_stage 
                THEN 3600-TIME_TO_SEC(SUBTIME(time_start,time_stage)) -- 求第一个时段阅读时间
            WHEN time_stage<'23:59:59' 
                AND time_stage>time_start_stage 
                THEN 3600 -- 求零点前的中间时段阅读时间
            WHEN time_stage<time_end_stage 
                THEN 3600 -- 求零点后的中间时段阅读时间
            ELSE TIME_TO_SEC(SUBTIME(time_end,time_stage)) -- 求末尾时段阅读时间
          END 
        )
        WHEN time_start_stage=time_end_stage 
            THEN TIME_TO_SEC(SUBTIME(time_end,time_start)) -- 开始、结束时间位于相同时段
        WHEN time_stage<time_start 
            THEN 3600-TIME_TO_SEC(SUBTIME(time_start,time_stage)) -- 求第一个时段阅读时间
        WHEN time_start_stage<time_stage AND time_stage<time_end_stage
            THEN 3600 -- 求中间时段阅读时间
        ELSE TIME_TO_SEC(SUBTIME(time_end,time_stage)) -- 求末尾时段阅读时间
    END AS read_time_sec
FROM 
(
SELECT 
  datetime_start,
  datetime_end,
  DATE_FORMAT(datetime_start,'%H:%i:%S') AS time_start,
  DATE_FORMAT(datetime_end,'%H:%i:%S') AS time_end,
  DATE_FORMAT(datetime_start,'%H:00:00') AS time_start_stage,
  DATE_FORMAT(datetime_end,'%H:00:00') AS time_end_stage
FROM dwd_book_read_record 
WHERE status = 'end'
) t2
LEFT JOIN t1 
ON (t2.time_start_stage <= t1.time_stage AND  t2.time_end_stage >= t1.time_stage) 
    OR (time_start_stage>time_end_stage 
        AND (t2.time_start_stage <= t1.time_stage 
            OR t2.time_end_stage >= t1.time_stage)) -- 处理阅读时间跨越了零点的情况

效果展示

time_start time_end time_start_stage time_end_stage time_stage read_time_sec
13:28:26 13:51:26 13:00:00 13:00:00 13:00:00 1380
12:58:04 13:33:04 12:00:00 13:00:00 13:00:00 1984
12:58:04 13:33:04 12:00:00 13:00:00 12:00:00 116
23:59:38 00:28:38 23:00:00 00:00:00 23:00:00 22
23:59:38 00:28:38 23:00:00 00:00:00 00:00:00 1718

-------- 本文结束 感谢阅读 --------
相关文章
觉得文章写的不错的话，请我喝瓶怡宝吧！😀
  • 本文标题： SQL：阅读记录各时段阅读时长统计
  • 本文作者： SiriYang
  • 创建时间： 2022年11月07日 - 18时11分
  • 修改时间： 2022年11月07日 - 20时11分
  • 本文链接： https://blog.siriyang.cn/posts/20221107182843id.html
  • 版权声明： 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明出处！
0%