SQL进阶技巧：如何分区间或分时段统计数据？| 等距分桶问题应用

时间：2024-10-03 07:20:34

目录

0 需求

1 数据准备

2 数据分析

3 小结

0 需求

有一批交易数据，并希望每5秒中汇总一下这些数据。trx_log数据如下：

trx_id trx_date trx_cnt
1 2005-07-28 19:03:07 44
2 2005-07-28 19:03:08 18
3 2005-07-28 19:03:09 23
4 2005-07-28 19:03:10 29
5 2005-07-28 19:03:11 27
6 2005-07-28 19:03:12 45
7 2005-07-28 19:03:13 45
8 2005-07-28 19:03:14 32
9 2005-07-28 19:03:15 41
10 2005-07-28 19:03:16 15
11 2005-07-28 19:03:17 24
12 2005-07-28 19:03:18 47
13 2005-07-28 19:03:19 37
14 2005-07-28 19:03:20 48
15 2005-07-28 19:03:21 46
16 2005-07-28 19:03:22 44
17 2005-07-28 19:03:23 36
18 2005-07-28 19:03:24 41
19 2005-07-28 19:03:25 33
20 2005-07-28 19:03:26 19

希望得到如下所示的结果集：

grp trx_start trx_end total
1 2005-07-28 19:03:07 2005-07-28 19:03:11 141
2 2005-07-28 19:03:12 2005-07-28 19:03:16 178
3 2005-07-28 19:03:17 2005-07-28 19:03:21 202
4 2005-07-28 19:03:22 2005-07-28 19:03:26 173

1 数据准备

（1）数据

vim trx_log.txt

    

  1. 

    

    
1	2005-07-28 19:03:07	44

    

    2. 

  2. 

    

    
2	2005-07-28 19:03:08	18

    

    3. 

  3. 

    

    
3	2005-07-28 19:03:09	23

    

    4. 

  4. 

    

    
4	2005-07-28 19:03:10	29

    

    5. 

  5. 

    

    
5	2005-07-28 19:03:11	27

    

    6. 

  6. 

    

    
6	2005-07-28 19:03:12	45

    

    7. 

  7. 

    

    
7	2005-07-28 19:03:13	45

    

    8. 

  8. 

    

    
8	2005-07-28 19:03:14	32

    

    9. 

  9. 

    

    
9	2005-07-28 19:03:15	41

    

    10. 

  10. 

    

    
10	2005-07-28 19:03:16	15

    

    11. 

  11. 

    

    
11	2005-07-28 19:03:17	24

    

    12. 

  12. 

    

    
12	2005-07-28 19:03:18	47

    

    13. 

  13. 

    

    
13	2005-07-28 19:03:19	37

    

    14. 

  14. 

    

    
14	2005-07-28 19:03:20	48

    

    15. 

  15. 

    

    
15	2005-07-28 19:03:21	46

    

    16. 

  16. 

    

    
16	2005-07-28 19:03:22	44

    

    17. 

  17. 

    

    
17	2005-07-28 19:03:23	36

    

    18. 

  18. 

    

    
18	2005-07-28 19:03:24	41

    

    19. 

  19. 

    

    
19	2005-07-28 19:03:25	33

    

    20. 

  20. 

    

    
20	2005-07-28 19:03:26	19

    

    21.

(2) 建表

    

  1. 

    

    
drop table trx_log;
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    
create table trx_log(
    

    4. 

  4. 

    

            trx_id string,
    

    5. 

  5. 

    

            trx_date string,
    

    6. 

  6. 

    

            trx_cnt string
    

    7. 

  7. 

    

        )
    

    8. 

  8. 

    

        row format delimited fields terminated by '\t'

    

    9.

(3) 加载数据 

load data local inpath "/home/centos/dan_test/trx_log.txt" into table trx_log;

（4） 查询数据

    

  1. 

    

    hive> select * from trx_log;
    

    2. 

  2. 

    

    OK
    

    3. 

  3. 

    

    
1	2005-07-28 19:03:07	44

    

    4. 

  4. 

    

    
2	2005-07-28 19:03:08	18

    

    5. 

  5. 

    

    
3	2005-07-28 19:03:09	23

    

    6. 

  6. 

    

    
4	2005-07-28 19:03:10	29

    

    7. 

  7. 

    

    
5	2005-07-28 19:03:11	27

    

    8. 

  8. 

    

    
6	2005-07-28 19:03:12	45

    

    9. 

  9. 

    

    
7	2005-07-28 19:03:13	45

    

    10. 

  10. 

    

    
8	2005-07-28 19:03:14	32

    

    11. 

  11. 

    

    
9	2005-07-28 19:03:15	41

    

    12. 

  12. 

    

    
10	2005-07-28 19:03:16	15

    

    13. 

  13. 

    

    
11	2005-07-28 19:03:17	24

    

    14. 

  14. 

    

    
12	2005-07-28 19:03:18	47

    

    15. 

  15. 

    

    
13	2005-07-28 19:03:19	37

    

    16. 

  16. 

    

    
14	2005-07-28 19:03:20	48

    

    17. 

  17. 

    

    
15	2005-07-28 19:03:21	46

    

    18. 

  18. 

    

    
16	2005-07-28 19:03:22	44

    

    19. 

  19. 

    

    
17	2005-07-28 19:03:23	36

    

    20. 

  20. 

    

    
18	2005-07-28 19:03:24	41

    

    21. 

  21. 

    

    
19	2005-07-28 19:03:25	33

    

    22. 

  22. 

    

    
20	2005-07-28 19:03:26	19

    

    23. 

  23. 

    

    
Time taken: 0.093 seconds, Fetched: 20 row(s)
    

    24.

2 数据分析

分析：此题就是典型的分区间分时间段统计问题。相当于把全部数据分别放入若干个桶，每个桶中放5行数据，桶的大小是固定的。创建好桶之后，调用聚合函数min(),max()和sum()函数分别计算开始时间和结束时间及每个分桶之后的交易数目合计。

第一步：分桶创建

    

  1. 

    

    
select trx_id
    

    2. 

  2. 

    

          ,trx_date
    

    3. 

  3. 

    

          ,trx_cnt
    

    4. 

  4. 

    

          ,int((unix_timestamp(trx_date)-unix_timestamp(first_value(trx_date) over(order by trx_date )))/5) as flag
    

    5. 

  5. 

    

    
from trx_log
    

    6.

分析：要把每五秒数据分成一个桶，利用unix_timestamp(trx_date)函数将时间字符串转换成秒值，然后用每一行的秒值与升序排序后的第一行数据的秒值相减的差值组成新的序列，然后对该序列使用int(序列/5)得到分桶id，这样便可以把每5秒的数据放在一个桶里。代码如下：

  • int((unix_timestamp(trx_date)-unix_timestamp(first_value(trx_date) over(order by trx_date )))/5)

注意此题，不能直接取秒除以5，原因是同一5秒钟的数据不一定在一个桶里（读者自行尝试）。由于本题给的时间数据比较连续，所以分桶的时候，可以用trx_id，但这样做不严谨，如果每5秒中的数据中间有不连续的，此时用trx_id便会出现问题，会把下一个时间段为5秒的部分数据分在一起。

第二步：按照分桶进行聚合

    

  1. 

    

    
select grp
    

    2. 

  2. 

    

          ,min(trx_date) as trx_start
    

    3. 

  3. 

    

          ,max(trx_date) as trx_end
    

    4. 

  4. 

    

          ,sum(trx_cnt)  as total 
    

    5. 

  5. 

    

    
from(
    

    6. 

  6. 

    

    
select trx_id
    

    7. 

  7. 

    

          ,trx_date
    

    8. 

  8. 

    

          ,trx_cnt
    

    9. 

  9. 

    

          ,int((unix_timestamp(trx_date)-unix_timestamp(first_value(trx_date) over(order by trx_date )))/5) as grp
    

    10. 

  10. 

    

    
from trx_log
    

    11. 

  11. 

    

    ) t
    

    12. 

  12. 

    

    
group by grp
    

    13.

 求的结果如下：

    

  1. 

    

    OK
    

    2. 

  2. 

    

    
0	2005-07-28 19:03:07	2005-07-28 19:03:11	141.0

    

    3. 

  3. 

    

    
1	2005-07-28 19:03:12	2005-07-28 19:03:16	178.0

    

    4. 

  4. 

    

    
2	2005-07-28 19:03:17	2005-07-28 19:03:21	202.0

    

    5. 

  5. 

    

    
3	2005-07-28 19:03:22	2005-07-28 19:03:26	173.0

    

    6. 

  6. 

    

    
Time taken: 20.033 seconds, Fetched: 4 row(s)
    

    7.

进一步本题也可以将小时时间展示出来，这样更符合业务意义：具体代码如下

    

  1. 

    

    
select hr
    

    2. 

  2. 

    

          ,grp
    

    3. 

  3. 

    

          ,min(trx_date) as trx_start
    

    4. 

  4. 

    

          ,max(trx_date) as trx_end
    

    5. 

  5. 

    

          ,sum(trx_cnt)  as total 
    

    6. 

  6. 

    

    
from(
    

    7. 

  7. 

    

    
select trx_id
    

    8. 

  8. 

    

          ,trx_date
    

    9. 

  9. 

    

          ,trx_cnt
    

    10. 

  10. 

    

          ,hour(trx_date) as hr
    

    11. 

  11. 

    

          ,int((unix_timestamp(trx_date)-unix_timestamp(first_value(trx_date) over(order by trx_date )))/5) as grp
    

    12. 

  12. 

    

    
from trx_log
    

    13. 

  13. 

    

    ) t
    

    14. 

  14. 

    

    
group by grp,hr
    

    15.

结果如下：

    

  1. 

    

    --------------------------------------------------------------------------------
    

    2. 

  2. 

    

    OK
    

    3. 

  3. 

    

    
19	0	2005-07-28 19:03:07	2005-07-28 19:03:11	141.0

    

    4. 

  4. 

    

    
19	1	2005-07-28 19:03:12	2005-07-28 19:03:16	178.0

    

    5. 

  5. 

    

    
19	2	2005-07-28 19:03:17	2005-07-28 19:03:21	202.0

    

    6. 

  6. 

    

    
19	3	2005-07-28 19:03:22	2005-07-28 19:03:26	173.0

    

    7. 

  7. 

    

    
Time taken: 8.706 seconds, Fetched: 4 row(s)
    

    8.

3 小结

本题主要分析了分区间、分时间段求解的思路，主要是利用按照创建固定桶个数进行分组的思路，进行动态分区间。当确定了桶的个数的时候，一定要找准是按照哪个字段进行分区间，如果该字段存在分桶的不唯一性，则需要观察规律，生成具有符合条件唯一确定分组意义的序列，比如本题按照时间进行求差值。本题知识点总结：

  • 时间字符串求秒的求法unix_timestamp()函数
  • 窗口函数first_value()的使用
  • 固定分桶的创建
  • 小时的求法hour()函数

本题题拓展：

表table中存在一个text字段，需要统计出文本长度为[0,20]、[20,40]、[40,60]…按区间划分的数量。其中，需要自动化到包含最大值max的区间。 

具体答案读者自行完成

相关文章