PostgreSQL教程FG033-PG窗口函数：基础语法与排名/统计场景应用

目录大纲

Part01-基础概念与理论知识
- 1.1 窗口函数的概念与特点
- 1.2 窗口函数的语法结构
Part02-生产环境规划与建议
- 2.1 窗口函数的使用场景
- 2.2 窗口函数的性能考虑
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
- 4.1 排名场景实战
- 4.2 统计分析实战
Part05-风哥经验总结与分享
- 5.1 窗口函数的性能优化
- 5.2 常见问题与解决方案

Part01-基础概念与理论知识

1.1 窗口函数的概念与特点

窗口函数是一种特殊的函数，它能够在不影响查询结果集的情况下，对结果集中的每一行数据执行计算。与聚合函数不同，窗口函数不会将多行数据汇总为一行，而是为每一行返回一个结果。

窗口函数的主要特点：

能够访问结果集中的多行数据，而不仅仅是当前行
不会减少结果集的行数
可以与其他列一起使用
可以指定窗口（即数据范围）进行计算

学习交流加群风哥微信: itpux-com

1.2 窗口函数的语法结构

窗口函数的基本语法：

function_name([expression]) OVER (
[PARTITION BY partition_expression, …]
[ORDER BY sort_expression [ASC | DESC], …]
[ROWS | RANGE BETWEEN frame_start AND frame_end]
)

各部分说明：

function_name：窗口函数名称，如ROW_NUMBER、RANK、DENSE_RANK等
expression：函数参数（如果需要）
PARTITION BY：将结果集分区，窗口函数在每个分区内独立计算
ORDER BY：指定分区内的排序方式
ROWS | RANGE BETWEEN：指定窗口的范围（框架）

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 窗口函数的使用场景

窗口函数适合以下场景：

排名：如销售业绩排名、学生成绩排名
统计分析：如移动平均值、累计求和
比较：如与前一行或后一行比较
分组内计算：如每个部门的最高工资与平均工资
百分比计算：如计算每个值占总和的百分比

使用建议：

合理使用PARTITION BY：根据实际需求选择分区列
正确设置ORDER BY：确保排序方式符合业务需求
注意窗口范围：根据需要设置合适的窗口范围
考虑性能：对于大型数据集，窗口函数可能会影响性能

风哥提示：窗口函数在PostgreSQL 8.4及以上版本中可用，是一种强大的数据分析工具。

2.2 窗口函数的性能考虑

窗口函数的性能影响因素：

数据量：数据量越大，窗口函数的执行时间越长
分区数量：分区数量越多，计算复杂度越高
排序操作：ORDER BY会增加计算开销
窗口范围：复杂的窗口范围定义会增加计算复杂度

性能优化建议：

为PARTITION BY和ORDER BY的列创建索引
限制结果集大小：使用WHERE子句过滤不必要的数据
合理设置窗口范围：只使用必要的窗口范围
避免在窗口函数中使用复杂表达式
考虑使用物化视图：对于频繁的窗口函数查询

更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 排名窗口函数

3.1.1 ROW_NUMBER()

ROW_NUMBER()函数为结果集中的每一行分配一个唯一的序号，从1开始。

— 创建测试表
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
salesperson_id INTEGER,
salesperson_name VARCHAR(100),
sale_date DATE,
amount DECIMAL(10, 2)
);

— 插入测试数据
INSERT INTO fgedu_sales (salesperson_id, salesperson_name, sale_date, amount) VALUES
(1, ‘张三’, ‘2026-04-01’, 10000.00),
(1, ‘张三’, ‘2026-04-02’, 15000.00),
(1, ‘张三’, ‘2026-04-03’, 12000.00),
(2, ‘李四’, ‘2026-04-01’, 8000.00),
(2, ‘李四’, ‘2026-04-02’, 9000.00),
(2, ‘李四’, ‘2026-04-03’, 11000.00),
(3, ‘王五’, ‘2026-04-01’, 12000.00),
(3, ‘王五’, ‘2026-04-02’, 13000.00),
(3, ‘王五’, ‘2026-04-03’, 14000.00);

— 使用ROW_NUMBER()进行排名
SELECT
salesperson_name,
sale_date,
amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS global_rank,
ROW_NUMBER() OVER (PARTITION BY salesperson_name ORDER BY amount DESC) AS personal_rank
FROM fgedu_sales;

3.1.2 RANK()和DENSE_RANK()

RANK()函数为结果集中的每一行分配一个排名，相同值的行具有相同的排名，且会跳过后续排名。DENSE_RANK()函数也为结果集中的每一行分配一个排名，相同值的行具有相同的排名，但不会跳过后续排名。

— 创建测试表
CREATE TABLE fgedu_students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
subject VARCHAR(100),
score INTEGER
);

— 插入测试数据
INSERT INTO fgedu_students (name, subject, score) VALUES
(‘张三’, ‘数学’, 95),
(‘张三’, ‘语文’, 85),
(‘张三’, ‘英语’, 90),
(‘李四’, ‘数学’, 95),
(‘李四’, ‘语文’, 90),
(‘李四’, ‘英语’, 85),
(‘王五’, ‘数学’, 90),
(‘王五’, ‘语文’, 80),
(‘王五’, ‘英语’, 85);

— 使用RANK()和DENSE_RANK()进行排名
SELECT
name,
subject,
score,
RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rank
FROM fgedu_students
ORDER BY subject, score DESC;

from PostgreSQL视频:www.itpux.com

3.2 统计窗口函数

3.2.1 累计统计函数

窗口函数可以用于计算累计值，如累计求和、累计平均值等。

— 累计求和
SELECT
salesperson_name,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS cumulative_sales,
AVG(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS moving_average
FROM fgedu_sales
ORDER BY salesperson_name, sale_date;

— 移动窗口统计
SELECT
salesperson_name,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS two_day_sum,
AVG(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_average
FROM fgedu_sales
ORDER BY salesperson_name, sale_date;

3.2.2 前后值比较函数

窗口函数可以用于获取前一行或后一行的值，方便进行比较。

— 前后值比较
SELECT
salesperson_name,
sale_date,
amount,
LAG(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS previous_amount,
LEAD(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS next_amount,
amount – LAG(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS amount_change
FROM fgedu_sales
ORDER BY salesperson_name, sale_date;

学习交流加群风哥QQ113257174

3.3 窗口函数的高级用法

3.3.1 自定义窗口

可以使用WINDOW子句定义自定义窗口，提高查询的可读性。

— 使用WINDOW子句
SELECT
salesperson_name,
sale_date,
amount,
SUM(amount) OVER sales_window AS cumulative_sales,
AVG(amount) OVER sales_window AS moving_average,
ROW_NUMBER() OVER sales_window AS row_num
FROM fgedu_sales
WINDOW sales_window AS (PARTITION BY salesperson_name ORDER BY sale_date)
ORDER BY salesperson_name, sale_date;

3.3.2 窗口函数与聚合函数结合

窗口函数可以与聚合函数结合使用，实现更复杂的计算。

— 窗口函数与聚合函数结合
SELECT
salesperson_name,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY salesperson_name) AS total_sales,
amount / SUM(amount) OVER (PARTITION BY salesperson_name) * 100 AS percentage_of_total
FROM fgedu_sales
ORDER BY salesperson_name, sale_date;

— 计算每个销售人员的销售额排名和占比
SELECT
salesperson_name,
SUM(amount) AS total_sales,
RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
SUM(amount) / SUM(SUM(amount)) OVER () * 100 AS percentage_of_grand_total
FROM fgedu_sales
GROUP BY salesperson_name
ORDER BY total_sales DESC;

Part04-生产案例与实战讲解

4.1 排名场景实战

4.1.1 销售业绩排名

— 销售业绩排名
SELECT
salesperson_name,
SUM(amount) AS total_sales,
RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_num
FROM fgedu_sales
GROUP BY salesperson_name
ORDER BY total_sales DESC;

— 按月份的销售排名
SELECT
TO_CHAR(sale_date, ‘YYYY-MM’) AS month,
salesperson_name,
SUM(amount) AS monthly_sales,
RANK() OVER (PARTITION BY TO_CHAR(sale_date, ‘YYYY-MM’) ORDER BY SUM(amount) DESC) AS monthly_rank
FROM fgedu_sales
GROUP BY TO_CHAR(sale_date, ‘YYYY-MM’), salesperson_name
ORDER BY month, monthly_rank;

4.1.2 学生成绩排名

— 学生总分排名
SELECT
name,
SUM(score) AS total_score,
RANK() OVER (ORDER BY SUM(score) DESC) AS rank
FROM fgedu_students
GROUP BY name
ORDER BY total_score DESC;

— 各科目成绩排名
SELECT
subject,
name,
score,
RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank
FROM fgedu_students
ORDER BY subject, rank;

风哥提示：在使用排名函数时，应根据实际需求选择合适的排名函数（ROW_NUMBER、RANK或DENSE_RANK）。

4.2 统计分析实战

4.2.1 销售趋势分析

— 销售趋势分析
SELECT
sale_date,
SUM(amount) AS daily_sales,
SUM(SUM(amount)) OVER (ORDER BY sale_date) AS cumulative_sales,
AVG(SUM(amount)) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_average
FROM fgedu_sales
GROUP BY sale_date
ORDER BY sale_date;

— 销售人员业绩趋势
SELECT
salesperson_name,
sale_date,
amount,
SUM(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS cumulative_sales,
AVG(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS average_sales,
MAX(amount) OVER (PARTITION BY salesperson_name ORDER BY sale_date) AS max_sale
FROM fgedu_sales
ORDER BY salesperson_name, sale_date;

4.2.2 库存管理分析

— 创建库存表
CREATE TABLE fgedu_inventory (
id SERIAL PRIMARY KEY,
product_id INTEGER,
product_name VARCHAR(100),
date DATE,
quantity INTEGER,
price DECIMAL(10, 2)
);

— 插入测试数据
INSERT INTO fgedu_inventory (product_id, product_name, date, quantity, price) VALUES
(1, ‘手机’, ‘2026-04-01’, 100, 5999.00),
(1, ‘手机’, ‘2026-04-02’, 95, 5999.00),
(1, ‘手机’, ‘2026-04-03’, 90, 5999.00),
(1, ‘手机’, ‘2026-04-04’, 85, 5999.00),
(2, ‘电脑’, ‘2026-04-01’, 50, 9999.00),
(2, ‘电脑’, ‘2026-04-02’, 48, 9999.00),
(2, ‘电脑’, ‘2026-04-03’, 45, 9999.00),
(2, ‘电脑’, ‘2026-04-04’, 42, 9999.00);

— 库存趋势分析
SELECT
product_name,
date,
quantity,
LAG(quantity) OVER (PARTITION BY product_id ORDER BY date) AS previous_quantity,
quantity – LAG(quantity) OVER (PARTITION BY product_id ORDER BY date) AS quantity_change,
AVG(quantity) OVER (PARTITION BY product_id ORDER BY date) AS average_quantity
FROM fgedu_inventory
ORDER BY product_name, date;