HiveSQL实战积累_根据流量底表按半小时统计累加流量指标

发表于 | 分类于

HiveSQL实战积累_根据流量底表按半小时统计累加流量指标

1.背景

有一张流量底表，如下：

item_second_cate_cd trade_type terminal_type browser_uniq_id browse_tm
10026 0 1 jd3433244448 2022-10-01 13:34:56

现要求根据该流量底表按半个小时统计累加pv、uv指标，比如00:30计算出0点到0点半的数据，01:00计算出0点到1点的数据。

2.SQL

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
SELECT
			item_second_cate_cd,
			COALESCE(trade_type,'ALL')  as trade_type,
			COALESCE(terminal_type,'ALL') as terminal_type,
			date_dim as halfhour,
			sum(pv) as pv,
			count(distinct browser_uniq_id) as uv
		FROM
				(
				SELECT
					item_second_cate_cd,
					trade_type,
					terminal_type,
					count(1) as pv,
					browser_uniq_id,
					int(substr(browse_tm,12,2))*2 + case when int(substr(browse_tm,15,2)) < 30 then 0 else 1 end AS halfhour
				FROM
					browse_table
				GROUP BY
					item_second_cate_cd,
					trade_type,
					terminal_type,
					browser_uniq_id,
					int(substr(browse_tm,12,2))*2 + case when int(substr(browse_tm,15,2)) < 30 then 0 else 1 end
				) tmp
				lateral view explode(array('0','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')) b as date_dim
		WHERE
			halfhour <= date_dim
		GROUP BY
			item_second_cate_cd,
			trade_type,
			terminal_type,
			date_dim
		GROUPING SETS
			(
			(item_second_cate_cd,terminal_type,trade_type,date_dim),
			(item_second_cate_cd,terminal_type,date_dim),
			(item_second_cate_cd,trade_type,date_dim),
			(item_second_cate_cd,date_dim)
			)

3.技术要点

3.1 case when快捷按半小时分区

使用简洁的case when语句int(substr(browse_tm,12,2))*2 + case when int(substr(browse_tm,15,2)) < 30 then 0 else 1 end将浏览时间戳字段按一天的48个半小时分别打上标，方便按照该标进行group by从而统计出每半个小时的汇总数据。

3.2 lateral view explode列展开结合where判断实现累加

  • explode()函数可以将array或者map拆分成多行，此处使用explode(array('0','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行的数据结构。

  • lateral view用于创建一个虚拟表，此处使用select 字段A from 主表名 lateral view explode（array）虚拟表名 as 字段A语句相当于将这48行左关联到主表上去了。

  • 再使用WHERE halfhour <= date_dim筛选出参与累加的数据行，堪称绝妙。

3.3 grouping sets配合coalesce实现分类汇总共同计算

grouping sets空出的聚合字段会赋上NULL，比如按照item_second_cate_cd,terminal_type,date_dim进行聚合计算得到terminal_type字段的不同值，而按照item_second_cate_cd,date_dim进行聚合计算得到的terminal_type字段为NULL，此时巧妙的使用coalesce为该字段赋值为’ALL’代表全渠道汇总行，堪称巧妙与优雅。

但是这种方式的sql执行并发度并不如union all，所以在数据量较大，对时效性要求较高时，建议还是使用不同组合的多组group by+union all代替grouping sets。