极客教程SQL 按照时间单位分组，你想按照某种时间间隔整理数据。例如，你有一些交易日志，并希望每隔 5 秒汇总一下这些数据。TRX_LOG 表的数据显示如下。
SQL 按照时间单位分组 问题描述
你想按照某种时间间隔整理数据。例如，你有一些交易日志，并希望每隔 5 秒汇总一下这些数据。TRX_LOG 表的数据显示如下。
select trx_id,
trx_date,
trx_cnt
from trx_log
TRX_ID TRX_DATE TRX_CNT
------ -------------------- ----------
1 28-JUL-2005 19:03:07 44
2 28-JUL-2005 19:03:08 18
3 28-JUL-2005 19:03:09 23
4 28-JUL-2005 19:03:10 29
5 28-JUL-2005 19:03:11 27
6 28-JUL-2005 19:03:12 45
7 28-JUL-2005 19:03:13 45
8 28-JUL-2005 19:03:14 32
9 28-JUL-2005 19:03:15 41
10 28-JUL-2005 19:03:16 15
11 28-JUL-2005 19:03:17 24
12 28-JUL-2005 19:03:18 47
13 28-JUL-2005 19:03:19 37
14 28-JUL-2005 19:03:20 48
15 28-JUL-2005 19:03:21 46
16 28-JUL-2005 19:03:22 44
17 28-JUL-2005 19:03:23 36
18 28-JUL-2005 19:03:24 41
19 28-JUL-2005 19:03:25 33
20 28-JUL-2005 19:03:26 19
你希望返回如下所示的结果集。
GRP TRX_START TRX_END TOTAL
--- -------------------- -------------------- ----------
1 28-JUL-2005 19:03:07 28-JUL-2005 19:03:11 141
2 28-JUL-2005 19:03:12 28-JUL-2005 19:03:16 178
3 28-JUL-2005 19:03:17 28-JUL-2005 19:03:21 202
4 28-JUL-2005 19:03:22 28-JUL-2005 19:03:26 173
SQL 按照时间单位分组 解决方案
把全部数据记录分别放入若干个桶，每个桶里放 5 行。要实现这种逻辑分组，有几种可能的实现方式。本实例的做法是用 TRX_ID 值除以 5，12.7 节曾使用过该技巧。
创建好“分组”之后，调用聚合函数 MIN、MAX 和 SUM 分别计算出开始时间、结束时间和每个“分组”的交易数目合计（如果是 SQL Server 的话，要记得用 CEILING 函数替换 CEIL）。
1 select ceil(trx_id/5.0) as grp,
2 min(trx_date) as trx_start,
3 max(trx_date) as trx_end,
4 sum(trx_cnt) as total
5 from trx_log
6 group by ceil(trx_id/5.0)
SQL 按照时间单位分组 扩展知识
首先要对所有行数据进行逻辑分组，这是整个解决方案的关键所在。TRX_ID 除以 5，并找到大于该商值的最小整数，这样我们就实现了逻辑分组。例如：
select trx_id,
trx_date,
trx_cnt,
trx_id/5.0 as val,
ceil(trx_id/5.0) as grp
from trx_log
TRX_ID TRX_DATE TRX_CNT VAL GRP
------ -------------------- ------- ------ ---
1 28-JUL-2005 19:03:07 44 .20 1
2 28-JUL-2005 19:03:08 18 .40 1
3 28-JUL-2005 19:03:09 23 .60 1
4 28-JUL-2005 19:03:10 29 .80 1
5 28-JUL-2005 19:03:11 27 1.00 1
6 28-JUL-2005 19:03:12 45 1.20 2
7 28-JUL-2005 19:03:13 45 1.40 2
8 28-JUL-2005 19:03:14 32 1.60 2
9 28-JUL-2005 19:03:15 41 1.80 2
10 28-JUL-2005 19:03:16 15 2.00 2
11 28-JUL-2005 19:03:17 24 2.20 3
12 28-JUL-2005 19:03:18 47 2.40 3
13 28-JUL-2005 19:03:19 37 2.60 3
14 28-JUL-2005 19:03:20 48 2.80 3
15 28-JUL-2005 19:03:21 46 3.00 3
16 28-JUL-2005 19:03:22 44 3.20 4
17 28-JUL-2005 19:03:23 36 3.40 4
18 28-JUL-2005 19:03:24 41 3.60 4
19 28-JUL-2005 19:03:25 33 3.80 4
20 28-JUL-2005 19:03:26 19 4.00 4
最后，调用合适的聚合函数计算出每 5 秒钟有多少个交易，同时找出每一组交易的开始时间和结束时间。
select ceil(trx_id/5.0) as grp,
min(trx_date) as trx_start,
max(trx_date) as trx_end,
sum(trx_cnt) as total
from trx_log
group by ceil(trx_id/5.0)
GRP TRX_START TRX_END TOTAL
--- -------------------- -------------------- ----------
1 28-JUL-2005 19:03:07 28-JUL-2005 19:03:11 141
2 28-JUL-2005 19:03:12 28-JUL-2005 19:03:16 178
3 28-JUL-2005 19:03:17 28-JUL-2005 19:03:21 202
4 28-JUL-2005 19:03:22 28-JUL-2005 19:03:26 173
如果输入数据的格式与 TRX_LOG 表有所不同（例如缺少了 ID 列），我们可以用每一行的 TRX_DATE 对应的“秒”值除以 5，这样也能生成和上述解决方案类似的分组。然后，我们可以把每个 TRX_DATE 对应的“小时”也一并提取出来，并按照小时和“逻辑分组”GRP 值进行分组。下面的示例展示了这一技巧（此处用到了 Oracle 的 TO_CHAR 和 TO_NUMBER 函数，对于其他数据库需要使用适当的日期和字符串格式函数）。
select trx_date,trx_cnt,
to_number(to_char(trx_date,'hh24')) hr,
ceil(to_number(to_char(trx_date-1/24/60/60,'miss'))/5.0) grp
from trx_log
TRX_DATE TRX_CNT HR GRP
-------------------- ---------- ---------- ----------
28-JUL-2005 19:03:07 44 19 62
28-JUL-2005 19:03:08 18 19 62
28-JUL-2005 19:03:09 23 19 62
28-JUL-2005 19:03:10 29 19 62
28-JUL-2005 19:03:11 27 19 62
28-JUL-2005 19:03:12 45 19 63
28-JUL-2005 19:03:13 45 19 63
28-JUL-2005 19:03:14 32 19 63
28-JUL-2005 19:03:15 41 19 63
28-JUL-2005 19:03:16 15 19 63
28-JUL-2005 19:03:17 24 19 64
28-JUL-2005 19:03:18 47 19 64
28-JUL-2005 19:03:19 37 19 64
28-JUL-2005 19:03:20 48 19 64
28-JUL-2005 19:03:21 46 19 64
28-JUL-2005 19:03:22 44 19 65
28-JUL-2005 19:03:23 36 19 65
28-JUL-2005 19:03:24 41 19 65
28-JUL-2005 19:03:25 33 19 65
28-JUL-2005 19:03:26 19 19 65
无论 GRP 的实际值是多少，关键在于我们要把数据按照时间分组，每 5 秒一组。然后调用聚合函数，就像最初的解决方案那样。
select hr,grp,sum(trx_cnt) total
from (
select trx_date,trx_cnt,
to_number(to_char(trx_date,'hh24')) hr,
ceil(to_number(to_char(trx_date-1/24/60/60,'miss'))/5.0) grp
from trx_log
)x
group by hr,grp
HR GRP TOTAL
-- ---------- ----------
19 62 141
19 63 178
19 64 202
19 65 173
把 TRX_DATE 对应的“小时”也作为分组列是有特殊用意的，因为交易日志可能会分布在相邻的几个小时里。对于 DB2 和 Oracle，我们还可以调用窗口函数 SUM OVER 得出同样的结果集。下面的查询打印出了 TRX_LOG 表的全部数据，并基于“逻辑分组”生成了 TRX_CNT 的累计合计值，同时还为“逻辑分组”的每一行都计算出了当前分组的 TRX_CNT 合计值 TOTAL。
select trx_id, trx_date, trx_cnt,
sum(trx_cnt)over(partition by ceil(trx_id/5.0)
order by trx_date
range between unbounded preceding
and current row) runing_total,
sum(trx_cnt)over(partition by ceil(trx_id/5.0)) total,
case when mod(trx_id,5.0) = 0 then 'X' end grp_end
from trx_log
TRX_ID TRX_DATE TRX_CNT RUNING_TOTAL TOTAL GRP_END
------ -------------------- ---------- ------------ ---------- -------
1 28-JUL-2005 19:03:07 44 44 141
2 28-JUL-2005 19:03:08 18 62 141
3 28-JUL-2005 19:03:09 23 85 141
4 28-JUL-2005 19:03:10 29 114 141
5 28-JUL-2005 19:03:11 27 141 141 X
6 28-JUL-2005 19:03:12 45 45 178
7 28-JUL-2005 19:03:13 45 90 178
8 28-JUL-2005 19:03:14 32 122 178
9 28-JUL-2005 19:03:15 41 163 178
10 28-JUL-2005 19:03:16 15 178 178 X
11 28-JUL-2005 19:03:17 24 24 202
12 28-JUL-2005 19:03:18 47 71 202
13 28-JUL-2005 19:03:19 37 108 202
14 28-JUL-2005 19:03:20 48 156 202
15 28-JUL-2005 19:03:21 46 202 202 X
16 28-JUL-2005 19:03:22 44 44 173
17 28-JUL-2005 19:03:23 36 80 173
18 28-JUL-2005 19:03:24 41 121 173
19 28-JUL-2005 19:03:25 33 154 173
20 28-JUL-2005 19:03:26 19 173 173 X