MySQL：跨多行匹配模式，计算用户从巧克力到闪电泡芙的平均耗时

嘿，这个需求我之前碰过类似的，咱们直接上实用方案。首先先假设你的数据表结构大概是这样（如果和你的实际表字段有出入，对应调整就行）：

CREATE TABLE dessert_eating (
    username VARCHAR(50),
    dessert_name VARCHAR(50),
    eat_time DATETIME
);

比如你提到的示例数据可以这么插入：

INSERT INTO dessert_eating VALUES
('Paul', '巧克力', '2024-05-20 12:00:00'),
('Paul', '闪电泡芙', '2024-05-20 14:00:00'),
('Jon', '巧克力', '2024-05-20 10:00:00'),
('Jon', '闪电泡芙', '2024-05-20 14:00:00');

核心实现思路 #

我们需要定位每个用户每一次吃巧克力的时间，匹配该用户在这之后第一次吃闪电泡芙的时间，计算两者的时间差，最后对每个用户的所有有效时间差取平均值。这里用自连接的方式实现最直观：

SELECT
    de_choco.username,
    AVG(TIMESTAMPDIFF(HOUR, de_choco.eat_time, de_puff.eat_time)) AS avg_hours_between
FROM
    dessert_eating de_choco
JOIN
    dessert_eating de_puff ON de_choco.username = de_puff.username
        AND de_puff.dessert_name = '闪电泡芙'
        AND de_puff.eat_time > de_choco.eat_time
LEFT JOIN
    dessert_eating de_middle ON de_choco.username = de_middle.username
        AND de_middle.dessert_name = '闪电泡芙'
        AND de_middle.eat_time > de_choco.eat_time
        AND de_middle.eat_time < de_puff.eat_time
WHERE
    de_choco.dessert_name = '巧克力'
    AND de_middle.username IS NULL -- 确保取到的是巧克力之后的第一次闪电泡芙
GROUP BY
    de_choco.username;

代码逐行解释 #

1. 先通过de_choco筛选出所有用户吃巧克力的记录；
2. 连接de_puff表，找到同一用户、吃闪电泡芙且时间在巧克力之后的记录；
3. 用LEFT JOIN和de_middle表排除掉中间存在更早闪电泡芙的情况，保证我们拿到的是巧克力之后的第一份闪电泡芙记录；
4. 用TIMESTAMPDIFF(HOUR, ...)计算小时级的时间差，再用AVG()取平均值，最后按用户分组输出。

简化版（不限制第一次闪电泡芙） #

如果你的需求是不区分是否是第一次，只要是巧克力之后的所有闪电泡芙都纳入计算，那可以去掉中间的LEFT JOIN部分，简化代码：

SELECT
    de_choco.username,
    AVG(TIMESTAMPDIFF(HOUR, de_choco.eat_time, de_puff.eat_time)) AS avg_hours_between
FROM
    dessert_eating de_choco
JOIN
    dessert_eating de_puff ON de_choco.username = de_puff.username
        AND de_puff.dessert_name = '闪电泡芙'
        AND de_puff.eat_time > de_choco.eat_time
WHERE
    de_choco.dessert_name = '巧克力'
GROUP BY
    de_choco.username;

额外注意点 #

• 时间差单位可以调整：把TIMESTAMPDIFF的第一个参数换成MINUTE、SECOND或者DAY，就能得到对应单位的时间差；
• 无效记录自动排除：如果用户只吃了巧克力没吃闪电泡芙，或者反过来，这类记录会被自动过滤，不会影响平均值计算；
• 复杂场景可换窗口函数：如果你的表有唯一标识字段，也可以用ROW_NUMBER()窗口函数标记甜点类型，再关联对应记录，不过自连接的方式更易懂。

内容的提问来源于stack exchange，提问作者Lets Script This