Task05：SQL高级处理

学习目标：

Task05：SQL高级处理

---

学习产出：

窗口函数（OLAP函数）

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

[]中的内容可以省略掉
PARTITON BY是用来分组
ORDER BY用于字段规则排序

几个专用窗口函数

• RANK 如果有并列，则跳过之后的位次。如有三个并列第一名则：1,1,1,4，
• DENSE_RANK 如果有并列，但是不会跳过之后的位次。如有三个并列第一名则：1,1,1,2，
• ROW_NUMBER 赋予唯一的连续位次，有 3 条记录排在第 1 位时：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

聚合函数在窗口函数上的使用

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;  

窗口函数的的应用 - 计算移动平均

指定更加详细的汇总范围。该汇总范围称为框架(frame)。

<窗口函数> 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行” + “自身”

原则上，窗口函数只能在SELECT子句中使用。
窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

运算符

• ROLLUP计算合计及小计

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

练习题

2.

SELECT product_name
	  ,regist_date
	  ,sale_price
	  ,SUM(sale_price) OVER(partition by regist_date order by regist_date)
from product;

3. ① 窗口函数不指定PARTITION BY的效果是什么？
   将当前表格看成一个大窗口进行计算
   ② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。
   窗口函数是对where或者group by子句处理后的结果进行操作