窗口函数

窗口函数

列存表目前只支持rank(expression)和row_number(expression)两个函数。

窗口函数与OVER语句一起使用。OVER语句用于对数据进行分组，并对组内元素进行排序。窗口函数用于给组内的值生成序号。

窗口函数 - 图1 说明：
窗口函数中的order by后面必须跟字段名，若order by后面跟数字，该数字会被按照常量处理，因此对目标列没有起到排序的作用。

  • RANK()

    描述：RANK函数为各组内值生成跳跃排序序号，其中，相同的值具有相同序号。

    返回值类型：BIGINT

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;
    2.    d_moy | d_fy_week_seq | rank 
    3. -------+---------------+------
    4.      1 |             1 |    1
    5.      1 |             1 |    1
    6.      1 |             1 |    1
    7.      1 |             1 |    1
    8.      1 |             1 |    1
    9.      1 |             1 |    1
    10.      1 |             1 |    1
    11.      1 |             2 |    8
    12.      1 |             2 |    8
    13.      1 |             2 |    8
    14.      1 |             2 |    8
    15.      1 |             2 |    8
    16.      1 |             2 |    8
    17.      1 |             2 |    8
    18.      1 |             3 |   15
    19.      1 |             3 |   15
    20.      1 |             3 |   15
    21.      1 |             3 |   15
    22.      1 |             3 |   15
    23.      1 |             3 |   15
    24.      1 |             3 |   15
    25.      1 |             4 |   22
    26.      1 |             4 |   22
    27.      1 |             4 |   22
    28.      1 |             4 |   22
    29.      1 |             4 |   22
    30.      1 |             4 |   22
    31.      1 |             4 |   22
    32.      1 |             5 |   29
    33.      1 |             5 |   29
    34.      2 |             5 |    1
    35.      2 |             5 |    1
    36.      2 |             5 |    1
    37.      2 |             5 |    1
    38.      2 |             5 |    1
    39.      2 |             6 |    6
    40.      2 |             6 |    6
    41.      2 |             6 |    6
    42.      2 |             6 |    6
    43.      2 |             6 |    6
    44.      2 |             6 |    6
    45.      2 |             6 |    6
    46. (42 rows)

  • ROW_NUMBER()

    描述：ROW_NUMBER函数为各组内值生成连续排序序号，其中，相同的值其序号也不相同。

    返回值类型：BIGINT

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, Row_number() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim  WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;
    2.  d_moy | d_fy_week_seq | row_number 
    3. -------+---------------+------------
    4.      1 |             1 |          1
    5.      1 |             1 |          2
    6.      1 |             1 |          3
    7.      1 |             1 |          4
    8.      1 |             1 |          5
    9.      1 |             1 |          6
    10.      1 |             1 |          7
    11.      1 |             2 |          8
    12.      1 |             2 |          9
    13.      1 |             2 |         10
    14.      1 |             2 |         11
    15.      1 |             2 |         12
    16.      1 |             2 |         13
    17.      1 |             2 |         14
    18.      1 |             3 |         15
    19.      1 |             3 |         16
    20.      1 |             3 |         17
    21.      1 |             3 |         18
    22.      1 |             3 |         19
    23.      1 |             3 |         20
    24.      1 |             3 |         21
    25.      1 |             4 |         22
    26.      1 |             4 |         23
    27.      1 |             4 |         24
    28.      1 |             4 |         25
    29.      1 |             4 |         26
    30.      1 |             4 |         27
    31.      1 |             4 |         28
    32.      1 |             5 |         29
    33.      1 |             5 |         30
    34.      2 |             5 |          1
    35.      2 |             5 |          2
    36.      2 |             5 |          3
    37.      2 |             5 |          4
    38.      2 |             5 |          5
    39.      2 |             6 |          6
    40.      2 |             6 |          7
    41.      2 |             6 |          8
    42.      2 |             6 |          9
    43.      2 |             6 |         10
    44.      2 |             6 |         11
    45.      2 |             6 |         12
    46. (42 rows)

  • DENSE_RANK()

    描述：DENSE_RANK函数为各组内值生成连续排序序号，其中，相同的值具有相同序号。

    返回值类型：BIGINT

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, dense_rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;
    2.  d_moy | d_fy_week_seq | dense_rank 
    3. -------+---------------+------------
    4.      1 |             1 |          1
    5.      1 |             1 |          1
    6.      1 |             1 |          1
    7.      1 |             1 |          1
    8.      1 |             1 |          1
    9.      1 |             1 |          1
    10.      1 |             1 |          1
    11.      1 |             2 |          2
    12.      1 |             2 |          2
    13.      1 |             2 |          2
    14.      1 |             2 |          2
    15.      1 |             2 |          2
    16.      1 |             2 |          2
    17.      1 |             2 |          2
    18.      1 |             3 |          3
    19.      1 |             3 |          3
    20.      1 |             3 |          3
    21.      1 |             3 |          3
    22.      1 |             3 |          3
    23.      1 |             3 |          3
    24.      1 |             3 |          3
    25.      1 |             4 |          4
    26.      1 |             4 |          4
    27.      1 |             4 |          4
    28.      1 |             4 |          4
    29.      1 |             4 |          4
    30.      1 |             4 |          4
    31.      1 |             4 |          4
    32.      1 |             5 |          5
    33.      1 |             5 |          5
    34.      2 |             5 |          1
    35.      2 |             5 |          1
    36.      2 |             5 |          1
    37.      2 |             5 |          1
    38.      2 |             5 |          1
    39.      2 |             6 |          2
    40.      2 |             6 |          2
    41.      2 |             6 |          2
    42.      2 |             6 |          2
    43.      2 |             6 |          2
    44.      2 |             6 |          2
    45.      2 |             6 |          2
    46. (42 rows)

  • PERCENT_RANK()

    描述：PERCENT_RANK函数为各组内对应值生成相对序号，即根据公式 (rank - 1) / (total rows - 1)计算所得的值。其中rank为该值依据RANK函数所生成的对应序号，totalrows为该分组内的总元素个数。

    返回值类型：DOUBLE PRECISION

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, percent_rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;
    2.  d_moy | d_fy_week_seq |   percent_rank   
    3. -------+---------------+------------------
    4.      1 |             1 |                0
    5.      1 |             1 |                0
    6.      1 |             1 |                0
    7.      1 |             1 |                0
    8.      1 |             1 |                0
    9.      1 |             1 |                0
    10.      1 |             1 |                0
    11.      1 |             2 | .241379310344828
    12.      1 |             2 | .241379310344828
    13.      1 |             2 | .241379310344828
    14.      1 |             2 | .241379310344828
    15.      1 |             2 | .241379310344828
    16.      1 |             2 | .241379310344828
    17.      1 |             2 | .241379310344828
    18.      1 |             3 | .482758620689655
    19.      1 |             3 | .482758620689655
    20.      1 |             3 | .482758620689655
    21.      1 |             3 | .482758620689655
    22.      1 |             3 | .482758620689655
    23.      1 |             3 | .482758620689655
    24.      1 |             3 | .482758620689655
    25.      1 |             4 | .724137931034483
    26.      1 |             4 | .724137931034483
    27.      1 |             4 | .724137931034483
    28.      1 |             4 | .724137931034483
    29.      1 |             4 | .724137931034483
    30.      1 |             4 | .724137931034483
    31.      1 |             4 | .724137931034483
    32.      1 |             5 |  .96551724137931
    33.      1 |             5 |  .96551724137931
    34.      2 |             5 |                0
    35.      2 |             5 |                0
    36.      2 |             5 |                0
    37.      2 |             5 |                0
    38.      2 |             5 |                0
    39.      2 |             6 | .454545454545455
    40.      2 |             6 | .454545454545455
    41.      2 |             6 | .454545454545455
    42.      2 |             6 | .454545454545455
    43.      2 |             6 | .454545454545455
    44.      2 |             6 | .454545454545455
    45.      2 |             6 | .454545454545455
    46. (42 rows)

  • CUME_DIST()

    描述：CUME_DIST函数为各组内对应值生成累积分布序号。即根据公式(小于等于当前值的数据行数)/(该分组总行数totalrows)计算所得的相对序号。

    返回值类型：DOUBLE PRECISION

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, cume_dist() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim e_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;
    2.  d_moy | d_fy_week_seq |    cume_dist     
    3. -------+---------------+------------------
    4.      1 |             1 | .233333333333333
    5.      1 |             1 | .233333333333333
    6.      1 |             1 | .233333333333333
    7.      1 |             1 | .233333333333333
    8.      1 |             1 | .233333333333333
    9.      1 |             1 | .233333333333333
    10.      1 |             1 | .233333333333333
    11.      1 |             2 | .466666666666667
    12.      1 |             2 | .466666666666667
    13.      1 |             2 | .466666666666667
    14.      1 |             2 | .466666666666667
    15.      1 |             2 | .466666666666667
    16.      1 |             2 | .466666666666667
    17.      1 |             2 | .466666666666667
    18.      1 |             3 |               .7
    19.      1 |             3 |               .7
    20.      1 |             3 |               .7
    21.      1 |             3 |               .7
    22.      1 |             3 |               .7
    23.      1 |             3 |               .7
    24.      1 |             3 |               .7
    25.      1 |             4 | .933333333333333
    26.      1 |             4 | .933333333333333
    27.      1 |             4 | .933333333333333
    28.      1 |             4 | .933333333333333
    29.      1 |             4 | .933333333333333
    30.      1 |             4 | .933333333333333
    31.      1 |             4 | .933333333333333
    32.      1 |             5 |                1
    33.      1 |             5 |                1
    34.      2 |             5 | .416666666666667
    35.      2 |             5 | .416666666666667
    36.      2 |             5 | .416666666666667
    37.      2 |             5 | .416666666666667
    38.      2 |             5 | .416666666666667
    39.      2 |             6 |                1
    40.      2 |             6 |                1
    41.      2 |             6 |                1
    42.      2 |             6 |                1
    43.      2 |             6 |                1
    44.      2 |             6 |                1
    45.      2 |             6 |                1
    46. (42 rows)

  • NTILE(num_buckets integer)

    描述：NTILE函数根据num_buckets integer将有序的数据集合平均分配到num_buckets所指定数量的桶中，并将桶号分配给每一行。分配时应尽量做到平均分配。

    返回值类型：INTEGER

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, ntile(3) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;
    2.  d_moy | d_fy_week_seq | ntile 
    3. -------+---------------+-------
    4.      1 |             1 |     1
    5.      1 |             1 |     1
    6.      1 |             1 |     1
    7.      1 |             1 |     1
    8.      1 |             1 |     1
    9.      1 |             1 |     1
    10.      1 |             1 |     1
    11.      1 |             2 |     1
    12.      1 |             2 |     1
    13.      1 |             2 |     1
    14.      1 |             2 |     2
    15.      1 |             2 |     2
    16.      1 |             2 |     2
    17.      1 |             2 |     2
    18.      1 |             3 |     2
    19.      1 |             3 |     2
    20.      1 |             3 |     2
    21.      1 |             3 |     2
    22.      1 |             3 |     2
    23.      1 |             3 |     2
    24.      1 |             3 |     3
    25.      1 |             4 |     3
    26.      1 |             4 |     3
    27.      1 |             4 |     3
    28.      1 |             4 |     3
    29.      1 |             4 |     3
    30.      1 |             4 |     3
    31.      1 |             4 |     3
    32.      1 |             5 |     3
    33.      1 |             5 |     3
    34.      2 |             5 |     1
    35.      2 |             5 |     1
    36.      2 |             5 |     1
    37.      2 |             5 |     1
    38.      2 |             5 |     2
    39.      2 |             6 |     2
    40.      2 |             6 |     2
    41.      2 |             6 |     2
    42.      2 |             6 |     3
    43.      2 |             6 |     3
    44.      2 |             6 |     3
    45.      2 |             6 |     3
    46. (42 rows)

  • LAG(value any [, offset integer [, default any ]])

    描述：LAG函数为各组内对应值生成滞后值。即当前值对应的行数往前偏移offset位后所得行的value值作为序号。若经过偏移后行数不存在，则对应结果取为default值。若无指定，在默认情况下，offset取为1，default值取为NULL。

    返回值类型：与参数数据类型相同

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, lag(d_moy,3,null) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;
    2.  d_moy | d_fy_week_seq | lag 
    3. -------+---------------+-----
    4.      1 |             1 |    
    5.      1 |             1 |    
    6.      1 |             1 |    
    7.      1 |             1 |   1
    8.      1 |             1 |   1
    9.      1 |             1 |   1
    10.      1 |             1 |   1
    11.      1 |             2 |   1
    12.      1 |             2 |   1
    13.      1 |             2 |   1
    14.      1 |             2 |   1
    15.      1 |             2 |   1
    16.      1 |             2 |   1
    17.      1 |             2 |   1
    18.      1 |             3 |   1
    19.      1 |             3 |   1
    20.      1 |             3 |   1
    21.      1 |             3 |   1
    22.      1 |             3 |   1
    23.      1 |             3 |   1
    24.      1 |             3 |   1
    25.      1 |             4 |   1
    26.      1 |             4 |   1
    27.      1 |             4 |   1
    28.      1 |             4 |   1
    29.      1 |             4 |   1
    30.      1 |             4 |   1
    31.      1 |             4 |   1
    32.      1 |             5 |   1
    33.      1 |             5 |   1
    34.      2 |             5 |    
    35.      2 |             5 |    
    36.      2 |             5 |    
    37.      2 |             5 |   2
    38.      2 |             5 |   2
    39.      2 |             6 |   2
    40.      2 |             6 |   2
    41.      2 |             6 |   2
    42.      2 |             6 |   2
    43.      2 |             6 |   2
    44.      2 |             6 |   2
    45.      2 |             6 |   2
    46. (42 rows)

  • LEAD(value any [, offset integer [, default any ]])

描述：LEAD函数为各组内对应值生成提前值。即当前值对应的行数向后偏移offset位后所得行的value值作为序号。若经过向后偏移后行数超过当前组内的总行数，则对应结果取为default值。若无指定，在默认情况下，offset取为1，default值取为NULL。

返回值类型：与参数数据类型相同。

示例：

  1.   postgres=# SELECT d_moy, d_fy_week_seq, lead(d_fy_week_seq,2) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM  tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;                 
  2.   d_moy | d_fy_week_seq | lead 
  3.   -------+---------------+------
  4.        1 |             1 |    1
  5.        1 |             1 |    1
  6.        1 |             1 |    1
  7.        1 |             1 |    1
  8.        1 |             1 |    1
  9.        1 |             1 |    2
  10.        1 |             1 |    2
  11.        1 |             2 |    2
  12.        1 |             2 |    2
  13.        1 |             2 |    2
  14.        1 |             2 |    2
  15.        1 |             2 |    2
  16.        1 |             2 |    3
  17.        1 |             2 |    3
  18.        1 |             3 |    3
  19.        1 |             3 |    3
  20.        1 |             3 |    3
  21.        1 |             3 |    3
  22.        1 |             3 |    3
  23.        1 |             3 |    4
  24.        1 |             3 |    4
  25.        1 |             4 |    4
  26.        1 |             4 |    4
  27.        1 |             4 |    4
  28.        1 |             4 |    4
  29.        1 |             4 |    4
  30.        1 |             4 |    5
  31.        1 |             4 |    5
  32.        1 |             5 |     
  33.        1 |             5 |     
  34.        2 |             5 |    5
  35.        2 |             5 |    5
  36.        2 |             5 |    5
  37.        2 |             5 |    6
  38.        2 |             5 |    6
  39.        2 |             6 |    6
  40.        2 |             6 |    6
  41.        2 |             6 |    6
  42.        2 |             6 |    6
  43.        2 |             6 |    6
  44.        2 |             6 |     
  45.        2 |             6 |     
  46.   (42 rows)

  • FIRST_VALUE(value any)

    描述：FIRST_VALUE函数取各组内的第一个值作为返回结果。

    返回值类型：与参数数据类型相同。

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, first_value(d_fy_week_seq) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; 
    2.  d_moy | d_fy_week_seq | first_value 
    3. -------+---------------+-------------
    4.      1 |             1 |           1
    5.      1 |             1 |           1
    6.      1 |             1 |           1
    7.      1 |             1 |           1
    8.      1 |             1 |           1
    9.      1 |             1 |           1
    10.      1 |             1 |           1
    11.      1 |             2 |           1
    12.      1 |             2 |           1
    13.      1 |             2 |           1
    14.      1 |             2 |           1
    15.      1 |             2 |           1
    16.      1 |             2 |           1
    17.      1 |             2 |           1
    18.      1 |             3 |           1
    19.      1 |             3 |           1
    20.      1 |             3 |           1
    21.      1 |             3 |           1
    22.      1 |             3 |           1
    23.      1 |             3 |           1
    24.      1 |             3 |           1
    25.      1 |             4 |           1
    26.      1 |             4 |           1
    27.      1 |             4 |           1
    28.      1 |             4 |           1
    29.      1 |             4 |           1
    30.      1 |             4 |           1
    31.      1 |             4 |           1
    32.      1 |             5 |           1
    33.      1 |             5 |           1
    34.      2 |             5 |           5
    35.      2 |             5 |           5
    36.      2 |             5 |           5
    37.      2 |             5 |           5
    38.      2 |             5 |           5
    39.      2 |             6 |           5
    40.      2 |             6 |           5
    41.      2 |             6 |           5
    42.      2 |             6 |           5
    43.      2 |             6 |           5
    44.      2 |             6 |           5
    45.      2 |             6 |           5
    46. (42 rows)

  • LAST_VALUE(value any)

    描述：LAST_VALUE函数取各组内的最后一个值作为返回结果。

    返回值类型：与参数数据类型相同。

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, last_value(d_moy) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 6 ORDER BY 1,2;
    2.   d_moy | d_fy_week_seq | last_value 
    3. -------+---------------+------------
    4.      1 |             1 |          1
    5.      1 |             1 |          1
    6.      1 |             1 |          1
    7.      1 |             1 |          1
    8.      1 |             1 |          1
    9.      1 |             1 |          1
    10.      1 |             1 |          1
    11.      1 |             2 |          1
    12.      1 |             2 |          1
    13.      1 |             2 |          1
    14.      1 |             2 |          1
    15.      1 |             2 |          1
    16.      1 |             2 |          1
    17.      1 |             2 |          1
    18.      1 |             2 |          1
    19.      1 |             3 |          1
    20.      1 |             3 |          1
    21.      1 |             3 |          1
    22.      1 |             3 |          1
    23.      1 |             3 |          1
    24.      1 |             3 |          1
    25.      1 |             3 |          1
    26.      1 |             4 |          1
    27.      1 |             4 |          1
    28.      1 |             4 |          1
    29.      1 |             4 |          1
    30.      1 |             4 |          1
    31.      1 |             4 |          1
    32.      1 |             4 |          1
    33.      1 |             5 |          1
    34.      1 |             5 |          1
    35.      2 |             5 |          2
    36.      2 |             5 |          2
    37.      2 |             5 |          2
    38.      2 |             5 |          2
    39.      2 |             5 |          2
    40. (35 rows)

  • NTH_VALUE(value any, nth integer)

    描述：NTH_VALUE函数返回该组内的第nth行作为结果。若该行不存在，则默认返回NULL。

    返回值类型：与参数数据类型相同。

    示例：

    1. postgres=# SELECT d_moy, d_fy_week_seq, nth_value(d_fy_week_seq,6) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 6 ORDER BY 1,2;
    2.  d_moy | d_fy_week_seq | nth_value 
    3. -------+---------------+-----------
    4.      1 |             1 |         1
    5.      1 |             1 |         1
    6.      1 |             1 |         1
    7.      1 |             1 |         1
    8.      1 |             1 |         1
    9.      1 |             1 |         1
    10.      1 |             1 |         1
    11.      1 |             2 |         1
    12.      1 |             2 |         1
    13.      1 |             2 |         1
    14.      1 |             2 |         1
    15.      1 |             2 |         1
    16.      1 |             2 |         1
    17.      1 |             2 |         1
    18.      1 |             3 |         1
    19.      1 |             3 |         1
    20.      1 |             3 |         1
    21.      1 |             3 |         1
    22.      1 |             3 |         1
    23.      1 |             3 |         1
    24.      1 |             3 |         1
    25.      1 |             4 |         1
    26.      1 |             4 |         1
    27.      1 |             4 |         1
    28.      1 |             4 |         1
    29.      1 |             4 |         1
    30.      1 |             4 |         1
    31.      1 |             4 |         1
    32.      1 |             5 |         1
    33.      1 |             5 |         1
    34.      2 |             5 |          
    35.      2 |             5 |          
    36.      2 |             5 |          
    37.      2 |             5 |          
    38.      2 |             5 |          
    39. (35 rows)