SQL基础学习8

窗口函数

何为窗口函数：也称为OLAP（OnLine Analytical Processing,意为对数据库数据进行实时分析处理）函数，是将表以窗口为单位进行分割，并在其中进行排序的函数（P.S：此处的窗口代表范围）

窗口函数的语法：

窗口函数 OVER (PARTITION BY 列清单
ORDER BY 排序用列清单)

解释：PARTITION BY能够 设定排序的对象范围；ORDER BY能够 指定按照哪一列、何种顺序进行排序，可以通过关键字ASC/DESC来指定升序和降序；

能够作为窗口函数使用的函数：聚合函数（SUM、AVG、COUNT、MAX、MIN）；专用窗口函数，如RANK、DENSE_RANK、ROW_NUMBER等

语法的基本使用方法——使用RANK函数：用于计算记录排序的函数

SELECT name,price,
RANK() VOER (PARTITION BY name
ORDER BY price) AS ranking
FROM Shop;

解释：以 name为窗口进行分组，对每一组按price升序进行排序；将上述程序 省略PARTITION BY时，得到的结果就是 对所有的商品按price进行升序排序；由于专 用窗口函数无需参数，所以通常括号中都是空的

专用窗口函数的种类：

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

2.DENSE_RANK函数：计算排序时，如果存在相同位次的记录，则不会跳过之后的位次。如有2条记录排在第1位时，1位、1位、2位 ......

3.ROW_NUMBER函数：计算排序时，赋予唯一的连续位次。如有2条记录排在第1位时，1位、2位、3位 ......

窗口函数的适用范围：窗口函数只能书写在SELECT子句之中，不能在WHERE子句或者GROUP子句中使用

作为窗口函数使用的聚合函数：所有的聚合函数都能用作窗口函数，其语法与专用窗口函数完全相同

SELECT name,price,
SUM(price) OVER (ORDER BY price) AS sum_price
FROM Shop;

解释：按照price进行升序排列，并计算出截止当前行的价格和；使用聚合函数作为窗口函数时，需要在括号内指定作为聚合对象的列

将聚合函数用作窗口函数使用时的最大特征：以“自身积累（当前记录）”作为基准，统计对象只是“排在自已之上”的记录进行统计

计算移动平均：在窗口中指定更加详细的统计范围的备选功能（该备选功能中的统计范围称为“框架”）

SELECT name,price
AVG (price) VOER (ORDER BY price
ROWS 2 PRECEDING) AS moving_avg
FROM Shop;

解释：使用 ROWS（行）和 PRECEDING（之前）两个关键字将框架指定为“截止到之前~行”，如上述程序的“ROWS 2 PRECEDING”表示截止到之前的前两行，即统计的范围为“自身（当前记录）、之前1行的记录、之前2行的记录”

将PRECEDING替换为FOLLOWING（之后），就可以指定“截止到之后~行”:：

SELECT name,price
AVG (price) VOER (ORDER BY price
ROWS 2 FOLLOWING) AS moving_avg
FROM Shop;

如果希望将当前记录的前后行作为统计对象时，可以同时使用PRECEDING和FOLLOWING关键字来实现

SELECT name,price
AVG (price) VOER (ORDER BY price
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM Shop;

解释：将之前1行和之后1行的区间作为统计对象，即统计的范围为“之前1行的记录、自身（当前记录）、之后1行的记录”

使用窗口函数是关于结果形式的注意点：OVER子句中的ORDER BY只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排序顺序并没有影响。想要保证结果的有序性，只需在SELECT语句的最后使用ORDER BY子句进行指定。