mysql - 从消息表中获取最新的 'role-switch' 时间戳

Question

问题

我正在尝试在工单对话中“一方”发生变化后获得最低时间戳(最早)，以查看自第一次回复最新消息以来已经过了多长时间。

示例:

A (10:00) : Hello
A (10:05) : How are you?
B (10:06) : I'm fine, thank you
B (10:08) : How about you?
A (10:10) : I'm fine too, thank you <------
A (10:15) : I have to go now, see you around!

现在我要找的是箭头指示的消息的时间戳。对话“一方”发生变化后的第一条消息，在本例中是从用户变为支持。

来自表“消息”的示例数据:

mid    conv_id   uid    created_at   message                           type
2750   1         3941   1341470051   Hello                             support
3615   1         3941   1342186946   How are you?                      support
4964   1         2210   1343588022   I'm fine, thank you               user
4965   1         2210   1343588129   How about you?                    user
5704   1         3941   1344258743   I'm fine too, thank you           support
5706   1         3941   1344258943   I have to go now, see you around! support

到目前为止我尝试了什么:

select
n.nid AS `node_id`,
(
    SELECT m_inner.created_at
    FROM messages m_inner
    WHERE m_inner.mid = messages.mid AND
    CASE
        WHEN MAX(m_support.created_at) < MAX(m_user.created_at) THEN -- latest reply from user
            m_support.created_at
        ELSE
            m_user.created_at
    END <= m_inner.created_at
    ORDER BY messages.created_at ASC
    LIMIT 0,1
) AS `latest_role_switch_timestamp`
from
node n
left join messages m on n.nid = messages.nid
left join messages m_user on n.nid = m_user.nid and m_user.type = 'user'
left join messages m_support on n.nid = m_support.nid and m_support.type = 'support'
GROUP BY messages.type, messages.nid
ORDER BY messages.nid, messages.created_at DESC

首选结果:

node_id    latest_role_switch_timestamp
1          1344258743

但这并没有为子查询产生任何结果。我是在寻找正确的方向还是应该尝试其他方法？我不知道这在 mysql 中是否可行。

此外，这还使用了一个子查询，出于性能原因，这并不理想，考虑到此查询可能会在概览中使用，这意味着它必须为概览中的每条消息运行该子查询。

如果您需要更多信息，请告诉我，因为我无能为力

Answer 1

将表连接到自身的最大日期摘要以获取最后一个 block 的消息，然后使用 mysql 的特殊分组支持从每个对话的行中选择第一行:

select * from (
 select * from (
  select m.*
  from messages m
  join (
    select conv_id, type, max(created_at) last_created
    from messages
    group by 1,2) x
    on x.conv_id = m.conv_id
    and x.type != m.type
    and x.last_created < m.created_at) y
 order by created_at) z
group by conv_id

这将返回作为最后一个 block 的第一条消息的整个行。

参见 SQLFiddle .

性能会很好，因为没有相关的子查询。