Hive SQL 常见需求集锦
需求一: 2021年9月购买过得用户/ 顾客及总人数
select
name ,
count(1) over(rows between unbouned preceding and unbounded following)
from
w_orders
where
year(time) = '2021-09'
group by name;
需求二: 顾客的购买明细信息以及每月购买总额
select
name,time,cost
sum(cost) over(partition by name,substring(time,1,7) order by time) month
from
w_orders;
需求三: 顾客的购买明细信息以及按时间累计总额
select
name,time,cost
sum(cost) over(partition by name order by time) costs
from
w_orders;
需求四: 顾客的购买明细信息以及上次的购买时间
select
name,time,cost
lag(time,1,'-----') over(partition by name order by time) last_cost
from
w_orders;
需求五: 顾客购买明细信息以及本月第一次购买时间
select
name,time,cost
first_value(time) over(partition by name,substring(time,1,7) order by time) time
from
w_orders;
顾客购买明细信息以及本月最后一次购买时间
select
name,orderdate,cost,
LAST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) lastorderdate
from business;
查询前 40% 时间的订单信息
select name,orderdate,cost from
(select name,orderdate,cost,cume_dist() over(order by orderdate) cumedist from business) tmp
where cumedist<0.2;
