MySQL查询去重：调度历史表资源分配时长统计优化

问题背景 #

我有一张scheduler_history表，存储测试资源分配历史，字段包括资源分配/释放时间戳、关联标识key、assigned_unit_id（分配单元ID），其中action_type=2代表资源分配成功，action_type=9代表资源释放。

我需要统计每个分配单元的使用时长（释放时间-分配时间），但当前仅用key关联分配和释放记录的查询产生了笛卡尔积，出现重复数据：

原查询SQL #

select TIMESTAMPDIFF(minute,his.created_date, aff.created_date) as "Total Time in use(minute)", his.created_date as "Veeahub Assigned Date", aff.created_date as "Veeahub Released Date", aff.key as "Assignment Key", his.reason as "Assigned Reason"
from scheduler_history his
inner join (select ab.created_date, ab.key, ab.action_type
from scheduler_history ab
where ab.action_type = 9) aff on aff.key = his.key
where his.action_type = 2 and his.key = "Hp8INWFYlT"

重复执行结果 #

# Total Time in use(minute), Veeahub Assigned Date, Veeahub Released Date, Assignment Key, Assigned Reason
903, 2023-11-01 18:56:55.415452, 2023-11-02 10:00:45.371648, Hp8INWFYlT, SOAK-TEST
903, 2023-11-01 18:56:55.415523, 2023-11-02 10:00:45.371648, Hp8INWFYlT, SOAK-TEST
903, 2023-11-01 18:56:55.415452, 2023-11-02 10:00:45.371716, Hp8INWFYlT, SOAK-TEST
903, 2023-11-01 18:56:55.415523, 2023-11-02 10:00:45.371716, Hp8INWFYlT, SOAK-TEST

期望结果 #

# Total Time in use(minute), Veeahub Assigned Date, Veeahub Released Date, Assignment Key, Assigned Reason
903, 2023-11-01 18:56:55.415452, 2023-11-02 10:00:45.371648, Hp8INWFYlT, SOAK-TEST
903, 2023-11-01 18:56:55.415452, 2023-11-02 10:00:45.371716, Hp8INWFYlT, SOAK-TEST

重复数据产生原因 #

• 同一key下存在多条分配记录（action_type=2）和多条释放记录（action_type=9），仅用key关联会让每条分配记录和每条释放记录两两配对，形成笛卡尔积，导致结果重复。
• 从结果可见，同一个key下有2条分配时间记录和2条释放时间记录，2*2=4条结果，这是笛卡尔积的典型表现。

正确的SQL查询语句 #

核心思路是通过分配单元ID（assigned_unit_id）关联分配与释放记录，同时确保操作的对应关系。

方案1：按单元+时间顺序精准匹配 #

如果分配单元的操作是按时间顺序对应的（先分配后释放，每个释放对应最近的未匹配分配），用窗口函数给同单元的操作排序后匹配：

WITH assigned AS (
    SELECT 
        assigned_unit_id,
        created_date AS assign_date,
        reason,
        key,
        ROW_NUMBER() OVER (PARTITION BY assigned_unit_id, key ORDER BY created_date) AS assign_seq
    FROM scheduler_history
    WHERE action_type = 2 AND key = "Hp8INWFYlT"
),
released AS (
    SELECT 
        assigned_unit_id,
        created_date AS release_date,
        key,
        ROW_NUMBER() OVER (PARTITION BY assigned_unit_id, key ORDER BY created_date) AS release_seq
    FROM scheduler_history
    WHERE action_type = 9 AND key = "Hp8INWFYlT"
)
SELECT 
    TIMESTAMPDIFF(minute, a.assign_date, r.release_date) AS "Total Time in use(minute)",
    a.assign_date AS "Veeahub Assigned Date",
    r.release_date AS "Veeahub Released Date",
    a.key AS "Assignment Key",
    a.reason AS "Assigned Reason"
FROM assigned a
JOIN released r 
    ON a.assigned_unit_id = r.assigned_unit_id 
    AND a.key = r.key 
    AND a.assign_seq = r.release_seq;

方案2：按单元+key分组聚合（适用于单单元单key仅一次有效操作） #

如果同一个assigned_unit_id+key组合下，只需要取最早的分配时间和对应的释放时间，可直接分组聚合：

SELECT 
    TIMESTAMPDIFF(minute, MIN(his.created_date), MAX(aff.created_date)) AS "Total Time in use(minute)",
    MIN(his.created_date) AS "Veeahub Assigned Date",
    MAX(aff.created_date) AS "Veeahub Released Date",
    his.key AS "Assignment Key",
    MAX(his.reason) AS "Assigned Reason"
FROM scheduler_history his
JOIN scheduler_history aff 
    ON his.assigned_unit_id = aff.assigned_unit_id 
    AND his.key = aff.key
WHERE his.action_type = 2 
    AND aff.action_type = 9 
    AND his.key = "Hp8INWFYlT"
GROUP BY his.assigned_unit_id, his.key;

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