MySQL中的窗口函数

窗口函数可以理解为记录集合，每条记录都要在窗口内执行函数，多行聚合为多行。MYSQL从8.0版本开始才支持窗口函数

ROW_NUMBER()      #分区中的当前行号

RANK()      #当前行在分区中的排名，含序号间隙

DENSE_RANK()      #当前行在分区中的排名，不含序号间隙

PERCENT_RANK()      #百分比等级值

CUME_DIST()      #累计分配值

FIRST_VALUE()      #窗口中的第一个行参数值

LAST_VALUE()      #窗口中的最后一行函数值

LAG()      #分区中指定行落后于当前行的参数值

LEAD()      #分区中领先当前行的参数值

NTH_VALUE()      #从第N行窗口框架的参数值

NTILE(N)      #分区中当前的桶号

 1. row_number()

SELECT 
  * 
FROM
  (SELECT 
    row_number () OVER (
      PARTITION BY user_no 
  ORDER BY create_date DESC
  ) AS row_num,
  order_id,
  user_no,
  amount,
  create_date 
  FROM
    order_tab) t 
WHERE row_num = 1 

SELECT 
  * 
FROM
  (SELECT 
    row_number () over w AS row_num,
    order_id,
    user_no,
    amount,
    create_date 
  FROM
    order_tab window w AS (
      PARTITION BY user_no 
  ORDER BY create_date DESC
  )) t 
WHERE row_num = 1 

 

 

 

SELECT 
  * 
FROM
  (SELECT 
    order_id,
    user_no,
    amount,
    AVG(amount) over w AS avg_num,
    create_date 
  FROM
    order_tab window w AS (
      PARTITION BY user_no 
  ORDER BY create_date DESC ROWS BETWEEN 1 preceding 
    AND 1 following
  )) t 

 

 2. RANK() 

SELECT * FROM
(
    SELECT row_number() over(PARTITION BY user_no ORDER BY amount DESC ) AS row_num1,
                 rank() over(PARTITION BY user_no ORDER BY amount DESC ) AS row_num2,
           dense_rank() over(PARTITION BY user_no ORDER BY amount DESC ) AS row_num3,
           order_id,user_no,amount,create_date
           FROM order_tab
) t

 

 3. DENSE_RANK()

SELECT * FROM (
    SELECT rank() over w AS row_num,
    percent_rank() over w AS percent,
    order_id,user_no,amount,create_date
    FROM order_tab
    window w AS (PARTITION BY user_no ORDER BY amount DESC)

) t

 

 

 

 

 

 

 4. NTILE()

SELECT * FROM 
(
    SELECT ntile(3) over w AS nf,
    order_id,user_no,amount,create_date
    FROM order_tab
    window w AS (PARTITION BY user_no ORDER BY amount DESC)
) t

 

 5. NTH_VALUE() 

SELECT * FROM 
(
    SELECT ntile(3) over w AS nf,
    nth_value(order_id,3) over w AS nth,
    order_id,user_no,amount,create_date
    FROM order_tab
    window w AS (PARTITION BY user_no ORDER BY amount DESC)
) t

 

 6. 聚合函数作为窗口函数

SELECT 
order_id,user_no,amount,create_date,
SUM(amount) over w AS sum1,
AVG(amount) over w AS avg1,
MAX(amount) over w AS max1,
MIN(amount) over w AS min1,
COUNT(amount) over w AS count1
FROM 
order_tab
window w AS (PARTITION BY user_no ORDER BY order_id)