目标：
1、掌握 sum()、avg()等用于累计计算的聚合函数，学会对行数的限制（移动计算）；
2、掌握 row_number(),rank()、dense_rank()用于排序的函数；
3、掌握 ntile()用于分组查询的函数；
4、掌握 lag()、lead()偏移分析函数

窗口函数（window function）：
与聚合函数类似，但是窗口函数是每一行数据都生成一个结果，聚合函数可以将多行数据按照规定聚合为一行，一般来说聚合后的行数要少于聚合前的行数，但是有时我们想要既显示聚合前的数据，又要显示聚合后的数据，这时便引入了窗口函数，窗口函数是在 select 时执行的，位于 order by 之前。

一、累计计算窗口函数(相当于计算聚合的聚合？二次聚合？)

1、sum(...) over(...)

在日常工作中，经常遇到计算截止某月或某天的累计数值，在Excel可以通过函数来实现，
在HiveSQL里，可以利用窗口函数实现。

1）2018年每月的支付总额和当年累计支付总额

select a.month
        ,a.total_amount
        ,sum(a.total_amount) over(order by a.month)
from 
   ( select month(dt) as month,sum(pay_amount) as total_amount
    from user_trade
    where year(dt)=2018
    group by month(dt)
    )a

2）对2017年和2018年公司的支付总额按月度累计进行分析，按年度进行汇总

写法一：

select a.year
        ,a.month
        ,a.amount
        ,sum(a.amount) over(partition by a.year order by a.month)
from 
   (select substr(trade_time,1,4) as year
            ,substr(trade_time,6,2) as month
            ,sum(amount)as amount
    from trade_2017
    group by substr(trade_time,1,4),substr(trade_time,6,2)
union all
    select substr(trade_time,1,4) as year
            ,substr(trade_time,6,2) as month
            ,sum(amount) as amount 
    from trade_2018
    group by substr(trade_time,1,4),substr(trade_time,6,2)
    )a

写法二：

select a.year
        ,a.month
        ,a.pay_amount
        ,sum(a.pay_amount) over(partition by a.year order by a.month)
from
   (select year(dt)as year
            ,month(dt) as month
            ,sum(pay_amount) as pay_amount
    from user_trade
    where year(dt) in (2017,2018)
    group by year(dt),month(dt)
    )a

说明：1、over中的 partition by 起到分组的作用；
2、order by 按照什么顺序进行累加，升序ASC、降序DESC，默认升序
3、正确的分组是非常重要的，partition by 后面的字段是需要累计计算的区域，需要仔细理解

2、avg(...) over(...)：移动平均

（计算三日留存、七日留存、三十日留存等方式可以使用这个函数。）

3）对2018年每个月的近三个月进行移动的求平均支付金额

    select a.month
            ,a.amount
            ,avg(a.amount) over(order by a.month rows between 2 preceding and current row)
    from 
       (select month(dt) as month
                ,sum(pay_amount) as amount
        from user_trade
        where year(dt)=2018
        group by month(dt)
        )a
    注意：如果使用聚合函数，则必须要在group by里使用，窗口函数使用时不用group by，
        但是窗口函数必须要在子查询里配合使用聚合函数才能得出正确的结果

3、语法总结

sum(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)
avg(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...) 

A：需要被加工的字段名称
B：分组的字段名称
C：排序的字段名称
D：计算的行数范围

rows between unbounded preceding and current row
包括本行和之前所有的行

rows between current row and unbounded following
包括本行和之后所有的行

rows between 3 preceding and current row
包括本行以内和前三行

rows between 3 preceding and 1 following 
从前三行到下一行（5行）

拓展：
max(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)
min(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...) 

二、分区排序窗口函数（row_number()、rank()、dense_rank())

用法：这三个函数的作用都是返回相应规则的排序序号，由于排序函数不是二次聚合计算，因此不一定要使用子查询

row_number() over(partition by ...A... order by ...B...)

rank() over(partition by ...A... order by ...B...)

dense_rank() over(partition by ...A... order by ...B...)

A：分组的字段名称
B：排序的字段名称

注意：这3个函数的括号内是不加任何字段名称的！

row_number：为查询出来的每一行生成一个序号，依次排序且不会重复；
rank和dense_rank：在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，
dense_rank()是连续排序，有两个第一名时，仍然跟着第二名。

4）2019年1月，用户购买商品品类数量的排名

select user_name
        ,count(distinct goods_category) as goods_num
        ,row_number() over(order by count(distinct goods_category)) as row_number
        ,rank() over(order by count(distinct goods_category)) as rank
        ,dense_rank() over(order by count(distinct goods_category)) as dense_rank
from user_trade
where substr(dt,1,7)='2019-01'
group by user_name

注意：由于substr()函数截取的日期是字符串格式，所以要加引号，用日期函数截取的日期才可以不加引号。

5）选出2019年支付金额排名在第10、20、30名的用户

select a.user_name,a.amount,a.row_number
from
   (select user_name
            ,sum(pay_amount) as amount
            ,row_number() over(order by sum(pay_amount) desc)as row_number
    from user_trade
    where year(dt)=2019
    group by user_name
    )a
   where a.row_number in(10,20,30)

三、切片排序窗口函数

    ntile(n) over(...)
    
    ntile(n) over(partition by ...A... order by ...B...)

    n：切分的片数
    A：分组的字段名称
    B：排序的字段名称

    ntile(n)：用于将分组数据按照顺序切分成n片，返回当前切片值。
    ntile不支持rows between，比如
    ntile(2) over(partition by ... order by ... rows between 3 preceding and current row)
    如果切片数据不均匀，则前面的组分得的数据较多。

6）将2019年1月的支付用户，按照支付金额分成5组

    select user_name
            ,sum(pay_amount) as amount
            ,ntile(5) over(order by sum(pay_amount) desc)as ntile
    from user_trade
    where substr(dt,1,7)='2019-01'
    group by user_name

7）选出2019年退款金额排名前10%的用户

   select a.user_name,a.amount,a.ntile
   from
    (select user_name
            ,sum(refund_amount) amount
            ,ntile(10) over(order by sum(refund_amount) desc)as ntile
    from user_refund
    where year(dt)=2019
    group by user_name
    )a
    where ntile=1

四、偏移分析窗口函数

说明：Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行数据(Lag)和后N行的数据(Lead)作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。
当然，这种操作可以用表的自连接实现，但是Lag和Lead与 left join、 right join等自连接相比，效率更高，SQL语句更简洁。

lag(exp_str,offset,defval) over(partition by ... order by ...)
lead(exp_str,offset,defval) over(partition by ... order by ...)

exp_str是字段名称；
offset表示偏移量，即是上一个或上N个的值，假设当前行在表中排在第5行，offset为3，
则表示我们所要找的数据行就是表中的第2行（即5-3=2），offset默认值为1。
defval默认值，当这两个函数取上N/下N个值时，在表中从当前行位置向前数N行已经超出了
表的范围时，lag()函数将defval这个参数作为函数的返回值，若每月指定默认值，则返回NULL。
lead()函数也是一样的道理。在数学运算中，一般都是要给一个默认值才不会出错。

8）支付时间间隔超过100天的用户数(这一次购买距离下一次购买的时间？，注意datediff函数是日期大的在前面)

写法一：

select count(distinct a.user_name)
from
   (select user_name
            ,dt
            ,lead(dt,1,dt) over(partition by user_name order by dt) lead_dt
    from user_trade
    where dt>'0'
    )a
where datediff(a.lead_dt,dt)>100



写法二：

select count(distinct a.user_name)
from
   (select user_name
            ,dt
            ,lag(dt,1,dt) over(partition by user_name order by dt) lag_dt
    from user_trade
    where dt>'0'
    )a
where datediff(dt,a.lag_dt)>100

9）每个城市，不同性别，2018年支付金额最高的TOP3用户

select *
from(
select
    b.city
    ,b.sex
    ,a.user_name
    ,a.amount
    ,row_number()over(partition by b.city,b.sex order by a.amount desc) rank 
from
    (select user_name
            ,sum(pay_amount) amount
    from user_trade
    where year(dt)=2018
    group by user_name)a
left join
    (select user_name,city,sex
    from user_info)b
on a.user_name=b.user_name)c
where c.rank<=3

步骤总结：
1、首先筛选出每个用户和每个用户总的消费金额；
2、对两个表进行连接提取需要的字段；
3、对连接后的表进行二次聚合计算，计算出不同城市、性别的金额排名；
4、对二次聚合计算的表进行条件筛选提取

10）每个手机品牌退款金额前25%的用户

                                                                        "phonebrand":"iphone X"

  select *
  from
  (
  select b.phonebrand
          ,a.user_name
          ,a.amount
          ,ntile(4) over(partition by b.phonebrand order by a.amount desc) ntile
  from 
      (select user_name,sum(refund_amount) amount
      from user_refund
      where dt>'0'
      group by user_name)a
  left join
      (select user_name,extra2["phonebrand"]as phonebrand
      from user_info
      )b
  on a.user_name=b.user_name
  )c
  where c.ntile=1

步骤总结：
1、首先筛选出每个用户和每个用户的总退款金额；
2、对两个表进行连接提取需要的字段；
3、对连接后的表进行按手机品牌内分组；
4、对分组后的表进行条件筛选提取