Task05_SQL高级处理

知识点

5.1 窗口函数

1.通用形式

<窗口函数> OVER ([PARTITION BY <列名>]
                     ORDER BY <排序用列名>)  

PARTITION BY:类似于分组GROUP BY，指定查看窗口

ORDER BY：决定窗口函数按何种顺序计算，不影响最终的排序结果

2.窗口函数类型

①专用窗口函数：

例：200，200，200，500，600

RANK 顺序：1，1，1，4，5

DENSE_RANK 顺序：1，1，1，2，3

ROW_NUMBER 顺序：1，2，3，4，5

②聚合函数：累计聚合

3.计算移动平均

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  
                 
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

n PRECEDING :前n行+自身

n FOLLOWING：后n行+自身

BETWEEN n PRECEDING AND n FOLLOWING：前n行+自身+后n行

5.2 ROLLUP-计算合计和小计

GROUP BY product_type, regist_date WITH ROLLUP

练习题

5.1

SELECT  product_id
       ,product_name
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
  FROM product

累计比较求出当前最大值，执行结果如下：

5.2 计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是将登记日期为NULL 的“运动 T 恤”记录排在第 1 位

SELECT  product_id
       ,product_name
       ,sale_price
			 ,regist_date
       ,SUM(sale_price) OVER (ORDER BY regist_date IS NOT NULL,regist_date) AS Current_sum_price
  FROM product;

注意：顺序一致的记录，SUM聚合函数会一并计算。

5.3

思考题

① 窗口函数不指定PARTITION BY的效果是什么？

不会有分组的效果， OVER里面的ORDER BY是面对整个表格排序。

② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。

跟SQL语句的执行顺序有关: FROM–WHERE–GROUP BY–HAVING–SELECT–ORDER BY。窗口函数会对记录进行一次排序，如果再进行筛选、汇总记录，窗口函数运行结果会发生改变；而ORDER BY可以在窗口函数的基础上对记录再排序。

DW_SQL学习