SQL训练营Task05：SQL语句高阶应用窗口函数等

本笔记为阿里云天池龙珠计划SQL训练营的学习内容&＃xff0c;链接为&＃xff1a;https://tianchi.aliyun.com/specials/promotion/aicampsql

本笔记为阿里云天池龙珠计划SQL训练营的学习内容&＃xff0c;链接为&＃xff1a;https://tianchi.aliyun.com/specials/promotion/aicampsql

窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称&＃xff0c;意思是对数据库数据进行实时分析处理。

窗口函数的通用形式&＃xff1a;

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


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

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

专用窗口函数&＃xff1a;

• row_number() over(…)
• rank() over(…)
• dense_rank() over(…)

注意&＃xff1a;一定是row_number()&＃xff0c;rank()&＃xff0c;dense_rank()&＃xff0c;后面一定要有括号&＃xff0c;括号里面什么都不用写

三个函数的区别如下&＃xff1a;

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


• RANK函数 计算排序时&＃xff0c;如果存在相同位次的记录&＃xff0c;则会跳过之后的位次。

  – 例&＃xff09;有 3 条记录排在第 1 位时&＃xff1a;1 位、1 位、1 位、4 位……

• DENSE_RANK函数 同样是计算排序&＃xff0c;即使存在相同位次的记录&＃xff0c;也不会跳过之后的位次。

  – 例&＃xff09;有 3 条记录排在第 1 位时&＃xff1a;1 位、1 位、1 位、2 位……

• ROW_NUMBER函数 赋予唯一的连续位次。

  – 例&＃xff09;有 3 条记录排在第 1 位时&＃xff1a;1 位、2 位、3 位、4 位

普通场景下&＃xff0c;聚合函数往往和group by一起使用&＃xff0c;但是窗口环境下&＃xff0c;聚合函数也可以应用进来&＃xff0c;那么此时它们就被称之为聚合类窗口函数&＃xff0c;属于窗口函数的一种

• sum()
• count()
• avg()
• max()
• min()

语法

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


PRECEDING&＃xff08;“之前”&＃xff09;&＃xff0c; 将框架指定为 “截止到之前 n 行”&＃xff0c;加上自身行

FOLLOWING&＃xff08;“之后”&＃xff09;&＃xff0c; 将框架指定为 “截止到之后 n 行”&＃xff0c;加上自身行

BETWEEN 1 PRECEDING AND 1 FOLLOWING&＃xff0c;将框架指定为 “之前1行” &＃43; “之后1行” &＃43; “自身”

5.1

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

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


5.2

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

selectproduct_id,product_name,regist_date,sale_price,sum(sale_price) over(order by regist_date) as current_sum_price
from product;

5.3

思考题

• ① 窗口函数不指定PARTITION BY的效果是什么&＃xff1f;

• ② 为什么说窗口函数只能在SELECT子句中使用&＃xff1f;实际上&＃xff0c;在ORDER BY 子句使用系统并不会报错。

答&＃xff1a;① partition by 用来选择要看哪个窗口&＃xff0c;不使用partition by是查看全局。
② 因为窗口函数是对where或者group by子句处理后的结果进行操作&＃xff0c;所以窗口函数原则上只能写在select子句中&＃xff0c;而不是order by。

注意&＃xff1a;SQL语句的执行顺序&＃xff1a;from–where–group by–having–select–order by
SQL语句的书写顺序&＃xff1a;select–from–where–group by–having–order by