Hive 窗口函数

窗口函数（开窗函数）

函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

CURRENT ROW：当前行

n PRECEDING：往前 n 行数据

n FOLLOWING：往后 n 行数据

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，

UNBOUNDED FOLLOWING 表示到后面的终点

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

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

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

数据准备 name，orderdate，cost
并创建本地business.txt数据

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

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

导入数据

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

load data local path "/opt/module/data/business.txt" into table 
business;

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

select
    name,
    count(*) over()
from business
where sbustrng(orderdata,1,7) = '2017-04'
group by name;
-- 第一种用法：

substr（string A，int start）和 substring（string A，int start），用法一样

功效：返回字符串A从下标start位置到结尾的字符串


第二种用法：

substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

功效：返回字符串A从下标start位置开始，长度为len的字符串
————————————————
版权声明：本文为CSDN博主「攻城狮Kevin」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/wx1528159409/article/details/90912557

（2） 查询顾客的购买明细及月购买总额

select
   name,
   orderdate,
   cost,
   sum(cost) over(partition by month(orderdate))
from business;

(3)将每个顾客的 cost 按照日期进行累加

select
    name,
	orderdata,
	cost,
	sum(cost) over(partition by name order by orderdata row between UNBOUNDED PERCEDING and current row)

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

select 
   name,
   orderdata
   LAG(orderdata,1)over(PARTITION by name ORDER by orderdata)
from 
   business; 
-- LOG(列，往下多少行，默认值（没有，默认为null；当然默认值也可以给自己为：原写的列）)   

select 
   name,
   orderdata
   LEAD(orderdata,1)over(PARTITION by name ORDER by orderdata)
from 
   business;
--LEAD(列，往上前多少行，默认值（没有，默认为null；当然默认值也可以给上一行：原写的列

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

select
    name,
    orderdata,
    cost
from(
SELECT
     name,
	 orderdata,
	 cost
	 NTILE(5) over(ORDER by orderdata) groupID
FROM
    business;t1
)where groupID = 1;

PS:
创建一个id,txt 1 2 3 3 4 5
CREATE table ID(id,int);
使用窗口函数的话是：
select id,
sum(id),
over(ORDER BY id)
from ID
出的值是
id sum_windo
1 1
2 3
3 9
3 9
4 13
5 18
当over（写：row，order by，between）这是因为当数值一样的时候会出现这样的情况，解决的话：按两个字段进行区别排序，或者把相同的给去掉