多表多状态请求统计需求及SQL优化方案咨询

一、满足需求的SQL实现 #

先通过UNION ALL合并三张表的emp_code和status数据，再用条件聚合分别统计三种状态的请求数，最终按emp_code分组展示：

SELECT
    emp_code,
    COUNT(CASE WHEN status = 'PENDING' THEN 1 END) AS total_pending_req,
    COUNT(CASE WHEN status = 'REJECTED' THEN 1 END) AS total_rejected_req,
    COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) AS total_completed_req
FROM (
    SELECT emp_code, status FROM table1
    UNION ALL
    SELECT emp_code, status FROM table2
    UNION ALL
    SELECT emp_code, status FROM table3
) AS combined_data
GROUP BY emp_code;

如果你的数据库支持FILTER子句（比如PostgreSQL），可以用更简洁的写法：

SELECT
    emp_code,
    COUNT(*) FILTER (WHERE status = 'PENDING') AS total_pending_req,
    COUNT(*) FILTER (WHERE status = 'REJECTED') AS total_rejected_req,
    COUNT(*) FILTER (WHERE status = 'COMPLETED') AS total_completed_req
FROM (
    SELECT emp_code, status FROM table1
    UNION ALL
    SELECT emp_code, status FROM table2
    UNION ALL
    SELECT emp_code, status FROM table3
) AS combined_data
GROUP BY emp_code;

二、高效数据提取优化方案 #

1. 避免重复扫描表 #

原写法如果是按状态分别查询三张表再合并，会重复扫描三次表。现在的写法只扫描三张表各一次，再做一次聚合，能显著减少IO开销。

2. 添加复合索引 #

给三张表分别创建(emp_code, status)的复合索引，让数据库直接通过索引获取所需字段，无需回表查询全量数据：

-- 为table1创建索引
CREATE INDEX idx_table1_emp_status ON table1(emp_code, status);
-- 为table2创建索引
CREATE INDEX idx_table2_emp_status ON table2(emp_code, status);
-- 为table3创建索引
CREATE INDEX idx_table3_emp_status ON table3(emp_code, status);

3. 提前过滤冗余数据 #

如果三张表存在除目标三种状态外的其他数据，可在UNION ALL子查询中先过滤，减少后续聚合的数据量：

SELECT
    emp_code,
    COUNT(CASE WHEN status = 'PENDING' THEN 1 END) AS total_pending_req,
    COUNT(CASE WHEN status = 'REJECTED' THEN 1 END) AS total_rejected_req,
    COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) AS total_completed_req
FROM (
    SELECT emp_code, status FROM table1 WHERE status IN ('PENDING','REJECTED','COMPLETED')
    UNION ALL
    SELECT emp_code, status FROM table2 WHERE status IN ('PENDING','REJECTED','COMPLETED')
    UNION ALL
    SELECT emp_code, status FROM table3 WHERE status IN ('PENDING','REJECTED','COMPLETED')
) AS combined_data
GROUP BY emp_code;

4. 物化视图优化（非实时场景） #

如果业务允许非实时统计，可创建物化视图定期刷新，查询时直接读取预聚合结果，大幅提升查询速度：

-- 创建物化视图
CREATE MATERIALIZED VIEW emp_status_stats AS
SELECT
    emp_code,
    COUNT(CASE WHEN status = 'PENDING' THEN 1 END) AS total_pending_req,
    COUNT(CASE WHEN status = 'REJECTED' THEN 1 END) AS total_rejected_req,
    COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) AS total_completed_req
FROM (
    SELECT emp_code, status FROM table1 WHERE status IN ('PENDING','REJECTED','COMPLETED')
    UNION ALL
    SELECT emp_code, status FROM table2 WHERE status IN ('PENDING','REJECTED','COMPLETED')
    UNION ALL
    SELECT emp_code, status FROM table3 WHERE status IN ('PENDING','REJECTED','COMPLETED')
) AS combined_data
GROUP BY emp_code;

-- 按需刷新物化视图
REFRESH MATERIALIZED VIEW emp_status_stats;

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