SQL学习笔记

1.窗口函数

相关练习题：
178. 分数排名
184. 部门工资最高的员工
185. 部门工资前三高的所有员工

1.1 基本语法：

<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
Remak: []中的部分可以省略

1.2 能够作为窗口函数使用的函数

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

示例数据：

1.3 语法的基本使用方法——使用RANK函数

根据不同的商品种类，按照销售单价从低到高的顺序创建排序表：

SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
 FROM Product;

• PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了 product_type。
• ORDER BY 能够指定按照哪一列、何种顺序进行排序。

执行效果：

1.4 无需指定PARTITION BY

使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。

SELECT product_name, product_type, sale_price, 
RANK () OVER (ORDER BY sale_price) AS ranking
 FROM Product;

执行结果：

1.5 专用窗口函数的种类

● RANK函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
● DENSE_RANK函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
● 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;

执行结果：

1.6 窗口函数的适用范围

目前为止我们学过的函数大部分都没有使用位置的限制，最多也就是在 WHERE 子句中使用聚合函数时会有些注意事项。但是，使用窗口函数的位置却有非常大的限制。更确切地说，窗口函数只能书写在一个特定的位置。这个位置就是 SELECT 子句之中。反过来说，就是这类函数不能在WHERE 子句或者 GROUP BY 子句中使用。


其理由就是，在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。大家仔细想一想就会明白，在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的。在得到排序结果之后，如果通过 WHERE 子句中的条件除去了某些记录，或者使用GROUP BY 子句进行了汇总处理，那好不容易得到的排序结果也无法使用了。

1.7 作为窗口函数使用的聚合函数

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。

SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
 FROM Product;

得到的并不仅仅是合计值，而是按照 ORDER BY 子句指定的 product_id 的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。

1.7 计算移动平均

指定“最靠近的3行”作为汇总对象

SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
 ROWS 2 PRECEDING) AS moving_avg
 FROM Product;

这里我们使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。
● 自身（当前记录）
● 之前 1行的记录
● 之前 2行的记录
也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指定“截止到之后 ~ 行”作为框架了（图 8-3）。


如果希望将当前记录的前后行作为汇总对象时，就可以像代码清单 8-7那样，同时使用PRECEDING（“之前”）和 FOLLOWING（“之后”）关键字来实现。

SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
 ROWS BETWEEN 1 PRECEDING AND 
 1 FOLLOWING) AS moving_avg
 FROM Product;

1.8 两个Order By

OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。在 SELECT 语句的最后，使用 ORDER BY子句进行指定（代码清单 8-9）。这样就能保证 SELECT 语句的结果中记录的排列顺序了，除此之外也没有其他办法了。

SELECT product_name, product_type, sale_price, 
RANK () OVER (ORDER BY sale_price) AS ranking
 FROM Product
 ORDER BY ranking;