Task05：SQL高级处理 组队学习

练习题
1.请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。
SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product

5.2
继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）

SELECT regist_date,sale_price,product_name,SUM(sale_price) over (ORDER BY regist_date) AS sum_price
   FROM product ;

5.3
思考题
① 窗口函数不指定PARTITION BY的效果是什么？
会对整体做汇总。排序操作
② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。
因为窗口函数在order by后面的话没有什么意义，并不会输出什么结果。

5.窗口函数（OLAP函数）
5.1 含义：对数据库数据进行实时分析处理。
可以有选择的去某一部分数据进行汇总、计算、排列。
窗口函数的通用形式：
<窗口函数> OVER ([PARTITION BY <列名>]
ORDER BY <排序用列名>).
窗口函数最关键的是搞明白关键字PARTITON BY 和ORDER BY的作用
PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。

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

5.2窗口函数种类
1）将SUM、 MAX、 MIN等集合函数用在窗口函数中
2）RANK、SENSE_PANK等排序用的专用窗口函数
5.2.1专用窗口函数
1）RANK函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例如：有三条记录排在第一位时：1位、1位、1位、四位……
2)DENSE_RANK函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例如：有三条记录在第一位时：1位、1位、1位、2位……
3）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

5.2.2聚合函数在窗口函数上的使用
集合函数在开窗函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。

SELECT product_id,product_name,sale_price, SUM(sale_price)OVER (ORDER BY product_id)AS current_sum,
                                               AVG(sale_price)OVER (ORDER BY product_id) AS current_avg
                                               FROM product;

聚合函数结果是，按我们指定的排序，这里是product_id，当前所在行及之前所有的行的合计或均值。即累计到当前行的聚合。
5.3窗口函数的应用-计算移动平均
聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合，实际上，还可以指定更加详细的汇总范围。该汇总范围称为框架
语法
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )

<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行
FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行
BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”

PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行

FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行

BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”

5.3.1窗口函数适用范围和注意事项
1）原则上，窗口函数只能在SELECT子句中使用。
2）窗口函数OVER中的ORDER BY子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。
5.4 GROUPING运算符
5.4.1 ROLLUP_计算合计及小计
计算分类的合计，可以用 ROLLUP关键字

SELECT product_type,regist_date,SUM(sale_price) AS sum_price
   FROM product
   GROUP BY product_type,regist_date WITH ROLLUP

ROLLUP可以对多列进行汇总求小计和合计