大数据hive08-hivesql窗口函数案例

2020-05-29  本文已影响0人  数据蝉
一、窗口函数

1.相关函数说明
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化

CURRENT ROW:当前行

n PRECEDING:往前n行数据

n FOLLOWING:往后n行数据

UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED
FOLLOWING表示到后面的终点

LAG(col,n):往前第n行数据

LEAD(col,n):往后第n行数据

NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。

二、需求

(1)查询在2017年4月份购买过的顾客及总人数
(2)查询顾客的购买明细及月购买总额
(3)上述的场景,要将cost按照日期进行累加
(4)查询顾客上次的购买时间
(5)查询前20%时间的订单信息

三、建表
create table online.business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
四、插入数据
insert into online.business(name,orderdate,cost) values('jack','2017-01-01',10);
insert into online.business(name,orderdate,cost) values('tony','2017-01-02',15);
insert into online.business(name,orderdate,cost) values('jack','2017-02-03',23);
insert into online.business(name,orderdate,cost) values('tony','2017-01-04',29);
insert into online.business (name,orderdate,cost)values('jack','2017-01-05',46);
insert into online.business(name,orderdate,cost) values('jack','2017-04-06',42);
insert into online.business (name,orderdate,cost)values('tony','2017-01-07',50);
insert into online.business(name,orderdate,cost) values('jack','2017-01-08',55);
insert into online.business(name,orderdate,cost) values('mart','2017-04-08',62);
insert into online.business (name,orderdate,cost)values('mart','2017-04-09',68);
insert into online.business(name,orderdate,cost) values('neil','2017-05-10',12);
insert into online.business (name,orderdate,cost)values('mart','2017-04-11',75);
insert into online.business (name,orderdate,cost)values('neil','2017-06-12',80);
insert into online.business (name,orderdate,cost)values('mart','2017-04-13',94);
五、按需求查询数据

(1)查询在2017年4月份购买过的顾客及总人数

select name,count(*) over () 
from business 
where substring(orderdate,1,7) = '2017-04' 
group by name;
image.png

(2)查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from online.business;
image.png

(3)上述的场景,要将cost按照日期进行累加

select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED 
PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING 
and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING 
AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row 
and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from online.business;
image.png

(4)查看顾客上次的购买时间

select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 
from online.business;
image.png

(5)查询前20%时间的订单信息

select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;
image.png
上一篇 下一篇

猜你喜欢

热点阅读