如何基于邮件接收时间和处理时长每日统计未回复邮件积压量？

需求说明 #

从emails表统计每日上午9点前的未回复邮件积压量，需满足：

• 邮件接收时间start_time_dte < 当日9点
• 邮件回复时间end_time_dte（通过DATEADD(SECOND, duration_total_seconds, start_time_dte)计算得出）> 当日9点
• 跨多天未回复的邮件，要在每一个符合条件的日期都被统计

核心思路 #

1. 生成连续日期序列：覆盖从最早邮件接收日期到最晚邮件回复日期的所有日期，确保每个日期都能被统计。
2. 计算当日9点基准时间：将每个日期转换为当天9点的具体时间点，作为判断邮件是否属于积压的基准。
3. 关联统计：将日期序列与邮件表关联，统计每个日期下符合条件的邮件数量。

分数据库实现示例 #

SQL Server

-- 生成连续日期范围
WITH DateRange AS (
    SELECT CAST(MIN(start_time_dte) AS DATE) AS stat_date
    FROM emails
    UNION ALL
    SELECT DATEADD(DAY, 1, stat_date)
    FROM DateRange
    WHERE DATEADD(DAY, 1, stat_date) <= (SELECT CAST(MAX(end_time_dte) AS DATE) FROM emails)
),
Daily9AM AS (
    SELECT 
        stat_date,
        DATEADD(HOUR, 9, CAST(stat_date AS DATETIME)) AS daily_9am
    FROM DateRange
)
SELECT 
    d.stat_date AS Date,
    COUNT(e.start_time_dte) AS Backlog
FROM Daily9AM d
LEFT JOIN emails e ON 
    e.start_time_dte < d.daily_9am
    AND DATEADD(SECOND, e.duration_total_seconds, e.start_time_dte) > d.daily_9am
GROUP BY d.stat_date
ORDER BY d.stat_date;

MySQL

-- 先获取日期范围的起止值
SET @min_date = (SELECT DATE(MIN(start_time_dte)) FROM emails);
SET @max_date = (SELECT DATE(MAX(end_time_dte)) FROM emails);

-- 生成连续日期序列并统计
WITH RECURSIVE DateRange AS (
    SELECT @min_date AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM DateRange
    WHERE stat_date < @max_date
),
Daily9AM AS (
    SELECT 
        stat_date,
        STR_TO_DATE(CONCAT(stat_date, ' 09:00:00'), '%Y-%m-%d %H:%i:%s') AS daily_9am
    FROM DateRange
)
SELECT 
    d.stat_date AS Date,
    COUNT(e.start_time_dte) AS Backlog
FROM Daily9AM d
LEFT JOIN emails e ON 
    e.start_time_dte < d.daily_9am
    AND DATE_ADD(e.start_time_dte, INTERVAL e.duration_total_seconds SECOND) > d.daily_9am
GROUP BY d.stat_date
ORDER BY d.stat_date;

PostgreSQL

-- 生成连续日期范围并统计
WITH DateRange AS (
    SELECT generate_series(
        DATE_TRUNC('day', MIN(start_time_dte))::DATE,
        DATE_TRUNC('day', MAX(end_time_dte))::DATE,
        INTERVAL '1 day'
    )::DATE AS stat_date
    FROM emails
),
Daily9AM AS (
    SELECT 
        stat_date,
        stat_date + INTERVAL '9 hours' AS daily_9am
    FROM DateRange
)
SELECT 
    d.stat_date AS Date,
    COUNT(e.start_time_dte) AS Backlog
FROM Daily9AM d
LEFT JOIN emails e ON 
    e.start_time_dte < d.daily_9am
    AND e.start_time_dte + e.duration_total_seconds * INTERVAL '1 second' > d.daily_9am
GROUP BY d.stat_date
ORDER BY d.stat_date;

关键逻辑说明 #

• 日期序列：通过递归CTE（SQL Server/MySQL）或generate_series（PostgreSQL）生成无间断的日期列表，避免遗漏任何需要统计的日期。
• 当日9点计算：将纯日期转换为包含9点的时间戳，作为判断邮件是否在当日9点前仍未回复的边界。
• 关联条件：邮件接收时间早于当日9点，且回复时间晚于当日9点，确保该邮件在当日9点时处于未回复状态，跨天邮件会在每个符合条件的日期被重复计数。

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