窗口函数

窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。
理解PARTITION BY和ORDER BY这两个关键字的含义十分重要。

什么是窗口函数

窗口函数也称为OLAP函数。为了让大家快速形成直观印象，才起 了这样一个容易理解的名称(“窗口”的含义我们将在随后进行说明)。
OLAP是OnLine Analytical Processing的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。
窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能。

窗口函数的语法

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

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

1. 能够作为窗口函数的聚合函数
   • SUM
   • AVG
   • COUNT
   • MAX
   • MIN
2. 专用窗口函数
   • RANK

   计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
   e.g. 有三条记录排在第1位时：1位、1位、1位、4位......
   

   • DENSE_RANK

   计算排序时，即使存在相同位次的记录，也不会跳过之后的位次。
   e.g. 有三条记录排在第1位时：1位、1位、1位、2位......
   

   • ROW_NUMBER

   赋予唯一的连续位次。
   e.g. 有三条记录排在第1位时：1位、2位、3位、4位... (不知道这么解释OK不OK，毕竟ROW_NUMBER很明显就是行号的意思）
   

e.g.

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

执行结果 PARTITION BY和ORDER BY的作用

上面代码中的PARTITION BY也不是必须的。

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

执行结果

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;

执行结果

窗口函数的适用范围

原则上，窗口函数只能在SELECT字句中使用。反过来说，这类函数是不能在WHERE字句或者GROUP BY字句中使用的。
在DBMS内部，窗口函数是对WHERE字句或者GROUP BY字句处理后的“结果”进行的操作。很明显，在得到用户想要的结果之前，即使进行了排序，那么结果也是错误的。在得到排序结果之后，如果通过WHERE字句中的条件去除了某些记录，或者使用GROUP BY字句进行了汇总处理，那么好不容易得到的排序结果也无法使用了。
而ORDER BY子句中能够使用窗口函数的原因（UPDATE的SET子句中也能够使用窗口函数）就是因为ORDER BY子句会在SELECT子句之后执行，并且记录也不会减少。

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

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

执行结果

SELECT
    product_id,
    product_name,
    sale_price,
    AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
FROM
    Product;

执行结果

很明显，如果我们是ORDER BY某一个日期的话，那么我们可以很容易的使用聚合函数配合OVER来得到一个统计趋势图。
像这样，以“当前记录”（即自身记录）作为基准进行统计，就是讲聚合函数当做窗口函数使用时的最大特征。

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口指定更加详细的汇总范围的备选功能。该备选功能中的汇总范围称为框架。

指定“最靠近的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 2 PRECEDING的意思就是“截止到之前2行”。也就是将作为汇总对象的记录限定为如下的“最靠近的3行”。

将框架指定为截止到当前记录之前2行（最靠近的3行）
这样的统计方法成为移动平均（moving average)。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪中。
使用关键字FOLLOWING(“之后”）替换PRECEDING，就可以指定“截止到之后~行”作为框架了。
将框架指定为截止到当前记录之后2行（最靠近的3行）

将当前记录的前后行作为汇总对象

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;

两个ORDER BY

在使用窗口函数时，必须要在OVER子句中使用ORDER BY。OVER子句中的ORDER BY只是用来决定窗口函数按照什么样的顺序进行计算，对结果的排序顺序并没有影响。

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

也许一条语句两个ORDER BY有点别扭，但它们作用是不同的。