我的书签
添加书签
移除书签窗口函数
窗口函数
列存表目前只支持rank(expression)和row_number(expression)两个函数。
窗口函数与OVER语句一起使用。OVER语句用于对数据进行分组，并对组内元素进行排序。窗口函数用于给组内的值生成序号。
说明：
窗口函数中的order by后面必须跟字段名，若order by后面跟数字，该数字会被按照常量处理，因此对目标列没有起到排序的作用。
- RANK()
描述：RANK函数为各组内值生成跳跃排序序号，其中，相同的值具有相同序号。
返回值类型：BIGINT
示例：
1. 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;
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. 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;
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. 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;
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. 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;
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. 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;
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. 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;
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. 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;
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. 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;
2. d_moy | d_fy_week_seq | lead
3. -------+---------------+------
4. 1 | 1 | 1
5. 1 | 1 | 1
6. 1 | 1 | 1
7. 1 | 1 | 1
8. 1 | 1 | 1
9. 1 | 1 | 2
10. 1 | 1 | 2
11. 1 | 2 | 2
12. 1 | 2 | 2
13. 1 | 2 | 2
14. 1 | 2 | 2
15. 1 | 2 | 2
16. 1 | 2 | 3
17. 1 | 2 | 3
18. 1 | 3 | 3
19. 1 | 3 | 3
20. 1 | 3 | 3
21. 1 | 3 | 3
22. 1 | 3 | 3
23. 1 | 3 | 4
24. 1 | 3 | 4
25. 1 | 4 | 4
26. 1 | 4 | 4
27. 1 | 4 | 4
28. 1 | 4 | 4
29. 1 | 4 | 4
30. 1 | 4 | 5
31. 1 | 4 | 5
32. 1 | 5 |
33. 1 | 5 |
34. 2 | 5 | 5
35. 2 | 5 | 5
36. 2 | 5 | 5
37. 2 | 5 | 6
38. 2 | 5 | 6
39. 2 | 6 | 6
40. 2 | 6 | 6
41. 2 | 6 | 6
42. 2 | 6 | 6
43. 2 | 6 | 6
44. 2 | 6 |
45. 2 | 6 |
46. (42 rows)
- FIRST_VALUE(value any)
描述：FIRST_VALUE函数取各组内的第一个值作为返回结果。
返回值类型：与参数数据类型相同。
示例：
1. 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;
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. 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;
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. 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;
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)
- row_count()
描述：上一条语句影响的行数。
- 若上次执行的是DDL语句，比如create table或者drop语句，会返回0。
- 对于insert/update/delete语句，以及其他会涉及元组修改的语句，会返回具体影响的行数。
- 对于select语句，若为select into, 会返回导出的元组数量，反之若语句不涉及修改则返回-1。
返回值类型：BIGINT
示例：
1. td_db=# insert into test_any_value values(1,1),(2,1),(3,2),(4,2);
2. INSERT 0 4
3. td_db=# select row_count();
4. row_count
5. -----------
6. 4
7. (1 row)
