窗口函数

窗口函数

列存表目前只支持rank(expression)和row_number(expression)两个函数。

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

窗口函数 - 图1 说明:
窗口函数中的order by后面必须跟字段名,若order by后面跟数字,该数字会被按照常量处理,因此对目标列没有起到排序的作用。

  • RANK()

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

    返回值类型:BIGINT

    示例:

    1. postgres=# 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;
    2. d_moy | d_fy_week_seq | 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 | 8
    12. 1 | 2 | 8
    13. 1 | 2 | 8
    14. 1 | 2 | 8
    15. 1 | 2 | 8
    16. 1 | 2 | 8
    17. 1 | 2 | 8
    18. 1 | 3 | 15
    19. 1 | 3 | 15
    20. 1 | 3 | 15
    21. 1 | 3 | 15
    22. 1 | 3 | 15
    23. 1 | 3 | 15
    24. 1 | 3 | 15
    25. 1 | 4 | 22
    26. 1 | 4 | 22
    27. 1 | 4 | 22
    28. 1 | 4 | 22
    29. 1 | 4 | 22
    30. 1 | 4 | 22
    31. 1 | 4 | 22
    32. 1 | 5 | 29
    33. 1 | 5 | 29
    34. 2 | 5 | 1
    35. 2 | 5 | 1
    36. 2 | 5 | 1
    37. 2 | 5 | 1
    38. 2 | 5 | 1
    39. 2 | 6 | 6
    40. 2 | 6 | 6
    41. 2 | 6 | 6
    42. 2 | 6 | 6
    43. 2 | 6 | 6
    44. 2 | 6 | 6
    45. 2 | 6 | 6
    46. (42 rows)
  • ROW_NUMBER()

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

    返回值类型:BIGINT

    示例:

    1. postgres=# 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;
    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. postgres=# 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()

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

    返回值类型:DOUBLE PRECISION

    示例:

    1. postgres=# 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;
    2. d_moy | d_fy_week_seq | percent_rank
    3. -------+---------------+------------------
    4. 1 | 1 | 0
    5. 1 | 1 | 0
    6. 1 | 1 | 0
    7. 1 | 1 | 0
    8. 1 | 1 | 0
    9. 1 | 1 | 0
    10. 1 | 1 | 0
    11. 1 | 2 | .241379310344828
    12. 1 | 2 | .241379310344828
    13. 1 | 2 | .241379310344828
    14. 1 | 2 | .241379310344828
    15. 1 | 2 | .241379310344828
    16. 1 | 2 | .241379310344828
    17. 1 | 2 | .241379310344828
    18. 1 | 3 | .482758620689655
    19. 1 | 3 | .482758620689655
    20. 1 | 3 | .482758620689655
    21. 1 | 3 | .482758620689655
    22. 1 | 3 | .482758620689655
    23. 1 | 3 | .482758620689655
    24. 1 | 3 | .482758620689655
    25. 1 | 4 | .724137931034483
    26. 1 | 4 | .724137931034483
    27. 1 | 4 | .724137931034483
    28. 1 | 4 | .724137931034483
    29. 1 | 4 | .724137931034483
    30. 1 | 4 | .724137931034483
    31. 1 | 4 | .724137931034483
    32. 1 | 5 | .96551724137931
    33. 1 | 5 | .96551724137931
    34. 2 | 5 | 0
    35. 2 | 5 | 0
    36. 2 | 5 | 0
    37. 2 | 5 | 0
    38. 2 | 5 | 0
    39. 2 | 6 | .454545454545455
    40. 2 | 6 | .454545454545455
    41. 2 | 6 | .454545454545455
    42. 2 | 6 | .454545454545455
    43. 2 | 6 | .454545454545455
    44. 2 | 6 | .454545454545455
    45. 2 | 6 | .454545454545455
    46. (42 rows)
  • CUME_DIST()

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

    返回值类型:DOUBLE PRECISION

    示例:

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

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

    返回值类型:INTEGER

    示例:

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

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

    示例:

    1. postgres=# 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;
    2. d_moy | d_fy_week_seq | lag
    3. -------+---------------+-----
    4. 1 | 1 |
    5. 1 | 1 |
    6. 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 | 2
    38. 2 | 5 | 2
    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)
  • LEAD(value any [, offset integer [, default any ]])

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

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

    示例:

    1. postgres=# 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
    2. -------+---------------+------
    3. 1 | 1 | 1
    4. 1 | 1 | 1
    5. 1 | 1 | 1
    6. 1 | 1 | 1
    7. 1 | 1 | 1
    8. 1 | 1 | 2
    9. 1 | 1 | 2
    10. 1 | 2 | 2
    11. 1 | 2 | 2
    12. 1 | 2 | 2
    13. 1 | 2 | 2
    14. 1 | 2 | 2
    15. 1 | 2 | 3
    16. 1 | 2 | 3
    17. 1 | 3 | 3
    18. 1 | 3 | 3
    19. 1 | 3 | 3
    20. 1 | 3 | 3
    21. 1 | 3 | 3
    22. 1 | 3 | 4
    23. 1 | 3 | 4
    24. 1 | 4 | 4
    25. 1 | 4 | 4
    26. 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. postgres=# 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函数取各组内的最后一个值作为返回结果。

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

    示例:

    1. postgres=# 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;
    2. d_moy | d_fy_week_seq | last_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 | 2 | 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 | 3 | 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 | 4 | 1
    33. 1 | 5 | 1
    34. 1 | 5 | 1
    35. 2 | 5 | 2
    36. 2 | 5 | 2
    37. 2 | 5 | 2
    38. 2 | 5 | 2
    39. 2 | 5 | 2
    40. (35 rows)
  • NTH_VALUE(value any, nth integer)

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

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

    示例:

    1. postgres=# 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 |
    39. (35 rows)