SQL笔记（一）窗口函数

窗口不仅仅可以搭配排序这些函数使用，本身开窗也是很便利的嵌套查询方法

max(pay_time) over(partition by duid order by pay_time desc) as last_pay_time

相关函数说明

       OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；（分析函数，分析的数据范围）。当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的，每个窗口函数应用自己的规则.

• CURRENT ROW：当前行；
• n PRECEDING：往前n行数据；
• n FOLLOWING：往后n行数据；
• UNBOUNDED：起点

UNBOUNDED PRECEDING 表示从前面的起点

UNBOUNDED FOLLOWING表示到后面的终点；

• LAG(col,n)：获取往前第n行某字段值；

lag(pay_time,1) over(partition by duid order by pay_time) as last_1_pay_time,

• LEAD(col,n)：获取往后第n行某字段值；
• NTILE(n)：把有序分区中的行分发到指定数据的组中，各组编号从1开始，对于每一行，NTILE返回此行所属的组的编号。

注意：n 必须为int类型。（分桶函数，类似于hive中的分桶策略）

• first_value()：取分组内排序后，截止到当前行，第一行某字段值。如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果。

（获取数据窗口的第一行某字段值）

• last_value() ：取分组内排序后，截止到当前行，最后一行某字段值。如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果。

（获取数据窗口的最后一行某字段值）

• sum() mean() 这种常规的计算函数也可以开窗

实例：

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

# 顾客及购买次数

select name,count(*)

from business

where substring(orderdate,1,7) = '2017-04'  --字符串筛选日期

group by name

# 顾客及总人数——开窗

select name,count(*) over ()

from business

where substring(orderdate,1,7) = '2017-04'

group by name;

开窗函数一般与分析函数一起使用，表示分析函数分析数据的范围。

如果开窗函数遇到 group by语句，那么窗口中无其他限定时，一般把一组看成一条记录，相当于先进行分组后，分组后这一组内整体的记录数被作为一条记录

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

select name, orderdate, cost,

sum(cost) over(partition by month(orderdate))

from business

order by name,orderdate; 

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 business;

注：sum用法

 sum(cost) over() as sample1

对窗口内所有数据进行相加

 sum(cost) over(partition by name) as sample2

按name分组，组内数据相加。

partition by子句表示分组（partition by分组的顺序要晚于group by，先进行group by分组后得到的数据被作为整体，看成一条输入数据）

sum(cost) over(partition by name) 就表示对分组后里面的数据进行相加。

sum(cost) over(partition by name order by orderdate) as sample3

按name分组，组内数据累加。注意此处多了order by 子句，表示组内数据累加

其数据的分析范围为从起点到当前行

order by子句会让输入的数据强制排序，Order By子句对于诸如Row_Number()，Lead()，LAG()等函数是必须的，因为如果数据无序，这些函数的结果就没有任何意义。

注意点：

       - 如果只使用partition by子句,未指定order by的话,我们的分析函数的作用范围是分组内的数据.

       - 使用了order by子句,未使用window子句的情况下,默认数据分析范围是从起点到当前行，往往用来实现累加. 

window子句：

          - PRECEDING：往前

          - FOLLOWING：往后

          - CURRENT ROW：当前行

          - UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

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 business;

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

查询前20%=1/5说明需要分五个桶，按照时间顺序由低到高进行排序，取第一个分桶中的数据

select * from (

    select name,orderdate,cost, ntile(5) over(order by orderdate) bucket

    from business

) t

where bucket = 1;

总结

窗口函数 over （partition by  用于分组的组名，order by 用于排序的列名）

排名函数：row_number()  连续不重复

                    rank()  重复不连续

                    dense_rank()  连续重复

聚合函数：max()   min()  count()  sum()  avg()  median()

向前向后取值：lag(field,n,default)  lead(field,n,default)

百分位：percent()

取值函数：first_value()  last_value()  nth_value()

分箱函数：ntile()