PostgreSQL多表关联查询求助：按规则分配AdSpend至销售数据

需求说明 #

需关联SalesData、AdsData与PlatformMapping三张表，实现：

• 基于Date和PlatformId维度，通过PlatformMapping的PlatformCode匹配AdsData的AdSpend
• 将当日该PlatformId下的总AdSpend，按每条SalesData记录的UnitsSold占比分配到对应记录中

各表结构与数据 #

Table1: SalesData #

Table2: AdsData #

Table3: PlatformMapping #

预期输出（Table4） #

注：预期输出中ABCD3第一条记录的UnitsSold应为32（与总AdSpend分配逻辑匹配），推测为输入笔误，以下查询按占比分配的核心逻辑实现

原查询问题分析 #

你的CTE查询存在两处关键错误：

1. AdSpendMapped未包含Revenue和ChannelId字段，最终查询无法输出这些必要列
2. 计算AdSpend时引用了未定义的a.totalquantitysold字段，属于无效引用

正确PostgreSQL查询方案 #

使用窗口函数直接计算当日每个PlatformId的总销量，无需额外CTE，逻辑简洁高效：

SELECT
    sd.Date,
    sd.PlatformId,
    sd.UnitsSold,
    sd.Revenue,
    sd.ChannelId,
    -- 按销量占比分配广告费用，处理除数为0的异常情况
    COALESCE(
        (ads.AdSpend * sd.UnitsSold) / NULLIF(SUM(sd.UnitsSold) OVER (PARTITION BY sd.Date, sd.PlatformId), 0),
        0
    )::INT AS AdSpend
FROM SalesData sd
-- 通过映射表关联广告表，确保日期与平台编码匹配
JOIN PlatformMapping pm ON sd.PlatformId = pm.PlatformId
JOIN AdsData ads ON pm.Platformcode = ads.PlatformCode AND sd.Date = ads.Date
ORDER BY sd.Date, sd.PlatformId, sd.ChannelId;

查询逻辑说明 #

1. 表关联：SalesData通过PlatformMapping与AdsData按日期、平台编码关联，获取对应维度的总广告费用
2. 窗口函数计算总销量：SUM(sd.UnitsSold) OVER (PARTITION BY sd.Date, sd.PlatformId)计算当日当前PlatformId下的总UnitsSold
3. 占比分配：单条记录的UnitsSold除以总销量，再乘以总AdSpend，得到该记录应分配的广告费用
4. 异常处理：用NULLIF和COALESCE处理总销量为0的情况，避免除以0错误，此时AdSpend设为0
5. 类型转换：用::INT将计算结果转为整数，匹配预期输出格式

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