PostgreSQL中如何将广告与流量表的时间戳合并至同一列？

你遇到的核心问题确实是只选取了单表的时间戳——哪怕用了FULL OUTER JOIN，但最终还是把ads.datetime_utc作为输出的时间列，那些仅存在于website_traffic的时间戳会因为关联不上而显示为NULL，自然无法被正确纳入结果。

在PostgreSQL里，我们可以通过先构建包含所有时间维度的基础数据集，再基于这个数据集关联两张表做聚合的方式解决这个问题。具体步骤如下：

关键思路 #

1. 用UNION提取两张表中所有唯一的时间（注意时区统一！）、关联键（lookup_code、device）组合，生成一个覆盖所有可能维度的基础时间集。
2. 基于这个基础集，分别左连ads_data和website_traffic，确保所有时间维度都被保留。
3. 用COALESCE处理空值，避免聚合时出现NULL影响结果。

完整SQL示例 #

WITH all_time_dimensions AS (
    -- 从广告表提取所有维度组合（时区为UTC）
    SELECT 
        ads.datetime_utc AS datetime,
        ads.lookup_code,
        ads.device
    FROM ads_data ads
    UNION
    -- 从网站流量表提取所有维度组合，将EST时间转换为UTC统一时区
    SELECT 
        web.datetime_est AT TIME ZONE 'EST' AT TIME ZONE 'UTC' AS datetime,
        web.lookup_code,
        web.device
    FROM website_traffic web
)
SELECT
    COALESCE(ads.phase, 'N/A') AS phase,
    atd.datetime AS datetime,
    COALESCE(lower(array_to_string((regexp_split_to_array(ads.placement, '_'))[1:9], '_')), 'N/A') AS delim_dims,
    COALESCE(a.name, 'N/A') AS name,
    atd.device AS device,
    COALESCE(SUM(ads.impressions), 0) AS impressions,
    COALESCE(SUM(ads.clicks), 0) AS clicks,
    COALESCE(SUM(ads.spend), 0) AS spend,
    COALESCE(SUM(web.sessions), 0) AS sessions,
    COALESCE(SUM(web.bounces), 0) AS bounces
FROM all_time_dimensions atd
-- 左连广告表，匹配时间、lookup_code、device
LEFT JOIN ads_data ads
    ON atd.datetime = ads.datetime_utc
    AND atd.lookup_code = ads.lookup_code
    AND atd.device = ads.device
-- 左连名称映射表
LEFT JOIN lookup.names_lookup a
    ON ads.lookup_code = a.lookup_code
-- 左连网站流量表，注意时区统一后匹配
LEFT JOIN website_traffic web
    ON atd.datetime = web.datetime_est AT TIME ZONE 'EST' AT TIME ZONE 'UTC'
    AND atd.lookup_code = web.lookup_code
    AND atd.device = web.device
GROUP BY
    atd.datetime,
    atd.device,
    atd.lookup_code,
    COALESCE(ads.phase, 'N/A'),
    COALESCE(lower(array_to_string((regexp_split_to_array(ads.placement, '_'))[1:9], '_')), 'N/A'),
    COALESCE(a.name, 'N/A')
-- 可选：保留有广告花费或网站流量的记录，如果你只想保留有花费的记录，可换回原HAVING条件
HAVING COALESCE(SUM(ads.spend), 0) > 0 OR COALESCE(SUM(web.sessions), 0) > 0

重要细节说明 #

1. 时区统一：你原查询里直接用ads.datetime_utc = web.datetime_est会导致时间匹配错误，必须先将EST转换为UTC（或反之），确保时间戳时区一致。
2. UNION去重：自动过滤重复的维度组合，避免后续聚合时出现重复统计。
3. COALESCE处理空值：当某条记录仅存在于其中一张表时，另一张表的字段会返回NULL，用COALESCE将其转为0或N/A，让结果更整洁。
4. 分组一致性：GROUP BY必须包含所有非聚合字段，这里用COALESCE后的字段，避免因NULL导致分组不一致。

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