SQL查询：如何统一月份字段并解决结果翻倍问题？

你的问题核心是仅通过client_id关联子查询，未同步关联月份字段，这会让同一client_id下不同月份的子查询数据产生笛卡尔积，最终导致结果行数翻倍甚至更多。要得到month | client_id | value_one | value_two | value_three | value_four的目标格式，需以client_id + month的组合作为唯一行基准，再关联各表的聚合结果。

修正思路1：先构建(client_id, month)维度，再左连各子查询 #

先生成所有需要统计的client_id与month的组合，再分别左连接每个value表的聚合结果，同时关联client_id和month，彻底避免笛卡尔积：

WITH month_dim AS (
    -- 生成所有需要统计的月份（固定范围可直接枚举，比如'01'到'12'）
    SELECT DISTINCT to_char(to_timestamp(t.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month
    FROM dim_time t
),
client_month AS (
    -- 生成client和月份的全组合
    SELECT c.client_id, m.month
    FROM client c
    CROSS JOIN month_dim m
),
value_one_agg AS (
    SELECT 
        to_char(to_timestamp(t1.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        sum(value) AS value_one
    FROM value_one
    JOIN dim_time t1 ON value_one.dt_timestamp_id = t1.time_id
    GROUP BY month, client_id
),
value_two_agg AS (
    SELECT 
        to_char(to_timestamp(t2.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        sum(value) AS value_two
    FROM value_two
    JOIN dim_time t2 ON value_two.dt_timestamp_id = t2.time_id
    GROUP BY month, client_id
),
value_three_agg AS (
    SELECT 
        to_char(to_timestamp(t3.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        sum(value) AS value_three
    FROM value_three
    JOIN dim_time t3 ON value_three.dt_timestamp_id = t3.time_id
    GROUP BY month, client_id
),
value_four_agg AS (
    SELECT 
        to_char(to_timestamp(t4.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        sum(value) AS value_four
    FROM value_four
    -- 修正原查询错误：此处应关联value_four的时间ID，而非value_three
    JOIN dim_time t4 ON value_four.dt_timestamp_id = t4.time_id
    GROUP BY month, client_id
)
SELECT 
    cm.month,
    cm.client_id,
    nvl(vo.value_one, 0) AS value_one,
    nvl(vt.value_two, 0) AS value_two,
    nvl(vth.value_three, 0) AS value_three,
    nvl(vf.value_four, 0) AS value_four
FROM client_month cm
LEFT JOIN value_one_agg vo ON cm.client_id = vo.client_id AND cm.month = vo.month
LEFT JOIN value_two_agg vt ON cm.client_id = vt.client_id AND cm.month = vt.month
LEFT JOIN value_three_agg vth ON cm.client_id = vth.client_id AND cm.month = vth.month
LEFT JOIN value_four_agg vf ON cm.client_id = vf.client_id AND cm.month = vf.month
-- 如需过滤指定月份，添加WHERE条件，比如WHERE cm.month IN ('03', '04')
ORDER BY cm.client_id, cm.month;

修正思路2：用UNION ALL整合数据后PIVOT（更简洁） #

先把四个value表的数据通过UNION ALL合并为统一结构，再用PIVOT函数转成目标列格式：

WITH all_values AS (
    SELECT 
        to_char(to_timestamp(t.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        'value_one' AS value_type,
        sum(value) AS value
    FROM value_one
    JOIN dim_time t ON value_one.dt_timestamp_id = t.time_id
    GROUP BY month, client_id
    UNION ALL
    SELECT 
        to_char(to_timestamp(t.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        'value_two' AS value_type,
        sum(value) AS value
    FROM value_two
    JOIN dim_time t ON value_two.dt_timestamp_id = t.time_id
    GROUP BY month, client_id
    UNION ALL
    SELECT 
        to_char(to_timestamp(t.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        'value_three' AS value_type,
        sum(value) AS value
    FROM value_three
    JOIN dim_time t ON value_three.dt_timestamp_id = t.time_id
    GROUP BY month, client_id
    UNION ALL
    SELECT 
        to_char(to_timestamp(t.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') AS month,
        client_id,
        'value_four' AS value_type,
        sum(value) AS value
    FROM value_four
    JOIN dim_time t ON value_four.dt_timestamp_id = t.time_id
    GROUP BY month, client_id
)
SELECT 
    month,
    client_id,
    nvl(value_one, 0) AS value_one,
    nvl(value_two, 0) AS value_two,
    nvl(value_three, 0) AS value_three,
    nvl(value_four, 0) AS value_four
FROM all_values
PIVOT (
    SUM(value)
    FOR value_type IN ('value_one' AS value_one, 'value_two' AS value_two, 'value_three' AS value_three, 'value_four' AS value_four)
)
ORDER BY client_id, month;

关键注意点 #

1. 原查询第四个子查询存在关联错误：value_three.dt_timestamp_id = t4.time_id应改为value_four.dt_timestamp_id = t4.time_id，否则会导致数据关联异常。
2. 用nvl()处理空值，确保某月份某client无数据时显示0而非NULL。
3. 若仅需统计指定月份，在对应CTE或主查询中添加WHERE条件过滤即可。

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