多表关联查询问题：按州统计指定日期销售总额

需求 #

按州统计2020-01-01和2021-01-01的销售总额，并显示州名。

表结构与测试数据 #

CREATE TABLE IF NOT EXISTS estado (
    id SERIAL PRIMARY KEY,
    estado VARCHAR(100) -- 州名
)

CREATE TABLE IF NOT EXISTS municipio (
    id SERIAL PRIMARY KEY,
    estado integer REFERENCES estado(id), -- 关联州ID
    municipio VARCHAR(100) -- 城市名
)

CREATE TABLE IF NOT EXISTS vendas (
    id SERIAL PRIMARY KEY,
    municipio integer REFERENCES municipio(id), -- 关联城市ID
    valor numeric, -- 销售金额
    data_venda date -- 销售日期
)

-- 插入测试数据
INSERT INTO estado VALUES (1, 'PR');
INSERT INTO estado VALUES (2, 'SC');
INSERT INTO estado VALUES (3, 'RS');
INSERT INTO municipio VALUES (1, 1, 'Pelotas');
INSERT INTO municipio VALUES (2, 1, 'Caxias do Sul');
INSERT INTO municipio VALUES (3, 1, 'Porto Alegre');
INSERT INTO municipio VALUES (4, 2, 'Florianopolis');
INSERT INTO municipio VALUES (5, 2, 'Chapeco');
INSERT INTO municipio VALUES (6, 2, 'Itajai');
INSERT INTO municipio VALUES (7, 3, 'Curitiba');
INSERT INTO municipio VALUES (8, 3, 'Maringa');
INSERT INTO municipio VALUES (9, 3, 'Foz do Iguaçu');
INSERT INTO vendas VALUES (1, 6, 5, '2020-01-01');
INSERT INTO vendas VALUES (2, 5, 10, '2021-01-01');
INSERT INTO vendas VALUES (3, 5, 5, '2020-01-01');
INSERT INTO vendas VALUES (4, 4, 2, '2020-01-01');
INSERT INTO vendas VALUES (5, 3, 10, '2021-01-01');
INSERT INTO vendas VALUES (6, 3, 12, '2020-01-01');
INSERT INTO vendas VALUES (7, 3, 20, '2020-01-01');
INSERT INTO vendas VALUES (8, 2, 10, '2020-01-01');
INSERT INTO vendas VALUES (9, 1, 11, '2021-01-01');
INSERT INTO vendas VALUES (10, 9, 4, '2020-01-01');

原查询的问题 #

SELECT 
    e.estado, SUM(v.valor) as sum2021, SUM(v2.valor) as sum2020
FROM vendas v 
    CROSS JOIN vendas v2 
    INNER JOIN municipio m ON v.municipio = m.id
    INNER JOIN estado e ON m.estado = e.id
WHERE v.data_venda = '2021-01-01' 
    AND v2.data_venda = '2020-01-01' 
GROUP BY 1;

• 数值异常原因：CROSS JOIN vendas v2会让2021年的每条销售记录与2020年的每条销售记录进行笛卡尔积配对，导致SUM计算时重复累加，最终金额被放大。
• RS州未显示原因：INNER JOIN配合WHERE条件同时要求存在2021和2020年的销售数据，而RS州只有2020年的销售记录，因此被过滤，无法出现在结果中。

正确查询方案 #

方案一：条件聚合（推荐） #

通过CASE语句在同一聚合中分别统计两个日期的销售额，同时用LEFT JOIN保证所有州都被展示：

SELECT 
    e.estado,
    -- 统计2021-01-01的销售总额，无数据则为0
    SUM(CASE WHEN v.data_venda = '2021-01-01' THEN v.valor ELSE 0 END) AS sum2021,
    -- 统计2020-01-01的销售总额，无数据则为0
    SUM(CASE WHEN v.data_venda = '2020-01-01' THEN v.valor ELSE 0 END) AS sum2020
FROM estado e
LEFT JOIN municipio m ON e.id = m.estado
LEFT JOIN vendas v ON m.id = v.municipio 
    AND v.data_venda IN ('2020-01-01', '2021-01-01') -- 提前过滤目标日期，减少计算量
GROUP BY e.estado;

方案二：子查询关联 #

分别统计两个日期的州级销售总额，再通过LEFT JOIN关联到所有州，用COALESCE将NULL转为0：

SELECT 
    e.estado,
    COALESCE(s2021.total, 0) AS sum2021,
    COALESCE(s2020.total, 0) AS sum2020
FROM estado e
-- 关联2021年的销售统计
LEFT JOIN (
    SELECT 
        es.id,
        SUM(v.valor) AS total
    FROM vendas v
    JOIN municipio m ON v.municipio = m.id
    JOIN estado es ON m.estado = es.id
    WHERE v.data_venda = '2021-01-01'
    GROUP BY es.id
) s2021 ON e.id = s2021.id
-- 关联2020年的销售统计
LEFT JOIN (
    SELECT 
        es.id,
        SUM(v.valor) AS total
    FROM vendas v
    JOIN municipio m ON v.municipio = m.id
    JOIN estado es ON m.estado = es.id
    WHERE v.data_venda = '2020-01-01'
    GROUP BY es.id
) s2020 ON e.id = s2020.id;

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