SQL需求：求各产品类别下第2、3封邮件发送间隔的平均时长（修正代码）

背景与表结构 #

我们有4张用于研究营销活动邮件表现的表：

• Table A：存储营销活动名称信息
• Table B：存储营销活动ID的投递表现（邮件发送对象）
• Table C：存储营销活动ID的打开表现（邮件打开对象）
• Table D：存储营销活动的点击表现（点击邮件元素的对象）

表结构及示例数据如下：

-- Table A: Campaign Information
CREATE TABLE TableA (
campaign_name VARCHAR(255),
campaign_id INT
);

-- Table B: Email Delivery Information
CREATE TABLE TableB (
campaign_id INT,
delivery_date DATE,
user_id VARCHAR(50)
);

-- Table C: Email Open Information
CREATE TABLE TableC (
campaign_id INT,
open_date DATE,
user_id VARCHAR(50)
);

-- Table D: Email Click Information
CREATE TABLE TableD (
campaign_id INT,
click_date DATE,
user_id VARCHAR(50)
);

-- Inserts for TableA (Campaign Information)
INSERT INTO TableA (campaign_name, campaign_id) VALUES
('Instacash Promo 1', 112233),
('Instacash Promo 2', 112244),
('RoarMoney Balance 5', 112259);

-- Inserts for TableB (Email Delivery Information)
INSERT INTO TableB (campaign_id, delivery_date, user_id) VALUES
(112233, '2021-01-01', 'a'),
(112233, '2021-01-01', 'b'),
(112233, '2021-01-01', 'c'),
(112244, '2021-01-05', 'd'),
(112244, '2021-01-05', 'e');

-- Inserts for TableC (Email Opened Information)
INSERT INTO TableC (campaign_id, open_date, user_id) VALUES
(112233, '2021-01-03', 'a'),
(112233, '2021-01-05', 'b'),
(112244, '2021-01-07', 'd'),
(112244, '2021-01-10', 'e');

-- Inserts for TableD (Email Link Clicked Information)
INSERT INTO TableD (campaign_id, click_date, user_id) VALUES
(112233, '2021-01-03', 'a'),
(112244, '2021-01-11', 'e');

需求说明 #

编写SQL查询各产品类别下，第2封与第3封邮件发送间隔的平均时长，输出需包含campaign_category（按campaign_name分为Instacash、RoarMoney、Others）和Average time taken to 2nd to 3rd email字段。

原错误SQL #

-- Calculate DATEDIFF between rn = 3 and rn = 2
WITH temp as (
    SELECT 
        CASE 
            WHEN campaign_name REGEXP '^Instacash' THEN 'Instacash'
            WHEN campaign_name REGEXP '^RoarMoney' THEN 'RoarMoney'
            ELSE 'Others'
        END AS campaign_category,
        a.campaign_id,
        a.campaign_name,
        b.delivery_date
    FROM TableA a
    LEFT JOIN TableB b ON a.campaign_id = b.campaign_id
),
temp_1 as (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY campaign_name ORDER BY delivery_date) as rn
    FROM temp
),
temp_2 as (
    SELECT *
    FROM temp_1
    WHERE rn in (2, 3)
),
temp_3 as (
    SELECT
        t1.campaign_category,
        t1.campaign_id,
        t1.campaign_name,
        DATEDIFF(day, t2.delivery_date, t3.delivery_date) as date_difference
    FROM temp_2 t1
    JOIN temp_2 t2 ON t1.campaign_id = t2.campaign_id AND t2.rn = 2
    JOIN temp_2 t3 ON t1.campaign_id = t3.campaign_id AND t3.rn = 3
)

-- Select the calculated result
SELECT * FROM temp_3;

错误分析 #

1. 分区逻辑错误：按campaign_name分区排序，同一活动（campaign_id）下的多用户投递会生成重复行号，无法准确对应活动的发送顺序。
2. 未去重投递日期：同一活动同一天给多个用户发邮件，应视为一次发送，原SQL未去重导致行号混乱。
3. 关联冗余：temp_3中用t1关联t2、t3会产生重复数据，且未按类别计算平均值，不符合需求。

修正后的SQL #

-- 计算各活动第2和第3次投递的间隔，再按类别求平均
WITH campaign_dates AS (
    -- 去重同一活动的投递日期，同一天发件视为一次发送
    SELECT DISTINCT
        a.campaign_id,
        CASE 
            WHEN a.campaign_name REGEXP '^Instacash' THEN 'Instacash'
            WHEN a.campaign_name REGEXP '^RoarMoney' THEN 'RoarMoney'
            ELSE 'Others'
        END AS campaign_category,
        b.delivery_date
    FROM TableA a
    JOIN TableB b ON a.campaign_id = b.campaign_id
),
ranked_dates AS (
    -- 按活动ID分区，给投递日期排序
    SELECT 
        campaign_category,
        campaign_id,
        delivery_date,
        ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY delivery_date) AS rn
    FROM campaign_dates
),
interval_calculation AS (
    -- 匹配第2和第3次投递日期，计算间隔
    SELECT 
        r2.campaign_category,
        DATEDIFF(day, r2.delivery_date, r3.delivery_date) AS date_diff
    FROM ranked_dates r2
    JOIN ranked_dates r3 
        ON r2.campaign_id = r3.campaign_id 
        AND r2.rn = 2 
        AND r3.rn = 3
)
-- 按类别计算平均间隔
SELECT 
    campaign_category,
    AVG(date_diff) AS `Average time taken to 2nd to 3rd email`
FROM interval_calculation
GROUP BY campaign_category;

修正说明 #

1. 投递日期去重：对同一活动的delivery_date去重，确保行号对应活动的实际发送次数。
2. 正确分区排序：按campaign_id分区，让行号准确对应每个活动的发送顺序。
3. 简化间隔计算：直接关联rn=2和rn=3的行，避免冗余数据。
4. 计算平均值：最后按campaign_category分组，用AVG()得到类别下的平均间隔，符合需求输出要求。

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