SQL SERVER的PARTITION BY应用场景

SQL SERVER的PARTITION BY关键字说明介绍

PARTITION BY关键字介绍
具体使用场景
- 排名计算
- 累计求和
- 分组求最值
- 分组内百分比计算
- 分组内移动平均计算
- 分组内数据分布统计
- 分组内数据偏移计算
总结

PARTITION BY关键字介绍

在SQL SERVER中，关键字PARTITION BY主要用于窗口函数中，它能将查询结果集按照指定的列或表达式划分成多个分区（组），然后窗口函数会在每个分区内独立地进行计算

通俗来讲就是：它可以把结果集拆分成多个逻辑组，窗口函数会基于这些组来执行操作，而不是对整个结果集进行统一处理。这样就能在每个分区内完成特定的计算，比如排名、求和、求平均值等

具体使用场景

假设存在一个 Sales 表，包含 Region（地区）、Salesperson（销售人员）和 SalesAmount（销售金额）列

排名计算

要在每个地区内为销售人员按销售金额进行排名



SELECT 
    Region,
    Salesperson,
    SalesAmount,
    RANK() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS SalesRank
FROM 
    Sales;
--PARTITION BY Region：将结果集按照 Region 列的值进行分区，每个地区形成一个独立的组。
--ORDER BY SalesAmount DESC：在每个地区分区内，按照 SalesAmount 列的值降序排序。
--RANK()：为每个分区内的销售人员计算排名。

累计求和

若要计算每个地区内销售人员的累计销售金额，可以使用 SUM() 窗口函数

SELECT 
    Region,
    Salesperson,
    SalesAmount,
    SUM(SalesAmount) OVER (PARTITION BY Region ORDER BY Salesperson) AS CumulativeSales
FROM 
    Sales;
    --PARTITION BY Region：按 Region 列的值对结果集进行分区。
	--ORDER BY Salesperson：在每个地区分区内，按照 Salesperson 列的值进行排序。
	--SUM(SalesAmount)：在每个分区内计算累计销售金额

分组求最值

在每个分组中找出最大值或最小值，例如有一个 Products 表，包含 Category（产品类别）、ProductName（产品名称）和 Price（价格）列，要找出每个类别中价格最高的产品信息

SELECT 
    Category,
    ProductName,
    Price
FROM (
    SELECT 
        Category,
        ProductName,
        Price,
        ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Price DESC) AS rn
    FROM 
        Products
) subquery
WHERE 
    rn = 1;
    --这里先使用 PARTITION BY Category 将产品按类别分组，在每个类别分组内按照价格降序排列并为每行分配行号 rn，最后筛选出 rn = 1 的记录，也就是每个类别中价格最高的产品

分组内百分比计算

计算每个分组内某一数值占该组总和的百分比。假设有一个 Orders 表，包含 Region（地区）和 OrderAmount（订单金额）列，要计算每个地区的订单金额占该地区订单总金额的百分比

SELECT 
    Region,
    OrderAmount,
    OrderAmount * 1.0 / SUM(OrderAmount) OVER (PARTITION BY Region) AS Percentage
FROM 
    Orders;
    --PARTITION BY Region 把订单按地区分组，SUM(OrderAmount) OVER (PARTITION BY Region) 计算每个地区的订单总金额，然后用当前订单金额除以该地区总金额得到百分比

分组内移动平均计算

在分组内计算移动平均值，常用于分析数据的趋势。例如有一个 StockPrices 表，包含 StockSymbol（股票代码）、TradeDate（交易日期）和 ClosingPrice（收盘价）列，要计算每个股票最近 3 天的移动平均收盘价。

SELECT 
    StockSymbol,
    TradeDate,
    ClosingPrice,
    AVG(ClosingPrice) OVER (
        PARTITION BY StockSymbol 
        ORDER BY TradeDate 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAverage
FROM 
    StockPrices;
    -- PARTITION BY StockSymbol 按股票代码分组，ORDER BY TradeDate 按交易日期排序，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示在当前行及前两行的范围内计算平均值，这样就得到了每个股票最近 3 天的移动平均收盘价

分组内数据分布统计

统计每个分组内不同数据区间的分布情况。比如有一个 Students 表，包含 Class（班级）和 Score（分数）列，要统计每个班级中不同分数段（如 0 - 59、60 - 79、80 - 100）的学生数量

SELECT 
    Class,
    CASE 
        WHEN Score BETWEEN 0 AND 59 THEN '0 - 59'
        WHEN Score BETWEEN 60 AND 79 THEN '60 - 79'
        WHEN Score BETWEEN 80 AND 100 THEN '80 - 100'
    END AS ScoreRange,
    COUNT(*) OVER (PARTITION BY Class, 
        CASE 
            WHEN Score BETWEEN 0 AND 59 THEN '0 - 59'
            WHEN Score BETWEEN 60 AND 79 THEN '60 - 79'
            WHEN Score BETWEEN 80 AND 100 THEN '80 - 100'
        END
    ) AS StudentCount
FROM 
    Students;
    --先通过 CASE 语句将分数划分成不同区间，然后使用 PARTITION BY Class, ScoreRange 按班级和分数段分组，COUNT(*) 统计每个分组内的学生数量

分组内数据偏移计算

计算每个分组内当前行与前一行或后一行数据的差值等偏移量。例如有一个 SalesData 表，包含 Product（产品）、Month（月份）和 SalesVolume（销售数量）列，要计算每个产品每月销售数量相较于前一个月的增长数量

SELECT 
    Product,
    Month,
    SalesVolume,
    SalesVolume - LAG(SalesVolume) OVER (PARTITION BY Product ORDER BY Month) AS Growth
FROM 
    SalesData;
    --PARTITION BY Product 按产品分组，ORDER BY Month 按月份排序，LAG(SalesVolume) 函数获取当前行前一行的销售数量，用当前行销售数量减去前一行的销售数量得到增长数量