MySQL入门：SQL高级处理

窗口函数

概念

窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。

为了便于理解，称之为窗口函数。常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

基本用法

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

PARTITON BY是用来分组，即选择要看哪个窗口，ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。
先新建一张表，以便后续进行样例展示

USE shop;
CREATE TABLE product (
product_id CHAR ( 4 ) NOT NULL,
product_name VARCHAR ( 100 ) NOT NULL,
product_type VARCHAR ( 32 ) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY ( product_id ) 
);
INSERT INTO product
VALUES
	( '0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20' );
INSERT INTO product
VALUES
	( '0002', '打孔器', '办公用品', 500, 320, '2009-09-11' );
INSERT INTO product
VALUES
	( '0003', '运动T恤', '衣服', 4000, 2800, NULL );
INSERT INTO product
VALUES
	( '0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20' );
INSERT INTO product
VALUES
	( '0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15' );
INSERT INTO product
VALUES
	( '0006', '叉子', '厨房用具', 500, NULL, '2009-09-20' );
INSERT INTO product
VALUES
	( '0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28' );
INSERT INTO product
VALUES
	( '0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11' );

比较以下两个查询：

SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	purchase_price 
FROM
	product 
WHERE
	sale_price < 800 
	UNION 
SELECT 
	product_id, 
	product_name, 
	product_type, 
	sale_price, 
	purchase_price 
FROM 
	product 
WHERE 
	sale_price > 1.5 * purchase_price;

举个例子：

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

得到的结果是：

窗口函数种类

大致来说，窗口函数可以分为两类。

一是 将SUM、MAX、MIN等聚合函数用在窗口函数中

二是 RANK、DENSE_RANK等排序用的专用窗口函数

专用窗口函数

RANK函数；
DENSE_RANK函数；
ROW_NUMBER函数。
通过以下查询和结果，可以发现三者的不同：

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
	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;

结果：

窗口函数的应用：计算移动平均

基本语法：

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

PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行

FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行

BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”
例：

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

结果：

GROUPING运算符

ROLLUP——计算合计与小计

常规的GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP关键字。
例：

SELECT
	product_type,
	regist_date,
	SUM( sale_price ) AS sum_price 
FROM
	product 
GROUP BY
	product_type,
	regist_date WITH ROLLUP;

结果：