窗口函数等--天池龙珠计划SQL训练营

1.窗口函数

是一类函数集合，对数据库进行实时数据分析，有动态作用。

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

• 将SUM、MAX、MIN等聚合函数用在窗口函数中
• RANK、DENSE_RANK等排序用的专用窗口函数

1.1 partition by

按照列名进行分组与group by类似，但是不进行聚合，即不改变行数。计算出每组的结果然后进行广播。
例子
计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额

select `product_name` , 
	 `regist_date` , 
	sum(sale_price)  over ( PARTITION   BY `regist_date` ) as sum_sale
from product;

计算出每天销售单价的累加和，直接求出一个结果，而不是动态求和。

1.2 order by

ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。
在没有 partition by时，会进行动态操作.

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;  

可用来计算移动平均，并且指定移动平均范围.

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

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  

2. ROLLUP - 计算合计及小计

常规的GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP关键字。

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

可以起到与 partition类似功能，不过会新增行数。

注意事项

SQL 语句的执⾏顺序:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
如果在 WHERE, GROUP BY, HAVING 使⽤了窗⼝函数，就是说提前进⾏了⼀次排序，排序之后再去除
记录、汇总、汇总过滤，第⼀次排序结果就是错误的，没有实际意义。⽽ ORDER BY 语句执⾏顺序在
SELECT 语句之后，⾃然是可以使⽤的.