8 SQL高级处理
8-1窗口函数
8-1-1什么是窗口函数
窗口函数也称为 OLAP 函数。OLAP是OnLine Analytical Processing的简称，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能。
窗口函数大体可以分为以下两种：
1.能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN)
2.RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
8-1-2语法的基本使用方法--使用RANK函数
根据不同的商品种类，按照销售单价从低到高的顺序创建排序表:
8-1.png
PARTITION BY能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了 product_type。
ORDER BY能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了 sale_price。省略该关键字时会默认按照 ASC，也就是升序进行排序。
通过PARTITION BY分组后的记录集合称为窗口。
8-1-3无需指定PARTITION BY
8-2.png
当不指定PARTITION BY函数时，排序变成了全部商品的排序。当希望先将表中的数据分为多个部分(窗口)，再使 用窗口函数时，可以使用PARTITION BY选项。
8-1-4专用窗口函数的种类
排序函数的种类：
- 1.RANK 函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。 例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位...... - 2.DENSE_RANK 函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。 例)有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位...... - 3.ROW_NUMBER 函数
赋予唯一的连续位次。
例)有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位......
来看看这几种排序的结果有什么不同：
8-3.png
8-1-5窗口函数的适用范围
使用窗口函数的位置有非常大的限制：
窗口函数只能在 SELECT 子句中使用(也就是不能在 WHERE 子句或者 GROUP BY 子句中使用)
8-1-6作为窗口函数使用的聚合函数
“按照商品id排序后计算累计的商品价格总量”
8-4.png
8-5.png
“按照用户id排序以后计算累计的商品价格平均值”
8-1-7计算移动平均
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。 其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
“指定最靠近的三行做平均”：
8-6.png
指定框架（汇总范围）
这里我们使用了 ROWS(“行”)和 PRECEDING(“之前”)两个关键字，将框架指定为“截止到之前 ~ 行”，这样的统计方法被称为移动平均。
使用关键字 FOLLOWING(“之后”)替换 PRECEDING，就可以指定“截止到之后 ~ 行”作为框架了。
将当前记录的前后行作为汇总对象
8-7.png
8-2 GROUPING运算符
8-2-1同时得到合计行
8-8.png
希望得到上图所示的表格，首先试试只用GROUP BY和SUM能不能实现：
8-9.png
结果是分组计算和的，但是和我们想要的结果相比，没有合计那一行。
通常的办法是分别计算出合计行和汇总结果再通过UNION ALL进行连接：
8-10.png
但是此方法不太简便，标准SQL中有GROUPING运算符解决此类问题。
8-2-2ROLLUP--同时得出合计和小计
GROUPING运算符包含以下三种：
● ROLLUP
● CUBE
● GROUPING SETS
ROLLUP的使用方法
8-11.png
ROLLUP运算符的作用，一言以蔽之，就 是“一次计算出不同聚合键组合的结果”。例如，在本例中就是一次计算出了如下两种组合的汇总结果:
- GROUP BY () --又称为超级分组记录，计算全部数据的合计行
- GROUP BY (product_type)
将“登记日期”添加到聚合键中
在GROUP BY中添加“登记日期”(不使用ROLLUP):
8-12.png
在GROUP BY中添加“登记日期”(使用ROLLUP):
8-13.png
可以看出，使用ROLLUP以后多出了最上方的合计行以及 3 条不同商品种类的小计行(也就是未使用登记日期作为聚合键的记录)，这 4 行就是我们所说的超级分组记录。
使用ROLLUP相当于UNION了以下三行的结果：
GROUP BY ()
GROUP BY (product_type)
GROUP BY (product_type, regist_date)
ROLLUP 可以同时得出合计和小计，是非常方便的工具。
8-2-3 GROUPING函数--让NULL更加容易分辨
仔细观察图8-13，可以发现一个不合理的地方，由于第二行的时间记录为空值，所以regist_date没有显示。但是最后我们合计衣服的结果（倒数第二行）也是无日期的，所以regist_date也为空。两行可能会造成误解。解决这个的办法是需要显示某行的日期数值是否为空值。
使用 GROUPING 函数来判断 NULL
8-14.png
这样就能分辨超级分组记录中的 NULL 和原始数据本身的 NULL 了。
使用 GROUPING 函数还能在超级分组记录的键值中插入字符串。
8-15.png
8-2-4CUBE--用数据来搭积木
使用CUBE取得全部组合的结果
8-16.png
与 ROLLUP 的结果相比，CUBE 的结果中多出了几行把regist_date作为聚合键所获得的汇总结果。相当于CUBE的结果是下列几行：
1.GROUP BY ()
2.GROUP BY (product_type)
3.GROUP BY (regist_date) ←添加的组合
4.GROUP BY (product_type, regist_date)
8-2-5GROUPING SETS--取得期望的积木
之前的 CUBE 的结果就是根据聚合键的所有可能的组合计算而来的。如果希望从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，或者不想得到“合计记录和使用 2 个聚合键的记录”时，可 以使用GROUPING SETS。
8-17.png
习题8.2
8-18.png
不知道regist_date为NULL值是怎么弄到最前面？
答案有两种：
8-19.png
网友评论