openGauss 資料庫視窗函數示例

視窗函數是一種分析型的OLAP函數，OLAP是online analytical processing的簡稱，意思是對資料庫資料進行實時分析處理。

列存表目前隻支援rank(expression)和row_number(expression)兩個函數。

視窗函數與OVER語句一起使用。OVER語句用于對資料進行分組，并對組内元素進行排序。視窗函數用于給組内的值生成序号。

說明：視窗函數中的order by後面必須跟字段名，若order by後面跟數字，該數字會被按照常量處理，是以對目标列沒有起到排序的作用。

• RANK()

  描述：RANK函數為各組内值生成跳躍排序序号，其中，相同的值具有相同序号。

  傳回值類型：BIGINT

  示例：openGauss=# 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;

• ROW_NUMBER()

  描述：ROW_NUMBER函數為各組内值生成連續排序序号，其中，相同的值其序号也不相同。

  傳回值類型：BIGINT

  示例：openGauss=# 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;

• DENSE_RANK()

  描述：DENSE_RANK函數為各組内值生成連續排序序号，其中，相同的值具有相同序号。

  傳回值類型：BIGINT

  示例：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;

• PERCENT_RANK()

  描述：PERCENT_RANK函數為各組内對應值生成相對序号，即根據公式 (rank - 1) / (total rows - 1)計算所得的值。其中rank為該值依據RANK函數所生成的對應序号，totalrows為該分組内的總元素個數。

  傳回值類型：DOUBLE PRECISION

  示例：openGauss=# 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;

• CUME_DIST()

  描述：CUME_DIST函數為各組内對應值生成累積分布序号。即根據公式(小于等于目前值的資料行數)/(該分組總行數totalrows)計算所得的相對序号。

  傳回值類型：DOUBLE PRECISION

  示例：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;

• NTILE(num_buckets integer)

  描述：NTILE函數根據num_buckets integer将有序的資料集合平均配置設定到num_buckets所指定數量的桶中，并将桶号配置設定給每一行。配置設定時應盡量做到平均配置設定。

  傳回值類型：INTEGER

  示例：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;

• 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;

• 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;

• 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;

• LAST_VALUE(value any)

  描述：LAST_VALUE函數取各組内的最後一個值作為傳回結果。

  傳回值類型：與參數資料類型相同。

  示例：openGauss=# 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;

• NTH_VALUE(value any, nth integer)

  描述：NTH_VALUE函數傳回該組内的第nth行作為結果。若該行不存在，則預設傳回NULL。

  傳回值類型：與參數資料類型相同。

  示例：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;