2021-09-25天池SQL训练营Task05

最新推荐文章于 2024-09-30 15:50:05 发布

好假的程序猿

最新推荐文章于 2024-09-30 15:50:05 发布

阅读量65

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/m0_47613175/article/details/120473209

版权

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

窗口函数(MYSQL8.0新增之前版本不支持)

基本语法

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

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

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

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

按照种类分区在每个区域内按照sale_price排序
每一个分区，都是一个窗口

窗口函数的分类

一是将SUM、MAX、MIN等聚合函数用在窗口函数中

二是 RANK、DENSE_RANK等排序用的专用窗口函数

对于专用窗口函数

RANK函数（英式排序）****
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

DENSE_RANK函数（中式排序）****
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

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

例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

对于聚合函数的窗口函数

聚合函数在开窗函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。
第一行的sum 结果时它本身
第二行的sum ：1行j+2行
第三行的sum :1+2+3;
同理对于平均也是如此

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

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

n PRECEDING 本行前的n行和本行
n FOLLOWING 本行后的n行和本行
BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”

ROLLUP - 计算合计及小计

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

在这里插入图片描述

值得注意的是

原则上，窗口函数只能在SELECT子句中使用。
窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

练习题

由于我的MySQL是5.4版本的无法实际执行窗口函数

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排序的，由于没有指定分组，所以所有数据数一组的，那么max(sale_price)有一个唯一的值，那么查询出来的结果应该是到当前行位置的 sale_price的最大值

5.2

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

select product_id,product_type, sale_price
,sum(sale_price) over 
(order by coalesce(regist_date,CAST('0001-01-01')))
from product ;
--当regist_date为null是将其转化为日期类型的0001-01-01

5.3

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

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

1.所有数据就是一个分组，order by对全局排序
2.窗口函数会进行排序所以，逻辑是在SELECT查询数据后排序才是正确的，SQL的执行是先FROM–>WHERE–>SELECT—>ORDER
order 在select 之后所以进行窗口函数的聚合是可以的

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