hive日常数据需求处理（窗口函数的使用）

作者: 喝奶茶不加奶茶 | 来源:发表于2020-07-29 17:00 被阅读0次

背景：

日常工作中有许多数据处理需求需要解决，在此之间，获得需求，用hive实现需求，最终完成任务。

数据源：

hive中的adventure_ods库的ods_sales_orders表
表结构：

需求一：

统计每个用户截止到每月为止的最大交易金额和累计到该月的总交易金额，结果数据格式如下：

思路：
“每个用户每月”--分组依据（create_date）
“最大”--max() over()
“累计”--sum() over()
实现：
(1)先按年月进行分组的基础查询（用户id，日期，数量，金额）

select 
customer_key,substr(create_date,1,7) as  `umonth(当月)`,
count(1) as ucount ,sum(unit_price) as usum
from ods_sales_orders
group by customer_key,substr(create_date,1,7)
limit 10;

（2）利用窗口函数，求最大金额和累计金额

select customer_key, 
`umonth(当月)`, 
ucount,
max(usum) over(partition by customer_key order by `umonth(当月)` rows between unbounded preceding and current row) as current_max,
sum(usum) over(partition by customer_key order by `umonth(当月)` rows between unbounded preceding and current row) as current_sum
from 
(select 
customer_key,substr(create_date,1,7) as  `umonth(当月)`,
count(1) as ucount ,
sum(unit_price) as usum
from ods_sales_orders
group by customer_key,substr(create_date,1,7)) a 
limit 10;

需求二：

计算用户的回购率和复购率

复购率：当前月份购买2次及以上的客户占所有客户比例
回购率：当前月份购买且上个月份也购买的客户占当月所有月份客户比例

思路：

复购率：
（1）对客户分组，计数购买次数

select customer_key,
substr(create_date,1,7) as umonth,
count(1) as con
from ods_sales_orders
group by customer_key,substr(create_date,1,7)
limit 10;

（2）筛选购买次数为2及以上的，即为复购群体，并计算复购率

select 
umonth,
count(1) as `该月客户总数`,
count(if (con>1,1,null)) as `该月再次购买客户数`,
count(if(con>1,1,null))/count(1) as `该月复购率`
from 
(select customer_key,
substr(create_date,1,7) as umonth,
count(1) as con
from ods_sales_orders
group by customer_key,substr(create_date,1,7)
)a 
group by umonth
limit 10;

注意：
mysql和hive中都是count(null)=0。

回购率：
思路：
这里回购的定义是：当月和下月购买时间相隔1个月即可（感觉定义不是很合理的样子...）

所以限制条件：表自关联，左右表在当月和间隔1个月的下月的相同客户id
把复购率和回购率一起查询以便于查看。

select 
a.`日期`,
count(if(a.con>1,1,null))/count(1) as `复购率`,
count(b.customer_key)/count(a.customer_key)  as `回购率`
from (select customer_key,
substr(create_date,1,7) as `日期`,
count(1) as con 
from ods_sales_orders
group by customer_key,substr(create_date,1,7)) a 
left join 
(select customer_key,
substr(create_date,1,7) as `日期`
from ods_sales_orders
group by customer_key,substr(create_date,1,7)) b 
on a.customer_key=b.customer_key
and concat(a.`日期`,'-01') =add_months(concat(b.`日期`,'-01'),1)
group by a.`日期`
limit 10;

注意：
hive中add_months(日期，N) 函数可将日期往前、往后推N个月。主要有两种格式：
add_months（‘yyyy-MM-dd hh:mm:ss’，N），add_months（‘yyyy-MM-dd’，N）。N为正往后推，N为负往前推。
（强调：这里的日期格式必须包含年月日三部分，否则（比如只包含年月）会报错，）

可以再优化一下格式，把比率以百分比的形式显示，利用concat(round(复购率*100，2),'%')
（round()函数的作用是四舍五入保留两位数）

select 
a.`日期`,
concat(round(count(if(a.con>1,1,null))/count(1)*100,2),'%') as `复购率`,
concat(round(count(b.customer_key)/count(a.customer_key)*100,2),'%')  as `回购率`
from (select customer_key,
substr(create_date,1,7) as `日期`,
count(1) as con 
from ods_sales_orders
group by customer_key,substr(create_date,1,7)) a 
left join 
(select customer_key,
substr(create_date,1,7) as `日期`
from ods_sales_orders
group by customer_key,substr(create_date,1,7)) b 
on a.customer_key=b.customer_key
and concat(a.`日期`,'-01') =add_months(concat(b.`日期`,'-01'),1)
group by a.`日期`
limit 10;

需求三：

求用户最先购买的两种子类别产品信息，要求拼接成 “用户号-产品1-产品2” 的形式

with demo as ()
,
demo2 as (
select ...
from demo)
select
concat(customer_key, '-', concat_ws('-', collect_set(cpzl_zw))) as zl
from demo2
where cpzl_zw_num < 3
GROUP BY customer_key;

完整代码：

with demo as (select
customer_key,
cpzl_zw,
order_num,
cpzl_zw1
from 
(select 
customer_key,
cpzl_zw,
row_number() over(partition by customer_key order by create_date asc)  as order_num,
lag(cpzl_zw,1,0) over(partition by customer_key order by create_date asc) as cpzl_zw1
from ods_sales_orders)a
where cpzl_zw!=cpzl_zw1
),demo2 as (
select
customer_key,
cpzl_zw,
order_num,
cpzl_zw1,
row_number()over(partition by customer_key order by order_num) as cpzl_zw_num
from demo)
select concat(customer_key,'-',concat_ws('-',collect_set(cpzl_zw))) as zl
from demo2
where cpzl_zw_num<3
group by customer_key
limit 10;

知识点回顾：

lag()over()
lag（参数1，参数2，参数3）over（order by 列）
参数1：表中列名参数2：往下偏移多少位参数3：超出行数时默认设置值
with as
with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。
with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！

WITH t1 AS (
        SELECT *
        FROM carinfo
    ), 
    t2 AS (
        SELECT *
        FROM car_blacklist
    )
SELECT *
FROM t1, t2

注意：这里必须要整体作为一条sql查询，即with as语句后不能加分号，不然会报错。

with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来.
如果定义了with子句，但其后没有跟select查询，则会报错！
没有使用 t1没关系，其后有select就行
前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句！

collect_set()
collect_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

select customer_key,
collect_set(cpzl_zw)
from ods_sales_orders
group by customer_key
limit 10;

concat_ws
如何指定参数之间的分隔符
使用函数CONCAT_WS（）。使用语法为：CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

select customer_key,
concat_ws('-',collect_set(cpzl_zw))
from ods_sales_orders
group by customer_key
limit 10;

对比：
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。

需求4：

统计各个省份所属城市下最受欢迎的Top 3产品和其销量（不能出现有null）
思路：

查询目标：
(ods_sales_orders 表)
"产品" --product_key（产品编号）
"销量"--sales_order_key（订单主键，一个订单表示销售一个产品）

分组依据：
（ods_customer表）
"省份、城市" --chinese_province(所在省份)、chinese_city（所在城市）
结合查询目标以及group by 特性，分组依据调整为：chinese_city（所在城市）以及product_key（产品编号）

需要用到 ods_customer 表和 ods_sales_orders 表，二者用customer_key关联

“最受欢迎top3” --row_number() over()
(1)基础查询

select 
a.chinese_city,
b.product_key,
count(b.sales_order_key)  as sale_amount
from ods_customer a 
left join ods_sales_orders b 
on a.customer_key=b.customer_key
group by a.chinese_city,b.product_key
limit 10;

(2)利用row_number()over()进行排序

select chinese_city,
product_key,
sale_amount,
row_number() over(partition by chinese_city order by sale_amount desc) as rank
from 
(select 
a.chinese_city ,
b.product_key,
count(b.sales_order_key)  as sale_amount
from ods_customer a 
left join ods_sales_orders b 
on a.customer_key=b.customer_key
group by a.chinese_city,b.product_key) m
limit 10;

(3)取Top3
框架：

select 
n.chinese_city,
n.product_key,
n.sale_amount,
n.rank
from ()n
where n.rank<4;

完整代码：

select 
n.chinese_city,
n.product_key,
n.sale_amount,
n.rank
from (select chinese_city,
product_key,
sale_amount,
row_number() over(partition by chinese_city order by sale_amount desc) as rank
from 
(select 
a.chinese_city ,
b.product_key,
count(b.sales_order_key)  as sale_amount
from ods_customer a 
left join ods_sales_orders b 
on a.customer_key=b.customer_key
group by a.chinese_city,b.product_key) m)n
where n.rank<4
limit 10;

但发现n.chinese_city中存在'null'值，修改完善完整代码如下：

select 
n.chinese_city,
n.product_key,
n.sale_amount,
n.rank
from (select chinese_city,
product_key,
sale_amount,
row_number() over(partition by chinese_city order by sale_amount desc) as rank
from 
(select 
a.chinese_city ,
b.product_key,
count(b.sales_order_key)  as sale_amount
from ods_customer a 
left join ods_sales_orders b 
on a.customer_key=b.customer_key
group by a.chinese_city,b.product_key) m)n
where n.rank<4
and n.chinese_city!='null'
limit 10;

注意：null需要加单引号，如果不加会报错。
但是存在疑问：
拿到一张表，如何判断表中的null是单纯的null还是‘null’呢？
都试一下。
总结：
对于取特定条件下的前几个排名的需求，一般分三步，
第一步：安装限定分组条件进行基础查询
第二步：利用row_number() over()进行排序标注
第三步：利用where取出目标记录

需求5：

商品的销售数量top10，排名需考虑并列排名的情况
思路：
“考虑并列排名”--dense_rank() over()
"top10"--基础查询-->排名-->目标筛选
（1）基础查询

select 
product_key,
count(sales_order_key) as sale_amount
from ods_sales_orders
group by product_key
limit 10;

(2)利用dense_rank() over()进行并列排序

select 
a.product_key,
a.sale_amount,
dense_rank() over(order by a.sale_amount desc ) as rank1
from (select 
product_key,
count(sales_order_key) as sale_amount
from ods_sales_orders
group by product_key) a limit 10;

(3)筛选目标查询值

select
m.product_key,
m.sale_amount,
m.rank1
from
(select 
a.product_key,
a.sale_amount,
dense_rank() over(order by a.sale_amount desc ) as rank1
from (select 
product_key,
count(sales_order_key) as sale_amount
from ods_sales_orders
group by product_key) a) m
where m.rank1<11
limit 15;

需求六：

计算累计和(统计2019年1-12月的累积销量，即1月为1月份的值，2月为1、2月份值的和，3月为1、2、3月份的和，12月为1-12月份值的和)

“到当前月份” --rows between...and...
"累计"--sum() over()
(1)按月分组进行基础查询

select 
substr(create_date,1,7) as `日期`,
count(sales_order_key) as sale_amount
from ods_sales_orders
group by substr(create_date,1,7)
limit 10;

（2）计算累计销量并筛选日期为2019年的记录

select 
substr(create_date,1,7) as `日期`,
count(sales_order_key) as sale_amount,
sum(count(sales_order_key))over(order by substr(create_date,1,7) asc rows between 
unbounded preceding and current row) as `累计销量`
from ods_sales_orders
where year(create_date)='2019'
group by substr(create_date,1,7)
limit 10;

需求七：

计算客户平均购买一次商品的间隔时间
思路：
利用lead() over()
(1)基础查询

select customer_key,
create_date,
lead(create_date,1) over(partition by customer_key order by create_date) as `下次购买时间`
,datediff(lead(create_date,1) over(partition by customer_key order by create_date),create_date) as `时间间隔`
from ods_sales_orders
limit 10;

（2）完善细化成目标查询
框架：

select customer_key,avg(`时间间隔`) as `平均时间间隔`
from () a 
where `时间间隔`>0
group by customer_key;

完整代码：

select customer_key,avg(`时间间隔`) as `平均时间间隔`
from (select customer_key,
create_date,
lead(create_date,1) over(partition by customer_key order by create_date) as `下次购买时间`
,datediff(lead(create_date,1) over(partition by customer_key order by create_date),create_date) as `时间间隔`
from ods_sales_orders) a 
where `时间间隔`>0
group by customer_key
limit 10 offset 50;

需求八：

查询最近前20%时间的订单信息
思路：使用ntile(n)切片函数
（1）使用ntile()函数将订单时间按顺序分为5部分

select *,
ntile(5) over(order by create_date desc) 
from ods_sales_orders 
limit 10;

(2)去编号数为1的即为所求前20%

select *
from (select *,
ntile(5) over(order by create_date desc) as number
from ods_sales_orders)m
where m.number=1
limit 10;

hive日常数据需求处理（窗口函数的使用）

背景：

数据源：

需求一：

需求二：

需求三：

需求4：

需求5：

需求六：

需求七：

需求八：

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

zhaoyqiu的数据分析进阶之路2.0