窗口函数

0-概念

窗口函数的意义：保持原数据条数的基础上，增加一列，这一列可以是原数据排名、组内排名、分组后组内聚合值(均值、最大值、最小值)、排序后上下行聚合值、小计合计值等等。

窗口函数：常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

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

PARTITON BY是用来分组，即选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。

ORDER BY是用来排序，即决定窗口内，是按那种规则(字段)来排序的。

1、单纯排名 并新一列显示

，RANK、DENSE_RANK等排序用的专用窗口函数，不需要partition by

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  

计算结果：

2、组内排序 rank +over+partition by +order  by，并新一列显示

对partition by 的分组后的结果进行组内排序，增加一列显示出来

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

计算结果：

例如：每类别商品中价格最高的商品，每个部门中工资最高的人， 

select *  from  (
select  product_id,product_name,product_type,sale_price,
rank() over (partition by product_type order by sale_price desc   ) as ranking from product
) a 
where  ranking='1'

3、聚合函数+over+order by  

将SUM、MAX、MIN等聚合函数用在窗口函数中---不使用partition by ,使用order by 

  SELECT  product_id
       ,product_name
       ,sale_price
       ,sum(sale_price) OVER (ORDER BY product_id) AS current_sum  /*按照id排序后，当前行及之前行的合计值*/ 
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price /*按照id排序后，当前行及之前行的最大值*/
       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  /*按照id排序后，当前行及之前行的平均值*/
       ,AVG(sale_price) OVER (ORDER BY product_id ROWS 2 preceding) AS moving_avg /*按照id排序后，截止到之前2 行”，加上自身行的平均值*/
       ,AVG(sale_price) OVER (ORDER BY product_id  rows between 1 preceding and 1 following) AS moving_avg /*按照id排序后， 之前1行” + “之后1行” + “自身” */
  FROM product  

4、 聚合函数+over+partition by  

将SUM、MAX、MIN等聚合函数用在窗口函数中---使用partition by，不使用order  by  ，求组内聚合函数值

例如：获取每类商品中里售价高于该类平均价格的商品信息

select *  from  (
select  product_id,product_name,product_type,sale_price,
avg(sale_price) over (partition by product_type  ) as avg_price from product
) a 
where  sale_price>avg_price

5、计算移动平均

  SELECT  product_id
       ,product_name
       ,sale_price
       ,AVG(sale_price) OVER (ORDER BY product_id ROWS 2 preceding) AS moving_avg /*按照id排序后，截止到之前2 行”，加上自身行的平均值*/
       ,AVG(sale_price) OVER (ORDER BY product_id  rows between 1 preceding and 1 following) AS moving_avg /*按照id排序后， 之前1行” + “之后1行” + “自身” */
  FROM product  

6、ROLLUP - 计算合计及小计

  SELECT  product_type,sale_price ,SUM(sale_price) AS sum_price
  FROM product
  GROUP BY product_type, sale_price with rollup

6、练习

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

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

按照product_id排序后，当前行及之前行中，价格最大值。

6-2继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。

排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早

 select regist_date
        ,sale_price
        ,sum(sale_price) over (order by regist_date asc)
from product

6-3

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

排序，新增一列显示

根据order by的排序结果，按使用的聚合函数新增一列显示

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

select中是为了不改变数据条数的情况下进行组内排序或者聚合操作，order by 中也可用，但是实际没有意义，直接按照想排序的字段进行排序就OK啦呀。