窗口函数

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

    • RANK()

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

      返回值类型：BIGINT

      示例：

    • ROW_NUMBER()

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

      返回值类型：BIGINT

      示例：

      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. openGauss=# 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函数为各组内对应值生成相对序号，即根据公式 (rank - 1) / (total rows - 1)计算所得的值。其中rank为该值依据RANK函数所生成的对应序号，totalrows为该分组内的总元素个数。

      返回值类型：DOUBLE PRECISION

      示例：

    • CUME_DIST()

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

      返回值类型：DOUBLE PRECISION

      示例：

      1. openGauss=# 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
      14.      1 |             2 | .466666666666667
      15.      1 |             2 | .466666666666667
      16.      1 |             2 | .466666666666667
      17.      1 |             3 |               .7
      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 |             4 | .933333333333333
      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 |             5 |                1
      32.      1 |             5 |                1
      33.      2 |             5 | .416666666666667
      34.      2 |             5 | .416666666666667
      35.      2 |             5 | .416666666666667
      36.      2 |             5 | .416666666666667
      37.      2 |             5 | .416666666666667
      38.      2 |             6 |                1
      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. (42 rows)

    • NTILE(num_buckets integer)

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

      返回值类型：INTEGER

      示例：

      1. openGauss=# 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。

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

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

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

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

      示例：

      1. openGauss=# 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
      27.      1 |             4 |    4
      28.      1 |             4 |    4
      29.      1 |             4 |    5
      30.      1 |             4 |    5
      31.      1 |             5 |     
      32.      1 |             5 |     
      33.      2 |             5 |    5
      34.      2 |             5 |    5
      35.      2 |             5 |    5
      36.      2 |             5 |    6
      37.      2 |             5 |    6
      38.      2 |             6 |    6
      39.      2 |             6 |    6
      40.      2 |             6 |    6
      41.      2 |             6 |    6
      42.      2 |             6 |    6
      43.      2 |             6 |     
      44.      2 |             6 |     
      45. (42 rows)

    • FIRST_VALUE(value any)

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

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

      示例：

      1. openGauss=# 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函数取各组内的最后一个值作为返回结果。

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

      示例：

    • NTH_VALUE(value any, nth integer)

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

      示例：

      1. openGauss=# 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 |