SQL 训练营

在 Hive 上进行 SQL 练习。

`over`

案例一

表中有如下字段：

domain           time     traffic(T)
gifshow.com   2019/01/01    5
yy.com        2019/01/01    4
huya.com      2019/01/01    1
gifshow.com   2019/01/20    6
gifshow.com   2019/02/01    8
yy.com        2019/01/20    5
gifshow.com   2019/02/02    7

需求是按月统计每个用户的累计访问量(只能用一个 SQL)，结果如下

domain          month     traffics   totals
gifshow.com     2019-01      11         11
gifshow.com     2019-02      15         26
yy.com          2019-01       9         9
huya.com        2019-01       1         1

需求分析：

每个用户每月的访问量：group by 用户，月；然后再 sum
相同每月数据累加：

第一步的按月和用户统计：

select domain,substr(time,1,7) ,sum(traffic) from domain_traffic group by domain,substr(time,1,7);

结果输出如下：

gifshow.com     2019/01 11
gifshow.com     2019/02 15
huya.com        2019/01 1
yy.com  2019/01 9

第二步，相同用户每月累加

select ms.domain,ms.m,ms.s,sum(ms.s)over(partition by ms.domain order by ms.m) from 
(select domain,substr(time,1,7) m ,sum(traffic) s from domain_traffic group by domain,substr(time,1,7)) ms;

使用 sum()over() 函数实现累加功能，over 实现分组排序，sum 实现就有点意思：将本组内当前行以及之前的行全部相加(01之前没有最终只有01，02之前是01最终是01+02)。

案例二：

表数据如下：

uid		pid
li  	a
zhang   b
li  	a
zhang   a
wang    a
john    a
zhang   a
wang    a
tom 	b
ao  	b
wang    b
tom 	b
wang    b
wang    b
wang    b
zhang   b
zhang   b
ao  	a

1) 统计产品 UV
2）统计每个产品 top3 用户

UV：按产品分组，count (uid 排重 )

select pid,count(distinct uid) from uid_pid group by pid;

结果：

a       5
b       4

top3：

按产品、用户分组，count(1) 排序，limit 取 top (整体取 top)

select uid,pid,count(1) m from uid_pid group by uid,pid order by m desc limit 3

产品 top ：

select t.uid,t.pid,t.m from (select s.uid,s.pid,s.m,row_number()over(partition by pid order by s.m desc) as rank from (select uid,pid,count(1) m from uid_pid group by uid,pid) s ) t where t.rank <= 3;

输出

zhang   a       2
wang    a       2
li      a       2
wang    b       4
zhang   b       3
tom     b       2

使用 row_number()over 函数实现分组 Top。

row_number()over() 作用就是分组排序并加上序号标记：over 中按 pid 分组，并按 次数 m 降序排列，row_numbe() 记录排序相当于增加了一列序号 rank。

总结

以上两个案例都用到 over 这个函数，我们从 MR 角度来讲解 over 作用。

行列转换

行转列

将多行数据合并成某列

user_id	order_id
104399	2105395
104399	1715131
104400	1609001
104400	2986088
104400	1795054

select user_id,concat_ws(',',collect_set(order_id)) as order_value
from table
group by user_id ;

结果输出

user_id	order_value
104399	2105395,1715131
104400	1609001,2986088,1795054

按 user_id 分组后，每个user_id 都对应多个order_id；接着 collect_set 收集多个 order_id 并去重；最后由 concat_ws 指定分隔符将数组中的 order_id 合并成一个字符串

collect_list 不去重，collect_set 去重，但类型要求是 string

将多行数据转成多列

name	subject	score
张三	语文	80
张三	数学	90
张三	英语	60

select name,
	max(case when subject ='语文' then score else 0 end) as Chinese,
	max(case when subject ='数学' then score else 0 end) as Math,
	max(case when subject ='英语' then score else 0 end) as English
from table
group by name

name	Chinese	Math	English
张三	80	90	60

列转行

将某列数据扩展成多行

user_id	order_value
104399	2105395,1715131
104400	1609001,2986088,1795054

select user_id,order_id
from table
lateral view explode(split(order_value,',')) t as order_id;

结果输出

user_id	order_id
104399	2105395
104399	1715131
104400	1609001
104400	2986088
104400	1795054

lateral view explode(数组) 将数组中每个值都扩展成一行中列值。上面例子中，由 split 将 order_value 变成一个 order 数组，然后再由 explode 扩展成列。

将多列数据转成多行

name	Chinese	Math	English
张三	80	90	60

 select a.name,b.label,b.value
 from (select * from table )a
    lateral view explode(map(
        '语文',Chinese,
        '数学',Math,
        '英语',English
    )) b as label ,value

name	subject	score
张三	语文	80
张三	数学	90
张三	英语	60

留存

用户留存率一般是面向新增用户的概念，指某一天注册后的几天还是否活跃,是以每天为单位进行计算的。一般收到的需求都是一个时间段内的新增用户的几天留存。

根据留存的定义可知，我们需要求两个数：新增和某日留存数，两者相除可得留存率。新增数很简单一般都会有标识，留存数需要有条件限定：假设求2018-05-18日的3日留存，先获取2018-05-18的新增 user_ids，然后判断当前活跃用户的 id 是否包含在 user_ids 且 ( 当前活跃的日期 - 2018-05-18 ) = 2。

/*计算某日新增登录设备的次日、3日、7日、14日、30日、90日留存率*/
SELECT
	log_day '日期',
	count(user_id_d0) '新增数量',
	count(user_id_d1) / count(user_id_d0) '次日留存',
	count(user_id_d3) / count(user_id_d0) '3日留存',
	count(user_id_d7) / count(user_id_d0) '7日留存',
	count(user_id_d14) / count(user_id_d0) '14日留存',
	count(user_id_d30) / count(user_id_d0) '30日留存',
	count(user_id_d90) / count(user_id_d0) '90日留存'
FROM
	(
		SELECT DISTINCT
			log_day,
			a.user_id_d0,
			b.device_id AS user_id_d1,
			c.device_id AS user_id_d3,
			d.device_id AS user_id_d7,
			e.device_id AS user_id_d14,
			f.device_id AS user_id_d30,
			g.device_id AS user_id_d90
		FROM
			(
				SELECT DISTINCT
					Date(event_time) AS log_day,
					device_id AS user_id_d0
				FROM
					role_login_back
				GROUP BY
					device_id
				ORDER BY
					log_day
			) a
		LEFT JOIN role_login_back b ON DATEDIFF(DATE(b.event_time),a.log_day) = 1
		AND a.user_id_d0 = b.device_id
		LEFT JOIN role_login_back c ON DATEDIFF(DATE(c.event_time),a.log_day) = 2
		AND a.user_id_d0 = c.device_id
		LEFT JOIN role_login_back d ON DATEDIFF(DATE(d.event_time),a.log_day) = 6
		AND a.user_id_d0 = d.device_id
		LEFT JOIN role_login_back e ON DATEDIFF(DATE(e.event_time),a.log_day) = 13
		AND a.user_id_d0 = e.device_id
		LEFT JOIN role_login_back f ON DATEDIFF(DATE(f.event_time),a.log_day) = 29
		AND a.user_id_d0 = f.device_id
		LEFT JOIN role_login_back g ON DATEDIFF(DATE(g.event_time),a.log_day) = 89
		AND a.user_id_d0 = g.device_id
	) AS temp
GROUP BY
log_day

参考资料

Hive窗口函数 row_number over()和sum() over()的使用详解 hive原理与源码分析-hive源码架构与理论（一）

次日、3日、7日、14日、30日、90日留存计算SQL命令

hive中统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数

`over`

案例一

案例二：

总结

行列转换

行转列

列转行

留存

参考资料

MENU

FEATURED TAGS

FRIENDS