窗口函数 :: 老张开工了

窗口函数是 PostgreSQL 最强大的分析工具之一。它在不改变行数的前提下，为每一行计算一个"窗口"内的聚合值——让你同时看到"行级"和"聚合级"的数据。

窗口函数 vs 普通聚合

普通聚合用 GROUP BY 将多行压缩为一行：

SELECT product, SUM(amount) FROM sales GROUP BY product;
-- 结果：每种商品一行

窗口函数保留原始行数，额外返回聚合值：

SELECT product, amount, SUM(amount) OVER () AS 总计 FROM sales;
-- 结果：每行不变，多一列"总计"

创建练习数据集

-- 月度销售数据
CREATE TABLE monthly_sales (
    id SERIAL PRIMARY KEY,
    product VARCHAR(50),
    category VARCHAR(50),
    month DATE NOT NULL,
    sales_amount NUMERIC(10,2),
    sales_qty INTEGER
);

INSERT INTO monthly_sales (product, category, month, sales_amount, sales_qty) VALUES
    ('笔记本电脑', '电子产品', '2025-01-01', 300000, 50),
    ('笔记本电脑', '电子产品', '2025-02-01', 350000, 58),
    ('笔记本电脑', '电子产品', '2025-03-01', 280000, 46),
    ('手机', '电子产品', '2025-01-01', 450000, 112),
    ('手机', '电子产品', '2025-02-01', 520000, 130),
    ('手机', '电子产品', '2025-03-01', 480000, 120),
    ('耳机', '电子产品', '2025-01-01', 60000, 200),
    ('耳机', '电子产品', '2025-02-01', 55000, 180),
    ('耳机', '电子产品', '2025-03-01', 70000, 240),
    ('运动鞋', '服饰', '2025-01-01', 80000, 135),
    ('运动鞋', '服饰', '2025-02-01', 75000, 125),
    ('运动鞋', '服饰', '2025-03-01', 65000, 108),
    ('T恤', '服饰', '2025-01-01', 30000, 300),
    ('T恤', '服饰', '2025-02-01', 35000, 350),
    ('T恤', '服饰', '2025-03-01', 28000, 280),
    ('咖啡机', '家居', '2025-01-01', 65000, 50),
    ('咖啡机', '家居', '2025-02-01', 55000, 42),
    ('咖啡机', '家居', '2025-03-01', 40000, 30);

排名函数

-- ROW_NUMBER：连续的排名（不并列）
SELECT 
    product,
    month,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS 总排名
FROM monthly_sales;

-- RANK：并列排名（跳过后续序号）
SELECT 
    product,
    month,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS 排名_可并列
FROM monthly_sales;

-- DENSE_RANK：并列排名（不跳过后续序号）
SELECT 
    product,
    month,
    sales_amount,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS 排名_密集
FROM monthly_sales;

三者的区别：

第1名销售额 = 500000，第2名 = 500000，第3名 = 490000
ROW_NUMBER: 1, 2, 3
RANK: 1, 1, 3（跳过 2）
DENSE_RANK: 1, 1, 2（不跳过）

分组排名（PARTITION BY）

-- 每个类别内排名
SELECT 
    category,
    product,
    sales_amount,
    ROW_NUMBER() OVER (
        PARTITION BY category 
        ORDER BY sales_amount DESC
    ) AS 类别内排名
FROM monthly_sales
ORDER BY category, 类别内排名;

-- 找出每个类别销售额最高的产品
SELECT * FROM (
    SELECT 
        product,
        category,
        sales_amount,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY sales_amount DESC
        ) AS rn
    FROM monthly_sales
) ranked
WHERE rn = 1;

聚合窗口函数

-- 销售额占总计的百分比
SELECT DISTINCT product,
    SUM(sales_amount) OVER (PARTITION BY product) AS 产品总销售额,
    SUM(sales_amount) OVER () AS 总计,
    ROUND(
        SUM(sales_amount) OVER (PARTITION BY product) / 
        SUM(sales_amount) OVER () * 100, 2
    ) AS 占比
FROM monthly_sales
ORDER BY 产品总销售额 DESC;

累计和

-- 按日期累计销售额
SELECT 
    product,
    month,
    sales_amount,
    SUM(sales_amount) OVER (
        PARTITION BY product 
        ORDER BY month
    ) AS 累计销售额
FROM monthly_sales
ORDER BY product, month;

移动平均

-- 3 期移动平均（含当前及前两行）
SELECT 
    product,
    month,
    sales_amount,
    ROUND(AVG(sales_amount) OVER (
        PARTITION BY product
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS 3期移动平均
FROM monthly_sales
ORDER BY product, month;

偏移函数

LAG / LEAD

LAG 看前一行，LEAD 看后一行：

-- 本月 vs 上月对比
SELECT 
    product,
    month,
    sales_amount,
    LAG(sales_amount, 1) OVER (
        PARTITION BY product ORDER BY month
    ) AS 上月销售额,
    ROUND(
        (sales_amount - LAG(sales_amount, 1) OVER (
            PARTITION BY product ORDER BY month
        )) / 
        LAG(sales_amount, 1) OVER (
            PARTITION BY product ORDER BY month
        ) * 100, 2
    ) AS 环比增长率
FROM monthly_sales
ORDER BY product, month;

-- FIRST_VALUE / LAST_VALUE：窗口内第一行/最后一行
SELECT 
    product,
    month,
    sales_amount,
    FIRST_VALUE(sales_amount) OVER (
        PARTITION BY product ORDER BY month
    ) AS 首月销售额,
    LAST_VALUE(sales_amount) OVER (
        PARTITION BY product ORDER BY month
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS 末月销售额
FROM monthly_sales
ORDER BY product, month;

⚠️ LAST_VALUE 的默认窗口范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，意味着返回的是当前行的值而非真正的最后一行！必须指定 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

窗口框架（Frame）详解

窗口框架定义了窗口内包含哪些行：

-- ROWS：按物理行数
SUM(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- 当前行 + 前 2 行

-- RANGE：按逻辑值（值相等时合并）
SUM(amount) OVER (ORDER BY salary RANGE BETWEEN 1000 PRECEDING AND 500 FOLLOWING)
-- 当前 salary ± 范围

-- GROUPS：按 PEERS 组
SUM(amount) OVER (ORDER BY date GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
-- 前 1 组 + 当前组 + 后 1 组

ROWS vs RANGE 示例

-- 准备数据
CREATE TABLE scores (name VARCHAR(50), score INTEGER);
INSERT INTO scores VALUES 
    ('张三', 80), ('李四', 80), ('王五', 90), ('赵六', 95);

-- ROWS：物理行
SELECT 
    name, score,
    SUM(score) OVER (ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_累计
FROM scores;

-- RANGE：逻辑值（80 有两个人，他们的范围相同）
SELECT 
    name, score,
    SUM(score) OVER (ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_累计
FROM scores;

NTILE 分桶函数

-- 把数据分成 4 等份
SELECT 
    product,
    sales_amount,
    NTILE(4) OVER (ORDER BY sales_amount DESC) AS 四分位
FROM monthly_sales;

-- 分桶后统计
SELECT 
    NTILE(4) OVER (ORDER BY sales_amount DESC) AS 四分位,
    COUNT(*) AS 产品数,
    ROUND(AVG(sales_amount), 2) AS 平均销售额,
    MIN(sales_amount) AS 最小值,
    MAX(sales_amount) AS 最大值
FROM monthly_sales
ORDER BY 四分位;

实战：销售分析

-- 1. 每个产品相对于其类别平均水平的对比
SELECT 
    product,
    category,
    month,
    sales_amount,
    AVG(sales_amount) OVER (PARTITION BY category) AS 类别均值,
    ROUND(
        (sales_amount - AVG(sales_amount) OVER (PARTITION BY category)) / 
        AVG(sales_amount) OVER (PARTITION BY category) * 100, 2
    ) AS 偏离百分比
FROM monthly_sales;

-- 2. 每个类别内各产品的销售占比
SELECT 
    category,
    product,
    month,
    sales_amount,
    ROUND(
        sales_amount / SUM(sales_amount) OVER (
            PARTITION BY category, month
        ) * 100, 2
    ) AS 月度品类占比
FROM monthly_sales
ORDER BY category, product, month;

-- 3. 产品销售额排名稳定性分析（排名变化）
SELECT 
    product,
    month,
    sales_amount,
    RANK() OVER (PARTITION BY month ORDER BY sales_amount DESC) AS 当月排名,
    LAG(RANK() OVER (PARTITION BY month ORDER BY sales_amount DESC)) OVER (
        PARTITION BY product ORDER BY month
    ) AS 上月排名
FROM monthly_sales;

窗口函数执行顺序

窗口函数在 SQL 中执行顺序靠后，理解这个对排错很重要：

FROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → DISTINCT → ORDER BY → LIMIT

这意味着：

WHERE 过滤后的行才进入窗口函数
窗口函数中不能直接引用 SELECT 列的别名
DISTINCT 在窗口函数之后执行，所以可能导致看起来"重复"的行被消除

小结

窗口函数是 PostgreSQL 中最强大的分析特性之一。通过 OVER、PARTITION BY、ORDER BY 和窗口框架的组合，可以在不改变行级粒度的同时进行复杂的行列分析和计算。

下一篇文章将学习索引与性能优化——让查询在大数据量下依然飞速运行。

Summary: RANK、LAG/LEAD、窗口框架（ROWS/RANGE）与销售分析实战。