SQL 行转列、列转行与窗口函数：从报表变形到分析计算

在业务系统中，数据表通常按照“便于存储和维护”的方式设计，而报表、对账、经营分析和接口输出往往要求另一种数据形态。

例如：

数据库中按“地区、商品、金额”逐行存储，报表却要求“手机、电脑、配件”分别展示为列；
数据库中已经存在“预算金额、实际金额、预测金额”多个字段，分析时却希望将它们还原成统一的“指标名称、指标值”结构；
既不能破坏原始明细，又需要计算组内排名、累计金额、移动平均、同比环比和前后记录差异。

这三类问题分别对应：

行转列：Pivot
列转行：Unpivot
窗口函数：Window Function

它们并不是三个互不相关的技巧，而是一套完整的数据整形与分析工具链：

flowchart LR
    A[业务明细表<br/>长表 Long Format] -->|行转列 Pivot| B[报表宽表<br/>Wide Format]
    B -->|列转行 Unpivot| C[标准指标明细<br/>Long Format]
    A -->|窗口函数| D[排名/累计/环比/移动计算]
    C -->|窗口函数| D
    D -->|条件聚合或 PIVOT| E[最终报表/接口/看板]

本文以一套统一的销售数据为例，系统讲解三部分内容，并补充 MySQL、PostgreSQL、SQL Server、Oracle 等数据库中的常见实现差异。

一、先理解长表与宽表

1. 长表

长表将不同维度值保存在行中。

例如，下面的数据按“地区 + 商品”逐行记录销售额：

region	product	amount
华东	手机	120000
华东	电脑	80000
华东	配件	30000
华南	手机	90000
华南	电脑	110000

长表的特点是：

表结构稳定；
新增商品时通常只需要新增数据，不需要增加字段；
适合存储、筛选、聚合和进一步分析；
适合数据库规范化设计；
适合 BI、数据仓库和指标平台。

2. 宽表

宽表将某个维度的不同取值展开为多个字段：

region	手机	电脑	配件
华东	120000	80000	30000
华南	90000	110000	0

宽表的特点是：

更接近人类阅读习惯；
适合固定格式报表、Excel 导出和前端表格；
维度值变化时可能需要增加列；
列数容易膨胀；
不适合作为高频变化业务的底层存储模型。

因此，企业系统通常采用：

底层按长表存储，查询层按需要转换为宽表，分析时再根据需要还原成长表。

二、准备示例数据

下面以销售明细表为例。

CREATE TABLE sales_detail (
    id          BIGINT PRIMARY KEY,
    biz_date    DATE           NOT NULL,
    region      VARCHAR(32)    NOT NULL,
    product     VARCHAR(32)    NOT NULL,
    channel     VARCHAR(32)    NOT NULL,
    quantity    INT            NOT NULL,
    amount      DECIMAL(18, 2) NOT NULL
);

示例数据：

INSERT INTO sales_detail
    (id, biz_date, region, product, channel, quantity, amount)
VALUES
    (1, '2026-07-01', '华东', '手机', '线上', 10, 50000.00),
    (2, '2026-07-01', '华东', '电脑', '线下', 5, 40000.00),
    (3, '2026-07-02', '华东', '配件', '线上', 30, 15000.00),
    (4, '2026-07-02', '华南', '手机', '线上', 8, 40000.00),
    (5, '2026-07-03', '华南', '电脑', '线下', 6, 48000.00),
    (6, '2026-07-03', '华南', '配件', '线上', 20, 10000.00),
    (7, '2026-07-04', '华北', '手机', '线下', 7, 35000.00),
    (8, '2026-07-04', '华北', '电脑', '线上', 4, 32000.00);

三、行转列：把维度值展开为字段

1. 行转列的本质

假设原始数据是：

region	product	amount
华东	手机	50000
华东	电脑	40000
华东	配件	15000

目标结果是：

region	phone_amount	computer_amount	accessory_amount
华东	50000	40000	15000

行转列本质上包含两个步骤：

按条件判断当前行属于哪一个目标列；
按分组维度对目标列进行聚合。

flowchart TD
    A[读取销售明细] --> B{product 是什么}
    B -->|手机| C[写入手机金额表达式]
    B -->|电脑| D[写入电脑金额表达式]
    B -->|配件| E[写入配件金额表达式]
    C --> F[按 region 聚合]
    D --> F
    E --> F
    F --> G[输出一行多列]

2. 最通用的写法：条件聚合

条件聚合是跨数据库兼容性最好的行转列方式。

SELECT
    region,
    SUM(CASE WHEN product = '手机' THEN amount ELSE 0 END) AS phone_amount,
    SUM(CASE WHEN product = '电脑' THEN amount ELSE 0 END) AS computer_amount,
    SUM(CASE WHEN product = '配件' THEN amount ELSE 0 END) AS accessory_amount,
    SUM(amount) AS total_amount
FROM sales_detail
GROUP BY region
ORDER BY region;

结果：

region	phone_amount	computer_amount	accessory_amount	total_amount
华东	50000	40000	15000	105000
华南	40000	48000	10000	98000
华北	35000	32000	0	67000

条件聚合可以理解为：

SUM(
    CASE
        WHEN 满足目标列条件 THEN 参与聚合的值
        ELSE 0
    END
)

这里的 CASE 负责“分流”，SUM 负责“汇总”。

3. 行转列不只是 SUM

根据业务目标，可以使用不同的聚合函数。

3.1 统计金额

1	`SUM(CASE WHEN product = '手机' THEN amount ELSE 0 END)`

3.2 统计订单行数

1	`COUNT(CASE WHEN product = '手机' THEN 1 END)`

也可以写成：

1	`SUM(CASE WHEN product = '手机' THEN 1 ELSE 0 END)`

3.3 计算平均值

1	`AVG(CASE WHEN product = '手机' THEN amount END)`

这里通常不要写 ELSE 0。

错误示例：

1	`AVG(CASE WHEN product = '手机' THEN amount ELSE 0 END)`

因为不属于手机的记录也会以 0 参与平均值计算，导致结果被稀释。

正确思路是让无关记录返回 NULL，大多数聚合函数会忽略 NULL：

1	`AVG(CASE WHEN product = '手机' THEN amount ELSE NULL END)`

ELSE NULL 可以省略。

3.4 获取最大值

1	`MAX(CASE WHEN product = '手机' THEN amount END)`

3.5 判断是否存在

1	`MAX(CASE WHEN product = '手机' THEN 1 ELSE 0 END) AS has_phone_sales`

只要存在一条手机销售记录，结果就是 1。

4. 多维度行转列

例如，需要同时按地区统计各商品的线上和线下销售额：

SELECT
    region,
    SUM(
        CASE
            WHEN product = '手机' AND channel = '线上'
            THEN amount
            ELSE 0
        END
    ) AS phone_online_amount,
    SUM(
        CASE
            WHEN product = '手机' AND channel = '线下'
            THEN amount
            ELSE 0
        END
    ) AS phone_offline_amount,
    SUM(
        CASE
            WHEN product = '电脑' AND channel = '线上'
            THEN amount
            ELSE 0
        END
    ) AS computer_online_amount,
    SUM(
        CASE
            WHEN product = '电脑' AND channel = '线下'
            THEN amount
            ELSE 0
        END
    ) AS computer_offline_amount
FROM sales_detail
GROUP BY region;

这种写法简单直接，但维度组合较多时，列数会迅速膨胀。

假设有：

20 个商品分类；
5 个销售渠道；
4 个指标。

最终可能产生：

1	`20 × 5 × 4 = 400 列`

这通常意味着结果已经不适合直接由 SQL 固定展开，应考虑：

返回长表给前端或 BI；
使用动态 SQL；
使用指标表或数据立方体；
在数据仓库中构建专用宽表；
将展示层转换与底层查询解耦。

5. PostgreSQL 的 FILTER 写法

PostgreSQL 支持在聚合函数上使用 FILTER：

SELECT
    region,
    SUM(amount) FILTER (WHERE product = '手机') AS phone_amount,
    SUM(amount) FILTER (WHERE product = '电脑') AS computer_amount,
    SUM(amount) FILTER (WHERE product = '配件') AS accessory_amount
FROM sales_detail
GROUP BY region;

它和条件聚合表达的是同一件事，但通常更容易阅读。

为了避免没有匹配记录时返回 NULL，可以使用 COALESCE：

SELECT
    region,
    COALESCE(
        SUM(amount) FILTER (WHERE product = '手机'),
        0
    ) AS phone_amount
FROM sales_detail
GROUP BY region;

6. SQL Server 的 PIVOT

SQL Server 提供原生 PIVOT：

SELECT
    region,
    COALESCE([手机], 0) AS phone_amount,
    COALESCE([电脑], 0) AS computer_amount,
    COALESCE([配件], 0) AS accessory_amount
FROM (
    SELECT
        region,
        product,
        amount
    FROM sales_detail
) AS source_data
PIVOT (
    SUM(amount)
    FOR product IN ([手机], [电脑], [配件])
) AS pivot_result;

语义可以拆解为：

1
2
3

SUM(amount)          对什么指标聚合
FOR product          哪一个字段的值需要转成列
IN (...)             最终生成哪些固定列

7. Oracle 的 PIVOT

Oracle 也支持原生 PIVOT：

SELECT
    region,
    phone_amount,
    computer_amount,
    accessory_amount
FROM (
    SELECT
        region,
        product,
        amount
    FROM sales_detail
)
PIVOT (
    SUM(amount)
    FOR product IN (
        '手机' AS phone_amount,
        '电脑' AS computer_amount,
        '配件' AS accessory_amount
    )
);

8. 动态行转列

固定列行转列要求提前知道商品类别：

1	`手机、电脑、配件`

如果商品类别是动态变化的，就不能在静态 SQL 中提前写死所有列。

动态行转列通常分为三步：

sequenceDiagram
    participant App as 应用程序
    participant DB as 数据库
    App->>DB: 查询所有需要展开的维度值
    DB-->>App: 手机、电脑、配件……
    App->>App: 生成 CASE/PIVOT 列表达式
    App->>DB: 执行动态 SQL
    DB-->>App: 返回动态宽表

MySQL 中可以使用 GROUP_CONCAT 生成条件聚合表达式：

SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SUM(CASE WHEN product = ',
        QUOTE(product),
        ' THEN amount ELSE 0 END) AS `',
        REPLACE(product, '`', '``'),
        '`'
    )
    ORDER BY product
    SEPARATOR ', '
)
INTO @pivot_columns
FROM sales_detail;

然后拼接完整 SQL：

SET @pivot_sql = CONCAT(
    'SELECT region, ',
    @pivot_columns,
    ' FROM sales_detail GROUP BY region ORDER BY region'
);

PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

动态 SQL 的风险

动态 SQL 必须重点防范：

SQL 注入；
非法字段名；
列名冲突；
列数失控；
SQL 长度超过限制；
GROUP_CONCAT 长度被截断；
查询计划无法稳定复用；
前端无法提前确定返回结构。

不要直接把用户输入拼接成字段名或 SQL 片段。

对于高频在线接口，更推荐返回长表：

[
  {"region": "华东", "product": "手机", "amount": 50000},
  {"region": "华东", "product": "电脑", "amount": 40000}
]

再由前端表格组件完成动态列渲染。

四、列转行：把多个字段还原为统一指标

1. 列转行的本质

假设现有宽表：

CREATE TABLE region_sales_wide (
    region            VARCHAR(32) PRIMARY KEY,
    phone_amount      DECIMAL(18, 2),
    computer_amount   DECIMAL(18, 2),
    accessory_amount  DECIMAL(18, 2)
);

数据如下：

region	phone_amount	computer_amount	accessory_amount
华东	50000	40000	15000
华南	40000	48000	10000

希望转换成：

region	product	amount
华东	手机	50000
华东	电脑	40000
华东	配件	15000

列转行的本质是：

将字段名映射为数据值，将字段值映射为统一的指标值。

flowchart LR
    A[phone_amount] --> D[product=手机<br/>amount=字段值]
    B[computer_amount] --> E[product=电脑<br/>amount=字段值]
    C[accessory_amount] --> F[product=配件<br/>amount=字段值]
    D --> G[统一长表]
    E --> G
    F --> G

2. 最通用的写法：UNION ALL

SELECT
    region,
    '手机' AS product,
    phone_amount AS amount
FROM region_sales_wide

UNION ALL

SELECT
    region,
    '电脑' AS product,
    computer_amount AS amount
FROM region_sales_wide

UNION ALL

SELECT
    region,
    '配件' AS product,
    accessory_amount AS amount
FROM region_sales_wide;

为什么通常使用 UNION ALL，而不是 UNION？

UNION ALL 直接合并结果；
UNION 还会进行去重；
去重通常需要排序或哈希；
列转行本来就需要保留每个指标；
使用 UNION 可能产生不必要的性能损耗，甚至误删业务上合法的重复记录。

3. PostgreSQL：LATERAL + VALUES

PostgreSQL 可以使用 LATERAL 和 VALUES：

SELECT
    t.region,
    v.product,
    v.amount
FROM region_sales_wide AS t
CROSS JOIN LATERAL (
    VALUES
        ('手机', t.phone_amount),
        ('电脑', t.computer_amount),
        ('配件', t.accessory_amount)
) AS v(product, amount);

这种方式只需要扫描一次主表，代码也比多个 UNION ALL 更紧凑。

4. SQL Server：CROSS APPLY + VALUES

SQL Server 可以使用 CROSS APPLY：

SELECT
    t.region,
    v.product,
    v.amount
FROM region_sales_wide AS t
CROSS APPLY (
    VALUES
        ('手机', t.phone_amount),
        ('电脑', t.computer_amount),
        ('配件', t.accessory_amount)
) AS v(product, amount);

5. SQL Server 的 UNPIVOT

SELECT
    region,
    product_column,
    amount
FROM region_sales_wide
UNPIVOT (
    amount
    FOR product_column IN (
        phone_amount,
        computer_amount,
        accessory_amount
    )
) AS unpivot_result;

此时 product_column 的值是原始字段名：

1
2
3

phone_amount
computer_amount
accessory_amount

如果需要中文业务名称，可以再使用 CASE 映射：

SELECT
    region,
    CASE product_column
        WHEN 'phone_amount' THEN '手机'
        WHEN 'computer_amount' THEN '电脑'
        WHEN 'accessory_amount' THEN '配件'
    END AS product,
    amount
FROM region_sales_wide
UNPIVOT (
    amount
    FOR product_column IN (
        phone_amount,
        computer_amount,
        accessory_amount
    )
) AS unpivot_result;

6. Oracle 的 UNPIVOT

SELECT
    region,
    product,
    amount
FROM region_sales_wide
UNPIVOT (
    amount
    FOR product IN (
        phone_amount AS '手机',
        computer_amount AS '电脑',
        accessory_amount AS '配件'
    )
);

7. 列转行时如何处理 NULL

假设：

region	phone_amount	computer_amount
华东	50000	NULL

列转行后是否保留：

1	`华东，电脑，NULL`

取决于业务语义。

场景一：NULL 表示没有发生

可以过滤：

SELECT *
FROM (
    -- 列转行 SQL
) AS unpivot_data
WHERE amount IS NOT NULL;

场景二：NULL 表示金额为零

可以转换：

1	`COALESCE(computer_amount, 0)`

但要注意：

“没有数据”和“数据值为零”不是同一个概念。

在财务、库存、统计分析中，随意将 NULL 转为 0 可能会改变业务含义。

五、行转列与列转行的业务场景

1. 财务报表

原始凭证或指标明细：

organization	period	subject	amount
A 公司	2026-07	主营业务收入	100000
A 公司	2026-07	主营业务成本	60000
A 公司	2026-07	销售费用	10000

行转列后：

organization	period	revenue	cost	selling_expense
A 公司	2026-07	100000	60000	10000

2. 预算、实际、预测统一分析

业务表可能设计为：

department	budget_amount	actual_amount	forecast_amount

分析平台更适合转换成：

department	metric_type	amount
研发部	BUDGET	100000
研发部	ACTUAL	95000
研发部	FORECAST	102000

列转行后，可以统一计算：

差异率；
完成率；
排名；
同比；
环比；
异常阈值。

3. 问卷和动态属性

错误或过度宽表化的设计：

question_1
question_2
question_3
...
question_200

更合理的长表：

respondent_id	question_id	answer
1001	Q1	A
1001	Q2	B

固定模板导出时再进行行转列。

4. 商品规格

宽表：

1	`color、size、weight、material`

长表：

sku_id	attribute_name	attribute_value
10001	color	black
10001	size	XL

当属性高度动态时，长表更有扩展性；当属性固定且高频查询时，适度宽表化可能更高效。

六、窗口函数：不减少明细行的组内计算

1. 窗口函数解决什么问题

普通聚合：

SELECT
    region,
    SUM(amount)
FROM sales_detail
GROUP BY region;

会把多条明细压缩成一条汇总记录。

窗口函数：

SELECT
    id,
    region,
    product,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
    ) AS region_total_amount
FROM sales_detail;

不会减少原始行数，而是在每条明细旁边附加组内汇总值。

结果类似：

id	region	product	amount	region_total_amount
1	华东	手机	50000	105000
2	华东	电脑	40000	105000
3	华东	配件	15000	105000

这是窗口函数与 GROUP BY 最重要的区别：

flowchart TD
    A[多条明细记录] --> B{使用什么计算}
    B -->|GROUP BY| C[多行压缩为一行]
    B -->|窗口函数| D[保留每条明细]
    C --> E[只得到分组汇总]
    D --> F[明细 + 排名/累计/组内指标]

2. 基本语法

窗口函数 OVER (
    PARTITION BY 分区字段
    ORDER BY 排序字段
    ROWS | RANGE | GROUPS 窗口范围
)

完整示例：

SUM(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

各部分含义：

子句	作用
`PARTITION BY`	将结果划分为多个独立计算分区
`ORDER BY`	定义分区内部的计算顺序
`ROWS/RANGE/GROUPS`	定义当前行能够看到的窗口范围
`OVER`	表明函数按窗口规则执行，而不是普通聚合

3. 窗口函数的逻辑执行位置

窗口函数通常在 WHERE、GROUP BY、HAVING 之后计算，在最终 ORDER BY 之前可被结果引用。

flowchart TD
    A[FROM / JOIN] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E[窗口函数]
    E --> F[SELECT]
    F --> G[DISTINCT]
    G --> H[ORDER BY]
    H --> I[LIMIT / FETCH]

因此，不能直接在同一层 WHERE 中使用窗口函数结果：

-- 错误或不被大多数数据库支持
SELECT
    region,
    product,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS rn
FROM sales_detail
WHERE rn <= 3;

正确做法是使用子查询或 CTE：

WITH ranked_sales AS (
    SELECT
        region,
        product,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY amount DESC, id
        ) AS rn
    FROM sales_detail
)
SELECT
    region,
    product,
    amount
FROM ranked_sales
WHERE rn <= 3;

部分分析型数据库支持 QUALIFY，可以直接过滤窗口结果：

SELECT
    region,
    product,
    amount
FROM sales_detail
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY region
    ORDER BY amount DESC, id
) <= 3;

但 MySQL、PostgreSQL、SQL Server 等常见事务型数据库通常仍需要 CTE 或子查询。

七、常用窗口函数详解

1. ROW_NUMBER：组内唯一序号

SELECT
    id,
    region,
    product,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC, id
    ) AS row_num
FROM sales_detail;

特点：

每一行都有唯一序号；
即使金额相同，序号也不同；
适合组内 Top N；
适合去重保留最新一条；
排序字段最好包含稳定的唯一键。

如果只写：

1	`ORDER BY amount DESC`

而多条记录的金额相同，数据库可以按任意顺序分配序号，结果可能不稳定。

更安全：

1	`ORDER BY amount DESC, id ASC`

2. RANK：并列排名并跳号

假设金额为：

1	`100、100、90、80`

RANK() 结果：

1、1、3、4

RANK() OVER (
    PARTITION BY region
    ORDER BY amount DESC
)

3. DENSE_RANK：并列排名不跳号

相同数据的 DENSE_RANK() 结果：

1、1、2、3

DENSE_RANK() OVER (
    PARTITION BY region
    ORDER BY amount DESC
)

三者对比：

金额	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2
80	4	4	3

选择原则：

只需要唯一行号：ROW_NUMBER
并列后需要保留名次空缺：RANK
并列后不希望跳号：DENSE_RANK

4. NTILE：分桶

将数据按金额分成 4 组：

SELECT
    id,
    region,
    amount,
    NTILE(4) OVER (
        ORDER BY amount DESC
    ) AS amount_quartile
FROM sales_detail;

典型用途：

用户价值分层；
销售额四分位；
成绩分档；
风险等级分桶。

需要注意，NTILE(4) 是尽量按行数均分，而不是按数值区间平均切割。

5. SUM OVER：分组总计

SELECT
    id,
    region,
    product,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
    ) AS region_total_amount
FROM sales_detail;

6. 累计求和

SELECT
    id,
    biz_date,
    region,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY biz_date, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_amount
FROM sales_detail;

窗口范围：

1	`从分区第一行，到当前行`

flowchart LR
    A[第1行] --> B[第2行]
    B --> C[第3行]
    C --> D[当前行]
    D -.当前累计窗口.-> A

也可以简写为：

1	`ROWS UNBOUNDED PRECEDING`

但显式写完整范围更容易阅读和维护。

7. 移动平均

先按天聚合销售额，再计算最近 7 个数据日的移动平均：

WITH daily_sales AS (
    SELECT
        biz_date,
        SUM(amount) AS daily_amount
    FROM sales_detail
    GROUP BY biz_date
)
SELECT
    biz_date,
    daily_amount,
    AVG(daily_amount) OVER (
        ORDER BY biz_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7_rows
FROM daily_sales
ORDER BY biz_date;

注意：

1	`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`

表示“当前行及前 6 行”，不一定严格等于自然时间上的 7 天。

如果日期中间存在缺失：

1	`7 月 1 日、7 月 2 日、7 月 10 日`

这里仍然只是 3 行数据。

如果业务要求自然日连续窗口，可以：

先用日历表补齐日期；
再使用 ROWS；
或使用数据库支持的时间范围窗口；
明确区分“7 个记录日”和“最近 7 个自然日”。

8. LAG：获取上一行

SELECT
    biz_date,
    region,
    amount,
    LAG(amount, 1) OVER (
        PARTITION BY region
        ORDER BY biz_date, id
    ) AS previous_amount
FROM sales_detail;

LAG(amount, 1) 表示取当前行之前第 1 行的 amount。

可指定默认值：

1	`LAG(amount, 1, 0)`

9. LEAD：获取下一行

LEAD(amount, 1) OVER (
    PARTITION BY region
    ORDER BY biz_date, id
)

适合：

计算下一次交易时间；
判断状态是否发生变化；
计算相邻区间；
找出会员下一次访问；
分析订单生命周期。

10. 环比计算

先按月汇总：

WITH monthly_sales AS (
    SELECT
        region,
        EXTRACT(YEAR FROM biz_date) AS sales_year,
        EXTRACT(MONTH FROM biz_date) AS sales_month,
        SUM(amount) AS monthly_amount
    FROM sales_detail
    GROUP BY
        region,
        EXTRACT(YEAR FROM biz_date),
        EXTRACT(MONTH FROM biz_date)
),
sales_with_previous AS (
    SELECT
        region,
        sales_year,
        sales_month,
        monthly_amount,
        LAG(monthly_amount) OVER (
            PARTITION BY region
            ORDER BY sales_year, sales_month
        ) AS previous_month_amount
    FROM monthly_sales
)
SELECT
    region,
    sales_year,
    sales_month,
    monthly_amount,
    previous_month_amount,
    CASE
        WHEN previous_month_amount IS NULL
             OR previous_month_amount = 0
        THEN NULL
        ELSE
            (monthly_amount - previous_month_amount)
            / previous_month_amount * 100
    END AS month_over_month_rate
FROM sales_with_previous;

计算公式：

1	`环比增长率 =（本期金额 - 上期金额）/ 上期金额 × 100%`

必须处理：

上期不存在；
上期金额为 0；
金额数据类型；
整数除法；
跨年月份排序；
缺失月份。

在 MySQL 中，月份提取可以使用：

1 2	`YEAR(biz_date) MONTH(biz_date)`

或者：

1	`DATE_FORMAT(biz_date, '%Y-%m')`

但排序时最好保留真正的年份和月份字段，避免字符串格式带来的隐患。

11. FIRST_VALUE 与 LAST_VALUE

获取分区第一笔金额：

FIRST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date, id
)

获取分区最后一笔金额时，要特别小心：

LAST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date, id
)

这段 SQL 的结果可能只是“当前窗口中的最后一行”，很多情况下就是当前行自己。

如果目标是获取整个分区的最后一个值，应显式指定完整窗口：

LAST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
)

这是窗口函数中非常常见的坑。

12. COUNT OVER：总行数与组内行数

SELECT
    id,
    region,
    product,
    COUNT(*) OVER () AS all_row_count,
    COUNT(*) OVER (
        PARTITION BY region
    ) AS region_row_count
FROM sales_detail;

适合在分页查询中同时返回总记录数：

SELECT
    id,
    region,
    product,
    amount,
    COUNT(*) OVER () AS total_count
FROM sales_detail
WHERE biz_date >= '2026-07-01'
ORDER BY id
LIMIT 20 OFFSET 0;

不过对于超大数据集，COUNT(*) OVER() 可能要求数据库处理完整结果集，未必比单独执行计数 SQL 更快。是否使用要结合执行计划和数据量验证。

13. 组内占比

SELECT
    id,
    region,
    product,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
    ) AS region_total_amount,
    amount / NULLIF(
        SUM(amount) OVER (
            PARTITION BY region
        ),
        0
    ) * 100 AS region_amount_rate
FROM sales_detail;

NULLIF(value, 0) 可以避免除零错误：

1	`NULLIF(region_total_amount, 0)`

当总金额为 0 时返回 NULL。

八、ROWS、RANGE 与 GROUPS 的区别

窗口范围是窗口函数最容易被忽视、也最容易出错的部分。

1. ROWS

ROWS 按物理行数计算。

1	`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`

表示：

1	`当前行 + 前两行`

无论排序值是否相同，都按实际行数截取。

2. RANGE

RANGE 按排序值范围或同值组计算。

当多行的 ORDER BY 值相同时，这些行可能被视为同一组同位值。

例如：

amount	ROWS 累计	RANGE 累计
100	100	200
100	200	200
80	280	280

具体语法能力和行为会因数据库版本、排序字段类型而存在差异。

3. GROUPS

GROUPS 按排序同值组计算，而不是按单行或连续值区间计算。

它适合表达：

1	`当前同值组以及前两个同值组`

并非所有数据库版本都支持 GROUPS，使用前需要确认兼容性。

4. 为什么建议显式写 ROWS

下面的写法：

SUM(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date
)

在许多数据库中会使用包含同位值语义的默认窗口范围。

如果同一天存在多条记录，累计结果可能一次性包含同一天所有记录，而不是逐行递增。

为了得到稳定的逐行累计结果，建议：

SUM(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

两个关键点：

使用唯一键作为稳定排序条件；
明确指定 ROWS 窗口范围。

九、窗口函数的经典业务写法

1. 每个地区销售额最高的 3 条记录

WITH ranked_sales AS (
    SELECT
        id,
        region,
        product,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY amount DESC, id
        ) AS rn
    FROM sales_detail
)
SELECT
    id,
    region,
    product,
    amount
FROM ranked_sales
WHERE rn <= 3
ORDER BY region, rn;

2. 每个商品保留最新一条记录

假设商品价格历史表：

CREATE TABLE product_price_history (
    id          BIGINT PRIMARY KEY,
    product_id  BIGINT         NOT NULL,
    price       DECIMAL(18, 2) NOT NULL,
    effective_at TIMESTAMP     NOT NULL
);

查询每个商品最新价格：

WITH latest_price AS (
    SELECT
        id,
        product_id,
        price,
        effective_at,
        ROW_NUMBER() OVER (
            PARTITION BY product_id
            ORDER BY effective_at DESC, id DESC
        ) AS rn
    FROM product_price_history
)
SELECT
    id,
    product_id,
    price,
    effective_at
FROM latest_price
WHERE rn = 1;

这比“先查询最大时间，再回表关联”的写法更直观，也能通过唯一键解决时间相同的歧义。

3. 连续登录区间识别

假设用户每天最多一条登录记录：

WITH login_ordered AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ) AS rn
    FROM user_login
),
login_grouped AS (
    SELECT
        user_id,
        login_date,
        login_date - rn * INTERVAL '1 day' AS group_key
    FROM login_ordered
)
SELECT
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
FROM login_grouped
GROUP BY user_id, group_key;

原理是：

1	`连续日期 - 连续序号 = 相同分组键`

不同数据库的日期运算语法不同：

PostgreSQL 可以使用 INTERVAL；
MySQL 可以使用 DATE_SUB；
SQL Server 可以使用 DATEADD；
Oracle 可以直接进行日期天数运算。

4. 状态变化分段

例如订单状态流水：

order_id	change_time	status
1001	10:00	CREATED
1001	10:05	CREATED
1001	10:10	PAID
1001	10:20	PAID
1001	10:30	SHIPPED

先判断当前状态是否与上一条不同：

WITH status_compare AS (
    SELECT
        order_id,
        change_time,
        status,
        LAG(status) OVER (
            PARTITION BY order_id
            ORDER BY change_time
        ) AS previous_status
    FROM order_status_log
),
status_flag AS (
    SELECT
        order_id,
        change_time,
        status,
        CASE
            WHEN previous_status = status THEN 0
            ELSE 1
        END AS is_new_group
    FROM status_compare
),
status_grouped AS (
    SELECT
        order_id,
        change_time,
        status,
        SUM(is_new_group) OVER (
            PARTITION BY order_id
            ORDER BY change_time
            ROWS UNBOUNDED PRECEDING
        ) AS group_no
    FROM status_flag
)
SELECT
    order_id,
    status,
    MIN(change_time) AS start_time,
    MAX(change_time) AS end_time
FROM status_grouped
GROUP BY order_id, group_no, status;

这是窗口函数中非常典型的“先比较，再累计分组”模式。

十、行转列与窗口函数组合

在真实报表中，经常需要先使用窗口函数计算排名，再将排名结果转成列。

例如，每个地区销售额最高的 3 个商品，输出为：

region	top1_product	top1_amount	top2_product	top2_amount	top3_product	top3_amount

1. 先按地区、商品聚合

WITH product_sales AS (
    SELECT
        region,
        product,
        SUM(amount) AS product_amount
    FROM sales_detail
    GROUP BY region, product
)

2. 使用窗口函数排名

, ranked_product AS (
    SELECT
        region,
        product,
        product_amount,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY product_amount DESC, product
        ) AS rn
    FROM product_sales
)

3. 使用条件聚合完成行转列

WITH product_sales AS (
    SELECT
        region,
        product,
        SUM(amount) AS product_amount
    FROM sales_detail
    GROUP BY region, product
),
ranked_product AS (
    SELECT
        region,
        product,
        product_amount,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY product_amount DESC, product
        ) AS rn
    FROM product_sales
)
SELECT
    region,
    MAX(CASE WHEN rn = 1 THEN product END) AS top1_product,
    MAX(CASE WHEN rn = 1 THEN product_amount END) AS top1_amount,
    MAX(CASE WHEN rn = 2 THEN product END) AS top2_product,
    MAX(CASE WHEN rn = 2 THEN product_amount END) AS top2_amount,
    MAX(CASE WHEN rn = 3 THEN product END) AS top3_product,
    MAX(CASE WHEN rn = 3 THEN product_amount END) AS top3_amount
FROM ranked_product
WHERE rn <= 3
GROUP BY region
ORDER BY region;

整体处理流程：

flowchart LR
    A[销售明细] --> B[按地区和商品聚合]
    B --> C[ROW_NUMBER 组内排名]
    C --> D[保留 Top 3]
    D --> E[CASE 条件聚合]
    E --> F[一地区一行的 Top 3 报表]

这里体现了一个非常重要的查询设计原则：

先计算正确的业务粒度，再排名；先排名，再做展示层行转列。

如果直接对原始明细排名，得到的是“单笔记录排名”，而不是“商品汇总排名”。

十一、列转行与窗口函数组合

假设财务预算表是宽表：

department	budget_amount	actual_amount	forecast_amount
研发部	100000	95000	102000
销售部	200000	220000	210000

希望计算每种指标下各部门的排名。

先列转行：

WITH metric_data AS (
    SELECT
        department,
        'BUDGET' AS metric_type,
        budget_amount AS amount
    FROM department_finance

    UNION ALL

    SELECT
        department,
        'ACTUAL' AS metric_type,
        actual_amount AS amount
    FROM department_finance

    UNION ALL

    SELECT
        department,
        'FORECAST' AS metric_type,
        forecast_amount AS amount
    FROM department_finance
)
SELECT
    department,
    metric_type,
    amount,
    DENSE_RANK() OVER (
        PARTITION BY metric_type
        ORDER BY amount DESC
    ) AS metric_rank
FROM metric_data;

转换后，所有指标都具有统一结构，因此可以复用同一套分析逻辑。

flowchart TD
    A[预算/实际/预测宽表] --> B[列转行]
    B --> C[统一 metric_type + amount]
    C --> D[窗口函数分指标排名]
    D --> E[差异分析/排名/占比/异常识别]

十二、GROUP BY 与窗口函数如何选择

对比项	GROUP BY	窗口函数
是否减少行数	会	不会
是否保留明细	否	是
分组汇总	擅长	支持
组内排名	不直接支持	擅长
累计计算	写法复杂	擅长
前后行比较	不擅长	`LAG/LEAD`
Top N	需要关联或子查询	`ROW_NUMBER/RANK`
报表总计	擅长	可附加到明细
使用位置	聚合阶段	聚合之后的分析阶段

常见组合方式：

WITH aggregated AS (
    SELECT
        region,
        product,
        SUM(amount) AS product_amount
    FROM sales_detail
    GROUP BY region, product
)
SELECT
    region,
    product,
    product_amount,
    RANK() OVER (
        PARTITION BY region
        ORDER BY product_amount DESC
    ) AS product_rank
FROM aggregated;

先 GROUP BY 形成正确粒度，再用窗口函数做组内分析，是企业 SQL 中非常常见的模式。

十三、常见错误与陷阱

1. 在 WHERE 中直接使用窗口别名

错误：

SELECT
    *,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS rn
FROM sales_detail
WHERE rn = 1;

原因：WHERE 执行时，窗口函数结果还没有生成。

解决：使用 CTE 或子查询。

2. 行转列时把 AVG 的 ELSE 写成 0

错误：

1	`AVG(CASE WHEN product = '手机' THEN amount ELSE 0 END)`

这会把无关行当作零值参与平均。

正确：

1	`AVG(CASE WHEN product = '手机' THEN amount END)`

3. 排名没有稳定排序字段

ROW_NUMBER() OVER (
    PARTITION BY region
    ORDER BY amount DESC
)

当金额相同时，结果不确定。

建议：

1	`ORDER BY amount DESC, id`

4. LAST_VALUE 没有指定完整窗口

错误：

LAST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date
)

它可能返回当前行，而不是分区最后一行。

正确：

LAST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY biz_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
)

5. ROWS 被误认为自然时间范围

1	`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`

表示 7 行，不一定是 7 个自然日。

6. 动态行转列列数失控

如果维度有几百或几千个不同值，动态 PIVOT 可能生成几百或几千列。

这通常不是数据库“写法问题”，而是结果模型设计问题。

更合理的方案往往是：

返回长表；
分页查询；
限制可选维度；
使用 BI 工具；
离线构建宽表；
只展开 Top N。

7. 混淆 0 与 NULL

1 2	`0 表示业务值确实为零 NULL 表示未知、不存在或未统计`

报表中显示都可能是 0，但数据库计算语义不同。

8. 先 JOIN 明细再聚合导致金额放大

假设一张订单表关联多条商品明细和多条支付明细：

1
2
3

订单 1
  商品明细 3 条
  支付明细 2 条

直接同时 JOIN 后可能产生：

1	`3 × 2 = 6 行`

再进行 SUM，金额可能重复累计。

正确做法通常是分别聚合到订单粒度，再 JOIN：

WITH item_summary AS (
    SELECT
        order_id,
        SUM(item_amount) AS item_amount
    FROM order_item
    GROUP BY order_id
),
payment_summary AS (
    SELECT
        order_id,
        SUM(payment_amount) AS payment_amount
    FROM order_payment
    GROUP BY order_id
)
SELECT
    o.id,
    i.item_amount,
    p.payment_amount
FROM orders AS o
LEFT JOIN item_summary AS i
    ON i.order_id = o.id
LEFT JOIN payment_summary AS p
    ON p.order_id = o.id;

行转列和窗口函数都不能自动修复错误的数据粒度。

十四、性能优化

1. 先过滤，再转换

不要对全表先行转列，再过滤日期：

-- 不推荐的思路
SELECT *
FROM (
    -- 对全量数据行转列
) AS pivot_data
WHERE ...

应该尽量在源数据阶段缩小范围：

SELECT
    region,
    SUM(CASE WHEN product = '手机' THEN amount ELSE 0 END) AS phone_amount
FROM sales_detail
WHERE biz_date >= '2026-07-01'
  AND biz_date <  '2026-08-01'
GROUP BY region;

2. 先聚合到目标粒度

如果原始表有数亿条明细，而最终只需要“地区 + 商品 + 月份”，可以先聚合：

WITH monthly_product_sales AS (
    SELECT
        region,
        product,
        EXTRACT(YEAR FROM biz_date) AS sales_year,
        EXTRACT(MONTH FROM biz_date) AS sales_month,
        SUM(amount) AS amount
    FROM sales_detail
    WHERE biz_date >= '2026-01-01'
      AND biz_date <  '2027-01-01'
    GROUP BY
        region,
        product,
        EXTRACT(YEAR FROM biz_date),
        EXTRACT(MONTH FROM biz_date)
)
SELECT
    region,
    sales_year,
    sales_month,
    SUM(CASE WHEN product = '手机' THEN amount ELSE 0 END) AS phone_amount,
    SUM(CASE WHEN product = '电脑' THEN amount ELSE 0 END) AS computer_amount
FROM monthly_product_sales
GROUP BY region, sales_year, sales_month;

3. 条件聚合通常优于多次关联

不推荐为每个商品分别聚合再 JOIN：

1
2
3

phone_summary
JOIN computer_summary
JOIN accessory_summary

这种方式可能多次扫描同一张表。

条件聚合通常可以在一次扫描和一次聚合中完成多个指标计算。

4. 窗口函数通常需要排序

下面的窗口：

1 2	`PARTITION BY region ORDER BY biz_date, id`

数据库通常需要按：

1	`region、biz_date、id`

进行组织或排序。

可以评估复合索引：

1 2	`CREATE INDEX idx_sales_region_date_id ON sales_detail(region, biz_date, id);`

但索引是否能消除排序，取决于：

查询过滤条件；
JOIN 顺序；
分区字段；
排序方向；
数据库优化器；
是否还需要读取其他字段；
是否发生聚合；
数据量和选择性。

不能看到 PARTITION BY + ORDER BY 就机械创建索引，必须查看执行计划。

5. 关注排序和临时空间

窗口查询的高成本操作常见于：

Sort；
Window Aggregate；
Temporary Table；
Disk Spill；
Filesort；
大分区内存占用。

应重点观察：

排序数据量；
单个分区最大行数；
是否落盘；
临时表大小；
执行内存；
并行度；
是否重复排序。

6. 复用相同窗口定义

如果多个窗口函数使用相同分区和排序规则，可以使用命名窗口。

MySQL、PostgreSQL 等数据库支持类似写法：

SELECT
    id,
    region,
    biz_date,
    amount,
    SUM(amount) OVER sales_window AS running_amount,
    AVG(amount) OVER sales_window AS running_avg
FROM sales_detail
WINDOW sales_window AS (
    PARTITION BY region
    ORDER BY biz_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

这样可以减少重复代码，也便于数据库识别可复用的排序规则。

7. 避免无意义的大窗口

1	`SUM(amount) OVER ()`

意味着所有结果行属于一个分区。

当数据量非常大时：

所有数据都要参与计算；
分页也未必能提前停止；
可能需要扫描完整结果；
延迟和内存压力可能显著增加。

8. 用 EXPLAIN 验证，而不是凭感觉优化

需要关注：

1 2	`EXPLAIN SELECT ...;`

或数据库支持的实际执行分析命令。

重点检查：

是否扫描了过多数据；
过滤是否尽早生效；
是否发生重复扫描；
是否出现超大排序；
是否使用临时表；
是否发生磁盘溢写；
估算行数是否明显失真；
统计信息是否过期。

十五、数据库实现选择建议

MySQL

优先使用：

行转列：SUM(CASE WHEN ... THEN ... END)
列转行：UNION ALL
窗口函数：MySQL 8.0 的 OVER
动态行转列：应用层拼接或存储过程动态 SQL

MySQL 没有类似 SQL Server 的原生 PIVOT/UNPIVOT 语法，条件聚合通常最实用。

PostgreSQL

优先使用：

条件聚合；
聚合 FILTER；
LATERAL + VALUES 列转行；
完整窗口函数；
复杂分析可结合数组、JSON、CTE；
特定场景可评估 tablefunc 扩展中的 crosstab。

SQL Server

可以使用：

条件聚合；
原生 PIVOT；
原生 UNPIVOT；
CROSS APPLY + VALUES；
窗口函数。

当列固定时，PIVOT 语义清晰；复杂业务条件下，条件聚合往往更灵活。

Oracle

可以使用：

条件聚合；
原生 PIVOT；
原生 UNPIVOT；
分析函数；
KEEP、窗口范围等高级分析能力。

不同数据库和不同版本在窗口范围、日期运算、动态 PIVOT、NULL 处理等方面存在差异，上线前应以目标数据库版本的官方文档和执行计划为准。

十六、应该在哪一层做数据转换

不是所有行转列都必须在数据库中完成。

flowchart TD
    A{结果列是否固定}
    A -->|是| B{数据量是否较大}
    A -->|否| C{是否用于 BI 或动态表格}
    B -->|是| D[数据库条件聚合或 PIVOT]
    B -->|否| E[数据库或应用层均可]
    C -->|是| F[优先返回长表]
    C -->|否| G[应用层生成动态列]
    D --> H[固定报表/导出]
    E --> H
    F --> I[BI/前端完成透视]
    G --> I

适合在数据库做

列固定；
数据量大；
需要减少网络传输；
转换逻辑可以被 SQL 清晰表达；
需要数据库直接输出固定报表；
需要利用数据库聚合能力。

适合在应用层做

列高度动态；
不同用户选择不同指标；
结果量较小；
前端组件天然支持动态列；
需要复杂格式、合并单元格或多层表头；
数据库 SQL 因动态列变得难以维护。

适合在数据仓库或 BI 做

超大规模离线分析；
多维钻取；
大量指标复用；
需要预聚合；
需要统一指标口径；
需要面向分析师自由透视。

十七、实战设计：财务经营分析报表

假设底层有统一指标明细表：

CREATE TABLE finance_metric_detail (
    id              BIGINT PRIMARY KEY,
    accounting_period VARCHAR(16)    NOT NULL,
    organization_id BIGINT           NOT NULL,
    metric_code     VARCHAR(64)      NOT NULL,
    metric_value    DECIMAL(18, 2)   NOT NULL
);

数据：

accounting_period	organization_id	metric_code	metric_value
2026-06	1001	REVENUE	100000
2026-06	1001	COST	60000
2026-07	1001	REVENUE	120000
2026-07	1001	COST	70000

目标：

展示收入、成本、利润；
计算收入环比；
计算组织收入排名；
最终输出固定宽表。

1. 先按业务粒度聚合

WITH metric_summary AS (
    SELECT
        accounting_period,
        organization_id,
        SUM(
            CASE
                WHEN metric_code = 'REVENUE'
                THEN metric_value
                ELSE 0
            END
        ) AS revenue,
        SUM(
            CASE
                WHEN metric_code = 'COST'
                THEN metric_value
                ELSE 0
            END
        ) AS cost
    FROM finance_metric_detail
    GROUP BY accounting_period, organization_id
)

2. 计算派生指标和窗口指标

, metric_analysis AS (
    SELECT
        accounting_period,
        organization_id,
        revenue,
        cost,
        revenue - cost AS profit,
        LAG(revenue) OVER (
            PARTITION BY organization_id
            ORDER BY accounting_period
        ) AS previous_revenue,
        DENSE_RANK() OVER (
            PARTITION BY accounting_period
            ORDER BY revenue DESC
        ) AS revenue_rank
    FROM metric_summary
)

3. 输出最终结果

WITH metric_summary AS (
    SELECT
        accounting_period,
        organization_id,
        SUM(
            CASE
                WHEN metric_code = 'REVENUE'
                THEN metric_value
                ELSE 0
            END
        ) AS revenue,
        SUM(
            CASE
                WHEN metric_code = 'COST'
                THEN metric_value
                ELSE 0
            END
        ) AS cost
    FROM finance_metric_detail
    GROUP BY accounting_period, organization_id
),
metric_analysis AS (
    SELECT
        accounting_period,
        organization_id,
        revenue,
        cost,
        revenue - cost AS profit,
        LAG(revenue) OVER (
            PARTITION BY organization_id
            ORDER BY accounting_period
        ) AS previous_revenue,
        DENSE_RANK() OVER (
            PARTITION BY accounting_period
            ORDER BY revenue DESC
        ) AS revenue_rank
    FROM metric_summary
)
SELECT
    accounting_period,
    organization_id,
    revenue,
    cost,
    profit,
    previous_revenue,
    CASE
        WHEN previous_revenue IS NULL
             OR previous_revenue = 0
        THEN NULL
        ELSE
            (revenue - previous_revenue)
            / previous_revenue * 100
    END AS revenue_mom_rate,
    revenue_rank
FROM metric_analysis
ORDER BY accounting_period, revenue_rank;

数据处理链路：

flowchart LR
    A[财务指标长表] --> B[条件聚合行转列]
    B --> C[收入/成本固定宽表]
    C --> D[计算利润]
    D --> E[LAG 计算上期收入]
    E --> F[计算环比]
    F --> G[DENSE_RANK 组织排名]
    G --> H[经营分析报表]

这种设计的优势是：

底层指标结构统一；
新增指标不一定需要修改表结构；
固定报表可以通过行转列生成；
窗口函数负责跨期间和组内分析；
数据粒度清晰；
业务口径更容易复用。

十八、选型速查表

需求	推荐方案
固定几种状态转成列	条件聚合
SQL Server 固定列透视	`PIVOT`
Oracle 固定列透视	`PIVOT`
MySQL 行转列	`SUM(CASE WHEN ...)`
固定宽表还原成长表	`UNION ALL`
PostgreSQL 列转行	`LATERAL + VALUES`
SQL Server 列转行	`CROSS APPLY` 或 `UNPIVOT`
每组 Top N	`ROW_NUMBER` / `RANK`
组内并列排名	`RANK` / `DENSE_RANK`
累计金额	`SUM() OVER`
前后记录差异	`LAG` / `LEAD`
移动平均	`AVG() OVER` + 窗口范围
最新一条去重	`ROW_NUMBER() ... WHERE rn = 1`
动态列数量较少	动态 SQL
动态列数量很多	返回长表，由 BI 或应用层透视
财务宽表统一分析	先列转行，再使用窗口函数
排名结果生成固定报表	先窗口排名，再条件聚合

十九、总结

行转列、列转行和窗口函数分别解决三个层次的问题。

行转列

负责将维度值展开成字段，适合：

固定报表；
Excel 导出；
多指标横向对比；
状态统计；
财务科目展示。

最通用的写法是：

1	`SUM(CASE WHEN condition THEN value ELSE 0 END)`

列转行

负责将多个字段还原为统一的“指标名称 + 指标值”，适合：

统一指标分析；
预算、实际、预测对比；
数据清洗；
宽表规范化；
动态分析。

最通用的写法是：

1	`UNION ALL`

窗口函数

负责在不丢失明细的前提下进行组内分析，适合：

排名；
累计；
移动平均；
同比环比；
前后行比较；
Top N；
去重；
连续区间识别。

三者组合后，可以形成完整的数据处理链路：

flowchart LR
    A[明细长表] --> B[聚合到正确粒度]
    B --> C[窗口函数分析]
    C --> D[排名/累计/环比/占比]
    D --> E[条件聚合行转列]
    E --> F[固定格式报表]
    F -->|需要再次统一分析| G[列转行]
    G --> C

真正高质量的 SQL，不只是把语法写出来，而是先回答三个问题：

当前数据的正确业务粒度是什么？
结果是更适合长表，还是更适合宽表？
计算需要压缩明细，还是需要保留明细？

只要这三个问题判断正确，行转列、列转行和窗口函数就不再是零散技巧，而会成为一套稳定、可复用的数据建模与分析方法。

数据库 > SQL

#MySQL #PostgreSQL #SQL #行转列 #列转行 #窗口函数 #数据分析

SQL 行转列、列转行与窗口函数：从报表变形到分析计算

https://allendericdalexander.github.io/2026/07/30/db/sql-pivot-unpivot-window-functions/

作者

AtLuoFu

发布于

2026年7月30日

许可协议

企业级短链系统设计：高并发、多域名治理、风控与容灾下一篇