单SQL实现营销活动各邮件发送量计数与聚合查询

业务场景为统计每个营销活动下每封配置邮件的已发送数量，简化后的表结构如下：

campaigns
+----+------+
| id | name |
+----+------+

emails
+----+-------------+---------+------+
| id | campaign_id | subject | body |
+----+-------------+---------+------+

sent_emails
+----+-------------+----------+
| id | campaign_id | email_id |
+----+-------------+----------+

期望输出格式为每个营销活动一行，每列对应活动下按顺序排列的邮件发送量，无发送记录则显示0：

+-------------+-----------------+---------+---------+---------+
| campaign_id |  campaign_name  | email_1 | email_2 | email_3 |
+-------------+-----------------+---------+---------+---------+
|           1 | First campaign  |      10 |       8 |       4 |
|           2 | Second campaign |       5 |       0 |       0 |
|           3 | Third campaign  |       3 |       2 |       0 |
+-------------+-----------------+---------+---------+---------+

原有实现为两次查询拆分实现，需要单SQL完成上述需求。

完全可以通过单条SQL实现，核心逻辑为窗口函数生成活动内邮件序号+条件聚合行转列，分两种场景给出写法：

场景1：已知单活动最大邮件数（推荐，性能最优） #

如果业务上可限定每个营销活动下最多配置的邮件数量（比如示例中最多3封），直接用静态SQL即可，性能最高、可维护性最好：

WITH email_ranked AS (
    SELECT
        id AS email_id,
        campaign_id,
        -- 按活动内邮件ID升序生成1开始的连续序号，可根据业务调整排序规则（如创建时间）
        ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY id) AS email_rank
    FROM emails
)
SELECT
    c.id AS campaign_id,
    c.name AS campaign_name,
    COUNT(CASE WHEN er.email_rank = 1 THEN se.id END) AS email_1,
    COUNT(CASE WHEN er.email_rank = 2 THEN se.id END) AS email_2,
    COUNT(CASE WHEN er.email_rank = 3 THEN se.id END) AS email_3
    -- 若单活动最多支持N封邮件，继续往下补充对应序号的判断即可
FROM campaigns c
LEFT JOIN email_ranked er
    ON c.id = er.campaign_id
LEFT JOIN sent_emails se
    ON er.email_id = se.email_id
    AND er.campaign_id = se.campaign_id -- 加关联条件避免跨活动数据匹配，同时利用联合索引提速
GROUP BY c.id, c.name
ORDER BY c.id;

逻辑说明：

• 左连接保证未配置邮件、或邮件无发送记录的营销活动也会出现在结果中，对应计数为0
• COUNT(CASE...)仅统计对应序号邮件的发送记录，无匹配记录时自动返回0

场景2：单活动邮件数不固定 #

如果每个活动下的邮件数量不确定，无法提前写死列数，可使用对应数据库的动态SQL能力自动生成查询列，以下为MySQL版本实现：

-- 第一步：动态生成所有邮件序号对应的聚合列
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'COUNT(CASE WHEN er.email_rank = ', rk.email_rank, ' THEN se.id END) AS email_', rk.email_rank
    )
) INTO @sql
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY id) AS email_rank FROM emails
) rk;

-- 第二步：拼接完整SQL并执行
SET @sql = CONCAT(
    'SELECT c.id AS campaign_id, c.name AS campaign_name, ', @sql,
    ' FROM campaigns c
      LEFT JOIN (
          SELECT id AS email_id, campaign_id,
          ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY id) AS email_rank
          FROM emails
      ) er ON c.id = er.campaign_id
      LEFT JOIN sent_emails se ON er.email_id = se.email_id AND er.campaign_id = se.campaign_id
      GROUP BY c.id, c.name
      ORDER BY c.id'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

其他数据库可使用对应特性实现：PostgreSQL可用tablefunc扩展的crosstab函数，SQL Server可用原生PIVOT语法。

• 给emails表创建(campaign_id, id)联合索引，加速窗口函数的分组排序
• 给sent_emails表创建(campaign_id, email_id)联合索引，避免关联查询时全表扫描
• 非必要不使用动态SQL，静态SQL的执行计划可缓存，性能远高于动态生成的SQL

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