窗口函数的定义和使用规则

1.什么是窗口函数
窗口函数属于高阶函数 5.6 5.7 是没有的 5.8有了
orcale 一直有窗口函数
hive也有窗口函数
窗口函数比较吃资源
2.哪些函数叫窗口函数，一下有三类
一：一些直接作为窗口函数
（1）LAG(col,n,default_val)：往前第n行数据 注：n默认值默认为1，default_val为替代null的值
（2）Lead(col,n,default_val)：往后第n行数据 注：n默认值默认为1，default_val为替代null的值
举例：查询顾客购买明细以及上次的购买时间和下次购买时间
lag(orderdate)over(partition by name order by orderdate) prve_time,
lead(orderdate)over(partition by name order by orderdate) next_time

（3）FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值
（4）LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值
二： 一些聚合函数
COUNT
SUM
MIN
MAX
AVG
三 排名分析函数
RANK 表示排名数，如果有并列则下一个人会排在他俩之后，如两个人并列第一，则下一个人为第三名
ROW_NUMBER 光表示行数
DENSE_RANK 于rank正好相反，并列第一的下一个还是2 举例：

NTILE

窗口函数定义 窗口+函数
窗口 可以限定函数计算的范围
窗口函数的语法是
窗口函数() over([partition by 字段…][order by 字段…][窗口子句])开窗

窗口的函数的计算顺序
1.over 表示开一个最大的窗口
2.partition 要对窗口划分更细一点
3.order by 表示窗内排序
4.窗口子句 限定返回
5.窗口函数

partition by 表示在你over的前提再按照你给的字段信息(字段相同进入同一个窗口) 再划分一个细窗口，并且多个细窗口之间相互独立，窗口子句对细窗口独立生效
order by 表示窗口内按什么排序 当有over没有Partition的时候 直接整个大窗口排序 当有over又有Partition的时候 按照partition内部排序
窗口子句 可以明确表示当前函数的计算范围，可以有三种形式

注：preceding为上，following为下，unbounded为无边界，num表示为数字，current row为当前行
形式1：(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
注：可以为上无边界，上num行和下多少行，当前行，下无边界的组合，一共为2*3=6种
举例： 每个顾客的cost按照日期求上一次和当前一次消费的和

sum(cost)over(partition by name order by orderdate rows between 1 PRECEDING and current row)

形式2：(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
注：可以为当前行和当前行，下num行，下无边界的组合，一共是1*3种
举例：每个顾客的cost按照日期求当前和下一次消费的和

 sum(cost)over(partition by name order by orderdate rows between current row and 1 following)

形式3：(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
注：可以为下num行和下无边界，下num行的组合，一共是1*2种。

–并不是所有的窗口函数都需要写窗口子句
Rank, NTile, DenseRank, rownumber. lag lead

–以下情况的窗口子句是有默认值的
当over里面有order by 没有窗口子句的时候 默认上无边界到当前行
When ORDER BY is specified with missing WINDOW clause, the WINDOW specification defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
当over里面没有order by 也没有窗口子句的时候 默认上无边界到下无边界
When both ORDER BY and WINDOW clauses are missing, the WINDOW specification defaults to ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

窗口函数理解

关于使用窗口函数和group by交错的个人理解

当你使用group by分区时，窗口函数的范围为group by分区的范围，即：

select
 name,
 count(*)over(rows between UNBOUNDED PRECEDING and current row)
 from business
 where date_format(orderdate,'yyyy-MM')='2017-04'
 group by name

它会对name的分区起作用count，而不是name分区内的数据count

如果想要对name分区内数据起作用，需要在窗口函数内做partion by来分区

也就是说，如果使用了group by，就相当于将原表分为了几个分区，而窗口函数是直接针对于这几个分区的
如果想要针对每个分区内的窗口，则需要在窗口函数内写partition by 来分区