首页 > 其他分享 >窗口函数-详细讲解分析

窗口函数-详细讲解分析

时间：2024-07-30 22:59:11浏览次数：14  
标签：Salary 窗口 函数 over partition 讲解 select

目录

窗口函数

1.窗口函数概述

2.窗口函数的语法

1.窗口函数语法解释-Function(arg1,..., argn)

1.聚合函数

2.排序函数

3.跨行函数

2.窗口函数语法解释-OVER [PARTITION BY <...>] 

3.窗口函数语法解释- [ORDER BY <....>] 

4.窗口函数语法解释-[window_expression]

窗口函数练习

建库建表语句：

题目：

答案：

窗口函数

1.窗口函数概述

        窗口函数是一种SQL函数，非常适合于数据分析，其最大的特点就是：输入值是从SELECT语句的结果集中的一行或者多行的"窗口"中获取的，也可以理解为窗口有大有小（行数有多有少）。

        通过OVER子句，窗口函数与其他的SQL函数有所区别，如果函数具有OVER子句，则它是窗口函数。如果它缺少了OVER子句，则他就是个普通的聚合函数。

        窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐去正在聚合的各个行，最终输出称为一行。但是窗口函数聚合完之后还可以访问当前行的其他数据，并且可以将这些行的某些属性添加到结果当中去。

下面可以通过两个图来区分普通的聚合函数和窗口函数

 

为了更加直观的反映窗口函数和普通聚合函数的区别，让我们通过代码的形式感受一下 

 首先让我们先添加测试数据，并查看表。

CREATE DATABASE IF NOT EXISTS EmployeeDB;
USE EmployeeDB;
CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (Name, DepartmentID, Salary) VALUES
('Alice', 1, 50000),
('Bob', 1, 55000),
('Charlie', 2, 60000),
('David', 1, 50000),
('Eve', 2, 65000),
('Frank', 3, 45000),
('Grace', 3, 47000),
('Hannah', 3, 48000),
('Ian', 2, 70000),
('Jack', 1, 52000);

-------------------------------------sum+group by常规的聚合函数操作--------------------------------------

select DepartmentID, sum(salary) as total
from employees
group by DepartmentID;

我们可以看的出来，常规聚合函数把id进行分组然后把每组的薪资综合计算出来放在最后面。

--------------------------------------------sum+窗口函数的聚合操作-------------------------------------------

select *, 
       sum(Salary) over (partition by DepartmentID) total
from employees

 我们可以通过这两个例子看出来，聚合函数和窗口聚合函数的区别。就是窗口函数会进行分组，但不会把行进行合并。对于每一组窗口函数返回出来的结果都会重复的放在最后面。

 2.窗口函数的语法

Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

对于以上的窗口函数的语法[   ]中的语法是可以根据自己的需求进行选择（非必须写入语法），并且此语法严格按照上面的顺序来规定。

Function(arg1,..., argn)是表示函数的分类，可以是下面分类中的任何一组。

----------聚合函数，例如sum,min,avg,count等函数（常用）

----------排序函数，例如rank row_number  dense_rank()等函数（常用）

----------跨行函数，lag  lead 函数

OVER [PARTITION BY <...>] 类似于group by 用于指定分组

--每个分组你可以把它叫做窗口

--不分组的情况可以写成partition by null 或者直接不写partition by，所有列为一个大组

--分组的情况下，partition by 后面可以跟多个列，例如partition by cid,cname

[ORDER BY <....>]  用于指定每个分组内的数据排序规则 支持ASC、DESC

[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

1.窗口函数语法解释-Function(arg1,..., argn)

通常和partition by分组使用。当然也可以不分组使用，但也不分组使用通常没有意义。

----------聚合函数，例如sum,min,avg,count等函数（常用）

----------排序函数，例如rank row_number  dense_rank()等函数（常用）

----------跨行函数，lag  lead 函数

 1.聚合函数

我们还通过上文的测试数据进行演示。我们就演示2个函数，其他的聚合类函数都是相同的用法。

sum函数：求和

select *,
       sum(Salary) over (partition by DepartmentID) total
from employees

min函数 ：最小值

select *,
       min(Salary) over (partition by DepartmentID) total
from employees;

其他的聚合函数都是同样的用法。

2.排序函数

rank   row_number   dense_rank()等函数，通常与order by函数一起使用。

row_number()函数：对分组之后按照某些规则从高到低或者从低到高进行排序(order by),然后打上序号,不考虑并列的情况。

select *,
       row_number() over (partition by DepartmentID order by Salary desc ) total
from employees;

rank()函数：对分组之后按照某些规则从高到低或者从低到高进行排序(order by),然后打上序号，考虑并列情况并且跳跃排名，对此我们需要增添一组数据。

INSERT INTO Employees (Name, DepartmentID, Salary) VALUES('css',1,45000);
select *,
       rank() over (partition by DepartmentID order by Salary desc ) total
from employees;

dense_rank()函数：

select *,
       dense_rank() over (partition by DepartmentID order by Salary desc ) total
from employees;

3.跨行函数

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）；

select *,
       lag(Salary,1) over (partition by DepartmentID order by Salary desc ) total
from employees;

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）；

select *,
       lead(Salary,1) over (partition by DepartmentID order by Salary desc ) total
from employees;

FIRST_VALUE 取分组内排序后，截止到当前行，第一个值；

select *,
       first_value(Salary) over (partition by DepartmentID order by Salary desc ) total
from employees;

LAST_VALUE 取分组内排序后，截止到当前行，最后一个值；

select *,
      last_value(Salary) over (partition by DepartmentID order by Salary desc  ) total
from employees;

从这个数据我们有个疑问，为啥不是去分组内的最后一个值呢？

在这里我给大家解释一下，对于我们分的窗口（比如部门id=1）里面还有个小窗口row函数

对于我们没有指定小窗口默认是当之前所有行到当前行，这样理解可以很抽象，我们举个例子。对于部门id=1来说，我们从第一行来看（心里默念从之前所有行到当前行）从之前所有行到当前行来看确实输出的值应该是55000.00，那么我们看第二行（心里默念从之前所有行到当前行）那么确实输出的是52000.00。这样我们通过row函数来改变一下小窗口的范围。更清晰的感受一下这个函数。

select *,
      last_value(Salary) over (partition by DepartmentID order by Salary desc
          rows between unbounded preceding and unbounded following ) total
from employees;

解释一下设置小窗口的含义：rows between unbounded preceding and unbounded following

之前所有的行到之后所有的行，那么让我们输出一下。

我们可以很清晰的看出来，输出的是每一组里面最后一个的薪资。

 2.窗口函数语法解释-OVER [PARTITION BY <...>] 

over是窗口函数的标志，partition by 用来指定分组，把partition by 后面跟的字段相同的放在一起

3.窗口函数语法解释- [ORDER BY <....>] 

用于指定每个分组内的数据排序规则 支持ASC、DESC， 跟group by 中的order by是一样的用法

4.窗口函数语法解释-[window_expression]

用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

窗口子句操作的数据范围：
1)起始行:N preceding/unbounded preceding
2)当前行:currentrow
3)终止行:N following/unbounded following
举例:
rows between unbounded preceding and current row 从之前所有的行到当前行

rows between 2 preceding and current row 从前面两行到当前行

rows between current row and unbounded following 从当前行到之后所有的行

rows between current row and 1following 从当前行到后面一行

注意:
排序子句后面缺少窗口子句，窗口规范默认是rows between unbounded preceding and current row

排序子句和窗口子句都缺失，窗口规范默认是 rows between unbounded preceding and unbounded following

总体流程
1)通过partition by和 order by 子句确定大窗口(定义出上界unbounded preceding和下界unbounded following)

2)通过row 子句针对每一行数据确定小窗口(滑动窗口)
3)对每行的小窗口内的数据执行函数并生成新的列

窗口函数练习

建库建表语句：

CREATE DATABASE IF NOT EXISTS sales_db;
USE sales_db;

CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT comment '商品id',
    sale_date DATE comment '销售日期',
    amount DECIMAL(10, 2)comment '销售额',
    region VARCHAR(50) comment '地区'
)comment '销售';

-- 插入一些示例数据
INSERT INTO sales (product_id, sale_date, amount, region) VALUES
(1, '2023-01-01', 100.00, 'East'),
(2, '2023-01-01', 150.00, 'West'),
(1, '2023-01-02', 200.00, 'East'),
(3, '2023-01-02', 120.00, 'South'),
(2, '2023-01-03', 180.00, 'West'),
(1, '2023-01-03', 220.00, 'East'),
(3, '2023-01-04', 140.00, 'South'),
(4, '2023-01-04', 300.00, 'North'),
(2, '2023-01-05', 250.00, 'West'),
(1, '2023-01-05', 280.00, 'East');
insert into sales(product_id, sale_date, amount, region) values
(1,'2023-01-01',200.00,'East');

题目：

1.计算每日销售额总和（分区按日期）
2.计算每个区域的总销售额
3.为每个产品计算其销售排名（按销售额降序）
4.计算每个区域每天的平均销售额
5.计算每个产品的销售累计总额
6.计算每个区域每个产品的销售总额
7.计算每个区域最近7天的平均销售额
8.为每个产品的销售记录添加序列号（按日期排序）
9.计算每个区域每天相对于前一天的销售额变化
10.计算每个产品的销售占比（销售额/总销售额）

自己可以先把这些最基础的窗口函数做完之后，再看下面的解析

 答案：

# 计算每日销售额总和（分区按日期）
SELECT *,sale_date, SUM(amount) OVER (PARTITION BY sale_date order by sale_date) AS total_daily_sales
FROM sales;
# 计算每个区域的总销售额
SELECT region, SUM(amount) OVER (PARTITION BY region) AS total_regional_sales
FROM sales;
# 为每个产品计算其销售排名（按销售额降序）
select product_id,rank() over (order by sum(amount) desc )as '销售排名' from sales group by product_id ;
# 计算每个区域每天的平均销售额
select *,avg(amount)over (partition by region,sale_date rows between unbounded preceding and unbounded following) from sales;
# 计算每个产品的销售累计总额
select *,sum(amount)over (partition by product_id) from sales;
# 计算每个区域每个产品的销售总额
select *,sum(amount)over (partition by product_id,region)from sales;
# 计算每个区域最近7天的平均销售额
with t1 as ( select *,dense_rank() over(partition by region order by sale_date)as ttime from sales )
select *,avg(amount)over(partition by region) from t1 where ttime<7;
;
# 为每个产品的销售记录添加序列号（按日期排序）
select *,dense_rank() over (partition by product_id order by sale_date)from sales;
# 计算每个区域每天相对于前一天的销售额变化
SELECT
    a.sale_date,
    a.region,
    a.amount,
    a.amount - LAG(a.amount) OVER (PARTITION BY a.region ORDER BY a.sale_date) AS daily_change
FROM sales a;
# 计算每个产品的销售占比（按总销售额
select *,sales.amount/sum(amount)over (partition by product_id)from sales;

窗口函数很重要，希望大家可以好好琢磨琢磨，也希望发布的文章可以帮到大家。

标签：Salary,窗口,函数,over,partition,讲解,select
From： https://blog.csdn.net/qq_62757927/article/details/140793659

相关文章

  • vue模板中使用临时变量实例，for 循环中使用临时变量继承函数运行结果示例，函数运行结果
    示例代码<!--vue模板中使用临时变量测试示例--><template><divclass="component-name"><!--vue模板中循环定义临时变量实现--><divv-for="iteminitems":key="item.id":data-item="(itemTemp=formatter......
  • Python基础知识笔记——常用函数
    一、range（）函数range()函数用于生成一个整数序列。它通常用于循环结构中，例如for循环，以提供循环的迭代次数。range()函数可以有1到3个参数。#range（start,stop,step）range(2,6,2)#生成从2开始，到6结束（不包括6），步长为2的一串数字#参数指定不完全时，默认从0开始，步长......
  • Python 69个 常用 内置函数 之 总结+实例 篇
    1.abs()返回一个数的绝对值print(abs(-5))#输出5print(abs(5))#输出52.dict()创建一个字典my_dict=dict(name="Alice",age=25)print(my_dict)#输出{'name':'Alice','age':25}3.help()用于获取关于对象的帮助信息help(......
  • SQL中的大小写操作函数
    在SQL中，直接处理字符串大小写转换的函数可能因不同的数据库系统（如MySQL、PostgreSQL、SQLServer等）而有所差异。不过，一些基本的、跨数据库的字符串大小写操作函数包括：转换为大写：UPPER(string):将字符串中的所有字符转换为大写。这是SQL中最常用的将字符串转换为大写的函......
  • 数论函数
    数论函数定义：定义域为正整数的函数。积性函数：若数论函数\(f\)满足\(\gcd(x,y)=1\)则\(f(xy)=f(x)f(y)\)，\(f\)就是一个积性函数。完全积性函数：若\(f(xy)=f(x)f(y)\)，则\(f\)为一个完全积性函数。若积性函数\(f(1)\ne0\)，则\(f(1)=1\)，容易由定义推得。......
  • 可利用的函数
    0x00AAgroup_concat();将查询结果合并成一个字符串；group_concat(table_name)frominformation_schema.tableswheretable_schema='challenges'eu2ivk78cbgroup_concat(column_name)frominformation_schema.columnswheretable_name='eu2ivk78cb'id,sessi......
  • 基于SpringBoot+Vue的电影院订票信息管理系统的详细设计和实现(源码+lw+部署文档+讲解
    文章目录前言详细视频演示项目运行截图技术框架后端采用SpringBoot框架前端框架Vue可行性分析系统测试系统测试的目的系统功能测试数据库表设计代码参考数据库脚本为什么选择我？获取源码前言......
  • 基于SpringBoot+Vue的甘肃旅游管理系统的详细设计和实现(源码+lw+部署文档+讲解等)
    文章目录前言详细视频演示项目运行截图技术框架后端采用SpringBoot框架前端框架Vue可行性分析系统测试系统测试的目的系统功能测试数据库表设计代码参考数据库脚本为什么选择我？获取源码前言......
  • 我无法插入背景图像、进入全屏模式并关闭以前的窗口
    我正在编写一个根据该算法工作的程序（也就是说，当您单击某些按钮时，会出现另一个窗口或带有结果的最终窗口，并且当您单击按钮时应该从Toplevel(opennewwindow(1))出现另一个窗口）我编写了这个程序的基本代码，但我未能在所有窗口上插入背景图像，以及为所有窗口设置全屏模式并关闭以前的......
  • 大咖公开课 | 大模型场景讲解以及测试方法
    在这个日新月异的科技时代，人工智能（AI）正以惊人的速度改变着我们的生活与工作方式。为了帮助大家系统地掌握人工智能的场景及测试方法，我们特别推出了大模型与人工智能平台测试公开课，带领大家从大模型场景、RAG及人工智能平台的测试开始，深入探索人工智能技术的场景及测试方法。本......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99