多对多关联下按维度聚合交易数据的SQL优化求助

需求说明 #

需要按维度组合聚合交易数据，每个维度组合对应的金额为交易初始金额乘以各维度项的分配百分比（百分比需转换为小数，如50%即0.5）。现有查询在大数据集下性能极差，新增维度时性能进一步下降，寻求更优实现方案。

表结构 #

交易表（transaction） #

维度表（dimension） #

维度项表（dimensionitem） #

维度项-交易关联表（dimensionitemtotransaction） #

示例数据 #

交易记录 #

维度表数据 #

维度项表数据 #

维度项-交易关联表数据 #

现有查询（性能不佳） #

子查询部分 #

(  
    SELECT distinct  
       tl.transaction_id,  

    dimension_1.id as d1,  
    dimension_1.percentage as d1_percentage,  

    dimension_2.id as d2,  
    dimension_2.percentage as d2_percentage,  

    dimension_3.id as d3,  
    dimension_3.percentage as d3_percentage,  

    tl.amount  

    FROM transaction tl  


left join (  
    select dtt.transaction_id, dtt.percentage, od.id as id  
        from dimensionitemtotransaction dtt  
        join dimensionitem od on dtt.dimension_item_id = od.id  
        where od.dimension_id = 1  
    ) dimension_1 on tl.transaction_id = dimension_1.transaction_id  

left join (  
    select dtt.transaction_id, dtt.percentage, od.id as id  
        from dimensionitemtotransaction dtt  
        join dimensionitem od on dtt.dimension_item_id = od.id  
        where od.dimension_id = 2  
    ) dimension_2 on tl.transaction_id = dimension_2.transaction_id  

left join (  
    select dtt.transaction_id, dtt.percentage, od.id as id  
        from dimensionitemtotransaction dtt  
        join dimensionitem od on dtt.dimension_item_id = od.id  
        where od.dimension_id = 3  
    ) dimension_3 on tl.transaction_id = dimension_3.transaction_id  

) sq

主查询部分 #

select distinct  
 transaction_id, d1, d2, d3,

coalesce(sum(  
    amount * coalesce(d1_percentage, 100) / 100 * coalesce(d2_percentage, 100) / 100 * coalesce(d3_percentage, 100) / 100  
), 0.0) as amount

from
    ( above subquery here ) sq

group by transaction_id, d1, d2, d3;

优化方案 #

方案思路 #

避免多次LEFT JOIN产生大量冗余中间数据，改为先按交易+维度提取各维度项数据，再通过CROSS JOIN组合维度项，最后关联交易表计算金额。这种方式能大幅减少中间数据量，提升查询效率。

优化后SQL #

WITH transaction_dim_data AS (
    SELECT
        dtt.transaction_id,
        od.dimension_id,
        od.id AS dimension_item_id,
        dtt.percentage
    FROM dimensionitemtotransaction dtt
    JOIN dimensionitem od ON dtt.dimension_item_id = od.id
),
d1_data AS (
    SELECT transaction_id, dimension_item_id AS d1, percentage AS d1_percent
    FROM transaction_dim_data
    WHERE dimension_id = 1
),
d2_data AS (
    SELECT transaction_id, dimension_item_id AS d2, percentage AS d2_percent
    FROM transaction_dim_data
    WHERE dimension_id = 2
),
d3_data AS (
    SELECT transaction_id, dimension_item_id AS d3, percentage AS d3_percent
    FROM transaction_dim_data
    WHERE dimension_id = 3
)
SELECT
    t.transaction_id,
    COALESCE(d1.d1, 'N/A') AS d1,
    COALESCE(d2.d2, 'N/A') AS d2,
    COALESCE(d3.d3, 'N/A') AS d3,
    t.amount * 
        COALESCE(d1.d1_percent / 100, 1) * 
        COALESCE(d2.d2_percent / 100, 1) * 
        COALESCE(d3.d3_percent / 100, 1) AS amount
FROM transaction t
LEFT JOIN d1_data d1 ON t.transaction_id = d1.transaction_id
LEFT JOIN d2_data d2 ON t.transaction_id = d2.transaction_id
LEFT JOIN d3_data d3 ON t.transaction_id = d3.transaction_id
-- 若需强制保留所有维度组合（即使某交易无对应维度项），替换为以下关联逻辑：
-- CROSS JOIN d1_data d1
-- CROSS JOIN d2_data d2
-- CROSS JOIN d3_data d3
-- WHERE t.transaction_id = d1.transaction_id 
--   AND t.transaction_id = d2.transaction_id 
--   AND t.transaction_id = d3.transaction_id
ORDER BY t.transaction_id;

额外优化建议 #

• 索引优化：
  • 在dimensionitemtotransaction表上创建复合索引：(transaction_id, dimension_item_id)
  • 在dimensionitem表上创建复合索引：(id, dimension_id)
  • 确保transaction表的transaction_id为主键（已索引）
• 动态维度扩展：新增维度时，只需在CTE中添加对应维度的数据集即可，核心关联逻辑无需修改
• 数据类型优化：将percentage和amount改为DECIMAL类型，避免FLOAT精度丢失问题

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