多表数据合并与结果对比：账户与基准权重匹配问题

需求与现状 #

• 目标结果集：每个账户、每个日期下的每个SEC_ID仅显示一行，可并排查看账户持仓权重（AP_WEIGHT）与基准持仓权重（BP_WEIGHT）
• 测试数据情况：账户有52个持仓，基准有869个持仓，两者共有47个共同证券，合并后应包含874个SEC_ID
• 此前尝试的问题：
  • 使用FULL OUTER JOIN直接关联账户和基准持仓表，生成了45000+行（52×869），是所有持仓的笛卡尔积组合，不符合需求
  • 使用UNION合并账户和基准数据时，同时存在于两者的SEC_ID会显示为两行，无法合并成一行

解决方案 #

核心是通过FULL OUTER JOIN按账户ID、日期、SEC_ID关联账户持仓和基准持仓，并用COALESCE合并两边的相同字段，确保共同SEC_ID只保留一行，同时保留两边独有的证券数据。

修改后的SQL代码 #

declare @BeginDate DATE = '2025-01-03';
declare @EndDate DATE = '2025-01-03';

DROP TABLE IF EXISTS #TempResults;

WITH BUSINESS_DATES AS (
    SELECT
        CONVERT(date, DATE) AS DATE_AS_OF
    FROM
        holdings.dbo.dates_us_trading D
    WHERE
        DATE BETWEEN @BeginDate AND @EndDate
)
SELECT 
    D.ACCT_ID,
    D.ACCT_SHORTNAME,
    D.ACCT_NAME,
    CONVERT(date,D.INCEPTION_DATE) as INCEPTION_DATE,
    D.BENCHMARK,
    L.ID as BENCHMARK_ID,
    BD.DATE_AS_OF
INTO #TempResults
FROM 
    BUSINESS_DATES BD
    CROSS JOIN VWACCOUNT_DETAILS D
    JOIN LKP_LOOKUPDETAIL L on D.BENCHMARK = L.DESCRIPTION
WHERE
    D.ACCT_ID = 123456

-- 核心：按账户、日期、SEC_ID做FULL JOIN，合并共同SEC_ID
SELECT 
    TR.*,
    COALESCE(AP.SEC_ID, BP.SEC_ID) AS SEC_ID,
    AP.MKT_PCT AS AP_WEIGHT,
    BP.WEIGHT AS BP_WEIGHT
FROM 
    #TempResults TR
    -- 先关联账户持仓
    LEFT JOIN ACCT_POSITION AP 
        ON AP.ACCT_ID = TR.ACCT_ID 
        AND AP.DATE_AS_OF = TR.DATE_AS_OF
    -- 再按SEC_ID FULL JOIN基准持仓
    FULL OUTER JOIN BENCHMARK_POSITION BP 
        ON BP.BENCHMARK_ID = TR.BENCHMARK_ID 
        AND BP.DATE_AS_OF = TR.DATE_AS_OF
        AND BP.SEC_ID = AP.SEC_ID
-- 过滤掉无对应证券的情况（确保只保留有账户或基准持仓的SEC_ID）
WHERE 
    AP.SEC_ID IS NOT NULL OR BP.SEC_ID IS NOT NULL
ORDER BY 
    TR.DATE_AS_OF, 
    COALESCE(AP.SEC_ID, BP.SEC_ID)

代码说明 #

1. COALESCE(AP.SEC_ID, BP.SEC_ID)：当SEC_ID同时存在于账户和基准时，取任意一方的SEC_ID，确保同一SEC_ID只显示一行
2. FULL OUTER JOIN结合BP.SEC_ID = AP.SEC_ID：仅关联同一SEC_ID的账户和基准持仓，避免笛卡尔积
3. WHERE AP.SEC_ID IS NOT NULL OR BP.SEC_ID IS NOT NULL：过滤掉既无账户持仓也无基准持仓的无效行

后续步骤 #

1. 计算权重差值：在SELECT中添加COALESCE(AP.MKT_PCT, 0) - COALESCE(BP.WEIGHT, 0) AS WEIGHT_DIFF
2. 扩展账户范围：去掉WHERE D.ACCT_ID = 123456的过滤条件，或修改为多个账户的IN条件
3. 扩展日期范围：调整@BeginDate和@EndDate的取值，覆盖更多交易日

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