在SQL中处理数据时，我们会经常用到窗口函数和临时表，但用法思路不一样。窗口函数就像“实时计算器”，它不会改动原始数据的行数和结构，能直接在现有数据上，针对某一部分数据（如：按地区分的组）做计算，如：想给每笔销售标上它在本地区的排名，又不想丢掉任何一笔销售记录。这时候，用窗口函数就很合适。临时表则像“临时文件夹”，它用来存中间结果。遇到复杂计算时，如：先筛选出有效订单，再统计每个品类销量，就可以先把筛选结果存进临时表，后面直接用这个表算，不用反复查原始数据，步骤更清楚。由此可见，虽然窗口函数和临时表都是用来处理数据的，但一个是有特殊作用的函数，擅长“在原数据上直接分析”，一个是表，擅长“存中间结果”用来分步算。下面我们一起来梳理梳理窗口函数和临时表，想到哪梳到那。

一、窗口函数

1、窗口函数（Window Functions）基本概念

我们知道，窗口函数是SQL中一种特殊的函数，它能够在保持查询结果集内所有原始行的同时，即：允许你在不破坏原始行结构的情况下，对数据集的特定子集（称为"窗口"）执行计算。这一点，与聚合函数（如：SUM、AVG）不同，聚合函数会将多行数据合并为单行结果，而窗口函数则为每行数据返回一个计算结果，同时保留所有原始行。聚合函数通过GROUP BY将组内多行合并为一行，窗口函数通过PARTITION BY划分“窗口”，但每行仍单独返回结果。
窗口函数基本语法结构如下：

-- 窗口函数基本语法
窗口函数名(参数) OVER(窗口定义)

语法元素解析：

• 窗口函数名：可以是内置的窗口函数（如：ROW_NUMBER、RANK、DENSE_RANK），也可以是聚合函数（如：SUM、AVG、COUNT）用作窗口函数
• 参数：函数所需的输入参数，不同函数有不同的参数要求
• OVER()：关键字，用于定义窗口
• 窗口定义：指定如何划分和排序数据以形成窗口

2、窗口函数OVER()子句解析

OVER()子句是窗口函数核心，它定义了如何将数据集划分为不同窗口，并且可以基于分区、排序或特定的框架来定义窗口。

完整OVER()子句语法

OVER (
    [PARTITION BY 列名1, 列名2, ...]  -- 可选：将数据分区
    [ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...]  -- 可选：在每个分区内排序
    [ROWS | RANGE BETWEEN 起始点 AND 终点]  -- 可选：定义窗口框架
)

语法元素解析：

• PARTITION BY：将整个数据集划分为多个分区（类似GROUP BY），窗口函数在每个分区内独立计算
• ORDER BY：指定每个分区内数据的排序方式，影响依赖顺序的窗口函数（如：RANK、ROW_NUMBER）的结果
• ROWS | RANGE BETWEEN ... AND ...：定义窗口框架，即：每个行的计算窗口包含哪些行
• UNBOUNDED PRECEDING：从分区第一行开始
• CURRENT ROW：当前行
• UNBOUNDED FOLLOWING：到分区最后一行结束
• ROWS：基于物理行数定义范围
• RANGE：基于逻辑范围定义（如：数值范围）
• 常用范围定义：

3、窗口函数的特点

• 保留原始行：与聚合函数不同，窗口函数不会减少结果集中的行数。每条记录都会保留在输出中，但可能会添加新的计算列。
• 灵活的数据分析：支持多种类型计算，包括但不限于聚合计算（如：SUM(), AVG()）、排名计算（如：ROW_NUMBER(), RANK()）、分布计算（如：CUME_DIST(), PERCENT_RANK()）等等。
• 不需要GROUP BY：可以在不对数据进行分组的情况下执行复杂的分析操作。

4、窗口函数应用示例

假设我们有一个销售数据表sales，结构如下：

下面展示多种窗口函数用法：

SELECT
    sale_id,
    product,
    region,
    sale_date,
    amount,
    
    -- 1.不分区，计算所有销售总额
    SUM(amount) OVER() AS total_all_sales,
    
    -- 2.按地区分区，计算每个地区销售总额
    SUM(amount) OVER(PARTITION BY region) AS total_region_sales,
    
    -- 3.按产品分区并按日期排序，计算累计销售额
    SUM(amount) OVER(
        PARTITION BY product 
        ORDER BY sale_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_product_sales,
    
    -- 4.按地区分区并按金额排序，计算每个销售在本地区排名
    RANK() OVER(
        PARTITION BY region 
        ORDER BY amount DESC
    ) AS region_sale_rank,
    
    -- 5.按产品分区，计算每个产品平均销售额
    AVG(amount) OVER(PARTITION BY product) AS avg_product_sales,
    
    -- 6.计算每个销售与上一个销售金额差（按日期排序）
    amount - LAG(amount, 1, 0) OVER(ORDER BY sale_date) AS amount_diff_from_prev,
    
    -- 7.计算移动平均值（包含当前行和前1行、后1行）
    AVG(amount) OVER(
        ORDER BY sale_date 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM sales
ORDER BY region, product, sale_date;

查询结果说明：

• total_all_sales：所有销售记录总金额，每行都显示相同的值
• total_region_sales：每个地区总销售额，同一地区的行显示相同的值
• cumulative_product_sales：按产品分组，按日期排序累计销售额
• region_sale_rank：在每个地区内按销售金额降序排列排名
• avg_product_sales：每个产品平均销售额
• amount_diff_from_prev：当前销售与上一笔销售金额差
• moving_avg：包含当前行和相邻两行的移动平均值

二、临时表

1、临时表定义与用途

临时表（Temporary Tables）是数据库系统提供的一种特殊表结构，用于存储查询过程中的临时数据，核心价值在简化复杂查询逻辑，提升处理效率，并避免对永久表的频繁操作。
在实际应用中，当我们需要处理多层嵌套查询、复杂的数据转换或多步骤计算时。这时候，临时表就可以作为中间数据的"中转站"，暂存中间结果。例如：在生成月度报表时，可能需要先过滤出有效数据，再进行聚合计算，最后与其他表关联。这时，可以用临时表存储过滤后的中间结果，使整个逻辑更清晰，同时减少重复计算。
不同数据库系统创建临时表的语法略有差异，以下是主流数据库的实现方式：

-- MySQL 创建临时表
CREATE TEMPORARY TABLE temp_sales_data (
    sale_id INT,
    product_name VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2)
);

-- PostgreSQL 创建临时表
CREATE TEMPORARY TABLE temp_sales_data (
    sale_id INT,
    product_name VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2)
) ON COMMIT PRESERVE ROWS; -- 事务提交后保留数据

-- SQL Server 创建临时表
CREATE TABLE #temp_sales_data ( -- 使用#前缀标识本地临时表
    sale_id INT,
    product_name VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2)
);

-- Oracle 创建临时表
CREATE GLOBAL TEMPORARY TABLE temp_sales_data (
    sale_id INT,
    product_name VARCHAR2(50),
    sale_date DATE,
    amount NUMBER(10,2)
) ON COMMIT DELETE ROWS; -- 事务提交后删除数据

语法元素解析：

• CREATE TEMPORARY TABLE（MySQL/PostgreSQL）：标准的临时表创建语句
• #前缀（SQL Server）：特殊标识，#表示本地临时表，##表示全局临时表
• GLOBAL TEMPORARY（Oracle）：Oracle中全局临时表声明方式
• ON COMMIT子句：定义事务提交时临时表数据处理策略
• PRESERVE ROWS：保留数据
• DELETE ROWS：删除数据

2、临时表的生命周期

临时表的生命周期与创建它的数据库会话紧密绑定，只在创建它的会话期间存在，这是其临时表与永久表的最大区别。当会话结束（正常断开连接或异常终止）时，临时表会被数据库自动清理，无需手动删除。

以下示例展示的是临时表在会话中的生命周期：

-- 会话1：创建临时表并插入数据
CREATE TEMPORARY TABLE temp_user_sessions (
    session_id VARCHAR(36) PRIMARY KEY,
    user_id INT,
    login_time DATETIME,
    ip_address VARCHAR(45)
);

-- 向临时表插入数据
INSERT INTO temp_user_sessions 
VALUES 
('a1b2c3d4', 1001, '2023-10-01 08:30:00', '192.168.1.1'),
('e5f6g7h8', 1002, '2023-10-01 09:15:00', '192.168.1.2');

-- 查询临时表数据（此时可以正常查询）
SELECT * FROM temp_user_sessions;

-- 会话1结束（断开数据库连接）
-- 临时表temp_user_sessions被自动删除

-- 新会话2：尝试查询已删除的临时表
SELECT * FROM temp_user_sessions; 
-- 结果：会抛出"表不存在"的错误

会话隔离性示例：

-- 会话A创建并插入数据
CREATE TEMPORARY TABLE temp_scores (student_id INT, score INT);
INSERT INTO temp_scores VALUES (1, 90), (2, 85);

-- 会话B尝试访问会话A的临时表
SELECT * FROM temp_scores; 
-- 结果：会抛出"表不存在"的错误（临时表仅对创建它的会话可见）

语法元素解析：

• 会话隔离性：临时表具有严格的会话隔离，其他会话无法访问
• 自动清理机制：无需DROP TABLE语句，数据库会在会话结束时自动清理
• 生命周期控制：
• 会话级临时表：随会话结束而删除（大多数数据库默认）
• 事务级临时表：随事务结束而删除（需显式指定，如：Oracle的ON COMMIT DELETE ROWS）

3、临时表的特点

• 持久化中间结果：临时表允许你将查询的部分结果存储在一个表中，以便后续查询可以使用这个结果。这在处理复杂查询时非常有用，特别是当查询涉及多个步骤时。
• 完整的表功能：临时表具有普通表的所有特性，比如：索引、约束等等（大多数数据库中，临时表支持索引和约束，功能与永久表一致），因此可以对其进行任何你可以对普通表执行的操作。
• 生命周期短：仅存在于创建它的数据库会话中，会话结束后自动删除。

4、临时表应用示例：电商订单数据分析

下面展示如何在复杂查询中使用临时表：

-- 场景：分析2023年第四季度各品类top3畅销商品

-- 1. 创建临时表存储第四季度订单数据
CREATE TEMPORARY TABLE temp_q4_orders (
    order_id INT,
    product_id INT,
    category_id INT,
    product_name VARCHAR(100),
    quantity INT,
    order_date DATE,
    total_amount DECIMAL(12,2)
);

-- 2. 向临时表插入筛选后的数据
INSERT INTO temp_q4_orders
SELECT 
    o.order_id,
    oi.product_id,
    p.category_id,
    p.product_name,
    oi.quantity,
    o.order_date,
    oi.quantity * oi.unit_price AS total_amount
FROM 
    orders o
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
WHERE 
    o.order_date BETWEEN '2023-10-01' AND '2023-12-31'
    AND o.status = 'completed'; -- 只考虑已完成订单

-- 3. 创建第二个临时表存储品类销售汇总
CREATE TEMPORARY TABLE temp_category_sales (
    category_id INT,
    product_id INT,
    product_name VARCHAR(100),
    total_quantity INT,
    total_revenue DECIMAL(12,2)
);

-- 4. 计算各商品销售数据
INSERT INTO temp_category_sales
SELECT 
    category_id,
    product_id,
    product_name,
    SUM(quantity) AS total_quantity,
    SUM(total_amount) AS total_revenue
FROM 
    temp_q4_orders
GROUP BY 
    category_id, product_id, product_name;

-- 5. 使用临时表结果获取最终分析报告
SELECT 
    c.category_name,
    t.product_name,
    t.total_quantity,
    t.total_revenue,
    RANK() OVER (PARTITION BY t.category_id ORDER BY t.total_revenue DESC) AS sales_rank
FROM 
    temp_category_sales t
JOIN 
    categories c ON t.category_id = c.category_id
QUALIFY 
    sales_rank <= 3; -- 获取每个品类的前三名

-- 会话结束后，上述两个临时表自动删除，无需手动清理

以上展示了临时表在复杂数据分析中的应用：通过分步处理，将复杂查询分解为多个简单步骤，每个步骤的结果存储在临时表中，既提高了查询可读性，也可能提升执行效率（数据库可以对临时表进行针对性优化）。

三、窗口函数与临时表的主要区别

总的来说，窗口函数以 “不改变原始数据结构” 为核心优势，擅长实时多维分析；临时表则靠 “持久化中间结果” 简化复杂流程，适合分步处理。实际应用中，二者并非对立，可结合在一起使用（如：临时表存储基础数据，窗口函数做深度分析）。所以，最后来一枚大彩蛋：以下是一个窗口函数与临时表结合使用的具体示例，展示两者如何协同工作解决复杂的数据处理问题。

四、窗口函数和临时表的协同工作示例

假设我们需要分析某电商平台2023年各季度的销售数据，包括每个产品的季度销售额、累计销售额、季度排名以及与上一季度的增长率。

-- 场景：电商平台销售数据分析，需计算各产品季度销售情况、累计销售及增长率

-- 1.创建临时表存储基础销售数据（第一阶段：数据提取与清洗）
CREATE TEMPORARY TABLE temp_quarterly_sales (
    product_id INT,
    product_name VARCHAR(100),
    quarter INT, -- 1-4表示四个季度
    total_sales DECIMAL(12,2), -- 季度销售总额
    sale_count INT -- 销售数量
);

-- 2.向临时表插入预处理数据
INSERT INTO temp_quarterly_sales
SELECT
    p.product_id,
    p.product_name,
    EXTRACT(QUARTER FROM o.order_date) AS quarter,
    SUM(oi.quantity * oi.unit_price) AS total_sales,
    COUNT(DISTINCT o.order_id) AS sale_count
FROM
    products p
JOIN
    order_items oi ON p.product_id = oi.product_id
JOIN
    orders o ON oi.order_id = o.order_id
WHERE
    EXTRACT(YEAR FROM o.order_date) = 2023 -- 只分析2023年数据
    AND o.status = 'completed' -- 只考虑已完成订单
GROUP BY
    p.product_id, p.product_name, EXTRACT(QUARTER FROM o.order_date)
ORDER BY
    p.product_id, quarter;

-- 3.使用窗口函数对临时表数据进行多维度分析（第二阶段：深度分析）
SELECT
    product_id,
    product_name,
    quarter,
    total_sales,
    sale_count,
    -- 计算每个产品的累计销售额（按季度累加）
    SUM(total_sales) OVER(
        PARTITION BY product_id
        ORDER BY quarter
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales,
    -- 计算每个季度内产品的销售额排名
    RANK() OVER(
        PARTITION BY quarter
        ORDER BY total_sales DESC
    ) AS quarterly_rank,
    -- 计算与上一季度相比的销售额增长率
    ROUND(
        (total_sales - LAG(total_sales, 1) OVER(
            PARTITION BY product_id
            ORDER BY quarter
        )) / LAG(total_sales, 1) OVER(
            PARTITION BY product_id
            ORDER BY quarter
        ) * 100, 2
    ) AS growth_rate_percent,
    -- 计算每个产品在全年总销售额中的占比
    total_sales / SUM(total_sales) OVER(PARTITION BY product_id) * 100 AS quarterly_contribution
FROM
    temp_quarterly_sales
ORDER BY
    product_id, quarter;

-- 4.会话结束后，临时表temp_quarterly_sales自动删除

示例解析

这个示例展示了临时表与窗口函数的结合使用：

1、临时表的作用：

• 首先通过temp_quarterly_sales临时表存储预处理后的季度销售数据
• 将复杂的多表连接和过滤操作结果持久化，简化后续分析步骤
• 避免重复计算相同的基础数据，提高查询效率

2、窗口函数的作用：

• 在临时表基础上进行多维度分析，无需再次关联原始表
• 使用SUM() OVER()计算累计销售额，追踪产品全年销售趋势
• 用RANK() OVER()实现季度内的产品排名，识别各季度畅销产品
• 通过LAG()函数计算环比增长率，分析销售波动情况
• 计算每个季度销售额占全年的比例，评估季度贡献度

3、协同优势：

• 临时表解决了"数据预处理和复用"问题
• 窗口函数解决了"在保留原始数据结构的同时进行复杂计算"的问题
• 两者结合使代码逻辑清晰，易于维护和扩展
• 相比纯嵌套查询，这种方式更高效且可读性更强

由此可见，我们用SQL做复杂数据分析时，临时表和窗口函数是好搭档。临时表能暂存中间结果，避免重复计算；窗口函数则能实时算出排名、累计值等。比如：分析销量，先存筛选后的临时表，再用窗口函数算各区域排名，又快又清晰，轻松搞定复杂需求。

阅读原文：原文链接