如何用T-SQL按日期分组计算销售记录占当日总数的百分比

我有一张ProductSales表，结构及测试数据如下。需要编写T-SQL语句，按SaleDate、ProductType、ProductSubType分组统计记录数量，并计算每组数量占当日总记录数的百分比。当前查询计算的是占指定时间范围内所有记录总数的百分比，无法满足按日期分组统计占比的需求，求正确实现方案。

表结构 #

CREATE TABLE ProductSales
(
    Id int IDENTITY(1,1) NOT NULL,
    AccountId int NOT NULL,
    SaleDate date NOT NULL,
    ProductType varchar(50) NOT NULL,
    ProductSubType varchar(50) NOT NULL,
    Amount money NOT NULL,

    CONSTRAINT PK_ProductSales 
        PRIMARY KEY CLUSTERED (Id ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO

测试数据 #

INSERT INTO ProductSales (AccountId, SaleDate, ProductType, ProductSubType, Amount) 
VALUES  (1, CAST(N'2023-11-01' AS Date), N'Books', N'Comics', 12.0000),
        (2, CAST(N'2023-11-01' AS Date), N'Books', N'Comics', 22.0000),
        (3, CAST(N'2023-11-01' AS Date), N'Clothing', N'Polos', 2.0000),
        (4, CAST(N'2023-11-01' AS Date), N'Books', N'Fonics', 18.0000),
        (5, CAST(N'2023-11-02' AS Date), N'Clothing', N'Jackets', 22.0000),
        (5, CAST(N'2023-11-02' AS Date), N'Clothing', N'Polos', 18.0000),
        (2, CAST(N'2023-11-02' AS Date), N'Clothing', N'Jeans', 19.0000),
        (1, CAST(N'2023-11-02' AS Date), N'Clothing', N'Jackets', 187.0000)

期望输出 #

SaleDate    ProductType ProductSubType  Total   Percentage
----------------------------------------------------------
11/1/2023   Books       Comics          2       50.00%
11/1/2023   Books       Fonics          1       25.00%
11/1/2023   Clothing    Polos           1       25.00%

11/2/2023   Clothing    Jackets         2       33.33%
11/2/2023   Clothing    Jeans           1       16.67%
11/2/2023   Clothing    Polos           1       16.67%

当前问题查询 #

DECLARE @startdt date = '2023-11-01', 
        @enddt date = '2023-12-13'

SELECT
    CAST(SaleDate as date) SaleDate, ProductType, ProductSubType, 
    COUNT(1) Total,
    (100.0 * COUNT(1) / (SELECT COUNT(1) FROM ProductSales 
                         WHERE SaleDate BETWEEN @startdt AND @enddt)) AS 'Percentage'
FROM
    ProductSales 
WHERE
    SaleDate BETWEEN @startdt AND @enddt
GROUP BY 
    CAST(SaleDate AS date), ProductType, ProductSubType
ORDER BY
    SaleDate, ProductType

解决方案 #

要实现按日期分组计算占比，核心是获取每日的总记录数，使用窗口函数COUNT(*) OVER (PARTITION BY SaleDate)即可高效实现，无需关联子查询。

正确T-SQL语句 #

DECLARE @startdt date = '2023-11-01', 
        @enddt date = '2023-12-13'

SELECT
    CAST(SaleDate AS date) AS SaleDate,
    ProductType,
    ProductSubType,
    COUNT(1) AS Total,
    -- 计算占比并格式化保留两位小数
    FORMAT(100.0 * COUNT(1) / COUNT(*) OVER (PARTITION BY CAST(SaleDate AS date)), 'N2') + '%' AS Percentage
FROM
    ProductSales
WHERE
    SaleDate BETWEEN @startdt AND @enddt
GROUP BY
    CAST(SaleDate AS date), ProductType, ProductSubType
ORDER BY
    SaleDate, ProductType, ProductSubType

关键说明 #

1. 窗口函数作用：COUNT(*) OVER (PARTITION BY CAST(SaleDate AS date))会为每一行计算出对应日期的总记录数，即当日销售总条数，避免了重复扫描表。
2. 百分比格式化：用分组统计数除以当日总数，乘以100后通过FORMAT函数保留两位小数并拼接百分号，完全匹配期望输出格式。
3. 性能优化：相比子查询关联，窗口函数仅需一次表扫描，数据量越大性能优势越明显。

内容的提问来源于stack exchange，提问作者isakavis