列存表目前只支持rank(expression)和row_number(expression)两个函数。 窗口函数与OVER 语句一起使用。OVER 语句用于对数据进行分组,并对组内元素进 行排序。窗口函数用于给组内的值生成序号。 窗口函数中的order by 后面必须跟字段名,若order by 后面跟数字,该数字会被按照常 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 355 量处理,因此对目标列没有起到排序的作用。 RANK() 描述:RANK 函数为各组内值生成跳跃排序序号,其中,相同的值具有相同序号。 返回值类型:BIGINT 示例: gbase=# SELECT d_moy, d_fy_week_seq, 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 | rank -------+---------------+------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 356 1 | 4 | 22 1 | 5 | 29 1 | 5 | 29 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 (42 rows) ROW_NUMBER() 描述:ROW_NUMBER 函数为各组内值生成连续排序序号,其中,相同的值其序号也 不相同。 返回值类型:BIGINT 示例: gbase=# SELECT d_moy, d_fy_week_seq, Row_number() 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 | row_number -------+---------------+------------ 1 | 1 | 1 1 | 1 | 2 1 | 1 | 3 1 | 1 | 4 1 | 1 | 5 1 | 1 | 6 1 | 1 | 7 1 | 2 | 8 1 | 2 | 9 1 | 2 | 10 1 | 2 | 11 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 357 1 | 2 | 12 1 | 2 | 13 1 | 2 | 14 1 | 3 | 15 1 | 3 | 16 1 | 3 | 17 1 | 3 | 18 1 | 3 | 19 1 | 3 | 20 1 | 3 | 21 1 | 4 | 22 1 | 4 | 23 1 | 4 | 24 1 | 4 | 25 1 | 4 | 26 1 | 4 | 27 1 | 4 | 28 1 | 5 | 29 1 | 5 | 30 2 | 5 | 1 2 | 5 | 2 2 | 5 | 3 2 | 5 | 4 2 | 5 | 5 2 | 6 | 6 2 | 6 | 7 2 | 6 | 8 2 | 6 | 9 2 | 6 | 10 2 | 6 | 11 2 | 6 | 12 (42 rows) DENSE_RANK() 描述:DENSE_RANK 函数为各组内值生成连续排序序号,其中,相同的值具有相同序 号。 返回值类型:BIGINT 示例: GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 358 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 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 359 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 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 360 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) CUME_DIST() 描述:CUME_DIST 函数为各组内对应值生成累积分布序号。即根据公式(小于等于当 前值的数据行数)/(该分组总行数totalrows)计算所得的相对序号。 返回值类型:DOUBLE PRECISION 示例: gbase=# SELECT d_moy, d_fy_week_seq, cume_dist() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM public.date_dim e_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | cume_dist -------+---------------+------------------ 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 361 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 5 | 1 1 | 5 | 1 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 (42 rows) NTILE(num_buckets integer) GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 362 描述:NTILE 函数根据num_buckets integer 将有序的数据集合平均分配到num_buckets 所指定数量的桶中,并将桶号分配给每一行。分配时应尽量做到平均分配。 返回值类型:INTEGER 示例: gbase=# SELECT d_moy, d_fy_week_seq, ntile(3) 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 | ntile -------+---------------+------- 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 5 | 3 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 363 1 | 5 | 3 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 3 2 | 6 | 3 2 | 6 | 3 2 | 6 | 3 (42 rows) LAG(value any [, offset integer [, default any ]]) 描述:LAG 函数为各组内对应值生成滞后值。即当前值对应的行数往前偏移offset 位 后所得行的value 值作为序号。若经过偏移后行数不存在,则对应结果取为default 值。若无 指定,在默认情况下,offset 取为1,default 值取为NULL。 返回值类型:与参数数据类型相同。 示例: gbase=# SELECT d_moy, d_fy_week_seq, lag(d_moy,3,null) 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 | lag -------+---------------+----- 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 364 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 2 | 5 | 2 | 5 | 2 | 5 | 2 2 | 5 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 (42 rows) LEAD(value any [, offset integer [, default any ]]) 描述:LEAD 函数为各组内对应值生成提前值。即当前值对应的行数向后偏移offset 位 后所得行的value 值作为序号。若经过向后偏移后行数超过当前组内的总行数,则对应结果 取为default 值。若无指定,在默认情况下,offset 取为1,default 值取为NULL。 返回值类型:与参数数据类型相同。 示例: GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 365 gbase=# 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 public.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | lead -------+---------------+------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 2 1 | 1 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 3 1 | 2 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 4 1 | 3 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 5 1 | 4 | 5 1 | 5 | 1 | 5 | 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 5 | 6 2 | 5 | 6 2 | 6 | 6 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 366 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 2 | 6 | (42 rows) FIRST_VALUE(value any) 描述:FIRST_VALUE 函数取各组内的第一个值作为返回结果。 返回值类型:与参数数据类型相同。 示例: gbase=# 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 public.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | first_value -------+---------------+------------- 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 367 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 (42 rows) LAST_VALUE(value any) 描述:LAST_VALUE 函数取各组内的最后一个值作为返回结果。 返回值类型:与参数数据类型相同。 示例: gbase=# SELECT d_moy, d_fy_week_seq, last_value(d_moy) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM public.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 6 ORDER BY 1,2; d_moy | d_fy_week_seq | last_value -------+---------------+------------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 368 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 2 2 | 5 | 2 2 | 5 | 2 2 | 5 | 2 2 | 5 | 2 (35 rows) NTH_VALUE(value any, nth integer) 描述:NTH_VALUE 函数返回该组内的第nth 行作为结果。若该行不存在,则默认返回 NULL。 返回值类型:与参数数据类型相同。 示例: gbase=# 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 public.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 6 ORDER BY 1,2; GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 369 d_moy | d_fy_week_seq | nth_value -------+---------------+----------- 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 2 | 5 | 2 | 5 | 2 | 5 | 2 | 5 | (35 rows) GBase 8c SQL 参考手册 南大通用数据技术股份有限公司 370