优化按批次分组的海报统计SQL查询并替换NULL为0

数据表结构 #

以下是本次场景用到的数据表定义及初始化数据：

CREATE TABLE poster (
    poster_id SERIAL NOT NULL PRIMARY KEY,
    country VARCHAR (50) NOT NULL
);

CREATE TABLE batch (
    batch_id SERIAL NOT NULL PRIMARY KEY,
    price VARCHAR (50) NOT NULL
);

ALTER TABLE poster 
    ADD COLUMN batch_id INT REFERENCES batch (batch_id); -- 原代码中SERIAL作为外键不合适，修正为普通INT类型

INSERT INTO batch (price) VALUES ('10');
INSERT INTO batch (price) VALUES ('11');

INSERT INTO poster (country, batch_id) VALUES ('DEU', 1);
INSERT INTO poster (country, batch_id) VALUES ('DEU', 1);
INSERT INTO poster (country, batch_id) VALUES ('FRA', 1);

注：原ALTER TABLE语句中ADD COLUMN batch_id SERIAL存在逻辑问题，SERIAL会自动生成新的自增序列，但外键字段应使用普通INT类型，已修正。

需求说明 #

按batch分组统计：

• 特定国家（DEU）的海报数量
• 其他地区的海报数量
• 该批次的总海报数
  要求：无海报的批次，所有统计值返回0而非NULL。

现有实现SQL #

当前方案通过三次子查询关联实现，但性能较差（需重复扫描poster表三次）：

SELECT 
    batch.batch_id, 
    germany.count AS germany, 
    other.count AS outside, 
    total.count AS total 
FROM
    batch 
LEFT JOIN
    (SELECT 
         poster.batch_id AS batch_id, 
         COUNT(*) AS count 
     FROM
         poster 
     WHERE
         poster.country = 'DEU' 
     GROUP BY
         poster.batch_id) AS germany ON germany.batch_id = batch.batch_id
LEFT JOIN
    (SELECT
         poster.batch_id AS batch_id, 
         COUNT(*) AS count 
     FROM
         poster 
     WHERE
         poster.country <> 'DEU' 
     GROUP BY
         poster.batch_id) AS other ON other.batch_id = batch.batch_id
LEFT JOIN
    (SELECT
         poster.batch_id AS batch_id, 
         COUNT(*) AS count 
     FROM
         poster 
     GROUP BY
         poster.batch_id) AS total ON total.batch_id = batch.batch_id

优化后的高性能方案 #

使用条件聚合仅扫描一次poster表，结合COALESCE处理NULL值，性能大幅提升：

SELECT 
    b.batch_id,
    COALESCE(SUM(CASE WHEN p.country = 'DEU' THEN 1 ELSE 0 END), 0) AS germany,
    COALESCE(SUM(CASE WHEN p.country <> 'DEU' THEN 1 ELSE 0 END), 0) AS outside,
    COALESCE(COUNT(p.poster_id), 0) AS total
FROM batch b
LEFT JOIN poster p ON b.batch_id = p.batch_id
GROUP BY b.batch_id
ORDER BY b.batch_id;

优化点说明 #

1. 单次表扫描：仅对poster表做一次关联扫描，避免多次子查询的重复IO开销
2. 条件聚合计算：通过CASE语句在分组时直接计算各区域的统计值，逻辑更紧凑
3. NULL值处理：用COALESCE将无海报批次的统计值从NULL转为0，满足需求
4. 全批次保留：基于batch表做左连接，天然保留所有批次记录，无需额外处理

执行结果 #

执行后将得到符合要求的统计结果：

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