- 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)
- 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)