【窗口函数的详细使用】

前言：
💞💞大家好，我是书生♡，今天主要和大家分享一下可MySQL中的窗口函数的概念，语法以及常用的窗口函数,希望对大家有所帮助。感谢大家关注点赞。
💞💞前路漫漫，希望大家坚持下去，不忘初心，成为一名优秀的程序员

个人主页⭐： 书生♡
gitee主页🙋‍♂：闲客
专栏主页💞：大数据开发
博客领域💥：大数据开发，java编程，前端，算法，Python
写作风格💞：超前知识点，干货，思路讲解，通俗易懂
支持博主💖：关注⭐，点赞、收藏⭐、留言💬

1.窗口函数的定义

  定义：窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。
    窗口函数可以对查询结果集中的行进行分组、排序，并在每个分组内进行聚合、排名、计算等操作，而不会改变原始查询结果的行数或顺序。

2. 窗口函数的作用

  窗口函数（Window Functions）在数据库查询中扮演着非常重要的角色，它们允许用户对查询结果集中的数据进行分组、排序，并在每个分组内进行聚合、排名、计算等操作，而不改变原始查询结果的行数或顺序。以下是窗口函数的一些主要作用：

• 分组内聚合：窗口函数可以在每个分区（由PARTITION BY子句定义）内执行聚合操作，如计算每个部门的平均工资、每个班级的最高分等。与传统的聚合函数不同，窗口函数会为每一行返回一个结果，而不是将整个分组压缩成一个结果。
• 排名和排序：窗口函数如ROW_NUMBER(), RANK(), DENSE_RANK()等可以用来为结果集中的每一行分配一个唯一的序号或排名。这些排名可以是基于某个字段的值，或者是在某个分区内的相对位置。
  滑动窗口计算：通过定义窗口的起始和结束范围（使用OVER子句中的ROWS BETWEEN子句），窗口函数可以执行滑动窗口计算，如计算连续几天的平均销售额、计算过去三个月的累计销售额等。
• 访问其他行：LAG()和LEAD()等窗口函数允许用户访问结果集中的前一行或后一行的数据。这对于计算与前一行或后一行的差异、比较或构建时间序列分析等非常有用。
• 获取边界值：FIRST_VALUE()和LAST_VALUE()等窗口函数可以返回每个分区中的第一行或最后一行的某个字段的值。
• 数据桶划分：NTILE()函数可以将结果集中的数据划分为指定数量的近似大小的数据桶，并为每行分配一个桶号。

3. 窗口函数的语法

窗口函数([参数]) OVER (
  [PARTITION BY <分组列>] 
  [ORDER BY <排序列 ASC/DESC>]
  [ROWS BETWEEN 开始行 AND 结束行]
)

• PARTITION BY 子句用于指定分组列，关键字：PARTITION BY 。
• ORDER BY 子句用于指定排序列，关键字ORDER BY 。
• ROWS BETWEEN 子句用于指定窗口的范围，关键字ROWS BETWEEN 即[开始行]、[结束行]

窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中
执行顺序: from>where>group by >聚合函数>having >select >distinct >order by >limit

4. 窗口函数的分类

• 排序函数：row_number()，rank()，dense_rank()
• 聚合函数：max()，min()，count()，sum()，avg()，median()
• 向前向后取值：lag()，lead()
• 百分位：percent_rank()
• 取值函数：first_value()，last_value()，nth_value()
• 分箱函数：ntile()

这里我们重点简述排序函数、聚合函数、取值函数、向前向后取值。

5. 窗口函数

5.1 聚合窗口函数

• 聚合窗口函数结合了聚合函数和窗口函数的特性，用于在数据的特定窗口或分区上执行聚合计算。
• 它允许在保持数据完整性的同时，对数据的子集（即窗口）进行复杂的统计和分析。

5.1.1 分组聚合函数

格式为：
聚合函数 over（partition by 分组字段）

-- 按照性别分组统计年龄和
SELECT sid,
       sname,
       age,
       gander,
       province,
       tuition,
       sum(age) OVER (PARTITION BY gander)
FROM stu;

-- 按照省份、性别分组统计年龄和
SELECT sid,
       sname,
       age,
       gander,
       province,
       tuition,
       sum(age) OVER (PARTITION BY province,gander)
FROM stu;

5.1.2 分组排序聚合函数

SELECT sid,
       sname,
       age,
       gander,
       province,
       tuition,
       sum(tuition) OVER (PARTITION BY gander ORDER BY age)
FROM stu;

SELECT sid,
       sname,
       age,
       gander,
       province,
       tuition,
       avg(tuition) OVER (PARTITION BY gander ORDER BY age)
FROM stu;

5.2 取值窗口

取值窗口函数是用于在数据库查询中返回指定位置上的数据的函数。这类函数允许在结果集的每一行上执行计算，同时保留原始数据行的上下文信息。

FIRST_VALUE：返回窗口内第一行的数据。
LAST_VALUE：返回窗口内最后一行的数据。
NTH_VALUE：返回窗口内第N行的数据。
LAG：返回分区中当前行之前的第N行的数据。需要注意的是，LAG函数不支持动态的窗口大小，而是以当前分区作为分析的窗口。
LEAD：返回分区中当前行之后第N行的数据。同样，LEAD函数也不支持动态的窗口大小。

向上向下取值，取第一个值
lag lead，first_value

5.2.1 向上取值

-- 向上一行取值
select empno,ename,lag(ename) over() from emp;
-- 将名字字段取上一行的值，没有上一行默认为null

-- 向上取二行
SELECT empno, ename, lag(ename, 2) OVER ()
FROM emp;

-- 取不到值给默认值
SELECT empno, ename, lag(ename, 2, 'itcast') OVER ()
FROM emp;

5.2.2 向下取值

-- 向下取一行
SELECT empno, ename, lead(ename) OVER ()
FROM emp;

-- 向下取二行
SELECT empno, ename, lead(ename, 2) OVER ()
FROM emp;

-- 取不到值给默认值
SELECT empno, ename, lead(ename, 2, 'itcast') OVER ()
FROM emp;

5.2.3 分组取值

分组取值–分组只进行组内查找

-- 分组取值--分组只进行组内查找
SELECT *, lag(ename) OVER (PARTITION BY job)
FROM emp;

5.2.4 取第一个值

只取组内第一个值

-- 取第一个值
SELECT *, first_value(ename) OVER (PARTITION BY job)
FROM emp;

5.3 排序窗口

• 按照指定字段排序后生成序号
• 排序窗口函数允许用户根据指定的排序规则对窗口内的数据进行排序，以便更好地理解和分析数据。这些函数在数据处理和分析中非常有用，特别是在需要获取前几名、排名等场景时。

RANK()：
    功能：对窗口内的数据进行排名。
    特点：如果存在并列情况，会占用下一个名次的位置。例如，如果有三个学生并列第一名，那么下一个学生的排名将是第四名。
    语法示例：RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
DENSE_RANK()：
    功能：与RANK()类似，但处理并列情况的方式不同。
    特点：即使存在并列情况，也不会跳过下一个名次。例如，如果有三个学生并列第一名，那么下一个学生的排名将是第二名。
    语法示例：DENSE_RANK() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
ROW_NUMBER()：
    功能：为窗口内的每一行分配一个唯一的序号。
    特点：不考虑并列情况，即使两行数据完全相同，也会分配不同的序号。
    语法示例：ROW_NUMBER() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking

案例：

-- 排序
select *,
       rank() over (order by sal)  as rnk, -- 如果有并列生成序号是不连续的
       dense_rank()  over (order by sal)  as dernk, -- 生成连续序号
       row_number() over (order by sal) as rw -- 生成行号
from emp;

select * from (
select *,
       dense_rank()  over (order by sal desc )  as dernk -- 生成连续序号
from emp) tb1
where dernk <=3;

5.4 窗口计算范围的指定

需要使用rows 进行指定 计算行数
范围的确认：
默认情况下没有指定partition by 的字段，范围是全表，如果指定了partition by ，范围是分组内的范围

可以通过rows指定计算行的范围大小,指定计算行范围后，只对范围内的数据进行计算
row between 起始行 and 结束行

指定范围关键字
    向上无限制:  unbounded preceding 向上的行数没有限制
    向上指定行数: 行数 preceding
    当前行:      current row
    向下指定行数: 行数 following
    向下无限制:  unbounded following

不同关键字可以组合成一个范围
between 起始行范围 and 结束行范围

between 2 preceding  and  1 following  范围查找是以当前行为基准  计算四行(上两行+当前行+下一行)

between current row  and  2 following     计算3行（当前行+下两行）

范围顺序要注意，一般起始行写向上查找，结束行写向下查找，可以同上或者同下，但是范围不能超过

-- 范围是向上无限制-向下无限制，以当前行为基准，就是行号
SELECT *, count(ename) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS count
FROM emp;

select *,count(ename) over(rows between 2 preceding and 2 following ) cnt from emp;

select *,sum(sal) over(rows between 2 following and 3 following ) cnt from emp;

5.5 order by 计算范围

使用了order by 后会自带计算范围统计数据

6. CTE语法

CTE语法是一种常见的SQL语法，全称为“公共表表达式”（Common Table Expressions，CTE）。

CTE语法类似子查询，可以将一个select语句计算的结果当成一个新的临时表使用，CTE语法允许你在一个SQL语句中创建一个命名的临时结果集，这个临时结果集只在当前的SQL语句执行期间存在。CTE可以提高SQL语句的可读性和维护性，同时也可能提高查询效率。

6.1 CTE的基本语法

-- 基本用法
with 临时表名 as(查询语句)
select * from 临时表名

-- 多个计算结果保存
with tb1 as(查询语句),,
	tb2 as(查询语句 select * from tb1),
	tb3 as(查询语句)
	.....
select * from tb3 join tb2

案例：

with tb1 as(select * from emp)
select ename,sal from tb1;

with tb1 as(
    SELECT user_id, shop, count(*) AS cnt
            FROM test2
            GROUP BY user_id, shop
),
    tb2 as(
        SELECT tb1.*,
             row_number() over (partition BY tb1.shop ORDER BY tb1.cnt DESC) rk
      FROM tb1
    )
select * from tb2 where rk <=3;