窗口函数

窗口函数也称为OLAP函数，全称Online Analytical Processing，意为对数据库进行实时分析处理 。

• 窗口函数的语法
  语法：

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

其中重要的关键字是PARTITION和ORDER BY，理解这两个关键词的作用是帮助理解窗口函数的关键。

能够作为窗口函数使用的函数
窗口函数大致可以分为以下两种：
1.能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）；
2.RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数；

• 语法的基本使用方法——使用RANK函数
  RANK函数如其名，是用来计算记录排序的函数。

--各类商品按照销售单价从低到高排序
SELECT shop_name,shop_type,sell_price,
RANK () OVER (PARTITION BY shop_type ORDER BY sell_price) AS ranking
FROM shop_list;
+-----------+-----------+------------+---------+
| shop_name | shop_type | sell_price | ranking |
+-----------+-----------+------------+---------+
| 砧板      | 厨房用具  |       2000 |       1 |
| 菜刀      | 厨房用具  |       6000 |       2 |
| 打孔器    | 工具      |        600 |       1 |
| 扳手      | 工具      |       3000 |       2 |
| T恤衫     | 衣服      |       2000 |       1 |
| 运动T恤   | 衣服      |       3000 |       2 |
+-----------+-----------+------------+---------+

PARTITION BY能够设定排序的对象范围，示例中我们按照商品的种类进行排序。
ORDER BY可以指定按照哪一列、哪一种顺序进行排列。为了按照销售单价的升序进行排列，我们指定了sell_price。
窗口函数中的ORDER BY可以通过在末尾加上关键字ASC/DESC来指定升序和降序。省略该关键字默认为升序。

• 无须指定PARTITION BY
  使用窗口函数起到关键作用的是PARTITION BY和ORDER BY。其中，PARTITION BY不是必须的，不指定也是可以正常使用窗口的。

--不使用PARTITION BY指定列的情况
 SELECT shop_name,shop_type,sell_price,
 RANK () OVER (ORDER BY sell_price) AS ranking
 FROM shop_list;
+-----------+-----------+------------+---------+
| shop_name | shop_type | sell_price | ranking |
+-----------+-----------+------------+---------+
| 打孔器    | 工具      |        600 |       1 |
| T恤衫     | 衣服      |       2000 |       2 |
| 砧板      | 厨房用具  |       2000 |       2 |
| 运动T恤   | 衣服      |       3000 |       4 |
| 扳手      | 工具      |       3000 |       4 |
| 菜刀      | 厨房用具  |       6000 |       6 |
+-----------+-----------+------------+---------+

不使用PARTITION BY指定列，变成了全部商品的排序。

• 专用窗口函数的种类
  如上面排序结果有两个第2位，因此下一位直接跳过第3位变成第4位。有些特殊情况下，可能需要1，2，3，4这种不需要跳过的排序，这时就需要RANK之外的函数来实现了。
  RANK函数：计算排序时，如果存在相同的位次的记录，则会跳过位次。
  DENSE_RANK函数：计算排序时，即使存在相同位次的记录，也不会跳过相同的位次。
  ROW_NUMBER函数：赋予唯一的连续位次。

--比较三种函数的区别
SELECT shop_name,shop_type,sell_price,
RANK () OVER (ORDER BY sell_price) AS ranking,
DENSE_RANK () OVER (ORDER BY sell_price) AS dense_ranking,
ROW_NUMBER () OVER (ORDER BY sell_price) AS row_num
FROM shop_list;
+-----------+-----------+------------+---------+---------------+---------+
| shop_name | shop_type | sell_price | ranking | dense_ranking | row_num |
+-----------+-----------+------------+---------+---------------+---------+
| 打孔器    | 工具      |        600 |       1 |             1 |       1 |
| T恤衫     | 衣服      |       2000 |       2 |             2 |       2 |
| 砧板      | 厨房用具  |       2000 |       2 |             2 |       3 |
| 运动T恤   | 衣服      |       3000 |       4 |             3 |       4 |
| 扳手      | 工具      |       3000 |       4 |             3 |       5 |
| 菜刀      | 厨房用具  |       6000 |       6 |             4 |       6 |
+-----------+-----------+------------+---------+---------------+---------+

• 窗口函数的适用范围
  窗口函数只能在SELECT子句中使用，另外，这类函数不能在WHERE子句或者GROUP BY子句中使用。
  因为窗口函数的目的是排序，当我们使用WHERE去除了某些记录或者使用GROUP BY对记录进行聚合处理，排序的结果就是错误的，因此没有任何意义。
  所以，SELECT之外使用窗口函数是没有意义的。

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

--将SUM函数作为窗口函数
SELECT shop_id,shop_name,sell_price,
SUM(sell_price) OVER (ORDER BY shop_id)AS current_sum
FROM shop_list;
+---------+-----------+------------+-------------+
| shop_id | shop_name | sell_price | current_sum |
+---------+-----------+------------+-------------+
| 0001    | T恤衫     |       2000 | 2000        |
| 0002    | 打孔器    |        600 | 2600        |
| 0003    | 运动T恤   |       3000 | 5600        |
| 0004    | 菜刀      |       6000 | 11600       |
| 0005    | 砧板      |       2000 | 13600       |
| 0006    | 扳手      |       3000 | 16600       |
+---------+-----------+------------+-------------+

• 计算移动平均
  窗口函数就是将表以窗口位单位进行分割，并在其中进行排序的函数。其实其中还包括在窗口中指定更加详细的统计范围的备选功能。该备选功能中的统计范围称为"框架"。

--指定"最靠近的3行"作为统计对象
SELECT shop_id,shop_name,sell_price,
    -> AVG(sell_price) OVER (ORDER BY shop_id ROWS 2 PRECEDING)AS moving_avg
    -> FROM shop_list;
+---------+-----------+------------+------------+
| shop_id | shop_name | sell_price | moving_avg |
+---------+-----------+------------+------------+
| 0001    | T恤衫     |       2000 | 2000.0000  |
| 0002    | 打孔器    |        600 | 1300.0000  |
| 0003    | 运动T恤   |       3000 | 1866.6667  |
| 0004    | 菜刀      |       6000 | 3200.0000  |
| 0005    | 砧板      |       2000 | 3666.6667  |
| 0006    | 扳手      |       3000 | 3666.6667  |
+---------+-----------+------------+------------+

从编号0003的数据开始数据有所不同，我们制定了框架——指定"最靠近的3行"作为统计对象，这里使用了ROWS（行）和PRECEDING(之前）两个关键字，将框架指定为"截止到之前~行"，因此ROWS 2 PRECEDING的意思就是截止到之前2行。
由于框架是根据当前记录决定的，所有和固定的窗口不一样，其范围会随着当前的记录的变化而变化。
自身（当前记录）→之前1行的记录→之前2行的记录
这样的统计方法称为移动平均。由于这种方法在希望实时把握最近状态时非常方便，所以常常会应用在对股市趋势的实时跟踪中。
使用关键字FOLLOWING(之后）替换PRECEDING，就可以指定"截至到之后~行"作为框架了。

• 两个ORDER BY
  窗口函数中的ORDER BY只是对排序的序号进行了排列，那么如何对排序做降序呢？
  很简单，在末尾加上ORDER BY子句就行了。

 SELECT shop_id,shop_name,sell_price,
 RANK() OVER (ORDER BY sell_price)AS ranking
 FROM shop_list
 ORDER BY ranking DESC;
+---------+-----------+------------+---------+
| shop_id | shop_name | sell_price | ranking |
+---------+-----------+------------+---------+
| 0004    | 菜刀      |       6000 |       6 |
| 0003    | 运动T恤   |       3000 |       4 |
| 0006    | 扳手      |       3000 |       4 |
| 0001    | T恤衫     |       2000 |       2 |
| 0005    | 砧板      |       2000 |       2 |
| 0002    | 打孔器    |        600 |       1 |
+---------+-----------+------------+---------+

GROUPING运算符

• 同时计算出合计值
  我们在使用GROUP BY子句合计各个种类的总价的时候，会得到如下的表：

+-----------+-----------------+
| shop_type | sum(sell_price) |
+-----------+-----------------+
| 衣服      | 5000            |
| 工具      | 3600            |
| 厨房用具  | 8000            |
+-----------+-----------------+

但是如果想要将这几个种类合计到一起，那么就需要UNION ALL将他们连接到一起了。

SELECT '合计' AS shop_type,SUM(sell_price)
FROM shop_list
UNION ALL
SELECT shop_type,SUM(sell_price)
FROM shop_list
GROUP BY shop_type;
+-----------+-----------------+
| shop_type | sum(sell_price) |
+-----------+-----------------+
| 合计      | 16600           |
| 衣服      | 5000            |
| 工具      | 3600            |
| 厨房用具  | 8000            |
+-----------+-----------------+

这样虽然能计算出我们想要的结果，但是需要执行两次相同的SELECT语句，再将其结果连接，不仅繁琐，而且DBMS内部处理成本也非常高。因此，我们需要试着找出其他更高效的方法。

• ROLLUP——同时计算出合计值和小计值
  为了满足类似上面的要求，标准SQL引入了GROUPING运算符，GROUPING运算符主要包括以下几种：
  1.ROLLUP
  2.CUBE
  3.GROUPING SETS

ROLLUP的用法

SELECT shop_type,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type WITH ROLLUP;
+-----------+-----------+
| shop_type | sum_price |
+-----------+-----------+
| 厨房用具  | 8000      |
| 工具      | 3600      |
| 衣服      | 5000      |
| NULL      | 16600     |
+-----------+-----------+

该运算符的作用，就是"一次计算出不同聚合键的组合结果"，其计算结果默认使用NULL作为聚合键。

将"登记日期"添加到聚合键中

--不使用ROLLUP的结果
SELECT shop_type,register_date,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
| 衣服      | 2009-09-20    | 5000      |
| 工具      | 2009-09-20    | 3600      |
| 厨房用具  | 2009-09-20    | 8000      |
+-----------+---------------+-----------+

--使用ROLLUP的结果
SELECT shop_type,register_date,SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
| 厨房用具  | 2009-09-20    | 8000      |
| 厨房用具  | NULL          | 8000      |    ←小计
| 工具      | 2009-09-20    | 3600      |
| 工具      | NULL          | 3600      |
| 衣服      | 2009-09-20    | 5000      |
| 衣服      | NULL          | 5000      |
| NULL      | NULL          | 16600     | ←合计
+-----------+---------------+-----------+

将两次结果做比较之后，发现使用ROLLUP多出了对每个类别的小计（使用了未登记日期作为聚合键）。

• GROUPING函数——让NULL更加容易分辨
  在使用ROLLUP的时候，出现了几处NULL，当日期本身为NULL时，那么NULL代表的意义就不太明确了。为了避免混淆，SQL提供了一个用来判断超级分组记录为NULL的特定函数——GROUPING函数。该函数在其参数列的值为超级分组记录所产生的NULL时返回1，其他情况返回0。

 SELECT GROUPING(shop_type)AS shop_type,GROUPING(register_date)AS  register_date,SUM(sell_price)AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+-----------+---------------+-----------+
| shop_type | register_date | sum_price |
+-----------+---------------+-----------+
|         0 |             0 | 8000      |
|         0 |             1 | 8000      |
|         0 |             0 | 3600      |
|         0 |             1 | 3600      |
|         0 |             0 | 5000      |
|         0 |             1 | 5000      |
|         1 |             1 | 16600     |   ←超级分组产生的NULL返回1
+-----------+---------------+-----------+

该表对应上面ROLLUP产生的表，查找NULL的位置就能知道是不是超级分组产生的了。

使用GROUPING函数还能在超级分组记录的键中插入字符串。也就是说，当GROUPING函数返回值为1时，指定”合计“和”小计“等字符串，其他情况返回正常的列的值。

SELECT CASE WHEN GROUPING(shop_type)=1
            THEN '商品种类合计'
            ELSE shop_type END AS shop_type,
            CASE WHEN GROUPING(register_date)=1
            THEN '登记日期合计'
            ELSE register_date END AS register_date,
            SUM(sell_price) AS sum_price
FROM shop_list
GROUP BY shop_type,register_date WITH ROLLUP;
+--------------+---------------+-----------+
| shop_type    | register_date | sum_price |
+--------------+---------------+-----------+
| 厨房用具     | 2009-09-20     | 8000      |
| 厨房用具     | 登记日期合计    | 8000      |
| 工具         | 2009-09-20    | 3600      |
| 工具         | 登记日期合计   | 3600      |
| 衣服         | 2009-09-20    | 5000      |
| 衣服         | 登记日期合计   | 5000      |
| 商品种类合计 | 登记日期合计    | 16600     |
+--------------+---------------+-----------+

• CUBE——用数据搭积木
  CUBE是立方体的意思，和ROLLUP一样，生动地说明了其作用。CUBE和ROLLUP的语法一样，只要将ROLLUP用CUBE代替就行了。

• GROUPING SETS——取得期望的积木
  该运算符可以从ROLLUP和CUBE结果中取得部分记录（即GROUPING返回值为1的数据）。