DataWhale SQL 组队Day5—窗口函数

1 知识点梳理

1.1 窗口函数

窗口函数的基本语法：

--[ ] 中的内容可以省略
<窗口函数> OVER ([PARTITION BY <列名>]
                     ORDER BY <排序用列名>)
                     

1.2 窗口函数的的应用

1.2.1 基本语法

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

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

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

1.2.2 例子

SELECT  product_id
       ,product_name
       ,sale_price
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS 2 PRECEDING) AS moving_avg
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS BETWEEN 1 PRECEDING 
                                        AND 1 FOLLOWING) AS moving_avg  
  FROM product  

ROWS 2 PRECEDING：

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：

1.3 ROLLUP - 计算合计及小计

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

2 练习题

2.1

请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。

运行结果：按照product_id的顺序，比较sale_price,新增的Current_max_price列保留当前的sale_price最大值。

2.2

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

 SELECT product_name
,product_id,sale_price,regist_date
,sum(sale_price) OVER (PARTITION BY regist_date)
FROM product;

2.3 思考题

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