高级查询：CTE 与窗口函数

高级查询：CTE 与窗口函数

高级查询：CTE 与窗口函数

3.1 公用表表达式（CTE）

公用表表达式（Common Table Expression，CTE）是用 WITH 关键字定义的临时结果集，仅在当前查询中可见。与子查询相比，CTE 具有更好的可读性，并且可以被多次引用。

3.1.1 基本 CTE 语法

-- 基本语法
WITH CTE名称 [(列名1, 列名2, ...)] AS (
    -- CTE 定义查询
    SELECT ...
)
-- 主查询
SELECT * FROM CTE名称;

-- 示例：计算每个部门的薪资统计
WITH DeptSalary AS (
    SELECT
        DepartmentID,
        COUNT(*)         AS HeadCount,
        AVG(Salary)      AS AvgSalary,
        MAX(Salary)      AS MaxSalary,
        MIN(Salary)      AS MinSalary,
        SUM(Salary)      AS TotalSalary
    FROM Employees
    WHERE IsActive = 1
    GROUP BY DepartmentID
)
SELECT
    d.DeptName,
    ds.HeadCount,
    ds.AvgSalary,
    ds.MaxSalary,
    ds.TotalSalary,
    ds.AvgSalary / SUM(ds.AvgSalary) OVER() * 100 AS PctOfTotal  -- 窗口函数结合
FROM   DeptSalary ds
JOIN   Departments d ON ds.DepartmentID = d.DepartmentID
ORDER BY ds.TotalSalary DESC;

3.1.2 多 CTE 链式定义

-- 多个 CTE 顺序定义，后面的可以引用前面的
WITH
-- 第一步：筛选活跃客户
ActiveCustomers AS (
    SELECT CustomerID, CustomerName, CityID
    FROM   Customers
    WHERE  IsActive = 1
),
-- 第二步：计算每个活跃客户的订单统计
CustomerStats AS (
    SELECT
        ac.CustomerID,
        ac.CustomerName,
        ac.CityID,
        COUNT(o.OrderID)     AS OrderCount,
        SUM(o.Amount)        AS TotalAmount,
        MAX(o.OrderDate)     AS LastOrderDate
    FROM   ActiveCustomers ac
    LEFT JOIN Orders o ON ac.CustomerID = o.CustomerID
                      AND o.Status <> 'Cancelled'
    GROUP BY ac.CustomerID, ac.CustomerName, ac.CityID
),
-- 第三步：关联城市并分层
RankedCustomers AS (
    SELECT
        cs.*,
        ci.CityName,
        NTILE(4) OVER (ORDER BY cs.TotalAmount DESC) AS ValueTier
    FROM CustomerStats cs
    LEFT JOIN Cities ci ON cs.CityID = ci.CityID
)
-- 主查询：输出高价值客户
SELECT
    CustomerName,
    CityName,
    OrderCount,
    CAST(TotalAmount AS DECIMAL(12,2)) AS TotalAmount,
    LastOrderDate,
    CASE ValueTier
        WHEN 1 THEN '钻石客户（TOP 25%）'
        WHEN 2 THEN '黄金客户（25-50%）'
        WHEN 3 THEN '白银客户（50-75%）'
        ELSE       '普通客户（后 25%）'
    END AS CustomerGrade
FROM RankedCustomers
ORDER BY TotalAmount DESC;

3.1.3 递归 CTE

递归 CTE 用于处理树形/层级数据（组织架构、商品分类、账户关系等）。

-- 组织架构递归查询
-- 表结构：Employees(EmpID, EmpName, JobTitle, ManagerID, Salary)
WITH OrgTree AS (
    -- 锚点成员（Anchor Member）：起始节点（最高层级）
    SELECT
        EmpID,
        EmpName,
        JobTitle,
        ManagerID,
        Salary,
        0                           AS Level,          -- 层级深度
        CAST(EmpName AS NVARCHAR(500)) AS Path,        -- 层级路径
        CAST(EmpID AS VARCHAR(MAX))    AS IDPath
    FROM Employees
    WHERE ManagerID IS NULL  -- 顶层（无上司）

    UNION ALL

    -- 递归成员（Recursive Member）：引用自身
    SELECT
        e.EmpID,
        e.EmpName,
        e.JobTitle,
        e.ManagerID,
        e.Salary,
        ot.Level + 1,
        CAST(ot.Path + N' → ' + e.EmpName AS NVARCHAR(500)),
        CAST(ot.IDPath + ',' + CAST(e.EmpID AS VARCHAR) AS VARCHAR(MAX))
    FROM Employees e
    INNER JOIN OrgTree ot ON e.ManagerID = ot.EmpID
)
-- OPTION (MAXRECURSION N) 控制最大递归深度，默认100，0=无限制
SELECT
    REPLICATE('  ', Level) + EmpName AS IndentedName,  -- 缩进显示层级
    JobTitle,
    Level,
    Path,
    Salary,
    SUM(Salary) OVER (PARTITION BY Level) AS LevelTotalSalary
FROM OrgTree
ORDER BY IDPath
OPTION (MAXRECURSION 50);

-- 递归 CTE：生成日期序列（实用技巧）
DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate   DATE = '2024-12-31';

WITH DateSeries AS (
    SELECT @StartDate AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt)
    FROM DateSeries
    WHERE dt < @EndDate
)
SELECT
    dt,
    DATENAME(WEEKDAY, dt) AS WeekdayName,
    DATEPART(WEEK, dt)    AS WeekNumber,
    IIF(DATEPART(WEEKDAY, dt) IN (1,7), '周末', '工作日') AS DayType
FROM DateSeries
OPTION (MAXRECURSION 0);

-- 递归 CTE：计算阶乘（演示算法）
WITH Factorial AS (
    SELECT 1 AS n, CAST(1 AS BIGINT) AS fact
    UNION ALL
    SELECT n + 1, CAST(fact * (n + 1) AS BIGINT)
    FROM Factorial
    WHERE n < 20
)
SELECT n, fact AS [n!] FROM Factorial;

3.1.4 CTE 与 UPDATE / DELETE / INSERT

-- CTE 用于 UPDATE（提高可读性）
WITH TopProducts AS (
    SELECT ProductID
    FROM Products
    WHERE CategoryID = 1
      AND StockQty > 100
)
UPDATE p
SET    p.Price = p.Price * 0.95
FROM   Products p
INNER JOIN TopProducts tp ON p.ProductID = tp.ProductID;

-- CTE 用于删除重复行（经典去重技巧）
WITH Duplicates AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerID, OrderDate, Amount
               ORDER BY OrderID
           ) AS RowNum
    FROM Orders
)
DELETE FROM Duplicates WHERE RowNum > 1;
-- 执行前先 SELECT 确认：
-- SELECT * FROM Duplicates WHERE RowNum > 1;

3.2 窗口函数

窗口函数（Window Functions）是 SQL Server 分析类查询的核心。与聚合函数不同，窗口函数不会减少行数，它对每一行计算一个基于”窗口”（相关行集合）的结果。

3.2.1 窗口函数语法解析

函数名() OVER (
    [PARTITION BY 分组列]   -- 定义窗口的分区（可选，不加则整个结果集为一个窗口）
    [ORDER BY 排序列]       -- 定义窗口内的排序（部分函数必须）
    [ROWS/RANGE 框架定义]   -- 定义当前行的计算范围（可选）
)

-- 框架（Frame）常用写法：
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- 从第一行到当前行
-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING            -- 当前行前后各1行
-- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 整个分区
-- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   -- 当前行到最后行

3.2.2 排名函数

-- 准备数据
CREATE TABLE #SalesData (
    SalesPersonID INT,
    SalesPersonName NVARCHAR(50),
    Region NVARCHAR(20),
    Quarter INT,
    SalesAmount DECIMAL(12,2)
);
INSERT INTO #SalesData VALUES
(1,N'张三',N'华北',1,250000),(1,N'张三',N'华北',2,320000),
(2,N'李四',N'华南',1,180000),(2,N'李四',N'华南',2,420000),
(3,N'王五',N'华北',1,320000),(3,N'王五',N'华北',2,320000),
(4,N'赵六',N'华南',1,550000),(4,N'赵六',N'华南',2,280000);

SELECT
    SalesPersonName,
    Region,
    Quarter,
    SalesAmount,
    -- 全局排名（相同值跳号）
    RANK()       OVER (ORDER BY SalesAmount DESC)            AS GlobalRank,
    -- 全局排名（相同值不跳号）
    DENSE_RANK() OVER (ORDER BY SalesAmount DESC)            AS GlobalDenseRank,
    -- 全局唯一排名（ORDER BY 必须唯一，否则结果不确定）
    ROW_NUMBER() OVER (ORDER BY SalesAmount DESC, SalesPersonID) AS GlobalRowNum,
    -- 分区内排名（按Region分组）
    RANK()       OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RegionRank,
    DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RegionDenseRank,
    -- 分桶：按 SalesAmount 排名分4组
    NTILE(4)     OVER (ORDER BY SalesAmount DESC)            AS Quartile
FROM #SalesData
ORDER BY SalesAmount DESC;

ROW_NUMBER vs RANK vs DENSE_RANK 对比：

SalesAmount ROW_NUMBER RANK DENSE_RANK
550000 1 1 1
420000 2 2 2
320000 3 3 3
320000 4 3 3
320000 5 3 3
280000 6 6 4

3.2.3 聚合窗口函数

SELECT
    SalesPersonName,
    Region,
    Quarter,
    SalesAmount,
    -- 分区聚合（按 Region）
    SUM(SalesAmount)  OVER (PARTITION BY Region)                    AS RegionTotal,
    AVG(SalesAmount)  OVER (PARTITION BY Region)                    AS RegionAvg,
    COUNT(*)          OVER (PARTITION BY Region)                    AS RegionCount,
    MIN(SalesAmount)  OVER (PARTITION BY Region)                    AS RegionMin,
    MAX(SalesAmount)  OVER (PARTITION BY Region)                    AS RegionMax,
    -- 占比计算（个人/区域总额）
    CAST(SalesAmount * 100.0 / SUM(SalesAmount) OVER (PARTITION BY Region)
        AS DECIMAL(5,2))                                            AS PctOfRegion,
    -- 累计求和（Running Total），按 Region 分区，按 Quarter 排序
    SUM(SalesAmount)  OVER (
        PARTITION BY Region, SalesPersonID
        ORDER BY Quarter
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                               AS RunningTotal,
    -- 移动平均（滑动窗口：前1行+当前行+后1行，共3行均值）
    AVG(SalesAmount)  OVER (
        PARTITION BY Region
        ORDER BY Quarter
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )                                                               AS MovingAvg3
FROM #SalesData
ORDER BY Region, SalesPersonID, Quarter;

3.2.4 偏移函数：LAG / LEAD / FIRST_VALUE / LAST_VALUE

SELECT
    SalesPersonName,
    Quarter,
    SalesAmount,
    -- LAG：取前 N 行的值（环比分析）
    LAG(SalesAmount, 1, 0) OVER (
        PARTITION BY SalesPersonID
        ORDER BY Quarter
    ) AS PrevQuarterAmount,
    -- 环比增长率
    CASE
        WHEN LAG(SalesAmount, 1) OVER (PARTITION BY SalesPersonID ORDER BY Quarter) = 0
            THEN NULL
        ELSE CAST(
            (SalesAmount - LAG(SalesAmount, 1) OVER (PARTITION BY SalesPersonID ORDER BY Quarter))
            * 100.0
            / LAG(SalesAmount, 1) OVER (PARTITION BY SalesPersonID ORDER BY Quarter)
            AS DECIMAL(5,2))
    END AS QoQ_Growth_Pct,
    -- LEAD：取后 N 行的值（预测分析）
    LEAD(SalesAmount, 1, NULL) OVER (
        PARTITION BY SalesPersonID
        ORDER BY Quarter
    ) AS NextQuarterAmount,
    -- FIRST_VALUE：分区内第一行的值
    FIRST_VALUE(SalesAmount) OVER (
        PARTITION BY SalesPersonID
        ORDER BY Quarter
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS Q1Amount,
    -- LAST_VALUE：分区内最后一行的值
    -- 注意：LAST_VALUE 默认框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    -- 必须指定完整框架才能得到真正的最后一行
    LAST_VALUE(SalesAmount) OVER (
        PARTITION BY SalesPersonID
        ORDER BY Quarter
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastQuarterAmount
FROM #SalesData
ORDER BY SalesPersonID, Quarter;

3.3 数据透视（PIVOT / UNPIVOT）

3.3.1 静态 PIVOT

-- 场景：将季度销售数据从行转列
-- 原始格式：
-- SalesPersonName | Quarter | SalesAmount
-- 张三            | 1       | 250000
-- 张三            | 2       | 320000
-- 目标格式：
-- SalesPersonName | Q1      | Q2      | Q3 | Q4

SELECT SalesPersonName, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
FROM (
    SELECT SalesPersonName, Quarter, SalesAmount
    FROM #SalesData
) AS SourceData
PIVOT (
    SUM(SalesAmount)
    FOR Quarter IN ([1], [2], [3], [4])
) AS PivotTable;

3.3.2 动态 PIVOT（列值不确定时）

-- 当 Quarter 的值是动态的时，用动态 SQL 构建 PIVOT
DECLARE @Columns NVARCHAR(MAX) = '';
DECLARE @SQL     NVARCHAR(MAX) = '';

-- 动态获取所有季度值
SELECT @Columns += ',' + QUOTENAME(Quarter)
FROM (SELECT DISTINCT Quarter FROM #SalesData) AS q
ORDER BY Quarter;

SET @Columns = STUFF(@Columns, 1, 1, '');  -- 去掉第一个逗号

SET @SQL = N'
SELECT SalesPersonName, ' + @Columns + N'
FROM (
    SELECT SalesPersonName, Quarter, SalesAmount
    FROM #SalesData
) AS src
PIVOT (
    SUM(SalesAmount) FOR Quarter IN (' + @Columns + N')
) AS pvt;';

EXEC sp_executesql @SQL;

3.3.3 UNPIVOT：列转行

-- 准备宽表
CREATE TABLE #WideTable (
    ProductName NVARCHAR(50),
    Q1_Sales DECIMAL(12,2),
    Q2_Sales DECIMAL(12,2),
    Q3_Sales DECIMAL(12,2),
    Q4_Sales DECIMAL(12,2)
);

INSERT INTO #WideTable VALUES
(N'Surface Pro', 100000, 120000, 90000, 150000),
(N'Xbox', 80000, 75000, 100000, 200000);

-- UNPIVOT：将 Q1~Q4 四列转回行
SELECT ProductName, Quarter, SalesAmount
FROM   #WideTable
UNPIVOT (
    SalesAmount FOR Quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
) AS UnpivotResult;

-- 用 CROSS APPLY + VALUES 实现（更灵活，不要求类型相同）
SELECT ProductName,
       Quarter,
       SalesAmount
FROM   #WideTable
CROSS APPLY (
    VALUES
        ('Q1', Q1_Sales),
        ('Q2', Q2_Sales),
        ('Q3', Q3_Sales),
        ('Q4', Q4_Sales)
) AS T(Quarter, SalesAmount);

3.4 复杂报表查询实战

3.4.1 销售排行榜（Top N per Group）

-- 每个地区销售额前3名（经典问题）
WITH RankedSales AS (
    SELECT
        SalesPersonName,
        Region,
        SUM(SalesAmount) AS TotalSales,
        ROW_NUMBER() OVER (
            PARTITION BY Region
            ORDER BY SUM(SalesAmount) DESC
        ) AS RegionRank
    FROM #SalesData
    GROUP BY SalesPersonName, Region
)
SELECT Region, RegionRank, SalesPersonName, TotalSales
FROM   RankedSales
WHERE  RegionRank <= 3
ORDER BY Region, RegionRank;

3.4.2 同比/环比分析

-- 月度销售同比（Year over Year）分析
WITH MonthlySales AS (
    SELECT
        YEAR(OrderDate)  AS SalesYear,
        MONTH(OrderDate) AS SalesMonth,
        SUM(Amount)      AS MonthlySales
    FROM Orders
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT
    SalesYear,
    SalesMonth,
    MonthlySales,
    LAG(MonthlySales, 12) OVER (ORDER BY SalesYear, SalesMonth) AS SameMonthLastYear,
    CAST(
        (MonthlySales - LAG(MonthlySales, 12) OVER (ORDER BY SalesYear, SalesMonth))
        * 100.0
        / NULLIF(LAG(MonthlySales, 12) OVER (ORDER BY SalesYear, SalesMonth), 0)
        AS DECIMAL(6,2)
    ) AS YoY_Growth_Pct,
    -- 累计（YTD）
    SUM(MonthlySales) OVER (
        PARTITION BY SalesYear
        ORDER BY SalesMonth
        ROWS UNBOUNDED PRECEDING
    ) AS YTD_Sales
FROM MonthlySales
ORDER BY SalesYear, SalesMonth;

3.4.3 漏斗分析（Funnel Analysis）

-- 电商转化漏斗：浏览→加购→结算→支付
WITH FunnelData AS (
    SELECT
        CAST(EventDate AS DATE) AS EventDay,
        SUM(CASE WHEN EventType = 'View'     THEN 1 ELSE 0 END) AS ViewCount,
        SUM(CASE WHEN EventType = 'AddCart'  THEN 1 ELSE 0 END) AS CartCount,
        SUM(CASE WHEN EventType = 'Checkout' THEN 1 ELSE 0 END) AS CheckoutCount,
        SUM(CASE WHEN EventType = 'Payment'  THEN 1 ELSE 0 END) AS PaymentCount
    FROM UserEvents
    WHERE EventDate >= DATEADD(DAY, -30, GETDATE())
    GROUP BY CAST(EventDate AS DATE)
)
SELECT
    EventDay,
    ViewCount,
    CartCount,
    CheckoutCount,
    PaymentCount,
    -- 各步骤转化率
    CAST(CartCount * 100.0 / NULLIF(ViewCount, 0) AS DECIMAL(5,2))     AS ViewToCart_Pct,
    CAST(CheckoutCount * 100.0 / NULLIF(CartCount, 0) AS DECIMAL(5,2)) AS CartToCheckout_Pct,
    CAST(PaymentCount * 100.0 / NULLIF(CheckoutCount, 0) AS DECIMAL(5,2)) AS CheckoutToPay_Pct,
    -- 整体转化率
    CAST(PaymentCount * 100.0 / NULLIF(ViewCount, 0) AS DECIMAL(5,2))  AS OverallConversion_Pct
FROM FunnelData
ORDER BY EventDay;

3.5 小结

特性 要点
CTE 多级链式；递归处理层级数据；可结合 UPDATE/DELETE
ROW_NUMBER 唯一行号，去重删除的利器
RANK/DENSE_RANK 相同值排名不同：RANK跳号，DENSE_RANK不跳
LAG/LEAD 环比/同比分析必备；默认偏移1行
LAST_VALUE 需显式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
PIVOT 列数已知用静态；列数动态用动态SQL

下一章：索引原理与执行计划优化

文章版权声明：除非注明，否则均为边学边练网络文章，版权归原作者所有
  • 上一篇：
  • 下一篇：

相关阅读

最新文章

热门文章

LinkUp：打破数据孤岛，新一代轻量级企业级数据集成平台深度解析

542 阅读 ，

记一次投影连接网络存储

313 阅读 ，

第6章 数据库集成

61 阅读 ，

本栏目文章

第5章 表单处理与验证
数据库技术2026-01-15
第4章 模板与静态文件
数据库技术2026-01-15
第3章 路由与视图
数据库技术2026-01-15
第2章 环境搭建与开发工具
数据库技术2026-01-15
Redis完整教程
数据库技术2026-01-15
芋道包容网关条件研究
数据库技术2026-01-15