MariaDB：匹配总工作量>0的请求与对应工作记录（含Case用法）

核心需求回顾 #

• 筛选request表中类型为1、2、4、5的请求记录，基于带具体时间的creationDate字段
• 对每个符合条件的请求，统计从该请求创建时间到下一个同类型请求创建时间前，work表中类型6（计+1）、7（计-1）的记录总和
• 仅保留工作总和>0的请求记录，同时排除早于对应请求时间的工作记录
• 解决原查询在大数据量下性能差、结果不符合预期的问题

优化后的SQL查询 #

WITH filtered_requests AS (
    SELECT 
        *,
        -- 获取当前请求的下一个同类型请求创建时间，无后续请求则取当前时间+1天作为统计边界
        LEAD(creationDate) OVER (ORDER BY creationDate) AS next_request_date
    FROM request
    WHERE type IN (1, 2, 4, 5)
),
daily_work_totals AS (
    SELECT
        work_date,
        -- 预计算每日有效工作记录的总和
        SUM(CASE WHEN type = 6 THEN 1 WHEN type = 7 THEN -1 ELSE 0 END) AS daily_sum
    FROM work
    WHERE type IN (6, 7)
    GROUP BY work_date
)
SELECT
    fr.id,
    fr.type,
    fr.creationDate,
    COALESCE(SUM(dwt.daily_sum), 0) AS total_work_score
FROM filtered_requests fr
LEFT JOIN daily_work_totals dwt
    -- 处理时间格式差异：匹配不早于当前请求日期、且早于下一个请求日期的工作记录
    ON dwt.work_date >= CAST(fr.creationDate AS DATE)
    AND (
        fr.next_request_date IS NULL 
        OR dwt.work_date < CAST(fr.next_request_date AS DATE)
    )
GROUP BY fr.id, fr.type, fr.creationDate, fr.next_request_date
HAVING COALESCE(SUM(dwt.daily_sum), 0) > 0
ORDER BY fr.creationDate;

关键优化点说明 #

1. 窗口函数替代自连接：用LEAD()窗口函数高效获取每个请求的下一个同类型请求时间，避免了性能低下的自连接操作，大数据量下性能提升显著
2. 预计算每日总和：提前对work表按日期统计有效记录的总和，避免关联时重复计算，减少计算量
3. 时间匹配逻辑修正：将request的datetime类型creationDate转为date类型，与work表的date字段做精确匹配，解决时间戳差异导致的统计范围错误
4. 索引优化建议：
   • 给request表创建联合索引：CREATE INDEX idx_request_type_creation ON request(type, creationDate);，加速请求筛选和窗口函数计算
   • 给work表创建联合索引：CREATE INDEX idx_work_type_date ON work(type, work_date);，提升每日总和统计的速度
5. 空值处理：用COALESCE()处理无匹配工作记录的情况，确保总和为0而非NULL，避免筛选逻辑出错

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