本文主要是阅读《SQL 基础教程》这本书总结而来,有需求的话可以阅读原著。
1. 窗口函数
- 窗口函数可以进行排序、生成序列号等一般的集合函数无法实现的高级操作
- 需要深入理解
partiton
和order by
两个关键字的含义 - 窗口函数意为数据库数据进行实时分析处理
1.1 窗口函数的基础知识
- 语法格式
<窗口函数> over ([partiton by <列清单>]) order by <排序用列清单>)
- 能够作为窗口函数使用的函数
- 聚合类函数
sum
、avg
、count
、max
、min
等- 专用窗口函数
rank
、dense_rank
、row_number
等
1.2 rank
函数
注意事项
- 用来计算记录排序的函数
- 窗口函数兼具分组和排序两种功能
partiton by
能够设定排序的对象范围order by
能够指定按照那一列、何种顺序进行排序
指定
partiton by
# Oracle、DB2、PostgreSQL
# 将各种商品按照销售单价从低到高的顺序创建排序表
mysql> select shohin_mei, shohin_bunrui, hanbai_tanka,
-> rank () over (partiton by shohin_bunrui
-> order by hanbai_tanka) as ranking
-> from Shohin;
- 无需指定
partiton by
# Oracle、DB2、PostgreSQL
# 只是对于hanbai_tanka进行从低到高的排序
mysql> select shohin_mei, shohin_bunrui, hanbai_tanka,
-> rank () over (order by hanbai_tanka) as ranking
-> from Shohin;
1.3 专用的窗口函数的种类
rank
- 计算排序时,如果存在相同位次的记录,则会跳过之后的位次
- 如:1,1,1,4 …
dense_rank
- 计算排序时,及时存在相同位次的记录,也不会跳过之后的位次
- 如:1,1,1,2 …
row_number
- 赋予唯一的连续位次
- 如:1,2,3,4 …
- 专用窗口函数无需参数,通常括号中都是空的
- 原则上窗口函数只能在
select
子句中使用
1.4 作为窗口函数使用的聚合函数
# Oracle、DB2、PostgreSQL
mysql> select shohin_id, shohin_mei, hanbai_tanka,
-> avg(hanbai_tanka) over (order by shohin_id) as current_avg
-> from Shohin;
2. grouping
运算符
- 只使用
group by
子句和聚合函数无法同时计算小计或者合计值 - 如果想要一次得到这两个值可以使用
grouping
运算符
2.1 同时计算出合计值
- 实例演示
# 使用`group by`无法得到合计行
mysql> select shohin_bunrui, sum(hanbai_tanka)
-> from Shohin
-> group by shohin_bunrui;
+---------------+-------------------+
| shohin_bunrui | sum(hanbai_tanka) |
+---------------+-------------------+
| 衣服 | 5000 |
| 办公用品 | 695 |
| 厨房用具 | 11180 |
+---------------+-------------------+
3 rows in set (0.00 sec)
- 优化优化
# 分别计算出合计行和聚合结果再通过union all进行连接
mysql> select '合计' as shohin_bunrui, sum(hanbai_tanka)
-> from Shohin
-> union all
-> select shohin_bunrui, sum(hanbai_tanka)
-> from Shohin
-> group by shohin_bunrui;
+---------------+-------------------+
| shohin_bunrui | sum(hanbai_tanka) |
+---------------+-------------------+
| 合计 | 16875 |
| 衣服 | 5000 |
| 办公用品 | 695 |
| 厨房用具 | 11180 |
+---------------+-------------------+
4 rows in set (0.00 sec)
2.2 同时计算出合计值和小计值
rollup
cube
grouping sets
- 后面的两个自己看吧,不做介绍了
2.3 rollup 使用方式
# 算出合计行
# MySQL
mysql> select shohin_bunrui, sum(hanbai_tanka) as sum_tanka
-> from Shohin
-> group by shohin_bunrui with rollup;
+---------------+-----------+
| shohin_bunrui | sum_tanka |
+---------------+-----------+
| 衣服 | 5000 |
| 办公用品 | 695 |
| 厨房用具 | 11180 |
| NULL | 16875 |
+---------------+-----------+
4 rows in set (0.00 sec)
# 算出合计行
# Oracle、DB2、PostgreSQL
mysql> select shohin_bunrui, sum(hanbai_tanka) as sum_tannka
-> from Shohin
-> group by rollup(shohin_bunrui);
- 将日期添加到聚合建当中
# 使用group by添加日期
mysql> select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
-> from Shohin
-> group by shohin_bunrui, torokubi;
+---------------+------------+-----------+
| shohin_bunrui | torokubi | sum_tanka |
+---------------+------------+-----------+
| 衣服 | NULL | 4000 |
| 衣服 | 2009-09-20 | 1000 |
| 办公用品 | 2009-09-11 | 500 |
| 办公用品 | 2009-11-11 | 100 |
| 办公用品 | 2009-11-30 | 95 |
| 厨房用具 | 2008-09-20 | 880 |
| 厨房用具 | 2009-09-15 | 6800 |
| 厨房用具 | 2009-09-20 | 3500 |
+---------------+------------+-----------+
8 rows in set (0.00 sec)
# 使用rollup添加日期
# MySQL
mysql> select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
-> from Shohin
-> group by shohin_bunrui, torokubi with rollup;
+---------------+------------+-----------+
| shohin_bunrui | torokubi | sum_tanka |
+---------------+------------+-----------+
| 衣服 | NULL | 4000 |
| 衣服 | 2009-09-20 | 1000 |
| 衣服 | NULL | 5000 |
| 办公用品 | 2009-09-11 | 500 |
| 办公用品 | 2009-11-11 | 100 |
| 办公用品 | 2009-11-30 | 95 |
| 办公用品 | NULL | 695 |
| 厨房用具 | 2008-09-20 | 880 |
| 厨房用具 | 2009-09-15 | 6800 |
| 厨房用具 | 2009-09-20 | 3500 |
| 厨房用具 | NULL | 11180 |
| NULL | NULL | 16875 |
+---------------+------------+-----------+
# Oracle、DB2、PostgreSQL
mysql> select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
-> from Shohin
-> group by rollup(shohin_bunrui, torokubi);
2.4 grouping 函数
- 让
null
更加容易分辨 - 碰到超级分组记录中的
null
时返回1
- 原始数据为
null
时返回0
mysql> select grouping(shohin_bunrui) as shohin_bunrui,
-> grouping(torokubi) as torokubi,
-> sum(hanbai_tanka) as sum_tanka
-> from Shohin
-> group by rollup(shohin_bunrui, torokubi);