【窗口函数的详细使用】

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

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

在这里插入图片描述

目录

  • 1.窗口函数的定义
  • 2. 窗口函数的作用
  • 3. 窗口函数的语法
  • 4. 窗口函数的分类
  • 5. 窗口函数
    • 5.1 聚合窗口函数
      • 5.1.1 分组聚合函数
      • 5.1.2 分组排序聚合函数
    • 5.2 取值窗口
      • 5.2.1 向上取值
      • 5.2.2 向下取值
      • 5.2.3 分组取值
      • 5.2.4 取第一个值
    • 5.3 排序窗口
    • 5.4 窗口计算范围的指定
    • 5.5 order by 计算范围
  • 6. CTE语法
    • 6.1 CTE的基本语法

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;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/686074.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【TS】进阶

【TS】进阶

一、类型别名 类型别名用来给一个类型起个新名字。 type s string; let str: s "123";type NameResolver () > string;: // 定义了一个类型别名NameResolver&#xff0c;它是一个函数类型。这个函数没有参数&#xff0c;返回值类型为string。这意味着任何被…
阅读更多...
【轻量化】YOLOv10: Real-Time End-to-End Object Detection

【轻量化】YOLOv10: Real-Time End-to-End Object Detection

论文题目&#xff1a;YOLOv10: Real-Time End-to-End Object Detection 研究单位&#xff1a;清华大学 论文链接&#xff1a;http://arxiv.org/abs/2405.14458 代码链接&#xff1a;https://github.com/THU-MIG/yolov10 推荐测试博客&#xff1a;YOLOv10最全使用教程&#xff0…
阅读更多...
一个月速刷leetcodeHOT100 day14 彻底搞懂二分搜索 以及相关题目

一个月速刷leetcodeHOT100 day14 彻底搞懂二分搜索 以及相关题目

二分查找算法&#xff08;Binary Search Algorithm&#xff09; 是一种用于在已排序数组中查找特定元素的高效算法。它的基本思想是每次将待查找的区间分成两部分&#xff0c;并确定目标元素位于哪一部分中&#xff0c;然后只在目标区间中继续查找&#xff0c;直到找到目标元素…
阅读更多...
水经微图IOS版5.3.0发布

水经微图IOS版5.3.0发布

随时随地&#xff0c;微图一下&#xff01; 水经微图&#xff08;以下简称“微图”&#xff09;IOS版&#xff0c;新版已上线。 当前版本 当前版本号为&#xff1a;5.3.0-beta 如果你发现该版本中存在问题&#xff0c;请及时反馈给我们修订。 关于我们产品的版本控制&…
阅读更多...
国产工业级实时数据库

国产工业级实时数据库

项目功能描述 Mars数据库的核心功能在于其能够高效地处理来自工业现场的大量传感器数据。它通过简化的可视化配置&#xff0c;允许用户轻松接入各种传感器&#xff0c;并进行数据记录和逻辑处理。Mars数据库在单机模式下支持高达120万个传感器信号的接入&#xff0c;而其分布式…
阅读更多...
【文末附gpt升级秘笈】埃隆·马斯克芯片调配策略对特斯拉股价的影响分析

【文末附gpt升级秘笈】埃隆·马斯克芯片调配策略对特斯拉股价的影响分析

埃隆马斯克芯片调配策略对特斯拉股价的影响分析 一、引言 在现代商业环境中&#xff0c;企业间的资源调配与策略布局往往对其股价产生深远影响。据外媒CNBC报道&#xff0c;埃隆马斯克在芯片资源分配上的决策引起了业界的广泛关注。他秘密要求英伟达将原本预留给特斯拉的高端…
阅读更多...
TMS320F280049学习3：烧录

TMS320F280049学习3：烧录

TMS320F280049学习3&#xff1a;烧录 文章目录 TMS320F280049学习3&#xff1a;烧录前言一、烧录RAM二、烧录FLASH总结 前言 DSP的烧录分为两种&#xff0c;一种是将程序烧录到RAM中&#xff0c;一种是烧录到FLASH中&#xff0c;烧录ARM中的程序&#xff0c;只要未掉电&#x…
阅读更多...
Vue3项目准备：utils工具插件文件夹中封装request.js配置axios请求基地址及超时时间、请求拦截器、响应拦截器

Vue3项目准备：utils工具插件文件夹中封装request.js配置axios请求基地址及超时时间、请求拦截器、响应拦截器

token介绍 概念&#xff1a;访问权限的令牌&#xff0c;本质上是一串字符串 创建&#xff1a;正确登录后&#xff0c;由后端签发并返回 作用&#xff1a;判断是否有登录状态等&#xff0c;控制访问权限 注意&#xff1a;前端只能判断token有无&#xff0c;而后端才能判断tok…
阅读更多...
Camtasia Studio2024永久免费版及最新版本功能讲解

Camtasia Studio2024永久免费版及最新版本功能讲解

在当前数字化时代&#xff0c;视频内容的制作与编辑变得愈发重要。无论是企业宣传、在线教育还是个人Vlog制作&#xff0c;一款功能强大且易于上手的视频编辑软件成为了刚需。Camtasia Studio作为市场上备受欢迎的视频编辑与屏幕录像工具&#xff0c;凭借其强大的功能与用户友好…
阅读更多...
在线标注流程

在线标注流程

文章目录 在线标注流程标注方法 在线标注流程 登录地址&#xff1a;http://7a27c5e078f644a2a9b734603913c65e.login.bce.baidu.com 出现页面&#xff1a; 登录名&#xff1a; 三个中任意一个 密码&#xff1a;ZNSJ123a 登录之后叉掉。再打开这个网站&#xff1a;https://…
阅读更多...
2938. 区分黑球与白球

2938. 区分黑球与白球

题目 桌子上有 n 个球&#xff0c;每个球的颜色不是黑色&#xff0c;就是白色。 给你一个长度为 n 、下标从 0 开始的二进制字符串 s&#xff0c;其中 1 和 0 分别代表黑色和白色的球。 在每一步中&#xff0c;你可以选择两个相邻的球并交换它们。 返回「将所有黑色球都移到…
阅读更多...
Leetcode：电话号码的字母组合

Leetcode：电话号码的字母组合

题目链接&#xff1a;17. 电话号码的字母组合 - 力扣&#xff08;LeetCode&#xff09; 普通版本&#xff08;回溯&#xff09; class Solution { public:string tmp;//临时存放尾插内容vector<string> res;//将尾插好的字符串成组尾插给resvector<string> board{…
阅读更多...
⌈ 传知代码 ⌋ AI驱动食物图像识别

⌈ 传知代码 ⌋ AI驱动食物图像识别

&#x1f49b;前情提要&#x1f49b; 本文是传知代码平台中的相关前沿知识与技术的分享~ 接下来我们即将进入一个全新的空间&#xff0c;对技术有一个全新的视角~ 本文所涉及所有资源均在传知代码平台可获取 以下的内容一定会让你对AI 赋能时代有一个颠覆性的认识哦&#x…
阅读更多...
【数据结构】平衡二叉树（AVL树）

【数据结构】平衡二叉树（AVL树）

目录 前言 一、AVL树概念 二、AVL树节点定义 三、AVL树插入 1. 按照二叉搜索树的方式插入新节点 2. 维护节点的平衡因子与调整树的结构 a. 新节点插入较高左子树的左侧---左左&#xff1a;右单旋 b. 新节点插入较高右子树的右侧---右右&#xff1a;左单旋 c. 新节点插入…
阅读更多...
前端面试项目细节重难点（已工作|做分享）想（八）

前端面试项目细节重难点（已工作|做分享）想（八）

面试官&#xff1a;请你讲讲你在该项目中遇到的印象深刻的问题是什么&#xff1f; 答&#xff1a;我的回答&#xff1a;该项目的实现过程中我确实遇到了问题&#xff1a;【我会给大家整理回答思路和角度&#xff0c;那那么遇到这样的问题也可借鉴这种思路进行阐述】 第一层面…
阅读更多...
RocketMQ教程(五)：RocketMQ的工作原理2

RocketMQ教程(五)：RocketMQ的工作原理2

工作原理 RocketMQ 是一个高性能、高吞吐量的分布式消息和流计算平台，它基于发布-订阅模式工作。其核心设计理念是确保消息传递的高效性、稳定性和可扩展性。RocketMQ 的工作原理主要可以分为以下几个部分： 1. 消息流程 消息发布: Producer 首先向 NameServer 查询目标 Top…
阅读更多...
二重，三重积分和曲面，曲线积分的关系和区别

二重，三重积分和曲面，曲线积分的关系和区别

这是我在学习完曲面曲线积分概念后容易和二重三重积分混淆而大概总结和区分了一下&#xff0c;如果有错误请大佬指出&#xff0c;多谢&#xff01;&#xff01;&#xff01;
阅读更多...
shell（一）

shell（一）

shell 既是脚本语言又是应用程序 查看自己linux系统的默认解析&#xff1a;echo $SHELL 创建第一个shell 文件 touch 01.sh编辑 vi 01.sh01.sh 文件内容 #!/bin/bash echo felicia保存 按Esc 然后输入:wq 定义以开头&#xff1a;#!/bin/bash #!用来声明脚本由什么shell解释…
阅读更多...
无线麦克风什么牌子的音质效果好？一文揭秘领夹麦克风哪个品牌好

无线麦克风什么牌子的音质效果好？一文揭秘领夹麦克风哪个品牌好

​近年来&#xff0c;无线领夹麦克风在各个领域都大放异彩&#xff0c;无论是直播、采访还是上课&#xff0c;都能看到它的身影。这款小小的无线麦克风&#xff0c;蕴含着巨大的能量&#xff0c;为媒体人的创作提供了强大的支持。对于想要更新设备的媒体人来说&#xff0c;现在…
阅读更多...
打造国产软硬件一体化解决方案 YashanDB与宏杉科技完成多项兼容互认证

打造国产软硬件一体化解决方案 YashanDB与宏杉科技完成多项兼容互认证

近日&#xff0c;深圳计算科学研究院崖山数据库系统YashanDB与宏杉科技系列存储、系列服务器与数据库一体机等多款产品顺利完成兼容性互认证。经严格测试&#xff0c;双方产品完全兼容&#xff0c;稳定运行&#xff0c;共同提供高效、稳定、安全的国产软硬件一体化解决方案&…
阅读更多...
最新文章

Copyright @ 2022~2023