Task05：窗口函数等（笔记5）

最新推荐文章于 2024-10-05 11:27:00 发布

wxiaoyiwenrong

最新推荐文章于 2024-10-05 11:27:00 发布

阅读量157

点赞数

文章标签：大数据 sql mysql 数据库

本文链接：https://blog.csdn.net/wxiaoyiwenrong/article/details/125600572

版权

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

1、窗口函数-----有选择的对某一部分数据进行汇总、计算和排序

概念：

对数据库数据进行实时分析处理，也称为olar 函数

通用形式：

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

[]中的内容可以省略

窗口函数最关键的是搞明白关键字“Partiton by”和“order by ”的作用：

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

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

2、窗口函数种类

可以分为：

1、将sum、max、min 等聚合函数用在窗口函数中

2、rank、dense_rank等排序用的专用窗口函数

--专用窗口函数

1、rank函数（英式排序）

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

2、dense_rank函数（中式排序）

同样是计算排序，即使存在相同的位次的记录，也不会跳过之后的位次。

3、row_number函数

赋予唯一的连续位次

---聚合函数在窗口函数上的使用

聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值

3、窗口函数的应用-计算移动平均

聚合函数在窗口函数的使用，还可以指定二更加详细的汇总范围，该汇总范围成为框架（frame）

语法

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

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

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

between 1 preceding and 1 follow，将框架指定为“之前1行”+“之后1行”+“自身”

----窗口函数适用范围和注意事项

1、原则上，窗口函数只能在select子句中使用

2、窗口函数over 中的order by子句并不会影响最终结果的排序。只是用来就决定窗口函数按何种顺序计算

4、grouping运算符

--rollup ---计算合计及小计

常规的group by 只能得到每个分类的小计，计算分类合计，可以用rollup关键字

练习题

5.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 升序排列，计算出截⾄当前⾏的最⾼价格 sale_price；

5.2

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

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

执行结果：

5.3

思考题

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

排序列进行全局配列

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

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