如何通过SQL利用三张聚合表还原原始明细数据？

要从三张A-B、A-C、B-C维度的聚合表还原出A-B-C明细数据，核心是让明细值同时满足三个维度的聚合约束。你之前尝试的简单加减逻辑无法覆盖所有约束，正确的方法是使用迭代比例拟合（IPF），通过逐步调整来满足所有聚合条件。

实现步骤 #

1. 生成所有可能的A-B-C组合（笛卡尔积）
2. 初始化明细值，通过多轮迭代依次调整，使其满足A-B、A-C、B-C的聚合约束，直到收敛

完整SQL代码 #

WITH all_combinations AS (
    -- 生成所有A-B-C的组合
    SELECT DISTINCT t1.ColumnA, t1.ColumnB, t2.ColumnC
    FROM Table1 t1
    CROSS JOIN Table2 t2
    WHERE t1.ColumnA = t2.ColumnA
),
initial AS (
    -- 初始化明细值为1.0
    SELECT ac.ColumnA, ac.ColumnB, ac.ColumnC,
           1.0 AS D
    FROM all_combinations ac
),
-- 第一轮调整：匹配A-B维度的聚合值
adjust_ab1 AS (
    SELECT i.ColumnA, i.ColumnB, i.ColumnC,
           i.D * (t1.ColumnD / SUM(i.D) OVER (PARTITION BY i.ColumnA, i.ColumnB)) AS D
    FROM initial i
    JOIN Table1 t1 ON i.ColumnA = t1.ColumnA AND i.ColumnB = t1.ColumnB
),
-- 第一轮调整：匹配A-C维度的聚合值
adjust_ac1 AS (
    SELECT ab.ColumnA, ab.ColumnB, ab.ColumnC,
           ab.D * (t2.ColumnD / SUM(ab.D) OVER (PARTITION BY ab.ColumnA, ab.ColumnC)) AS D
    FROM adjust_ab1 ab
    JOIN Table2 t2 ON ab.ColumnA = t2.ColumnA AND ab.ColumnC = t2.ColumnC
),
-- 第一轮调整：匹配B-C维度的聚合值
adjust_bc1 AS (
    SELECT ac.ColumnA, ac.ColumnB, ac.ColumnC,
           ac.D * (t3.ColumnD / SUM(ac.D) OVER (PARTITION BY ac.ColumnB, ac.ColumnC)) AS D
    FROM adjust_ac1 ac
    JOIN Table3 t3 ON ac.ColumnB = t3.ColumnB AND ac.ColumnC = t3.ColumnC
),
-- 第二轮调整：再次匹配A-B维度，提升精度
adjust_ab2 AS (
    SELECT bc.ColumnA, bc.ColumnB, bc.ColumnC,
           bc.D * (t1.ColumnD / SUM(bc.D) OVER (PARTITION BY bc.ColumnA, bc.ColumnB)) AS D
    FROM adjust_bc1 bc
    JOIN Table1 t1 ON bc.ColumnA = t1.ColumnA AND bc.ColumnB = t1.ColumnB
),
-- 第二轮调整：再次匹配A-C维度
adjust_ac2 AS (
    SELECT ab.ColumnA, ab.ColumnB, ab.ColumnC,
           ab.D * (t2.ColumnD / SUM(ab.D) OVER (PARTITION BY ab.ColumnA, ab.ColumnC)) AS D
    FROM adjust_ab2 ab
    JOIN Table2 t2 ON ab.ColumnA = t2.ColumnA AND ab.ColumnC = t2.ColumnC
),
-- 第二轮调整：再次匹配B-C维度
adjust_bc2 AS (
    SELECT ac.ColumnA, ac.ColumnB, ac.ColumnC,
           ac.D * (t3.ColumnD / SUM(ac.D) OVER (PARTITION BY ac.ColumnB, ac.ColumnC)) AS D
    FROM adjust_ac2 ac
    JOIN Table3 t3 ON ac.ColumnB = t3.ColumnB AND ac.ColumnC = t3.ColumnC
)
-- 输出最终结果，保留8位小数
SELECT ColumnA, ColumnB, ColumnC, ROUND(D, 8) AS D
FROM adjust_bc2
ORDER BY ColumnA, ColumnB, ColumnC;

代码说明 #

• 每一轮调整都会将当前明细值按对应维度的聚合比例缩放，确保该维度的总和与聚合表一致
• 多轮迭代后，明细值会同时满足三个维度的聚合约束，精度足够时即可停止迭代
• 最终结果的浮点精度可以通过ROUND函数调整

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