openGauss SQL参考—函数和操作符：窗口函数（4）

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

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

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

  示例：

  openGauss=# 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;
   d_moy | d_fy_week_seq | lag 
  -------+---------------+-----
       1 |             1 |    
       1 |             1 |    
       1 |             1 |    
       1 |             1 |   1
       1 |             1 |   1
       1 |             1 |   1
       1 |             1 |   1
       1 |             2 |   1
       1 |             2 |   1
       1 |             2 |   1
       1 |             2 |   1
       1 |             2 |   1
       1 |             2 |   1
       1 |             2 |   1
       1 |             3 |   1
       1 |             3 |   1
       1 |             3 |   1
       1 |             3 |   1
       1 |             3 |   1
       1 |             3 |   1
       1 |             3 |   1
       1 |             4 |   1
       1 |             4 |   1
       1 |             4 |   1
       1 |             4 |   1
       1 |             4 |   1
       1 |             4 |   1
       1 |             4 |   1
       1 |             5 |   1
       1 |             5 |   1
       2 |             5 |    
       2 |             5 |    
       2 |             5 |    
       2 |             5 |   2
       2 |             5 |   2
       2 |             6 |   2
       2 |             6 |   2
       2 |             6 |   2
       2 |             6 |   2
       2 |             6 |   2
       2 |             6 |   2
       2 |             6 |   2
  (42 rows)
  

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

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

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

  示例：

  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;
  d_moy | d_fy_week_seq | lead
  -------+---------------+------
       1 |             1 |    1
       1 |             1 |    1
       1 |             1 |    1
       1 |             1 |    1
       1 |             1 |    1
       1 |             1 |    2
       1 |             1 |    2
       1 |             2 |    2
       1 |             2 |    2
       1 |             2 |    2
       1 |             2 |    2
       1 |             2 |    2
       1 |             2 |    3
       1 |             2 |    3
       1 |             3 |    3
       1 |             3 |    3
       1 |             3 |    3
       1 |             3 |    3
       1 |             3 |    3
       1 |             3 |    4
       1 |             3 |    4
       1 |             4 |    4
       1 |             4 |    4
       1 |             4 |    4
       1 |             4 |    4
       1 |             4 |    4
       1 |             4 |    5
       1 |             4 |    5
       1 |             5 |     
       1 |             5 |     
       2 |             5 |    5
       2 |             5 |    5
       2 |             5 |    5
       2 |             5 |    6
       2 |             5 |    6
       2 |             6 |    6
       2 |             6 |    6
       2 |             6 |    6
       2 |             6 |    6
       2 |             6 |    6
       2 |             6 |     
       2 |             6 |     
  (42 rows)
  

• FIRST_VALUE(value any)

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

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

  示例：

  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; 
   d_moy | d_fy_week_seq | first_value 
  -------+---------------+-------------
       1 |             1 |           1
       1 |             1 |           1
       1 |             1 |           1
       1 |             1 |           1
       1 |             1 |           1
       1 |             1 |           1
       1 |             1 |           1
       1 |             2 |           1
       1 |             2 |           1
       1 |             2 |           1
       1 |             2 |           1
       1 |             2 |           1
       1 |             2 |           1
       1 |             2 |           1
       1 |             3 |           1
       1 |             3 |           1
       1 |             3 |           1
       1 |             3 |           1
       1 |             3 |           1
       1 |             3 |           1
       1 |             3 |           1
       1 |             4 |           1
       1 |             4 |           1
       1 |             4 |           1
       1 |             4 |           1
       1 |             4 |           1
       1 |             4 |           1
       1 |             4 |           1
       1 |             5 |           1
       1 |             5 |           1
       2 |             5 |           5
       2 |             5 |           5
       2 |             5 |           5
       2 |             5 |           5
       2 |             5 |           5
       2 |             6 |           5
       2 |             6 |           5
       2 |             6 |           5
       2 |             6 |           5
       2 |             6 |           5
       2 |             6 |           5
       2 |             6 |           5
  (42 rows)