Day5:高级查询

窗口函数

<窗口函数> OVER ([PARTITION BY <列名>]
                     ORDER BY <排序用列名>)  

搞懂PARTITION BY 和ORDER BY

PARTITON BY
是用来分组，即选择要看哪个窗口，类似于 GROUP BY 子句的分组功能，但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。

ORDER BY 
是用来排序，即决定窗口内，是按那种规则(字段)来排序的。

排序专用窗口函数

RANK

RANK函数计算排序时，如果存在相同位次的记录，则会跳过之后的位次。例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

DENSE_RANK

DENSE_RANK函数同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

ROW_NUMBER

ROW_NUMBER函数赋予唯一的连续位次。例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

SELECT  product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (ORDER BY sale_price) AS ranking
       ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
       ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product;  

结果展示：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HG76V1Fa-1656125300139)(en-resource://database/1246:1)]

聚合函数在窗口函数的使用

简单应用

SELECT  product_id
       ,product_name
       ,sale_price
       ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  
  FROM product;  

计算移动平均

• PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行
• FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行
• BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

注意事项

1. 原则上，窗口函数只能在SELECT子句中使用。
2. 窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

存储过程

[delimiter //]($$，可以是其他特殊字符）
CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] 
[BEGIN]
  routine_body
[END//]($$，可以是其他特殊字符）