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啦呀。