统计两张表中postcode列记录数及SQL查询优化咨询

核心问题解决：合并两张表的数据 #

你需要先将transactions和transactions_original两张表中符合条件（长度为2的postcode）的数据合并，再进行统计——而非试图在WHERE子句中关联另一张表。以下是两种可行实现方式：

方式1：兼容性优先（适配所有标准SQL数据库） #

WITH combined_postcodes AS (
    -- 合并两张表中符合条件的postcode数据
    SELECT postcode
    FROM transactions
    WHERE LENGTH(postcode) = 2
    UNION ALL
    SELECT postcode
    FROM transactions_original
    WHERE LENGTH(postcode) = 2
)
-- 统计各国家代码的出现次数
SELECT
    postcode AS country_code,
    COUNT(*) AS count,
    '' AS notes
FROM combined_postcodes
GROUP BY postcode
UNION ALL
-- 统计唯一国家代码总数
SELECT
    '' AS country_code,
    COUNT(DISTINCT postcode) AS count,
    'Total Unique Countries' AS notes
FROM combined_postcodes
-- 确保总计行排在末尾，其余按国家代码排序
ORDER BY 
    CASE WHEN notes = 'Total Unique Countries' THEN 1 ELSE 0 END,
    country_code;

方式2：高效聚合（支持GROUP BY ROLLUP的数据库） #

如果你的数据库支持GROUP BY ROLLUP（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用这种方式减少一次聚合操作：

WITH combined_postcodes AS (
    SELECT postcode
    FROM transactions
    WHERE LENGTH(postcode) = 2
    UNION ALL
    SELECT postcode
    FROM transactions_original
    WHERE LENGTH(postcode) = 2
)
SELECT
    -- GROUPING(postcode)=1表示该行是ROLLUP生成的总计行
    CASE WHEN GROUPING(postcode) = 1 THEN '' ELSE postcode END AS country_code,
    COUNT(*) AS count,
    CASE WHEN GROUPING(postcode) = 1 THEN 'Total Unique Countries' ELSE '' END AS notes
FROM combined_postcodes
GROUP BY postcode WITH ROLLUP
-- 排除无效的空分组
HAVING GROUPING(postcode) = 0 OR COUNT(DISTINCT postcode) IS NOT NULL
ORDER BY 
    GROUPING(postcode), -- 总计行排在最后
    country_code;

优化建议 #

1. 添加针对性索引
   由于查询依赖LENGTH(postcode) = 2的筛选条件，为两张表创建索引可大幅提升数据读取速度：

   -- 支持部分索引的数据库（如PostgreSQL、MySQL 8.0.13+）
   CREATE INDEX idx_transactions_postcode_country ON transactions(postcode) WHERE LENGTH(postcode) = 2;
   CREATE INDEX idx_transactions_original_postcode_country ON transactions_original(postcode) WHERE LENGTH(postcode) = 2;
   

   若数据库不支持部分索引，可创建复合索引：

   CREATE INDEX idx_transactions_postcode_len ON transactions(LENGTH(postcode), postcode);
   CREATE INDEX idx_transactions_original_postcode_len ON transactions_original(LENGTH(postcode), postcode);
   

2. 统一国家代码大小写
   若postcode列存在大小写不一致（如US和us），会被误统计为不同国家。可通过UPPER()函数统一转换：

   WITH combined_postcodes AS (
       SELECT UPPER(postcode) AS country_code
       FROM transactions
       WHERE LENGTH(postcode) = 2
       UNION ALL
       SELECT UPPER(postcode) AS country_code
       FROM transactions_original
       WHERE LENGTH(postcode) = 2
   )
   -- 后续统计逻辑基于统一后的country_code即可
   

3. 避免重复扫描表
   原查询两次扫描transactions表，使用CTE（WITH子句）合并数据后，只需扫描两张表各一次，有效提升查询效率。

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