• GBase 8c V3.0.0数据类型——窗口函数


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

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

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

    1. 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

         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)

    1. 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

         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)

    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)

    1. 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

         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)

    1. NTILE(num_buckets integer)

    描述: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

         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)

    1. 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

         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)

    1. LEAD(value any [, offset integer [, default any ]])

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

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

    示例:

     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

         2 |             6 |    6

         2 |             6 |    6

         2 |             6 |    6

         2 |             6 |    6

         2 |             6 |     

         2 |             6 |     

    (42 rows)

    1. 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

         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)

    1. 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

         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)

    1. 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;

     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)

  • 相关阅读:
    Try llama2 in NUC (by quqi99)
    CentOS 7基础操作06_Linux创建目录和文件
    「torch.cosine_smilarity() = 0」引发的关于cpu与gpu精度问题的探讨
    Python 合并两张图片
    vcpkg切换 Visual Studio 版本
    基础题--数组
    机器学习总结(一)——回归问题概述
    Spring注入bean的常用的六种方式
    SpringBoot的测试方案
    阿里云oss对象存储跨域设置
  • 原文地址:https://blog.csdn.net/futianxia061112/article/details/125482975