HiveSQL实战积累_多个去重指标用户id列与加和数据共同存储

发表于 | 分类于

HiveSQL实战积累_多个去重指标用户id列与加和数据共同存储

1.背景

在线数据引擎主要存储两种数据：预计算数据和明细数据，但是实际上有一些实际应用场景需要在这中间做一些均衡。比如最典型的行业模块数据，以同样一份成交底表明细数据为数据源，需要展示类目、品牌、店铺等不同维度的数据。如果完全使用预计算，需要预计算的维度有些多，而且只能限定预计算时间维度，无法进行自定义时间查询；如果使用明细数据做olap数据量又太大了，会造成线上查询性能差。于是想到一个折中的办法就是进行明细上卷，将数据聚合到能满足查询条件的最大粒度。

进行数据上卷要考虑的主要有两类数据，一类是可加和数据，如pv、成交金额等，计算大粒度数据时可以直接将小粒度数据加和得到；还有一类是去重数据，如uv、成交客户数、加购客户数等，这一类数据在做上卷时不能直接计算出结果，需要保留原始数据以便在任意维度做去重聚合计算。

在上卷时保留去重指标原始数据的方式，常见的有bitmap存储、ck物化视图，现在介绍一种比较原始，没那么节约存储方法。

2.SQL实例

基于明细数据将可加和数据按sku粒度聚合计算保存，将去重指标数据按用户id+sku粒度group by保存，然后将可加和指标的聚合计算结果插入非去重指标数据的某一行。这种方案还可以将多个不同的去重指标数据存储在同一个表的同一行中。

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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
SELECT
	COALESCE(index.item_sku_id, user.item_sku_id) AS item_sku_id,
	COALESCE(index.rank_flag, user.rank_flag) AS rank_flag, --这个字段并没有实际意义，后续也不需要再使用到。
	pv,
	deal_sale_qtty,
	deal_sale_amount,
	deal_user_acct,
	browser_uniq_id,
	cart_user_acct,
	focus_user_acct
FROM
	(
		--可加和指标聚合计算数据
		SELECT
			item_sku_id,
			'1' AS rank_flag, --非常重要：这个非常关键，给可加和指标聚合数据打上一个标，相当于每个sku的每个指标数据只会放到一行中去
			pv,
			deal_sale_qtty,
			deal_sale_amount
		FROM
			adm.adm_zh_industry_sku_index_all_ref
		WHERE
			dt = '""" + ht.data_day_str + """'
			AND stat_ct_cd = 'day'
	)
	INDEX
FULL JOIN
	(
		--去重指标group by数据
		SELECT
			item_sku_id,
			rank_flag,
			MAX(deal_user_acct) AS deal_user_acct,
			MAX(browser_uniq_id) AS browser_uniq_id,
			MAX(cart_user_acct) AS cart_user_acct,
			MAX(focus_user_acct) AS focus_user_acct
		FROM
			(
				--成交用户
				SELECT
					item_sku_id,
					row_number() over(partition BY item_sku_id) rank_flag, --这个字段并没有实际意义，只是用来将不同指标数据关联存放在同一个行中。
					user_log_acct AS deal_user_acct,
					NULL AS browser_uniq_id,
					NULL AS cart_user_acct,
					NULL AS focus_user_acct
				FROM
					adm.adm_zh_deal_ord_idty
				WHERE
					dt = '""" + ht.data_day_str + """'
					AND user_log_acct IS NOT NULL
				GROUP BY
					item_sku_id,
					user_log_acct,
				
				UNION ALL
				
				--访问用户
				SELECT
					sku_id AS item_sku_id,
					row_number() over(partition BY sku_id) rank_flag,
					NULL AS deal_user_acct,
					browser_uniq_id,
					NULL AS cart_user_acct,
					NULL AS focus_user_acct
				FROM
					adm.adm_zh_industry_browse_base a
				WHERE
					dt = '"""+ ht.data_day_str + """'
					AND browser_uniq_id IS NOT NULL
				GROUP BY
					sku_id,
					browser_uniq_id
				
				UNION ALL
				
				--加购用户
				SELECT
					sku_id AS item_sku_id,
					row_number() over(partition BY sku_id) rank_flag,
					NULL AS deal_user_acct,
					NULL AS browser_uniq_id,
					user_log_acct AS cart_user_acct,
					NULL AS focus_user_acct
				FROM
					adm.adm_zh_m14_online_log_cart a
				WHERE
					dt = '"""+ ht.data_day_str + """'
					AND user_log_acct IS NOT NULL
				GROUP BY
					sku_id,
					user_log_acct
				
				UNION ALL
				
				--关注用户
				SELECT
					biz_content AS item_sku_id,
					row_number() over(partition BY biz_content) rank_flag,
					NULL AS deal_user_acct,
					NULL AS browser_uniq_id,
					NULL AS cart_user_acct,
					pin AS focus_user_acct
				FROM
					fdm.fdm_follow_p_general_follow_pin_v2_chain a
				WHERE
					start_date <= '"""+ ht.data_day_str + """'
					AND end_date > '"""+ ht.data_day_str + """'
					AND to_date(create_time) = '"""+ ht.data_day_str + """'
					AND pin IS NOT NULL
				GROUP BY
					biz_content,
					pin
			)
			tmp
		GROUP BY
			item_sku_id,
			terminal_type,
			trade_type,
			rank_flag
	)
	USER
ON
	index.item_sku_id = user.item_sku_id
	AND index.rank_flag = user.rank_flag

知识要点：上述实例巧妙的使用一个rank_flag先将不同指标的用户id关联到同一行中，尽可能的减少行数，使得一个sku存储的行数就是用户id最多的那个指标的用户id数，然后又用这个rank_flag将可加和指标数据存放到其中一行中去，仅可能的节约了存储。