SQL窗口函数与CTE的最佳组合方式是什么

SQL窗口函数和CTE（公用表表达式）是处理复杂查询场景的两大核心工具，两者结合使用可以大幅简化查询逻辑，提升代码的可维护性。CTE能够将复杂的子查询拆分成可读性更强的临时结果集，窗口函数则可以在不改变原表行数的前提下完成分组内的计算、排名等操作，两者的组合能覆盖绝大多数复杂的数据分析需求。

CTE与窗口函数基础概念

CTE的定义与用法

CTE全称Common Table Expression，也就是公用表表达式，通过WITH关键字定义，本质是一个临时的命名结果集，只在当前查询执行期间有效。它的主要作用是拆分复杂查询逻辑，避免多层嵌套子查询导致的可读性下降。基础语法如下：

-- 定义单个CTE的语法
WITH cte_name AS (
    SELECT column1, column2
    FROM source_table
    WHERE condition
)
SELECT * FROM cte_name;

窗口函数的定义与用法

窗口函数是对一组行进行计算的函数，这组行被称为窗口，窗口函数不会将多行合并成一行，而是为每一行返回一个计算结果。常见的窗口函数包括排名函数ROW_NUMBER()、RANK()，聚合类窗口函数SUM() OVER()、AVG() OVER()等，基础语法如下：

SELECT 
    column1,
    column2,
    window_function() OVER (
        PARTITION BY partition_column 
        ORDER BY order_column
    ) AS window_result
FROM source_table;

两者组合的核心优势

单独使用CTE或者窗口函数都能解决部分问题，但组合使用能发挥更大的价值：

• CTE先对原始数据做预处理，过滤、清洗、关联得到干净的临时结果集，后续窗口函数只需要基于这个干净的结果集计算，逻辑更清晰
• 避免窗口函数的重复计算，如果多个窗口函数需要基于同一个预处理结果，只需要定义一次CTE即可复用
• 复杂的多层窗口计算可以拆分成多个CTE，每一层CTE处理一个计算步骤，可读性远高于单个复杂查询

常见组合使用场景

场景一：分层统计计算

需求：先统计每个部门的月度销售额，再计算每个部门月度销售额的排名，以及部门内月度销售额的累计值。如果不用CTE，需要把部门月度统计的逻辑写两次，用CTE预处理后只需要一次即可。

-- 第一步：CTE预处理，统计每个部门每个月的销售额
WITH dept_month_sales AS (
    SELECT 
        dept_id,
        DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
        SUM(sale_amount) AS total_sales
    FROM sale_record
    GROUP BY dept_id, DATE_FORMAT(sale_date, '%Y-%m')
)
-- 第二步：基于CTE的结果使用窗口函数计算排名和累计值
SELECT 
    dept_id,
    sale_month,
    total_sales,
    -- 部门内按月度销售额排名
    RANK() OVER (PARTITION BY dept_id ORDER BY total_sales DESC) AS dept_sales_rank,
    -- 部门内月度销售额累计值
    SUM(total_sales) OVER (
        PARTITION BY dept_id 
        ORDER BY sale_month
    ) AS dept_cumulative_sales
FROM dept_month_sales
ORDER BY dept_id, sale_month;

场景二：连续数据状态判断

需求：找出用户连续登录天数大于等于3天的用户记录。需要先按用户分组排序登录日期，再用窗口函数计算日期差判断连续性，CTE可以先处理用户的登录日期去重，避免重复数据干扰计算。

-- 第一步：CTE处理用户登录数据，去重得到每个用户每天的登录记录
WITH user_login_distinct AS (
    SELECT 
        user_id,
        DISTINCT DATE(login_time) AS login_date
    FROM user_login_log
)
-- 第二步：窗口函数计算连续登录标记
SELECT 
    user_id,
    login_date,
    -- 计算当前登录日期和前一次登录日期的差值
    DATEDIFF(
        login_date, 
        LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date)
    ) AS date_diff
FROM user_login_distinct;

后续可以基于这个date_diff的结果，进一步统计连续登录天数，逻辑会非常清晰。

场景三：分页查询优化

需求：查询订单表按创建时间倒序的分页数据，同时返回总订单数。传统分页需要两次查询，一次查数据一次查总数，用CTE和窗口函数可以一次查询完成。

WITH order_data AS (
    SELECT 
        order_id,
        user_id,
        order_amount,
        create_time,
        -- 窗口函数计算总订单数，每一行都会带上总条数
        COUNT(*) OVER () AS total_count
    FROM order_table
    ORDER BY create_time DESC
)
SELECT 
    order_id,
    user_id,
    order_amount,
    create_time,
    total_count
FROM order_data
-- 假设查询第2页，每页10条
LIMIT 10 OFFSET 10;

组合使用注意事项

• CTE的递归特性不要和窗口函数混用，递归CTE每一层的结果都是动态生成的，窗口函数基于递归结果计算可能会出现不符合预期的情况
• 如果CTE的结果集非常大，需要注意数据库对CTE的优化策略，部分数据库会将CTE物化，可能会影响查询性能，需要结合实际执行计划调整
• 窗口函数的PARTITION BY和ORDER BY子句要基于CTE输出的字段定义，避免引用原始表的字段导致逻辑错误

在实际开发中，遇到需要多层嵌套子查询、多个窗口函数基于同一个预处理结果、复杂分步计算的场景，都可以优先考虑CTE和窗口函数的组合方案，既能提升代码的可读性，也能减少重复计算，提升查询效率。

SQL窗口函数CTECommon_Table_Expression修改时间：2026-06-16 08:09:37