mysql - SQL 连接 3 个表，按最新的组值分组

Question

我有 3 个表。具有以下结构的用户、消息和用户分析:

• user (userId) - 包含所有用户
• message (messageId(pk),userId(fk),time) - 包含所有消息
• user_analytics (user_analyticsId(pk),userId(fk),device,time) -
  包含连接时收集的数据

user : messages (1:n)
user : device (1:n)

现在我想知道每天使用什么设备发送了多少条消息。因此，我首先需要根据消息时间本身为每条消息收集用于发送消息的设备(桌面、iOS、Android)。这意味着我需要 user_analytics.time <= message.time 并显示最新结果。

我看到了很多关于 greatest-n-per-group 的解决方案，但我没有让它发挥作用。

我只让它与一个需要 20 秒的子查询一起工作(user_analytics 拥有 100k 条记录和 3k 条消息......所以不多):

select  date_format(m.time,'%Y-%m-%d') as date,
        count(*) as message_count,
        ua.device
from    message m,
        user u left join user_analytics ua on (
            u.userId = ua.userId and
            ua.user_analyticsId = ( select max(user_analyticsId) 
                                from    user_analytics
                                where   userId = m.userId and
                                        time < m.time))
where   m.userId = u.userId
group by 1,3;

但这看起来非常低效。还有其他方法可以达到同样的效果吗？

更新:我忘了提到我在用户表上有一个重要条件。这就是为什么我需要连接到这个表。

我创建了一个 sql fiddle 来给你举个例子。现在我已经实现了 Jaguar Chang's比我的快 100 倍的解决方案:

sql fiddle

Answer 1

没有必要加入用户表，所以你可以像这样简化你的代码:

select  date_format(m.time,'%Y-%m-%d') as date,
        count(*) as message_count,
        ua.device
from    message m,
        left join user_analytics ua on (
            m.userId = ua.userId and
            ua.user_analyticsId = ( select max(user_analyticsId) 
                                from    user_analytics
                                where   userId = m.userId and
                                        time < m.time))
group by 1,3;

这可能不够有效，但你可以试试这个:

select  date_format(t2.time,'%Y-%m-%d') as date,
        count(*) as message_count,
        t2.last_device
from    
    (select 
      @device := 
          if(@uid = userid,
             if(tbl = 'm' ,@device, device),
             if(@uid := userid,device,device)) as last_device
      ,t1.*
      from 
          (select @device := '' , @uid :=0) as t0
      join
          (select 'ua' as tbl,userid,time,device from user_analytics
           union all
           select 'm' as tbl,userid,time,null as device from messages
          ) as t1
      order by userid,time
    ) as t2
where tbl='m'
group by 1,3;

我猜你最初的目的是根据设备上的连接时间来划分消息，所以按时间序列将消息和连接记录一起排序，然后你可以获得每条消息的最后一次连接使用的设备。

我认为这种方法会非常有效，因为 100k+3k 排序会比 3k*100k*100k 连接操作快得多

一个测试Sql Fiddle Demo .