-
【SQL 初级语法 6】 SQL 高级处理
目录
6 SQL 高级处理
6.1 窗口函数
1、窗口函数语法
- <窗口函数> over([partition by <列清单>]
- order by <排序用列清单>)
- # [] 中的内容可以省略
窗口函数主体分为以下两种：1.能够作为窗口函数的聚合函数（sum、avg、count、max、min）；2.rank、dense_rank、row_number 等专用窗口函数。
2、语法的基本使用方法 —— 使用 rank 函数
- 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 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排序，指定了 sale_price。此外，窗口函数的 order by 与 select 语句末尾的 order by 一样，可以通过关键字 asc 、desc 来指定升序或者降序。省略该关键字时会默认按照升序排列。
group by 也可以分组 order by 也可以排序，但是 partition by 子句并不具备 group by 子句的汇总功能。
窗口函数兼具分组和排序两种功能。
通过 partition by 分组后的记录集合称为 窗口，此处的窗口并非“窗户”的意思，而是代表范围，这也是窗口函数名称的由来。
各个窗口在定义上绝对不会包含共通的部分，就像刀切蛋糕一样，干净利落，这与通过 group by 子句分割后的集合 具有相同的特征。
3、无需指定 partition by
窗口函数中的 partition by 和 group by ，其中 partition by 不是必须的，即使不指定也可以正常使用窗口函数。不指定partition by 和没有使用 group by 的聚合函数时 效果是一样的，也就是将整个表作为一个大窗口来使用。
- select product_name,product_type,sale_price,
- rank()over(order by sale_price) as ranking
- from product
不分组，直接排序。
4、专用窗口函数的种类
rank() 1,2,2,4
dense_rank() 1,2,2,3
row_number() 1,2,3,4
由于专用窗口函数无需参数，因此通常括号中都是空的。
5、窗口函数的适用范围
窗口函数只能在 select 子句中使用，反过来说，窗口函数不能写在 where 子句 或者 group 子句之中。
原因：在 DBMS 内部，窗口函数是对 where 子句或者 group by 子句处理后的“结果”进行的操作，所以在得到想要的结果之前，即使进行了排序处理，结果也是错误的。在得到排序结果之后，如果通过 where 子句中的条件除去了某些记录，或者在使用 group by 子句进行了汇总处理，那好不容易得到的排序结果也无法使用了，所以 在 select 子句之外“使用窗口函数时没有意义的”。
6、作为窗口函数使用的聚合函数
- select product_id,product_name,sale_price,
- avg(sale_price) over(order by product_id) as current_avg
- from product
7、计算移动平均
窗口函数是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
- select product_id,product_name,sale_price,
- avg(sale_price) over(order by product_id rows 2 preceding) as current_avg
- from product
这里我们使用了 rows （行）和 preceding （之前）的两个关键字，将框架指定为“截至到之前~行”，因此 “rows 2 preceding” 就是将框架指定为“截至到之前 2 行” ，也就是将作为汇总对象的记录限定为如下的 “最靠近的3行”：自身（当前记录）、之前 1 行的记录、之前 2 行的记录。
输出结果为：
- product_id | product_name | sale_price | moving_avg
- 0001 | txue衫 | 1000 | 1000/1
- 0002 | xxxxxx | 500 | (1000+500)/2
- 0003 | xxxxxx | 4000 | (1000+500+4000)/3
- 0004 | xxxxxx | 3000 | (500+4000+3000)/3
- 0005 | xxxxxx | 6800 | (4000+3000+6800)/3
- .......
- # 最靠近的 3 行
这样的统计方法称为移动平均。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。
使用关键字 following（之后）替换 preceding ，就可以指定“截止到之后~行”作为框架了。
指定之前一行，自身，之后一行 的记录：
avg(s_price) over(order by product_id rows between 1 preceding and 1 following) as rk8、两个 order by
使用窗口函数时必须要在 over 子句中使用 order by，所以可能初学者咋一看会觉得结果中的记录会按照该 order by 指定的顺序进行排序。但这只是错觉，over子句中的order by 是用来决定窗口函数按照什么样的顺序来进行计算的，对结果的排列顺序并没有影响。但是有些 DBMS 也可以按照窗口函数的 order by 子句所指定的顺序对结果进行排序，但也仅仅是个例而已。
那么如何才能让数据按照ranking 列的升序进行排序呢？
直接在 sql 语句最后 使用 order by 进行排序，除此之外没有其它的方法了。
6.2 grouping 运算符
1、添加合计行
如果使用 group 无法一次得到两种结果，只能再借助 union all
- select '合计' as product_type, sum(sale_price)
- from Product
- union all
- select product_type,sum(sale_price)
- from Product
- group by product_type
得到结果如下：
- product_type | sum
- 合计 | 16780
- 衣服 | 5000
- 办公用品 | 600
- 厨房用具 | 11180
2、rollup 同时得到合计和小计
标准 SQL 引入了 grouping 运算符，grouping 运算符包含以下3种：rollup、cube、grouping sets
1. rollup 的使用方法
通过 rollup 可以使用非常简单的 select 语句同时计算出合计行了。
- select product_type,sum(sale_price) as sum_price
- from Product
- group by rollup(product_type)
- # 在 MySQL 中 该语句变为：group by product_type with rollup
输出结果为：
- product_type sum_price
- ————————————————————————————————
- 16780
- 厨房用具 11180
- 办公用品 600
- 衣服 5000
rollup 运算符的作用，一言以蔽之，“一次计算出不同聚合键组合的结果”。例如，在本例中就是一次计算出了如下两种组合的汇总结果： group by（）；group by（product_type）
group by（）表示没有聚合键，也就相当于没有 group by 子句，这时会得到全部数据的合计行的记录，该合计行记录称为超级分组记录(super group row)。虽然名字听上去很炫酷，但把它当作未使用 group by 的合计行来理解就可以了。超级分组记录的 group_type 列的键值（对 DBMS 来说）并不明确，因此会默认使用 NULL。之后会讲在此处插入恰当的字符串的方法。
将日期加入到聚合键中
- 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
- ——————————————————————————————————————————
- 厨房用具 2008-04-28 880
- 厨房用具 2009-01-15 6800
- 厨房用具 2009-09-20 3500
- 办公用品 2009-09-11 500
- 办公用品 2009-11-11 1000
- 衣服 4000
使用 rollup 的：
- select product_type, regist_date,sum(sale_price) as sum_price
- from Product
- group by rollup(product_type, regist_date)
输出结果为：
- product_type regist_date sum_price
- ——————————————————————————————————————————
- 16780 # 合计
- 厨房用具 11180 # 小计（厨房用具）
- 厨房用具 2008-04-28 880
- 厨房用具 2009-01-15 6800
- 厨房用具 2009-09-20 3500
- 办公用品 600 # 小计（办公用品）
- 办公用品 2009-09-11 500
- 办公用品 2009-11-11 100
- 衣服 5000 # 小计（衣服）
- 衣服 2009-09-20 4000
- 衣服 2009-09-20 1000
相当于 ：group by()；group by（product_type）; group by(product_type，regist_date)
rollup 就是“卷起”的意思，该操作能得到像小计到合计这样，从最小的聚合级开始，聚合单位逐渐扩大的结果。
3、grouping 函数 —— 让 null 更容易分辨
上一小节，使用 rollup所得到的结果中，衣服的分组之中，有两条记录的 regist_date 列 为null，但是原因不相同，超级分组的小计记录，日期为null正常，另一条是本来的登记日期就是null。
为了避免混淆，SQL提供了一个用来判断超级分组记录记录的null的特定函数 —— Grouping 函数。该函数在其参数列的值为 超级分组记录 所产生的 null 时 返回 1， 其他情况返回 0。
- 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)
输出结果为：
- product_type regist_date sum_price
- ——————————————————————————————————————————
- 1 1 16780 # 合计
- 0 1 11180 # 小计（厨房用具）
- 0 0 880
- 0 0 6800
- 0 0 3500
- 0 1 600 # 小计（办公用品）
- 0 1 500
- 0 0 100
- 0 1 5000 # 小计（衣服）
- 0 0 4000
- 0 0 1000
这样就能分辨超级分组记录中的 null 和原始数据本身的 null 了。 使用 grouping 函数还能在超级分组记录的键值中插入字符串，也就是说，当grouping 函数 的返回值为1 时，指定合计或者小计等字符串，其他情况返回通常的列的值。
- select case when grouping(product_type) = 1
- then '商品种类 合计'
- else product_type end as product_type
- ,case when grouping(regist_date) = 1
- then '登记日期 合计'
- else regist_date end as regist_date
- ,sum(sale_price) as sum_price
- from Product
- group by rollup(product_type, regist_date)
输出结果为：
- product_type regist_date sum_price
- ——————————————————————————————————————————
- 商品种类 合计 登记日期 合计 16780 # 合计
- 厨房用具 登记日期 合计 11180 # 小计（厨房用具）
- 厨房用具 2008-04-28 880
- 厨房用具 2009-01-15 6800
- 厨房用具 2009-09-20 3500
- 办公用品 登记日期 合计 600 # 小计（办公用品）
- 办公用品 2009-09-11 500
- 办公用品 2009-11-11 100
- 衣服 登记日期 合 5000 # 小计（衣服）
- 衣服 2009-09-20 4000
- 衣服 2009-09-20 1000
在实际业务中需要获取包含合计或者小计的汇总结果（这种情况最多的）时，就可以使用 rollup 和 grouping 函数来实现了。
- cast(regist_date as varchar(16))
- # 注意 要把select 中的 regist_date 列转换为如上所示的字符串呢？
- 这是为了满足case 表达式所有分支的返回值必须一致的条件，
- 如果不这样的话，各分支会分别返回日期类型和字符串类型的值，执行时会发生语法错误
4、cube —— 用数据来搭积木
来介绍另一个常用的 grouping 运算符 —— cube；cube 和rollup的语法相同，只需要将 rollup 替换成 cube 就可以了。
- select case when grouping(product_type) = 1
- then '商品种类 合计'
- else product_type end as product_type
- ,case when grouping(regist_date) = 1
- then '登记日期 合计'
- else regist_date end as regist_date
- ,sum(sale_price) as sum_price
- from Product
- group by cube(product_type, regist_date)
输出结果为：
- product_type regist_date sum_price
- ——————————————————————————————————————————
- 商品种类 合计 登记日期 合计 16780 # 合计
- 商品种类 合计 2008-04-28 880
- 商品种类 合计 2009-01-15 6800
- 商品种类 合计 2009-09-20 3500
- 商品种类 合计 2009-11-11 100
- 商品种类 合计 2009-09-20 4000
- 厨房用具 登记日期 合计 11180 # 小计（厨房用具）
- 厨房用具 2008-04-28 880
- 厨房用具 2009-01-15 6800
- 厨房用具 2009-09-20 3500
- 办公用品 登记日期 合计 600 # 小计（办公用品）
- 办公用品 2009-09-11 500
- 办公用品 2009-11-11 100
- 衣服 登记日期 合 5000 # 小计（衣服）
- 衣服 2009-09-20 4000
- 衣服 1000
cube 多出来了几条记录，多出来的记录是将 regist_date 作为聚合键所得到的汇总结果：group by()；group by(product_type)；group by(regist_date) 这是cube 添加的组合；group by(product_type，regist_date)；
cube 就是将 group by 子句中的聚合键的所有可能的组合的结果集中到一个结果中，因此组合的个数就是2n（2的n次方）。
可以把 cube 理解为将使用聚合键进行切割的模块堆积成的一个立方体。
5、grouping sets —— 取得期望的积木
第三个 grouping 运算符 是 grouping sets，该运算符可以用于从 rollup 或者 cube 的结果中取出部分记录。
- 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_type regist_date sum_price
- ——————————————————————————————————————————
- 商品种类 合计 2008-04-28 880
- 商品种类 合计 2009-01-15 6800
- 商品种类 合计 2009-09-11 500
- 商品种类 合计 2009-11-11 100
- 商品种类 合计 2009-09-20 4500
- 商品种类 合计 4000
- 厨房用具 登记日期 合计 11180 # 小计（厨房用具）
- 办公用品 登记日期 合计 600 # 小计（办公用品）
- 衣服 登记日期 合 5000 # 小计（衣服）
上述结果中没有全体的合计行(16780)，与 rollup 和 cube 相比，grouping sets 用于从中去除个别条件对应的不固定的结果。然而，由于期望获得不固定结果的情况少之又少，因此与cube 和rollup 相比，使用 grouping sets 的机会也就很少了。
———————————————————————————————————————————
点击链接 查看SQL 专栏更多文章：https://blog.csdn.net/weixin_46249441/category_11913899.html?spm=1001.2014.3001.5482
-
相关阅读:
【初赛题解】CSP-J 2019 入门级 第一轮 第16题
Android开发-Mac Android开发环境搭建(Android Studio Mac环境详细安装教程，适合新手）...
C++ 字符串string
JavaScript中的内部类属性和对象封装详解
Docker快速极简配置nginx实现不同域名访问分流
一文教会你用 IDEA 从 0 到 1 构建 Tomcat , Maven 再到 Servlet(输出hello world)
使用容器方式创建firecracker虚拟机
【云原生--Kubernetes】Pod容器与镜像拉取策略
android 9 OTA到android11弹出密码框
C primer plus学习笔记 —— 6、数组和指针
- 原文地址：https://blog.csdn.net/weixin_46249441/article/details/125982842
