Hive补充之窗口函数

20201210,10001,84
20201210,10002,83
20201210,10003,86
20201211,10001,87
20201211,10002,65
20201211,10003,98
20201212,10001,67
20201212,10002,28
20201212,10003,89
20201213,10001,99
20201213,10002,55
20201213,10003,57

create table test_window
(day string,    
uid string, 
score int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath '/export/data/hive/test_window.txt' into table test_window;

select *, count(uid) over() as total  from  test_window;

select  *,count(*) over(partition by logday)as day_total from  test_window;

select  *,count(*) over(order by logday rows between unbounded preceding and current row)as total from  test_window where score > 80;

jack,2020-01-01,11
tony,2020-01-02,16
jack,2020-02-03,22
tony,2020-01-04,28
jack,2020-01-05,47
jack,2020-04-06,43
tony,2020-01-07,50
jack,2020-01-08,55
mart,2020-04-08,63
mart,2020-04-09,69
tom,2020-05-10,13
mart,2020-04-11,76
tom,2020-06-12,81
mart,2020-04-13,95

create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load  data local inpath "/export/data/hive/business.txt" into table business;

select   *,count(*) over() as total  from  business  where substr(orderdate,1,7) = '2020-04';

select *,sum(cost) over(partition by name,substr(orderdate,1,7)) total_amount
from business;

select *,sum(cost) over(partition by name order  by  orderdate 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_amount
from business;

select name,orderdate,cost,lag(orderdate,1) over(partition by name order by orderdate) last_date
from business;

select  * 
from 
(select *,
ntile(5)over(order  by  orderdate)  group_num from  business) t 
where t. group_num = 1;

建胜	语文	87
建胜	数学	95
建胜	英语	68
班长	语文	94
班长	数学	56
班长	英语	84
副班长	语文	64
副班长	数学	86
副班长	英语	84
团支书	语文	65
团支书	数学	85
团支书	英语	78

create table score
(
name string,
subject string, 
score int
) row format delimited fields terminated by "\t";

load data local inpath '/export/data/hive/window_score.txt' into table window_score;

select  *,
row_number()over(partition by subject order by score desc) as rn,
rank()over(partition by subject order by score desc) as rk,
dense_rank()over(partition by subject order by score desc) as dr
from window_score;

select 
*
from 
(
select 
*,
row_number() over(partition by subject order by score desc) rmp
from window_score
) t
where t.rmp<=3;