sql之窗口函数详解

官方地址 https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html                                
                                
窗口函数：也可以被称为OLAP函数或分析函数。                                
                                
窗口函数大体可以分为以下两种：                                
    1. 能够作为窗口函数的聚合函数（sum，avg，count，max，min）                            
    2. rank、dense_rank、row_number等专用窗口函数。                            
                                
窗口函数和聚合函数的区别：            
    聚合函数针对分组只返回一个结果                    
    窗口函数针对分组中的每一行返回函数的结果    
                
窗口函数语法四个部分:                                
    1、function本身 用于对窗口中的数据进行操作；                            
    2、partition by 用于将结果集分区；                            
    3、order by 用于对分区中的数据进行排序；                            
    4、window 用于定义function在其上操作的行的集合，即function所影响的范围。                            
                                
1.RANK()、DENSE_RANK()、ROW_NUMBER() 、PERCENT_RANK()  → 排序                            
                                
    ROW_NUMBER()：将某字段按照顺序输出表的行号                            
    select user_name,user_address_kbn, row_number() over(order by user_address_kbn) as num from t_user;                            
                                
        user_name    user_address_kbn    num                
        wang2    A    1                
        wang3    A    2                
        wang4    A    3                
        wang1    B    4                
                                
    RANK()：按照某字段的排序结果添加排名，但它是跳跃的、间断的排名。前三人并列第一，第四人就是第四名。                            
    select user_name,user_address_kbn, rank() over(order by user_address_kbn) as num from t_user;                            
                                
        user_name    user_address_kbn    num                
        wang2    A    1                
        wang3    A    1                
        wang4    A    1                
        wang1    B    4                
                                
    DENSE_RANK()：连续的、不间断。前三人并列第一，第四人就是第二名。                            
    select user_name,user_address_kbn, dense_rank() over(order by user_address_kbn) as num from t_user;                            
                                
        user_name    user_address_kbn    num                
        wang2    A    1                
        wang3    A    1                
        wang4    A    1                
        wang1    B    2                
                                
    PERCENT_RANK()：返回小于当前行中的值的分区值的百分比，不包括最高值。返回值范围从 0 到 1，表示行相对排名。百分比排名值。                            
    select user_name,                            
        ROW_NUMBER() over w as 'ROW_NUMBER',                            
        PERCENT_RANK() over w as 'PERCENT_RANK'                             
    from t_user WINDOW w AS (order by user_name);                            
        user_name    ROW_NUMBER    PERCENT_RANK                
        wang1    1    0                
        wang1    2    0                
        wang3    3    0.5                
        wang4    4    0.75                
        wang5    5    1                
                                
2.FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()                            
                                
    FIRST_VALUE()          窗口第一行的参数值                    
    SELECT user_name, user_address, user_address_kbn,                            
        FIRST_VALUE(user_address)  OVER (order by user_address_kbn) AS 'first'                            
    FROM t_user;                            
        user_name    user_address    user_address_kbn    first            
        wang2    北京    A    北京            
        wang4    广州    A    北京            
        wang5    西安    A    北京            
        wang1    西安    B    北京            
        wang3    上海    B    北京            
                                
    LAST_VALUE()          窗口最后一行的参数值                    
    SELECT user_name, user_address, user_address_kbn,                            
        LAST_VALUE(user_address)  OVER (order by user_address_kbn) AS 'last'                            
    FROM t_user;                            
        user_name    user_address    user_address_kbn    last            
        wang2    北京    A    西安            
        wang4    广州    A    西安            
        wang5    西安    A    西安            
        wang1    西安    B    上海            
        wang3    上海    B    上海            
                                
    NTH_VALUE()          窗口第 N 行的参数值                    
    SELECT user_name, user_address, user_address_kbn,                            
        NTH_VALUE(user_address, 2) OVER (order by user_address_kbn) AS 'second'                            
    FROM t_user;                            
        user_name    user_address    user_address_kbn    second            
        wang2    北京    A    广州            
        wang4    广州    A    广州            
        wang5    西安    A    广州            
        wang1    西安    B    广州            
        wang3    上海    B    广州            
                                
3. LAG()、LEAD() 位移函数                            
                                
    LAG()    向前行偏移量，默认为1。 当找不到值时返回null                         
    LEAD()   向后行偏移量，默认为1。 当找不到值时返回null                         
    select user_name,user_address,                             
        LAG(user_address) over(order by user_name) as 'LAG',                            
        LEAD(user_address) over(order by user_name) as 'LEAD'                            
    from t_user;                            
                                
        user_name    user_address    LAG    LEAD            
        wang1    西安    « NULL »    北京            
        wang2    北京    西安    上海            
        wang3    上海    北京    广州            
        wang4    广州    上海    西安            
        wang5    西安    广州    « NULL »