开窗函数：定义 行 为 列
简单讲，就是在你查询的结果上，直接多出一列值（可以是聚合值或是排序号），特征就是带有 over()。

NTILE 切片(前三分之一)

  • NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
  • NTILE不支持ROWS BETWEEN，
  • 比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
  • 如果切片不均匀，默认增加第一个切片的分布

场景：假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数

  1. select name,orderdate,cost,
  2.        ntile(3) over() as sample1 , --全局数据切片
  3.        ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
  4.        ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
  5.        ntile(3) over(partition by name order by cost ) --按照name分组，在分组内按照cost升序排列,数据切成3份
  6. from t_window

row_number、rank、dense_rank

  • row_number()从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
  • RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
  • DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

rank和dense_rank的区别在于排名相等时会不会留下空位.**

  1. SELECT 
  2. cookieid,
  3. createtime,
  4. pv,
  5. RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
  6. DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
  7. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
  8. FROM lxw1234 
  9. WHERE cookieid = 'cookie1';
  11. cookieid day           pv       rn1     rn2     rn3 
  12. cookie1 2015-04-12      7       1       1       1
  13. cookie1 2015-04-11      5       2       2       2
  14. cookie1 2015-04-15      4       3       3       3
  15. cookie1 2015-04-16      4       3       3       4
  16. cookie1 2015-04-13      3       5       4       5
  17. cookie1 2015-04-14      2       6       5       6
  18. cookie1 2015-04-10      1       7       6       7
  20. rn1: 15号和16号并列第3, 13号排第5
  21. rn2: 15号和16号并列第3, 13号排第4
  22. rn3: 如果相等，则按记录值排序，生成唯一的次序，如果所有记录值都相等，或许会随机排吧。

LAG和LEAD函数

这两个函数为常用的窗口函数,可以返回上下数据行的数据.
以我们的订单表为例,假如我们想要查看顾客上次的购买时间可以这样去查询

  1. select name,orderdate,cost,
  2. lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,
  3. lag(orderdate,2) over (partition by name order by orderdate) as time2
  4. from t_window;
  6. name    orderdate   cost    time1   time2
  7. jack    2015-01-01  10  1900-01-01  NULL
  8. jack    2015-01-05  46  2015-01-01  NULL
  9. jack    2015-01-08  55  2015-01-05  2015-01-01
  10. jack    2015-02-03  23  2015-01-08  2015-01-05
  11. jack    2015-04-06  42  2015-02-03  2015-01-08
  12. mart    2015-04-08  62  1900-01-01  NULL
  13. mart    2015-04-09  68  2015-04-08  NULL
  14. mart    2015-04-11  75  2015-04-09  2015-04-08
  15. mart    2015-04-13  94  2015-04-11  2015-04-09
  16. neil    2015-05-10  12  1900-01-01  NULL
  17. neil    2015-06-12  80  2015-05-10  NULL
  18. tony    2015-01-02  15  1900-01-01  NULL
  19. tony    2015-01-04  29  2015-01-02  NULL
  20. tony    2015-01-07  50  2015-01-04  2015-01-02
  21. --------------------- 
  22. time1取的为按照name进行分组,分组内升序排列,取上一行数据的值.
  24. time2取的为按照name进行分组，分组内升序排列,取上面2行的数据的值,注意当lag函数为设置行数值时,默认为1行.未设定取不到时的默认值时,取null值.
  26. lead函数与lag函数方向相反,取向下的数据.

first_value和last_value

first_value取分组内排序后，截止到当前行，第一个值
last_value取分组内排序后，截止到当前行，最后一个值

  1. select name,orderdate,cost,
  2. first_value(orderdate) over(partition by name order by orderdate) as time1,
  3. last_value(orderdate) over(partition by name order by orderdate) as time2
  4. from t_window
  5. --------------------- 
  6. name    orderdate   cost    time1   time2
  7. jack    2015-01-01  10  2015-01-01  2015-01-01
  8. jack    2015-01-05  46  2015-01-01  2015-01-05
  9. jack    2015-01-08  55  2015-01-01  2015-01-08
  10. jack    2015-02-03  23  2015-01-01  2015-02-03
  11. jack    2015-04-06  42  2015-01-01  2015-04-06
  12. mart    2015-04-08  62  2015-04-08  2015-04-08
  13. mart    2015-04-09  68  2015-04-08  2015-04-09
  14. mart    2015-04-11  75  2015-04-08  2015-04-11
  15. mart    2015-04-13  94  2015-04-08  2015-04-13
  16. neil    2015-05-10  12  2015-05-10  2015-05-10
  17. neil    2015-06-12  80  2015-05-10  2015-06-12
  18. tony    2015-01-02  15  2015-01-02  2015-01-02
  19. tony    2015-01-04  29  2015-01-02  2015-01-04
  20. tony    2015-01-07  50  2015-01-02  2015-01-07

SUM,AVG,MIN,MAX

用于实现分组内所有和连续累积的统计。

  1. cookieid string,  
  2. createtime string,
  3. pv INT  
  4. cookie1 2015-04-10      1  
  5. cookie1 2015-04-11      5  
  6. cookie1 2015-04-12      7  
  7. cookie1 2015-04-13      3  
  8. cookie1 2015-04-14      2  
  9. cookie1 2015-04-15      4  
  10. cookie1 2015-04-16      4


  1. SELECT cookieid,
  2.     createtime,
  3.     pv,
  4.     SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
  5.     SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
  6.     SUM(pv) OVER(PARTITION BY cookieid) AS pv3,    --分组内所有行
  7.     SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行
  8.     SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行
  9.     SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行  
  10.     FROM lxw1234;
  12.     cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6 
  13.     -----------------------------------------------------------------------------
  14.     cookie1  2015-04-10      1       1       1       26      1       6       26
  15.     cookie1  2015-04-11      5       6       6       26      6       13      25
  16.     cookie1  2015-04-12      7       13      13      26      13      16      20
  17.     cookie1  2015-04-13      3       16      16      26      16      18      13
  18.     cookie1  2015-04-14      2       18      18      26      17      21      10
  19.     cookie1  2015-04-15      4       22      22      26      16      20      8
  20.     cookie1  2015-04-16      4       26      26      26      13      13      4
  22. pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
  23. pv2: 同pv1
  24. pv3: 分组内(cookie1)所有的pv累加
  25. pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
  26. pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
  27. pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点
其他AVG，MIN，MAX，和SUM用法一样。

  1. --AVG
  2.     SELECT cookieid,
  3.     createtime,
  4.     pv,
  5.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
  6.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
  7.     AVG(pv) OVER(PARTITION BY cookieid) AS pv3,    --分组内所有行
  8.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
  9.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
  10.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行  
  11.     FROM lxw1234; 
  12.     cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6 
  13.     -----------------------------------------------------------------------------
  14.     cookie1 2015-04-10      1       1.0     1.0     3.7142857142857144      1.0     3.0     3.7142857142857144
  15.     cookie1 2015-04-11      5       3.0     3.0     3.7142857142857144      3.0     4.333333333333333       4.166666666666667
  16.     cookie1 2015-04-12      7       4.333333333333333       4.333333333333333       3.7142857142857144      4.333333333333333       4.0     4.0
  17.     cookie1 2015-04-13      3       4.0     4.0     3.7142857142857144      4.0     3.6     3.25
  18.     cookie1 2015-04-14      2       3.6     3.6     3.7142857142857144      4.25    4.2     3.3333333333333335
  19.     cookie1 2015-04-15      4       3.6666666666666665      3.6666666666666665      3.7142857142857144      4.0     4.0     4.0
  20.     cookie1 2015-04-16      4       3.7142857142857144      3.7142857142857144      3.7142857142857144      3.25    3.25    4.0
  22. --AVG
  23.     SELECT cookieid,
  24.     createtime,
  25.     pv,
  26.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
  27.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
  28.     AVG(pv) OVER(PARTITION BY cookieid) AS pv3,    --分组内所有行
  29.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
  30.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
  31.     AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行  
  32.     FROM lxw1234; 
  33.     cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6 
  34.     -----------------------------------------------------------------------------
  35.     cookie1 2015-04-10      1       1.0     1.0     3.7142857142857144      1.0     3.0     3.7142857142857144
  36.     cookie1 2015-04-11      5       3.0     3.0     3.7142857142857144      3.0     4.333333333333333       4.166666666666667
  37.     cookie1 2015-04-12      7       4.333333333333333       4.333333333333333       3.7142857142857144      4.333333333333333       4.0     4.0
  38.     cookie1 2015-04-13      3       4.0     4.0     3.7142857142857144      4.0     3.6     3.25
  39.     cookie1 2015-04-14      2       3.6     3.6     3.7142857142857144      4.25    4.2     3.3333333333333335
  40.     cookie1 2015-04-15      4       3.6666666666666665      3.6666666666666665      3.7142857142857144      4.0     4.0     4.0
  41.     cookie1 2015-04-16      4       3.7142857142857144      3.7142857142857144      3.7142857142857144      3.25    3.25    4.0
  43. --MAX
  44.     SELECT cookieid,
  45.     createtime,
  46.     pv,
  47.     MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
  48.     MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
  49.     MAX(pv) OVER(PARTITION BY cookieid) AS pv3,    --分组内所有行
  50.     MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
  51.     MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
  52.     MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行  
  53.     FROM lxw1234;
  55.     cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6 
  56.     -----------------------------------------------------------------------------
  57.     cookie1 2015-04-10      1       1       1       7       1       5       7
  58.     cookie1 2015-04-11      5       5       5       7       5       7       7
  59.     cookie1 2015-04-12      7       7       7       7       7       7       7
  60.     cookie1 2015-04-13      3       7       7       7       7       7       4
  61.     cookie1 2015-04-14      2       7       7       7       7       7       4
  62.     cookie1 2015-04-15      4       7       7       7       7       7       4
  63.     cookie1 2015-04-16      4       7       7       7       4       4       4

GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

  1. month STRING,  
  2. day STRING,   
  3. cookieid STRING  
  5. 2015-03 2015-03-10      cookie1  
  6. 2015-03 2015-03-10      cookie5  
  7. 2015-03 2015-03-12      cookie7  
  8. 2015-04 2015-04-12      cookie3  
  9. 2015-04 2015-04-13      cookie2  
  10. 2015-04 2015-04-13      cookie4  
  11. 2015-04 2015-04-16      cookie4  
  12. 2015-03 2015-03-10      cookie2  
  13. 2015-03 2015-03-10      cookie3  
  14. 2015-04 2015-04-12      cookie5  
  15. 2015-04 2015-04-13      cookie6  
  16. 2015-04 2015-04-15      cookie3  
  17. 2015-04 2015-04-15      cookie2  
  18. 2015-04 2015-04-16      cookie1

在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL。其中的 GROUPING__ID，表示结果属于哪一个分组集合。

  1. SELECT 
  2.     month,
  3.     day,
  4.     COUNT(DISTINCT cookieid) AS uv,
  5.     GROUPING__ID 
  6.     FROM lxw1234 
  7.     GROUP BY month,day 
  8.     GROUPING SETS (month,day) 
  9.     ORDER BY GROUPING__ID;
  11.     month      day            uv      GROUPING__ID
  12.     ------------------------------------------------
  13.     2015-03    NULL            5       1
  14.     2015-04    NULL            6       1
  15.     NULL       2015-03-10      4       2
  16.     NULL       2015-03-12      1       2
  17.     NULL       2015-04-12      2       2
  18.     NULL       2015-04-13      3       2
  19.     NULL       2015-04-15      2       2
  20.     NULL       2015-04-16      2       2
  23.     等价于 
  24.     SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
  25.     UNION ALL 
  26.     SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
  1.  SELECT 
  2.     month,
  3.     day,
  4.     COUNT(DISTINCT cookieid) AS uv,
  5.     GROUPING__ID 
  6.     FROM lxw1234 
  7.     GROUP BY month,day 
  8.     GROUPING SETS (month,day,(month,day)) 
  9.     ORDER BY GROUPING__ID;
  11.     month         day             uv      GROUPING__ID
  12.     ------------------------------------------------
  13.     2015-03       NULL            5       1
  14.     2015-04       NULL            6       1
  15.     NULL          2015-03-10      4       2
  16.     NULL          2015-03-12      1       2
  17.     NULL          2015-04-12      2       2
  18.     NULL          2015-04-13      3       2
  19.     NULL          2015-04-15      2       2
  20.     NULL          2015-04-16      2       2
  21.     2015-03       2015-03-10      4       3
  22.     2015-03       2015-03-12      1       3
  23.     2015-04       2015-04-12      2       3
  24.     2015-04       2015-04-13      3       3
  25.     2015-04       2015-04-15      2       3
  26.     2015-04       2015-04-16      2       3
  29.     等价于
  30.     SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
  31.     UNION ALL 
  32.     SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
  33.     UNION ALL 
  34.     SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

CUBE

根据GROUP BY的维度的所有组合进行聚合。

  1.  SELECT 
  2.     month,
  3.     day,
  4.     COUNT(DISTINCT cookieid) AS uv,
  5.     GROUPING__ID 
  6.     FROM lxw1234 
  7.     GROUP BY month,day 
  8.     WITH CUBE 
  9.     ORDER BY GROUPING__ID;
  12.     month           day             uv     GROUPING__ID
  13.     --------------------------------------------
  14.     NULL            NULL            7       0
  15.     2015-03         NULL            5       1
  16.     2015-04         NULL            6       1
  17.     NULL            2015-04-12      2       2
  18.     NULL            2015-04-13      3       2
  19.     NULL            2015-04-15      2       2
  20.     NULL            2015-04-16      2       2
  21.     NULL            2015-03-10      4       2
  22.     NULL            2015-03-12      1       2
  23.     2015-03         2015-03-10      4       3
  24.     2015-03         2015-03-12      1       3
  25.     2015-04         2015-04-16      2       3
  26.     2015-04         2015-04-12      2       3
  27.     2015-04         2015-04-13      3       3
  28.     2015-04         2015-04-15      2       3
  32.     等价于
  33.     SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
  34.     UNION ALL 
  35.     SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
  36.     UNION ALL 
  37.     SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
  38.     UNION ALL 
  39.     SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

  2.     比如，以month维度进行层级聚合：
  3.     SELECT 
  4.     month,
  5.     day,
  6.     COUNT(DISTINCT cookieid) AS uv,
  7.     GROUPING__ID  
  8.     FROM lxw1234 
  9.     GROUP BY month,day
  10.     WITH ROLLUP 
  11.     ORDER BY GROUPING__ID;
  13.     month           day             uv     GROUPING__ID
  14.     ---------------------------------------------------
  15.     NULL             NULL            7       0
  16.     2015-03          NULL            5       1
  17.     2015-04          NULL            6       1
  18.     2015-03          2015-03-10      4       3
  19.     2015-03          2015-03-12      1       3
  20.     2015-04          2015-04-12      2       3
  21.     2015-04          2015-04-13      3       3
  22.     2015-04          2015-04-15      2       3
  23.     2015-04          2015-04-16      2       3
  25.     可以实现这样的上钻过程：
  26.     月天的UV->月的UV->总UV
  1. --把month和day调换顺序，则以day维度进行层级聚合：
  3.     SELECT 
  4.     day,
  5.     month,
  6.     COUNT(DISTINCT cookieid) AS uv,
  7.     GROUPING__ID  
  8.     FROM lxw1234 
  9.     GROUP BY day,month 
  10.     WITH ROLLUP 
  11.     ORDER BY GROUPING__ID;
  14.     day          month              uv     GROUPING__ID
  15.     -------------------------------------------------------
  16.     NULL            NULL               7       0
  17.     2015-04-13      NULL               3       1
  18.     2015-03-12      NULL               1       1
  19.     2015-04-15      NULL               2       1
  20.     2015-03-10      NULL               4       1
  21.     2015-04-16      NULL               2       1
  22.     2015-04-12      NULL               2       1
  23.     2015-04-12      2015-04            2       3
  24.     2015-03-10      2015-03            4       3
  25.     2015-03-12      2015-03            1       3
  26.     2015-04-13      2015-04            3       3
  27.     2015-04-15      2015-04            2       3
  28.     2015-04-16      2015-04            2       3
  30.     可以实现这样的上钻过程：
  31.     天月的UV->天的UV->总UV
  32.     （这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）

这种函数，需要结合实际场景和数据去使用和研究，只看说明的话，很难理解。
官网的介绍： https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup
原文地址 http://lxw1234.com/archives/tag/hive-window-functions
参考：http://blog.csdn.net/xiepeifeng/article/details/42676567
http://www.cnblogs.com/skyEva/p/5730531.html

根据使用的目的，开窗函数可以分为两类：聚合开窗函数和排序开窗函数。
下面主要解析四种常用的排序开窗函数:
1、ROW_NUMBER() OVER();
2、RANK() OVER();
3、DENSE_RANK() OVER();
4、NTILE(n) OVER().
count() over(partition by … order by …)：求分组后的总数。
max() over(partition by … order by …)：求分组后的最大值。
min() over(partition by … order by …)：求分组后的最小值。
avg() over(partition by … order by …)：求分组后的平均值。
lag() over(partition by … order by …)：取出前n行数据。　　
lead() over(partition by … order by …)：取出后n行数据。
ratio_to_report() over(partition by … order by …)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母。
percent_rank() over(partition by … order by …)：