• 窗口函数详细用法

    仅做记录，原文：https://blog.csdn.net/scgaliguodong123_/article/details/60135385

    窗口函数与分析函数
    应用场景：
    （1）用于分区排序
    （2）动态Group By
    （3）Top N
    （4）累计计算
    （5）层次查询

    窗口函数
    FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
    LAST_VALUE： 取分组内排序后，截止到当前行，最后一个值
    LEAD(col,n,DEFAULT) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
    LAG(col,n,DEFAULT) ：与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

    OVER从句
    1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
    2、使用PARTITION BY语句，使用一个或者多个原始数据类型的列
    3、使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
    4、使用窗口规范，窗口规范支持以下格式：
    ---------------------

    ?
    1
    2
    3
    (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
    (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
    (ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

    　　

    当ORDER BY后面缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

    当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

    OVER从句支持以下函数， 但是并不支持和窗口一起使用它们。
    Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
    Lead 和 Lag 函数.

    分析函数
    ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列,比如，按照pv降序排列，生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。
    RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
    DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
    CUME_DIST 小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
    PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
    NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。

    Hive2.1.0及以后支持Distinct
    在聚合函数（SUM, COUNT and AVG）中，支持distinct，但是在ORDER BY 或者 窗口限制不支持。

    ?
    1
    COUNT(DISTINCT a) OVER (PARTITION BY c)

    Hive 2.2.0中在使用ORDER BY和窗口限制时支持distinct

    ?
    1
    COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

    Hive2.1.0及以后支持在OVER从句中支持聚合函数

    ?
    1
    2
    3
    SELECT rank() OVER (ORDER BY sum(b))
    FROM T
    GROUP BY a;

    测试数据集：

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    ## COUNT、SUM、MIN、MAX、AVG
    select
        user_id,
        user_type,
        sales,
        --默认为从起点到当前行
        sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc) AS sales_1,
        --从起点到当前行，结果与sales_1不同。
        sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sales_2,
        --当前行+往前3行
        sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS sales_3,
        --当前行+往前3行+往后1行
        sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS sales_4,
        --当前行+往后所有行 
        sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sales_5,
        --分组内所有行
        SUM(sales) OVER(PARTITION BY user_type) AS sales_6                         
    from
        order_detail
    order by
        user_type,
        sales,
        user_id
     
    +----------+------------+--------+----------+----------+----------+----------+----------+----------+--+
    | user_id  | user_type  | sales  | sales_1  | sales_2  | sales_3  | sales_4  | sales_5  | sales_6  |
    +----------+------------+--------+----------+----------+----------+----------+----------+----------+--+
    | liiu     | new        | 1      | 2        | 2        | 2        | 4        | 22       | 23       |
    | qibaqiu  | new        | 1      | 2        | 1        | 1        | 2        | 23       | 23       |
    | zhangsa  | new        | 2      | 4        | 4        | 4        | 7        | 21       | 23       |
    | wanger   | new        | 3      | 7        | 7        | 7        | 12       | 19       | 23       |
    | lilisi   | new        | 5      | 17       | 17       | 15       | 21       | 11       | 23       |
    | qishili  | new        | 5      | 17       | 12       | 11       | 16       | 16       | 23       |
    | wutong   | new        | 6      | 23       | 23       | 19       | 19       | 6        | 23       |
    | lisi     | old        | 1      | 1        | 1        | 1        | 3        | 6        | 6        |
    | wangshi  | old        | 2      | 3        | 3        | 3        | 6        | 5        | 6        |
    | liwei    | old        | 3      | 6        | 6        | 6        | 6        | 3        | 6        |
    +----------+------------+--------+----------+----------+----------+----------+----------+----------+--+
     
    注意:
    结果和ORDER BY相关,默认为升序
    如果不指定ROWS BETWEEN,默认为从起点到当前行;
    如果不指定ORDER BY，则将分组内所有值累加;
     
    关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
    PRECEDING：往前
    FOLLOWING：往后
    CURRENT ROW：当前行
    UNBOUNDED：无界限（起点或终点）
    UNBOUNDED PRECEDING：表示从前面的起点
    UNBOUNDED FOLLOWING：表示到后面的终点
    其他COUNT、AVG，MIN，MAX，和SUM用法一样。
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    ## first_value与last_value
    select
        user_id,
        user_type,
        ROW_NUMBER() OVER(PARTITION BY user_type ORDER BY sales) AS row_num, 
        first_value(user_id) over (partition by user_type order by sales desc) as max_sales_user,
        first_value(user_id) over (partition by user_type order by sales asc) as min_sales_user,
        last_value(user_id) over (partition by user_type order by sales desc) as curr_last_min_user,
        last_value(user_id) over (partition by user_type order by sales asc) as curr_last_max_user
    from
        order_detail;
     
    +----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+
    | user_id  | user_type  | row_num  | max_sales_user  | min_sales_user  | curr_last_min_user  | curr_last_max_user  |
    +----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+
    | wutong   | new        | 7        | wutong          | qibaqiu         | wutong              | wutong              |
    | lilisi   | new        | 6        | wutong          | qibaqiu         | qishili             | lilisi              |
    | qishili  | new        | 5        | wutong          | qibaqiu         | qishili             | lilisi              |
    | wanger   | new        | 4        | wutong          | qibaqiu         | wanger              | wanger              |
    | zhangsa  | new        | 3        | wutong          | qibaqiu         | zhangsa             | zhangsa             |
    | liiu     | new        | 2        | wutong          | qibaqiu         | qibaqiu             | liiu                |
    | qibaqiu  | new        | 1        | wutong          | qibaqiu         | qibaqiu             | liiu                |
    | liwei    | old        | 3        | liwei           | lisi            | liwei               | liwei               |
    | wangshi  | old        | 2        | liwei           | lisi            | wangshi             | wangshi             |
    | lisi     | old        | 1        | liwei           | lisi            | lisi                | lisi                |
    +----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+
     
    ## lead与lag
    select
        user_id,device_id,
        lead(device_id) over (order by sales) as default_after_one_line,
        lag(device_id) over (order by sales) as default_before_one_line,
        lead(device_id,2) over (order by sales) as after_two_line,
        lag(device_id,2,'abc') over (order by sales) as before_two_line
    from
        order_detail;
     
    +----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+
    | user_id  |  device_id  | default_after_one_line  | default_before_one_line  | after_two_line  | before_two_line  |
    +----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+
    | qibaqiu  | fds         | fdsfagwe                | NULL                     | 543gfd          | abc              |
    | liiu     | fdsfagwe    | 543gfd                  | fds                      | f332            | abc              |
    | lisi     | 543gfd      | f332                    | fdsfagwe                 | dfsadsa323      | fds              |
    | wangshi  | f332        | dfsadsa323              | 543gfd                   | hfd             | fdsfagwe         |
    | zhangsa  | dfsadsa323  | hfd                     | f332                     | 65ghf           | 543gfd           |
    | liwei    | hfd         | 65ghf                   | dfsadsa323               | fds             | f332             |
    | wanger   | 65ghf       | fds                     | hfd                      | dsfgg           | dfsadsa323       |
    | qishili  | fds         | dsfgg                   | 65ghf                    | 543gdfsd        | hfd              |
    | lilisi   | dsfgg       | 543gdfsd                | fds                      | NULL            | 65ghf            |
    | wutong   | 543gdfsd    | NULL                    | dsfgg                    | NULL            | fds              |
    +----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    ## RANK、ROW_NUMBER、DENSE_RANK
    select
        user_id,user_type,sales,
        RANK() over (partition by user_type order by sales desc) as r,
        ROW_NUMBER() over (partition by user_type order by sales desc) as rn,
        DENSE_RANK() over (partition by user_type order by sales desc) as dr
    from
        order_detail;  
     
     
    +----------+------------+--------+----+-----+-----+--+
    | user_id  | user_type  | sales  | r  | rn  | dr  |
    +----------+------------+--------+----+-----+-----+--+
    | wutong   | new        | 6      | 1  | 1   | 1   |
    | qishili  | new        | 5      | 2  | 2   | 2   |
    | lilisi   | new        | 5      | 2  | 3   | 2   |
    | wanger   | new        | 3      | 4  | 4   | 3   |
    | zhangsa  | new        | 2      | 5  | 5   | 4   |
    | qibaqiu  | new        | 1      | 6  | 6   | 5   |
    | liiu     | new        | 1      | 6  | 7   | 5   |
    | liwei    | old        | 3      | 1  | 1   | 1   |
    | wangshi  | old        | 2      | 2  | 2   | 2   |
    | lisi     | old        | 1      | 3  | 3   | 3   |
    +----------+------------+--------+----+-----+-----+--+ 
     
    ## NTILE
     
    select
        user_type,sales,
        --分组内将数据分成2片
        NTILE(2) OVER(PARTITION BY user_type ORDER BY sales) AS nt2,
        --分组内将数据分成3片   
        NTILE(3) OVER(PARTITION BY user_type ORDER BY sales) AS nt3,
        --分组内将数据分成4片   
        NTILE(4) OVER(PARTITION BY user_type ORDER BY sales) AS nt4,
        --将所有数据分成4片
        NTILE(4) OVER(ORDER BY sales) AS all_nt4
    from
        order_detail
    order by
        user_type,
        sales
     
     
    +------------+--------+------+------+------+----------+--+
    | user_type  | sales  | nt2  | nt3  | nt4  | all_nt4  |
    +------------+--------+------+------+------+----------+--+
    | new        | 1      | 1    | 1    | 1    | 1        |
    | new        | 1      | 1    | 1    | 1    | 1        |
    | new        | 2      | 1    | 1    | 2    | 2        |
    | new        | 3      | 1    | 2    | 2    | 3        |
    | new        | 5      | 2    | 2    | 3    | 4        |
    | new        | 5      | 2    | 3    | 3    | 3        |
    | new        | 6      | 2    | 3    | 4    | 4        |
    | old        | 1      | 1    | 1    | 1    | 1        |
    | old        | 2      | 1    | 2    | 2    | 2        |
    | old        | 3      | 2    | 3    | 3    | 2        |
    +------------+--------+------+------+------+----------+--+
     
     
    求取sale前20%的用户ID
     
    select
        user_id
    from
    (
        select
            user_id,
            NTILE(5) OVER(ORDER BY sales desc) AS nt
        from
            order_detail
    )A
    where nt=1;
     
    ## CUME_DIST、PERCENT_RANK
     
    select
    user_id,user_type,sales,
    --没有partition,所有数据均为1组
    CUME_DIST() OVER(ORDER BY sales) AS cd1,
    --按照user_type进行分组
    CUME_DIST() OVER(PARTITION BY user_type ORDER BY sales) AS cd2
    from
    order_detail;  
     
    +----------+------------+--------+------+----------------------+--+
    | user_id  | user_type  | sales  | cd1  |         cd2          |
    +----------+------------+--------+------+----------------------+--+
    | liiu     | new        | 1      | 0.3  | 0.2857142857142857   |
    | qibaqiu  | new        | 1      | 0.3  | 0.2857142857142857   |
    | zhangsa  | new        | 2      | 0.5  | 0.42857142857142855  |
    | wanger   | new        | 3      | 0.7  | 0.5714285714285714   |
    | lilisi   | new        | 5      | 0.9  | 0.8571428571428571   |
    | qishili  | new        | 5      | 0.9  | 0.8571428571428571   |
    | wutong   | new        | 6      | 1.0  | 1.0                  |
    | lisi     | old        | 1      | 0.3  | 0.3333333333333333   |
    | wangshi  | old        | 2      | 0.5  | 0.6666666666666666   |
    | liwei    | old        | 3      | 0.7  | 1.0                  |
    +----------+------------+--------+------+----------------------+--+
     
     
    select
    user_type,sales
    --分组内总行数     
    SUM(1) OVER(PARTITION BY user_type) AS s,
    --RANK值 
    RANK() OVER(ORDER BY sales) AS r,   
    PERCENT_RANK() OVER(ORDER BY sales) AS pr,
    --分组内    
    PERCENT_RANK() OVER(PARTITION BY user_type ORDER BY sales) AS prg
    from
    order_detail;  
     
    +----+-----+---------------------+---------------------+--+
    | s  |  r  |         pr          |         prg         |
    +----+-----+---------------------+---------------------+--+
    | 7  | 1   | 0.0                 | 0.0                 |
    | 7  | 1   | 0.0                 | 0.0                 |
    | 7  | 4   | 0.3333333333333333  | 0.3333333333333333  |
    | 7  | 6   | 0.5555555555555556  | 0.5                 |
    | 7  | 8   | 0.7777777777777778  | 0.6666666666666666  |
    | 7  | 8   | 0.7777777777777778  | 0.6666666666666666  |
    | 7  | 10  | 1.0                 | 1.0                 |
    | 3  | 1   | 0.0                 | 0.0                 |
    | 3  | 4   | 0.3333333333333333  | 0.5                 |
    | 3  | 6   | 0.5555555555555556  | 1.0                 |
    +----+-----+---------------------+---------------------+--+

    　　
  • 相关阅读:
    一点技巧
    题解G
    WA七次，疯了》》》》》OTZ
    就是过不了啊无奈。。。。。水题都过不了…………OTZ OTZ OTZ
    [IOS]使用UIScrollView和UIPageControl显示半透明帮助蒙板
    [System]几种同步方式
    [Objective C] Singleton类的一个模版
    [IOS] 自定义AlertView实现模态对话框
    [IOS] UIKit Animation
    [IOS]使用genstrings和NSLocalizedString实现App文本的本地化
  • 原文地址：https://www.cnblogs.com/jpfss/p/12697454.html
Copyright © 2020-2023  润新知