SQL技术需求：计算2019-2021年各客户平均账单金额

核心思路 #

• 先构建2019-2021这三个目标年份的维度表，确保每一年都被纳入统计
• 提取所有唯一客户，和年份维度做交叉连接，保证每个客户都对应三年的统计行
• 左连接原始临时表，按客户+年份聚合年度账单总额，无记录的年份用0填充
• 最后按客户计算三年账单的平均值

最终SQL语句 #

WITH Years AS (
    SELECT 2019 AS BillYear UNION ALL
    SELECT 2020 UNION ALL
    SELECT 2021
),
UniqueCustomers AS (
    SELECT DISTINCT CustomerID FROM #TEMP
),
CustomerAnnualBills AS (
    SELECT
        uc.CustomerID,
        y.BillYear,
        -- 无账单记录的年份按0计算
        ISNULL(SUM(t.BillAmount), 0) AS AnnualBillTotal
    FROM UniqueCustomers uc
    CROSS JOIN Years y
    LEFT JOIN #TEMP t
        ON uc.CustomerID = t.CustomerID
        AND YEAR(t.BillDate) = y.BillYear
    GROUP BY uc.CustomerID, y.BillYear
)
SELECT
    CustomerID,
    AVG(AnnualBillTotal) AS AverageBillAmount
FROM CustomerAnnualBills
GROUP BY CustomerID
ORDER BY CustomerID;

关键细节说明 #

• Years CTE 明确列出目标年份，避免遗漏或错误包含其他年份
• UniqueCustomers 确保所有存在于临时表中的客户都被统计，哪怕某一年没有账单
• CROSS JOIN 生成客户与年份的全组合，这是实现无记录年份按0算的核心步骤
• ISNULL(SUM(...), 0) 处理左连接后无匹配数据的情况，把空值转为0
• 最终用AVG()直接计算三年的平均值，自动包含了0值的年份

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