基于Northwind数据库按年统计客户采购额与折扣,实现全客户全年度结果输出
原有查询的逻辑缺陷
- 原查询直接将
Customers与全量Orders做CROSS JOIN,没有预先构建「每个客户固定对应3个统计年度」的维度骨架,导致无对应年度下单记录的「客户+年度」组合无法进入结果集 - 后续按
CustomerID、订单年度分组时,仅会对实际存在的订单行做聚合,自然过滤掉没有产生订单的客户-年度组合,最终仅返回234条有实际订单匹配的结果,无法满足全客户覆盖要求
调整后的实现逻辑
- 先搭建固定结果骨架:将全量客户与需要统计的3个固定年度(1996、1997、1998)做CROSS JOIN,直接生成精确91*3=273行的基础行集,每一行唯一对应「一个客户+一个统计年度」,无论该客户当年是否有下单,都会保留对应行
- 预先对实际订单和订单明细做聚合,按「客户ID+订单所属年度」维度,提前计算好每个客户每年的采购总金额、折扣总金额、折后金额,减少后续关联的计算量
- 以第一步生成的客户-年度骨架作为主表,左连接第二步的预聚合统计结果,无匹配订单记录的行对应统计值自动保留为NULL,完全符合统计要求
调整后可直接运行的查询代码
WITH CustomerYearSkeleton AS ( -- 生成全客户*3统计年度的固定结果骨架 SELECT c.CustomerID, y.StatYear FROM Customers c CROSS JOIN (VALUES (1996), (1997), (1998)) AS y(StatYear) ), CustomerYearOrderStats AS ( -- 预聚合客户年度级的订单统计值 SELECT o.CustomerID, YEAR(o.OrderDate) AS OrderYear, SUM(od.UnitPrice * od.Quantity) AS Total_purchase_value, SUM(od.UnitPrice * od.Quantity * od.Discount) AS Total_discount_received, SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS Total_after_discount FROM Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID -- 预先过滤统计范围内的订单,降低计算量 WHERE YEAR(o.OrderDate) IN (1996, 1997, 1998) GROUP BY o.CustomerID, YEAR(o.OrderDate) ) -- 关联骨架与统计值,输出最终结果 SELECT sk.CustomerID, sk.StatYear AS [Year], st.Total_purchase_value, st.Total_discount_received, st.Total_after_discount FROM CustomerYearSkeleton sk LEFT JOIN CustomerYearOrderStats st ON sk.CustomerID = st.CustomerID AND sk.StatYear = st.OrderYear ORDER BY sk.CustomerID, sk.StatYear
效果校验
- 最终返回结果的总行数固定为273行(91个客户 * 3个统计年度)
- 有对应年度下单记录的客户,会展示实际计算得到的采购额、折扣额、折后金额
- 无对应年度下单记录的客户,统计字段自动为NULL,符合需求
内容的提问来源于stack exchange,提问作者dwarfplanet
相关产品推荐
相关产品推荐

