GBase 8c 窗口函数（二）

1. DENSE_RANK()

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

返回值类型：BIGINT

示例：

 gbase=# SELECT d_moy, d_fy_week_seq, dense_rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM public.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;

 d_moy | d_fy_week_seq | dense_rank

-------+---------------+------------

     1 |             1 |          1

     1 |             1 |          1

     1 |             1 |          1

     1 |             1 |          1

     1 |             1 |          1

     1 |             1 |          1

     1 |             1 |          1

     1 |             2 |          2

     1 |             2 |          2

     1 |             2 |          2

     1 |             2 |          2

     1 |             2 |          2

     1 |             2 |          2

     1 |             2 |          2

     1 |             3 |          3

     1 |             3 |          3

     1 |             3 |          3

     1 |             3 |          3

     1 |             3 |          3

     1 |             3 |          3

     1 |             3 |          3

     1 |             4 |          4

     1 |             4 |          4

     1 |             4 |          4

     1 |             4 |          4

     1 |             4 |          4

     1 |             4 |          4

     1 |             4 |          4

     1 |             5 |          5

     1 |             5 |          5

     2 |             5 |          1

     2 |             5 |          1

     2 |             5 |          1

     2 |             5 |          1

     2 |             5 |          1

     2 |             6 |          2

     2 |             6 |          2

     2 |             6 |          2

     2 |             6 |          2

     2 |             6 |          2

     2 |             6 |          2

     2 |             6 |          2

(42 rows)

1. PERCENT_RANK()

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

返回值类型：DOUBLE PRECISION

示例：

 gbase=# SELECT d_moy, d_fy_week_seq, percent_rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM public.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2;

 d_moy | d_fy_week_seq |   percent_rank   

-------+---------------+------------------

     1 |             1 |                0

     1 |             1 |                0

     1 |             1 |                0

     1 |             1 |                0

     1 |             1 |                0

     1 |             1 |                0

     1 |             1 |                0

     1 |             2 | .241379310344828

     1 |             2 | .241379310344828

     1 |             2 | .241379310344828

     1 |             2 | .241379310344828

     1 |             2 | .241379310344828

     1 |             2 | .241379310344828

     1 |             2 | .241379310344828

     1 |             3 | .482758620689655

     1 |             3 | .482758620689655

     1 |             3 | .482758620689655

     1 |             3 | .482758620689655

     1 |             3 | .482758620689655

     1 |             3 | .482758620689655

     1 |             3 | .482758620689655

     1 |             4 | .724137931034483

     1 |             4 | .724137931034483

     1 |             4 | .724137931034483

     1 |             4 | .724137931034483

     1 |             4 | .724137931034483

     1 |             4 | .724137931034483

     1 |             4 | .724137931034483

     1 |             5 |  .96551724137931

     1 |             5 |  .96551724137931

     2 |             5 |                0

     2 |             5 |                0

     2 |             5 |                0

     2 |             5 |                0

     2 |             5 |                0

     2 |             6 | .454545454545455

     2 |             6 | .454545454545455

     2 |             6 | .454545454545455

     2 |             6 | .454545454545455

     2 |             6 | .454545454545455

     2 |             6 | .454545454545455

     2 |             6 | .454545454545455

(42 rows)