5.8 窗口查询

5.8.2 概述

sql 中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行.

一般来讲聚集后的行数是要少于聚集前的行数的.

但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

SQL 窗口查询引入了三个新的概念：窗口分区、窗口帧、以及窗口函数。

PARTITION 语句会按照一个或多个指定字段，将查询结果集拆分到不同的 窗口分区 中，并可按照一定规则排序。如果没有 PARTITION BY，则整个结果集将作为单个窗口分区；

窗口帧 用于从分区中选择指定的多条记录，供窗口函数处理。 Hive 提供了两种定义窗口帧的形式：ROWS 和 RANGE。两种类型都需要配置上界和下界。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示选择分区起始记录到当前记录的所有行； SUM(close) RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING 则通过 字段差值 来进行选择。 如当前行的 close 字段值是 200，那么这个窗口帧的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录。以下是所有可能的窗口帧定义组合。如果没有定义窗口帧，则默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

窗口函数 会基于当前窗口帧的记录计算结果。Hive 提供了以下窗口函数： FIRST_VALUE(col), LAST_VALUE(col) 可以返回窗口帧中第一条或最后一条记录的指定字段值； LEAD(col, n), LAG(col, n) 返回当前记录的上 n 条或下 n 条记录的字段值； RANK(), ROW_NUMBER() 会为帧内的每一行返回一个序数，区别在于存在字段值相等的记录时，RANK() 会返回相同的序数； COUNT(), SUM(col), MIN(col) 和一般的聚合操作相同。

5.8.2 相关函数说明

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类型。

first_value(col), last_value(col), 指定列的第一个或者最后一列的值

实战练习

准备数据

用户在电商购物的情况的记录.

三个字段分别表示: 用户名, 用户在电商购物的时间, 消费的金额.

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

创建本地文件:business.txt

需求

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

创建表并导入数据

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

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

按需求查询数据

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

select name,count(*) over () 
from business 
where substring(orderdate,6,2) = '04' 
group by name;

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

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

要将cost按照日期进行累加

select *, sum(cost) over(oder orderdate rows between unbounded preceding and current row) 
from business;

1. 相邻三行相加

select *, sum(cost) over(order by orderdate rows between 1 preceding and 1 following) 
from business;

1. 分区统计每个用户的cost累加

select *, sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) 
from business;

查看顾客上次的购买时间

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

查询订单价值前20%的订单信息

select *
from  (
    select *, ntile(5) over(order by cost desc) as gid 
    from business
) t
where gid=1;