8-1 窗口函数
窗口函数也称为OLAP函数。OLAP就是对数据库数据进行实时分析处理。
窗口函数的语法
<窗口函数> OVER ([PARTITION BY <列清单>]ORDER BY<排序列清单>)
能够作为窗口函数使用的函数
- 能够作为窗口函数使用的聚合函数(SUM AVG COUNT MAX MIN)
- RANK DENSE_RANK ROW_NUMBER等专用窗口函数
语法的基本使用方法——使用RANK函数
建表
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');
RANK是用来计算记录排序的函数。
对Product表中8件商品，根据商品种类(product_type)，按照销售单价(sale_price)从低到高排序。
-- 按照不同的商品种类，按照销售单价从低到高排序
mysql> -- Oracle, SQL Server, DB2, PostgreSQL(我使用的是mariadb)
SELECT product_name, product_type, sale_price,
RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking
FROM Product;
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
| 叉子 | 厨房用具 | 500 | 1 |
| 擦菜板 | 厨房用具 | 880 | 2 |
| 菜刀 | 厨房用具 | 3000 | 3 |
| 高压锅 | 厨房用具 | 6800 | 4 |
| T恤 | 衣服 | 1000 | 1 |
| 运动T恤 | 衣服 | 4000 | 2 |
+--------------+--------------+------------+---------+
PARTITION BY 能够设定排序的对象范围
ORDER BY 能够指定按照哪一列，何种数据进行排序。
窗口函数兼具GROUP BY 子句的分组功能以及ORDER BY子句的排序功能。但是PARTITION BY子句不具备GROUP BY子句的汇总功能。因此RANK函数不会减少原表中的记录的行数。
专用窗口函数的种类
RANK函数
计算排序时，如果有相同位次的记录，则会跳过之后的位次。例如 有3条记录排在第一位时：1位、1位、1位、4位……
DENSE_RANK
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。有3 条记录排在第1 位时：1 位、1 位、1 位、2 位……
ROW_NUMBER函数
赋予唯一的连续位次
有3 条记录排在第1 位时：1 位、2 位、3 位、4 位……
mysql> -- Oracle, SQL Server, DB2, PostgreSQL
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;
+--------------+--------------+------------+---------+---------------+---------+
| product_name | product_type | sale_price | ranking | dense_ranking | row_num |
+--------------+--------------+------------+---------+---------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 | 1 | 1 |
| 叉子 | 厨房用具 | 500 | 2 | 2 | 2 |
| 打孔器 | 办公用品 | 500 | 2 | 2 | 3 |
| 擦菜板 | 厨房用具 | 880 | 4 | 3 | 4 |
| T恤 | 衣服 | 1000 | 5 | 4 | 5 |
| 菜刀 | 厨房用具 | 3000 | 6 | 5 | 6 |
| 运动T恤 | 衣服 | 4000 | 7 | 6 | 7 |
| 高压锅 | 厨房用具 | 6800 | 8 | 7 | 8 |
+--------------+--------------+------------+---------+---------------+---------+
8 rows in set (0.06 sec)
窗口函数的适用范围
原则上窗口函数只能在SELECT子句中使用。
在DBMS内部，窗口函数是对WHERE子句或者GROUP BY子句处理后的结果进行的操作。在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的。在得到排序结果之后，如果通过WHERE子句中的条件除去了某些记录，或者使用了GROUP BY子句进行了汇总，那好不容易得到的排序结果也无法使用了。
**
作为窗口函数使用的聚合函数
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
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
指定框架(汇总范围)
上述汇总限定为了“最高近的3行”
使用了“row”和“PRECEDING”两个关键字，将框架指定为“截止到之前~行”，因此”ROW 2 PRECEDING”就是将框架指定为“截止到之前2行”。
使用关键字“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
使用窗口函数必须在OVER子句中使用ORDER BY。但是结果中的记录不会按照该ORDER BY指定的顺序进行排序。
OVER 子句中的ORDER BY只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序没没有什么影响。有些DBMS也可以按照窗口函数的ORDER BY子句所指定的顺序对结果进行排序，但这仅仅是个个例而已。
8-2 GROUPING运算符
只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想同时得到，可以使用GROUPING运算符。
同时得到合计行
GROUP BY子句是用来指定聚合键的场所。所以只会根据这里指定的键分割数据，当然不会出现合计行，而合计行是不指定聚合键时得到的汇总结果。
ROLLUP——同时得出合计和消极
标准SQL引入了GROUPING运算符，使用该运算符可以通过非常简单的SQL得出之前那样单位的不同汇总结果。
GROUPING运算符包含以下3种。
- ROLLUP
- CUBE
- GROUPING SETS
ROLLUP的使用方法——同时得出合计和小计
mysql> -- MySQL SELECT product_type, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type WITH ROLLUP; +--------------+-----------+ | product_type | sum_price | +--------------+-----------+ | 办公用品 | 600 | | 厨房用具 | 11180 | | 衣服 | 5000 | | NULL | 16780 | +--------------+-----------+
docker run --name nginx --net=host -p 8080:80 -d nginx
docker run --name nginx -p 8080:80 -d nginx
docker run -d --net="host" -p 8081:80 --name nginx1 nginx
# 重建docker网络
systemctl stop docker
yum install -y bridge-utils
pkill docker
iptables -t nat -F
ifconfig docker0 down
brctl delbr docker0
docker -d
systemctl restart docker
将“登记日期”添加到聚合建当中
mysql> SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type, regist_date;
+--------------+-------------+-----------+
| product_type | regist_date | sum_price |
+--------------+-------------+-----------+
| 办公用品 | 2009-09-11 | 500 |
| 办公用品 | 2009-11-11 | 100 |
| 厨房用具 | 2008-04-28 | 880 |
| 厨房用具 | 2009-01-15 | 6800 |
| 厨房用具 | 2009-09-20 | 3500 |
| 衣服 | NULL | 4000 |
| 衣服 | 2009-09-20 | 1000 |
+--------------+-------------+-----------+
7 rows in set (0.04 sec)
mysql> SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type, regist_date WITH ROLLUP;
+--------------+-------------+-----------+
| product_type | regist_date | sum_price |
+--------------+-------------+-----------+
| 办公用品 | 2009-09-11 | 500 |
| 办公用品 | 2009-11-11 | 100 |
| 办公用品 | NULL | 600 |
| 厨房用具 | 2008-04-28 | 880 |
| 厨房用具 | 2009-01-15 | 6800 |
| 厨房用具 | 2009-09-20 | 3500 |
| 厨房用具 | NULL | 11180 |
| 衣服 | NULL | 4000 |
| 衣服 | 2009-09-20 | 1000 |
| 衣服 | NULL | 5000 |
| NULL | NULL | 16780 |
+--------------+-------------+-----------+
11 rows in set (0.06 sec)
-- oracle多列的用法
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
GROUPING函数——让NULL更加容易分辨
使用ROLLUP得到的结果合计为NULL。SQL提供了一个用来判断超级分组记录的NULL的特定函数——GROUPING函数。该函数在其参数列的值为超级分组记录所产生的NULL时返回1，其他情况返回0。
若有收获，就点个赞吧
0 人点赞
