mysql 窗口函数 求和,SQL进阶之窗口函数

窗口函数

What's 窗口函数？

窗口函数也称为OLAP(OnLine Analytical Processing)函数，目前MySQL还不支持。

窗口函数的语法

OVER ([PARTITION BY ] ORDER BY )

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

能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN)

RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数

语法的基本使用方法-使用RANK函数

-- 使用PARTITION BY进行分组

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

窗口函数兼具GROUP BY的分组功能及ORDER BY的排序功能，通过PARTITION BY分组后的记录集合称为窗口

无需指定PARTITION BY

-- 将全部数据作为一个组

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

专用窗口函数的分类

RANK函数

计算排序时存在相同的位次，则会跳过之后的位次(1/1/1/4)

DENSE_RANK函数

计算排序时存在相同位次的记录，也不跳过之后的位次(1/1/1/2)

ROW_NUMBER函数

赋予唯一的连续位次(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;

专用窗口函数无需参数，因此通常括号中都是空的

窗口函数的适用范围

窗口函数只能写在SELECT子句的位置，原因在于：窗口函数是对WHERE过滤和GROUP BY分组之后的结果进行的操作，所以在SELECT子句以外的位置"使用窗口函数"是没有意义的。

作为窗口函数的聚合函数

-- 求累计和

SELECT product_id,product_name,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;

计算移动平均

-- moving average

SELECT product_id,product_name,sale_price,

AVG(sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg

FROM Product;

指定框架：

ROWS+PROCEDING两个关键字，将框架指定为截止到之前~行

FOLLOWING关键词可以指定截止到之后~行。

同时使用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;

两个ORDER BY

-- 窗口函数中的ORDER BY并不能保证最终结果的排序顺序，下图纯属巧合

SELECT product_name,product_type,sale_price,

RANK() OVER (ORDER BY sale_price) AS ranking

FROM Product;

-- 在SELECT后使用ORDER BY子句可以对结果进行排序

SELECT product_name,product_type,sale_price,

RANK() OVER (ORDER BY sale_price) AS ranking

FROM Product ORDER BY ranking;

GROUPING运算符

why? 只使用GROUP BY子句和聚合函数是无法同时得到小计和合计的。要解决这一问题，得使用GROUPING运算符。

理解GROUPING运算符中CUBE的关键在于形成"积木搭建出的立方体"的印象。

虽然GROUPING运算符是标准SQL的功能，但部分DBMS尚未支持这一功能。

同时得到合计行

-- 不是用GROUPING如何实现? union 缺点：无法保证总计行的顺序

SELECT '总计' AS product_type,SUM(sale_price) FROM Product

UNION

SELECT product_type,SUM(sale_price) FROM Product GROUP BY product_type;

ROLLUP-同时得出合计和小计

GROUPING运算符包含：

ROLLUP

CUBE

GROUPING SETS

-- Oracle/SQL Sever/DB2/PostgreSQL

SELECT product_type,SUM(sale_price) AS sum_price

FROM Product

GROUP BY ROLLUP(product_type);

-- MySQL

SELECT product_type,SUM(sale_price) AS sum_price

FROM Product

GROUP BY product_type WITH ROLLUP;

SELECT product_type,regist_date,SUM(sale_price) AS sum_price

FROM Product

GROUP BY product_type,regist_date;

-- 同时得到ROLLUP的总计和product_type中个值的小计

-- Oracle/SQL Sever/DB2/PostgreSQL

SELECT product_type,regist_date,SUM(sale_price) AS sum_price

FROM Product

GROUP BY ROLLUP(product_type,regist_date);

-- MySQL

SELECT product_type,regist_date,SUM(sale_price) AS sum_price

FROM Product

GROUP BY product_type,regist_date WITH ROLLUP;

GROUPING函数-让NULL更容易分辨

-- Oracle/SQL Sever/DB2/PostgreSQL

SELECT GROUPING(product_type) AS product_type,

GROUPING(regist_date) AS regist_date,SUM(sale_price) AS sum_price

FROM Product

GROUP BY ROLLUP(product_type,regist_date);

-- 注意碰到超级分组记录中的NULL时返回1，原始数据为NULL时返回0，这是分辨超级分组记录中的NULL和原始数据中的NULL的方法

-- Oracle/SQL Sever/DB2/PostgreSQL

SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_price,

COUNT(regist_date)

FROM Product

GROUP BY ROLLUP(product_type,regist_date);

CUBE-用数据搭积木

-- Oracle/SQL Sever/DB2/PostgreSQL

SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_price

FROM Product

GROUP BY CUBE(product_type,regist_date);

GROUPING SETS - 取得期望的积木

-- Oracle/SQL Sever/DB2/PostgreSQL

SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类 合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期 合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_price

FROM Product

GROUP BY GROUPING SETS (product_type,regist_date);

标签：product,窗口,进阶,price,regist,sale,SQL,type,SELECT

来源： https://www.cnblogs.com/evian-jeff/p/11478155.html