30/60/90天内工作日时段商品日均销量SQL改写需求

问题背景 #

现有SQL代码可正常查询交易明细，需修改为按站点、商品、工作日、时段计算日均销量。遇到的问题：

• 用SUM(ItemQuantity)结合GROUP BY时，因12 OR AFTER字段处理不当报错；
• 统计日期范围内的有效天数时，因每日多笔交易，DISTINCT使用方式错误导致统计不准。

核心需求：先按指定维度汇总总销量，再除以对应工作日在时间范围（30/60/90天）内的有效天数，得到日均销量。期望输出示例：

原查询代码：

select 
Date,
Locations.Description as 'Site',
StockItems.Description as 'Item Description',
ItemQuantity,
CASE
WHEN DATEPART(hour,(Date))<12 THEN 'Before 12PM'
ELSE 'After 12PM'
END AS '12 OR AFTER',
DATENAME(WEEKDAY, (date)) as 'Day'
from Transactions 
left join TransactionsLine on TransactionsLine.TransactionID = Transactions.id
left join StockItems  on TransactionsLine.StockItemID = StockItems.ID
left join Departments on StockItems.DepartmentCode = Departments.Code
left join Locations on  Transactions.Location = Locations.Code
where DATEDIFF(DAY, CONVERT(datetime, Date,11), GETDATE()) <= 30
AND Departments.Description in ('Hot Food') 
and (ISNULL(dbo.TransactionsLine.StockItemID, '') <> '') 
AND (dbo.TransactionsLine.Vol <> - 1) 
AND (ISNULL(dbo.TransactionsLine.Department, '') <> '')

解决方案 #

通过CTE（公共表表达式）拆分逻辑，先统计总销量，再统计有效天数，最后关联计算日均：

WITH TotalSales AS (
    -- 按维度汇总总销量
    SELECT
        Locations.Description AS Site,
        StockItems.Description AS [Item Description],
        DATENAME(WEEKDAY, Transactions.Date) AS Day,
        CASE
            WHEN DATEPART(hour, Transactions.Date) < 12 THEN 'Before 12PM'
            ELSE 'After 12PM'
        END AS [12 OR AFTER],
        SUM(TransactionsLine.ItemQuantity) AS TotalQuantity
    FROM Transactions
    JOIN TransactionsLine ON TransactionsLine.TransactionID = Transactions.id
    JOIN StockItems ON TransactionsLine.StockItemID = StockItems.ID
    JOIN Departments ON StockItems.DepartmentCode = Departments.Code
    JOIN Locations ON Transactions.Location = Locations.Code
    WHERE DATEDIFF(DAY, CONVERT(datetime, Transactions.Date, 11), GETDATE()) <= 30
        AND Departments.Description IN ('Hot Food')
        AND TransactionsLine.StockItemID IS NOT NULL
        AND TransactionsLine.Vol <> -1
        AND TransactionsLine.Department IS NOT NULL
    GROUP BY
        Locations.Description,
        StockItems.Description,
        DATENAME(WEEKDAY, Transactions.Date),
        CASE
            WHEN DATEPART(hour, Transactions.Date) < 12 THEN 'Before 12PM'
            ELSE 'After 12PM'
        END
),
ValidDays AS (
    -- 统计各维度下的有效天数（去重日期）
    SELECT
        Locations.Description AS Site,
        DATENAME(WEEKDAY, Transactions.Date) AS Day,
        CASE
            WHEN DATEPART(hour, Transactions.Date) < 12 THEN 'Before 12PM'
            ELSE 'After 12PM'
        END AS [12 OR AFTER],
        COUNT(DISTINCT CONVERT(date, Transactions.Date)) AS DayCount
    FROM Transactions
    JOIN TransactionsLine ON TransactionsLine.TransactionID = Transactions.id
    JOIN StockItems ON TransactionsLine.StockItemID = StockItems.ID
    JOIN Departments ON StockItems.DepartmentCode = Departments.Code
    JOIN Locations ON Transactions.Location = Locations.Code
    WHERE DATEDIFF(DAY, CONVERT(datetime, Transactions.Date, 11), GETDATE()) <= 30
        AND Departments.Description IN ('Hot Food')
        AND TransactionsLine.StockItemID IS NOT NULL
        AND TransactionsLine.Vol <> -1
        AND TransactionsLine.Department IS NOT NULL
    GROUP BY
        Locations.Description,
        DATENAME(WEEKDAY, Transactions.Date),
        CASE
            WHEN DATEPART(hour, Transactions.Date) < 12 THEN 'Before 12PM'
            ELSE 'After 12PM'
        END
)
-- 关联计算日均销量
SELECT
    ts.Site,
    ts.Day,
    ts.[12 OR AFTER],
    ts.[Item Description],
    ROUND(ts.TotalQuantity / vd.DayCount, 1) AS [Daily Average]
FROM TotalSales ts
JOIN ValidDays vd
    ON ts.Site = vd.Site
    AND ts.Day = vd.Day
    AND ts.[12 OR AFTER] = vd.[12 OR AFTER]
ORDER BY ts.Site, ts.Day, ts.[12 OR AFTER], ts.[Item Description];

关键优化点 #

1. JOIN类型调整：将原LEFT JOIN改为JOIN，因为WHERE条件已过滤TransactionsLine非空值，左联会引入无效行；
2. 有效天数统计：用COUNT(DISTINCT CONVERT(date, Transactions.Date))确保同一日期同一维度下只算1天；
3. GROUP BY规范：所有非聚合字段均纳入GROUP BY，避免函数字段导致的分组错误；
4. 可扩展性：修改DATEDIFF中的天数（30→60/90）即可切换统计周期；
5. 精度控制：用ROUND函数控制日均销量的小数位数，适配业务需求。

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