从messages表中获取最新的“角色切换”时间戳

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!

现在我要找的是箭头所指示的消息的时间戳。会话“侧”之后的第一条消息发生了变化，在这种情况下，从用户到支持。

表“messages”中的示例数据：

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

将表加入到自己的最大日期摘要中，获取最后一个块的消息，然后使用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

这将返回整个行，这是最后一个块的第一个消息。

见SQLFiddle。

性能将相当好，因为没有相关的子查询。