窗口函数

2025-09-09 19:48:30 1

SQL 查询的一般逻辑执行顺序

  1. FROM:先加载数据源(表或视图),包括任何 JOIN。
  2. WHERE:对行进行筛选。
  3. GROUP BY:对行进行分组(如果有聚合)。
  4. HAVING:对分组结果筛选。
  5. SELECT:选择列、计算表达式,同时处理窗口函数。
  6. WINDOW FUNCTIONS(OVER 子句):窗口函数在 SELECT 阶段计算,它可以看到当前行及“窗口帧”内的其他行。
  7. DISTINCT:去重(如果有)。
  8. ORDER BY:排序。
  9. LIMIT / OFFSET:返回最终结果

可以看到窗口函数是在SELECT阶段执行的, 近似看作最终结果集


什么是窗口

如图, 10条SELECT出的数据, 根据分区规则 (department), 同样部门的人视为窗口, 在窗口内的每一行数据, 都能看到窗口范围内的数据. 比如id为4/7/8/5的行, 都能看黑色范围内的数据.

如果定义了排序规则, 则数据还会进行窗口内的排序


语法定义如下



OVER (  
    [PARTITION BY partition_expression, ... ]  
    [ORDER BY sort_expression [ASC | DESC], ...]  
    [ROWS frame_specification]  
    -- or  
    [RANGE frame_specification]  
)

UNBOUNDED PRECEDING:窗口从当前分区的第一行开始。
N PRECEDING:窗口从当前行之前的第N行开始,N是一个正整数。
CURRENT ROW:窗口从当前行开始。
N FOLLOWING:窗口从当前行之后的第N行开始。
UNBOUNDED FOLLOWING:窗口到当前分区的最后一行结束(通常只用于frame_end)。

窗口函数的执行步骤可以这样理解:

  1. 分区(PARTITION BY)如果有 PARTITION BY,就先把整个结果集按照这个字段分区,每一行只看自己分区内的数据。
  2. 排序(ORDER BY)如果有 ORDER BY,就对分区内的数据排序,这影响累计函数、排名函数等的结果。
  3. 计算窗口帧(frame)默认窗口帧是:
    1. 没有 ORDER BY:整个分区。
    2. 有 ORDER BY:从分区开始到当前行( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )。
  4. 执行函数对窗口帧里的数据执行函数计算,比如:
    1. ROW_NUMBER()  → 给分区内每行生成序号
    2. SUM(salary) OVER(PARTITION BY dept ORDER BY salary)  → 计算累积和
    3. AVG(salary) OVER(PARTITION BY dept)  → 计算分区平均



关于ROWS和RANGE的区别

●ROWS → 多少行

●RANGE → 值在多大区间

比如上图, 在 IT 分区内如果根据age排序后


ROWS

select
	name,
	age,
	department,
	avg(age) over (partition by department
				   order by age 
				   rows between 1 preceding and 1 following) as avg_age
from
	employees


|name           |age|department|avg_age      |
|---------------|---|----------|-------------|
|Michael Smith  |45 |Finance   |45           |
|Ashley Wilson  |24 |HR        |24           |
|Sarah Williams |22 |IT        |25.5         |
|David Brown    |29 |IT        |26.6666666667|
|Jessica Davis  |29 |IT        |33           |
|Daniel Miller  |41 |IT        |35           |
|Emily Johnson  |27 |Marketing |30.5         |
|James Carter   |34 |Marketing |30.5         |
|Amanda Moore   |36 |Operations|43           |
|Christopher Lee|50 |Operations|43           |

窗口范围 = 当前行的前一行 + 当前行 + 当前行的后一行

Jessica Davis这行, 可以看到David Brown和Daniel Miller的数据, (29+29+41)/3=33


RANGE


select
	name,
	age,
	department,
	avg(age) over (partition by department
				   order by age 
				   range between 7 preceding and 12 following) as avg_age
from
	employees

|name           |age|department|avg_age      |
|---------------|---|----------|-------------|
|Michael Smith  |45 |Finance   |45           |
|Ashley Wilson  |24 |HR        |24           |
|Sarah Williams |22 |IT        |26.6666666667|
|David Brown    |29 |IT        |30.25        |
|Jessica Davis  |29 |IT        |30.25        |
|Daniel Miller  |41 |IT        |41           |
|Emily Johnson  |27 |Marketing |30.5         |
|James Carter   |34 |Marketing |30.5         |
|Amanda Moore   |36 |Operations|36           |
|Christopher Lee|50 |Operations|50           |

窗口范围 =  当前行的值 - 7 到当前行的值 +12 之间的所有行

Sarah Williams: (22+29+29) / 3 = 26.6666666667

David Brown/Jessica Davis: (22+29+29+41) / 4 = 30.25

Daniel Miller: 之前和之后都没有数据, 所以仍是41



1. 排名类


函数说明特点
ROW_NUMBER()为分区内的每一行分配唯一递增编号(从 1 开始)。不考虑重复值,每行编号都不同。
RANK()为分区内的行排名,相同值排名相同,下一名会跳号。有“并列”。例:1,2,2,4。
DENSE_RANK()类似 RANK,但不会跳号。有“并列”。例:1,2,2,3。
NTILE(n)将分区内的行尽量平均分为 n 组,并返回组号。常用于分位数计算。


2. 聚合类(与普通聚合类似,但不减少行数)


函数说明特点
SUM(expr)计算窗口内的总和。窗口范围由 ROWS/RANGE 控制。且受ORDER BY影响
AVG(expr)计算窗口内的平均值。
COUNT(expr)统计窗口内的行数。
MIN(expr) / MAX(expr)窗口内的最小值 / 最大值。


3. 分析类


函数说明特点
LAG(expr, offset, default)返回当前行之前第 offset 行的值。常用于对比前一行。
LEAD(expr, offset, default)返回当前行之后第 offset 行的值。常用于对比后一行。
FIRST_VALUE(expr)返回窗口内排序后第一行的值。
LAST_VALUE(expr)返回窗口内排序后最后一行的值。
NTH_VALUE(expr, n)返回窗口内第 n 行的值。


示例

1. 查询每个人的年龄与部门平均年龄的差值


select
	name,
	age,
	department,
	avg(age) over (partition by department) - age  as age_value
from
	employees

|name           |age|department|age_value|
|---------------|---|----------|---------|
|Michael Smith  |45 |Finance   |0        |
|Ashley Wilson  |24 |HR        |0        |
|Jessica Davis  |29 |IT        |1.25     |
|Daniel Miller  |41 |IT        |-10.75   |
|David Brown    |29 |IT        |1.25     |
|Sarah Williams |22 |IT        |8.25     |
|Emily Johnson  |27 |Marketing |3.5      |
|James Carter   |34 |Marketing |-3.5     |
|Christopher Lee|50 |Operations|-7       |
|Amanda Moore   |36 |Operations|7        |


2. 给各部门的人按年龄排序


select
	name,
	age,
	department,
	rank() over (partition by department order by age) sort
from
	employees

|name           |age|department|sort|
|---------------|---|----------|----|
|Michael Smith  |45 |Finance   |1   |
|Ashley Wilson  |24 |HR        |1   |
|Sarah Williams |22 |IT        |1   |
|David Brown    |29 |IT        |2   |
|Jessica Davis  |29 |IT        |2   |
|Daniel Miller  |41 |IT        |4   |
|Emily Johnson  |27 |Marketing |1   |
|James Carter   |34 |Marketing |2   |
|Amanda Moore   |36 |Operations|1   |
|Christopher Lee|50 |Operations|2   |




CREATE TABLE public.employees (
	id int4 NOT NULL,
	"name" varchar(100) NOT NULL,
	age int4 NULL,
	department varchar(50) NOT NULL,
	CONSTRAINT employees_pkey PRIMARY KEY (id)
);
INSERT INTO public.employees (id,"name",age,department) VALUES
	 (2,'James Carter',34,'Marketing'),
	 (1,'Emily Johnson',27,'Marketing'),
	 (3,'Michael Smith',45,'Finance'),
	 (4,'Sarah Williams',22,'IT'),
	 (8,'Jessica Davis',29,'IT'),
	 (5,'Daniel Miller',41,'IT'),
	 (6,'Ashley Wilson',24,'HR'),
	 (10,'Christopher Lee',50,'Operations'),
	 (9,'Amanda Moore',36,'Operations'),
	 (7,'David Brown',29,'IT');


窗口函数

SQL 查询的一般逻辑执行顺序FROM:先加载数据源(表或视图),包括任何 JOIN。WHERE:对行进行筛选。GROUP BY:对行进行分组(如果有聚合)。HAVING:对分组结果筛选。SELECT:选择列、计算表达式,同时处理窗口函数。WINDOW FUNCTIONS(OVER 子句):窗口函数
2025-09-09

PostgreSQL bytea与字符串互转

假如name是bytea类型的字段新增时INSERT INTO users (name) VALUES ('zhangsan'::bytea)查询时SELECT CONVERT_FROM(name, 'UTF-8') AS name FROM users
2024-11-04

MySQL隐式转换带来的BUG

问题sqlSELECT IF(o.type = 'A', '优惠', '原价') AS type, SUM(amount) AS sum FROM order o WHERE GROUP BY IF(o.type = 'A', '优惠', '原价')实际type是tinyint 0-优惠 1-原价这
2024-06-01

freemarker 时间显示不正常 设置时区

项目在本地开发的时候显示正常,部署上服务器就一直差8个小时,最后发现freemarker官方文档有这样的说明time_zone:时区的名称来显示并格式化时间。 默认情况下,使用JVM的时区。 也可以是 Java 时区 API 接受的值,或者 "JVM default" (从 FreeMarker 2
2020-03-28
IDEA 2019.1 xml 不高亮

IDEA 2019.1 xml 不高亮

前几天更新了idea后,发现xml里的代码都没有了高亮,变得跟记事本一个德性了打开setting ,搜索 File Types,找到xml项, 查看下方的匹配格式,果然没有xml,(idea真是厉害)点击右方的+,输入*.xml,点击ok,解决问题
2020-03-28

npm install 淘宝镜像

npm install --registry=https://registry.npm.taobao.org
2020-03-28
Java中方法的参数传递机制

Java中方法的参数传递机制

来看一段代码 public class Man { private String name; private Integer age; public String getName() { return name; } publi
2020-03-28
基于自定义注解手写权限控制

基于自定义注解手写权限控制

方法一: AOP 方法二: 拦截器项目结构项目依赖<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-w
2020-03-28

Docker 部署 详细全过程 附代码

Docker 部署本站 全过程环境:CentOS7.61. 安装Docker其他版本CentOS可以参考这个https://help.aliyun.com/document_detail/187598.html查看本机内核版本,内核版本需高于 3.10uname -r 确保 yum 包最新yum u
2020-03-28

SpringBoot 启动普通java工程

引入依赖<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> <version>2.0.9</version> </dependency>
2020-03-28

Vue.js DOM操作

<template> <input type="button" @click="reply($event)" value="回复"> </template> export default { methods: { replyFun(e) {
2020-03-29
CentOS7编译调试OpenJDK12

CentOS7编译调试OpenJDK12

1. 下载源码https://hg.openjdk.java.net/jdk/jdk12点击左侧的browse,再点击zip,就可以下载zip格式的源码压缩包。unzip xxx.zip 解压文件2. 安装jdkyum install java-11-openjdk-devel -y3. 运行con
2020-04-23
编写自己的Spring Boot Starter

编写自己的Spring Boot Starter

1.新建一个maven项目命名规则统一是xxx-spring-boot-starter完整pom.xml<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0"
2020-06-29