Inner Join查询执行缓慢且结果值偏大的技术求助

问题根源 #

• 你的查询同时关联了SalesKPI（按销售员工统计的KPI）和SalesOrderLineItem（订单明细行），这两个表通过中间表关联后会产生笛卡尔积：一个销售员工的KPI记录会和他负责区域下的每一条订单行匹配，导致SUM(SKPI.KPI)和SUM(SOLI.SalePrice)都被重复计算，最终结果远大于实际值。
• 多表全量关联也大幅增加了数据库的计算量，导致查询超时。

修正后的SQL #

采用子查询分别聚合+关联的方式，先单独计算两个指标，再通过共同维度合并，避免笛卡尔积：

WITH SalesAgg AS (
    SELECT 
        R.RegionID,
        C.CountryName,
        S.SegmentName,
        ROUND(SUM(SOLI.SalePrice), 2) AS YearlySales
    FROM 
        Country C
    INNER JOIN Region R ON C.CountryID = R.CountryID
    INNER JOIN Segment S ON R.SegmentID = S.SegmentID
    INNER JOIN SalesRegion SR ON R.RegionID = SR.RegionID
    INNER JOIN SalesOrder SO ON SR.SalesRegionID = SO.SalesRegionID
    INNER JOIN SalesOrderLineItem SOLI ON SO.SalesOrderID = SOLI.SalesOrderID
    GROUP BY 
        R.RegionID, C.CountryName, S.SegmentName
),
KpiAgg AS (
    SELECT 
        R.RegionID,
        C.CountryName,
        S.SegmentName,
        SUM(SKPI.KPI) AS YearlySalesKPI
    FROM 
        Country C
    INNER JOIN Region R ON C.CountryID = R.CountryID
    INNER JOIN Segment S ON R.SegmentID = S.SegmentID
    INNER JOIN SalesRegion SR ON R.RegionID = SR.RegionID
    INNER JOIN SalesPerson SP ON SR.SalesPersonID = SP.SalesPersonID
    INNER JOIN SalesKPI SKPI ON SP.SalesPersonID = SKPI.SalesPersonID
    GROUP BY 
        R.RegionID, C.CountryName, S.SegmentName
)
SELECT 
    sa.RegionID AS Region,
    sa.CountryName AS Country,
    sa.SegmentName AS Segment,
    ka.YearlySalesKPI,
    sa.YearlySales
FROM SalesAgg sa
LEFT JOIN KpiAgg ka 
    ON sa.RegionID = ka.RegionID 
    AND sa.CountryName = ka.CountryName 
    AND sa.SegmentName = ka.SegmentName
ORDER BY 
    sa.CountryName ASC, sa.RegionID ASC;

优化说明 #

1. 拆分聚合逻辑：将销售额和KPI的统计拆分为两个独立的子查询，各自完成聚合后再关联，彻底避免了跨表笛卡尔积。
2. 减少计算量：每个子查询只关联必要的表，数据库无需处理大量中间重复行，查询效率会显著提升。
3. LEFT JOIN兼容维度差异：如果存在某个区域只有销售额数据或只有KPI数据的情况，LEFT JOIN可以保证结果不丢失数据（根据业务需求也可改为INNER JOIN）。

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