如何在单条SQL查询中关联两表获取SUBS与UNITS统计数据

问题背景 #

需要从table1和table2分别按日期、发送ID统计SUBS和UNITS数据，并合并到同一结果集中。直接关联两张未聚合的大表时，要么因数据量过大超时，要么统计结果严重偏离真实值。

错误原因分析 #

1. 笛卡尔积导致统计失真：直接关联两张原始表时，table1中同一(date,send_id)的每条记录都会与table2中对应(o_date,o_send_id)的每条记录匹配，产生大量交叉数据，count()统计的是交叉后的行数而非原表真实计数。
2. 索引缺失引发性能问题：仅依赖主键索引无法高效过滤datetime和send_id字段，百万级数据下触发全表扫描，最终导致查询超时。

正确SQL写法 #

核心思路：先分别对两张表做聚合统计，得到小结果集后再关联，既避免笛卡尔积，又大幅提升查询性能。

方法1：LEFT JOIN关联聚合结果（匹配双方都存在的日期+发送ID） #

SELECT 
    t1.`Date`,
    t1.`send_id`,
    t1.`SUBS`,
    COALESCE(t2.`UNITS`, 0) AS `UNITS`
FROM (
    -- 预统计table1的SUBS数据
    SELECT 
        DATE(`date`) AS `Date`,
        `send_id`,
        COUNT(`id`) AS `SUBS`
    FROM `table1`
    WHERE `date` >= '2024-09-01 00:00:00' 
      AND `date` < '2024-10-01 00:00:00'
    GROUP BY DATE(`date`), `send_id`
) t1
LEFT JOIN (
    -- 预统计table2的UNITS数据
    SELECT 
        DATE(`o_date`) AS `Date`,
        `o_send_id` AS `send_id`,
        COUNT(`id`) AS `UNITS`
    FROM `table2`
    WHERE `o_date` >= '2024-09-01 00:00:00' 
      AND `o_date` < '2024-10-01 00:00:00'
    GROUP BY DATE(`o_date`), `o_send_id`
) t2 ON t1.`Date` = t2.`Date` AND t1.`send_id` = t2.`send_id`;

注：用>=和<替代BETWEEN更严谨，避免datetime类型的边界遗漏问题，原SQL中BETWEEN '2024-09-01%'的写法本身存在语法错误（BETWEEN不支持模糊匹配格式）。

方法2：UNION ALL结合条件聚合（展示所有存在的日期+发送ID组合） #

如果需要包含仅在table1或仅在table2中存在的(date,send_id)组合，可使用此方法：

SELECT 
    `Date`,
    `send_id`,
    SUM(`SUBS`) AS `SUBS`,
    SUM(`UNITS`) AS `UNITS`
FROM (
    SELECT 
        DATE(`date`) AS `Date`,
        `send_id`,
        COUNT(`id`) AS `SUBS`,
        0 AS `UNITS`
    FROM `table1`
    WHERE `date` >= '2024-09-01 00:00:00' 
      AND `date` < '2024-10-01 00:00:00'
    GROUP BY DATE(`date`), `send_id`
    
    UNION ALL
    
    SELECT 
        DATE(`o_date`) AS `Date`,
        `o_send_id` AS `send_id`,
        0 AS `SUBS`,
        COUNT(`id`) AS `UNITS`
    FROM `table2`
    WHERE `o_date` >= '2024-09-01 00:00:00' 
      AND `o_date` < '2024-10-01 00:00:00'
    GROUP BY DATE(`o_date`), `o_send_id`
) combined
GROUP BY `Date`, `send_id`;

性能优化建议 #

为百万级数据的查询添加复合索引，避免全表扫描：

• 给table1创建索引：CREATE INDEX idx_table1_date_sendid ON table1(date, send_id);
• 给table2创建索引：CREATE INDEX idx_table2_odate_sendid ON table2(o_date, o_send_id);

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