Mysql的顺序记录由类似的时间和相同的用户 [英] Mysql order records by similar time and same user

问题描述

我在MySql表中有这些记录

  id | record_date | record_user 
 1 | 4/20/2015 5:00:00 PM |肯尼
 2 | 4/20/2015 5:08:00 PM |凯尔
 3 | 4/20/2015 5:08:20 PM | Stan 
 4 | 4/20/2015 5:09:00 PM |凯尔
 5 | 4/20/2015 6:40:00 PM |卡特曼
 6 | 4/20/2015 6:41:00 PM | Timmy 
 7 | 4/20/2015 6:45:00 PM | Timmy 
 8 | 4/20/2015 6:45:20 PM | Timmy 
 9 | 4/20/2015 6:45:30 PM | Stan 
  

我想通过record_user和2分钟的时间窗口来排序这些记录。例如：凯尔在5:08和5:09之间有两个记录，必须粘在一起。

  record_date | ids | record_user 
 4/20/2015 5:08:00 PM | 2,4 | kyle 
  

在其他情况下，Stan有2条记录，但在不同的时间窗口

  record_date | ids | record_user 
 4/20/2015 5:08:20 PM | 3 | stan 
 4/20/2015 6:40:00 PM | 9 | stan 
  

对于本示例中的所有表，结果必须是

  record_date | ids | record_user 
 4/20/2015 5:00:00 PM | 1 | kenny 
 4/20/2015 5:08:00 PM | 2,4 | kyle 
 4/20/2015 5:08:20 PM | 3 | stan 
 4/20/2015 6:40:00 PM | 5 | cartman 
 4/20/2015 6:40:00 PM | 6,7,8 | timmy 
 4/20/2015 6:40:00 PM | 9 | stan 
  

我可以连接id中的id，并获取record_user和record_date，但我不知道怎么做日期的事情。 在这个问题 @pala_（谢谢你，你摇滚）。他解释了mysql的交易时间如何很好，并帮助我了解很多时间窗口的麻烦，但是mysql现在对我来说很头痛。

解决方案

请注意，我认为您的预期结果是错误的（即，显示某些群组的日期错误，显示分组ID不应该组合在一起，例如显示三个值，只有两个在两分钟的窗口内落下），同时还要注意要从组而不是最后显示第一次，以下查询将会做它：

  select min（record_date）record_date，group_concat（id order by id asc）ids，record_user 
 from 
选择if（（record_date< @last_record + interval 2分钟）和（@last_user = record_user））或@last_record ='0000-00-00 00:00:00'，
 if（ （@last_record：= record_date）和（@last_user：= record_user），@group，@group），
 if（（@ last_record：= record_date）和（@last_user：= record_user），@group：= @组+ 1，@group：= @group + 1））子组，记录。* 
来自记录，（选择@last_record：='0000-00-00 00:00:00'，@last_user：=''，@group：= 1）q 
 order by record_user，record_date asc 
）q 
 group by subgroup 
 order by min（record_date）asc; 
  

由于mysql没有任何窗口功能，因此必须使用变量创建每个组排名，为了强制mysql以一定的顺序来评估我们的变量，我们必须将它们包装成一堆条件语句。这使得查询丑陋，但它的功能。

这里的结果

I have these records in MySql table

   id  |  record_date            |   record_user
   1   |  4/20/2015 5:00:00 PM   |   Kenny
   2   |  4/20/2015 5:08:00 PM   |   Kyle
   3   |  4/20/2015 5:08:20 PM   |   Stan
   4   |  4/20/2015 5:09:00 PM   |   Kyle
   5   |  4/20/2015 6:40:00 PM   |   Cartman
   6   |  4/20/2015 6:41:00 PM   |   Timmy
   7   |  4/20/2015 6:45:00 PM   |   Timmy
   8   |  4/20/2015 6:45:20 PM   |   Timmy
   9   |  4/20/2015 6:45:30 PM   |   Stan

And i want to order those records by record_user and a 2 minutes time window. For example: Kyle have two records between in 5:08 and 5:09 and must be stick together.

 record_date           |  ids |  record_user
4/20/2015 5:08:00 PM   |  2,4 | kyle

in other case, Stan have 2 records but are in different time window

 record_date           |  ids |  record_user
4/20/2015 5:08:20 PM   |    3 | stan
4/20/2015 6:40:00 PM   |    9 | stan

For all the table in this example, the result must be

 record_date           |  ids |  record_user
4/20/2015 5:00:00 PM   |    1 | kenny
4/20/2015 5:08:00 PM   |  2,4 | kyle
4/20/2015 5:08:20 PM   |    3 | stan
4/20/2015 6:40:00 PM   |    5 | cartman
4/20/2015 6:40:00 PM   |6,7,8 | timmy
4/20/2015 6:40:00 PM   |    9 | stan

I can concatenate the id's in ids and get the record_user, and record_date but i don't know how do the date thing. In this question @pala_ (thanks man, u rock). He explain how deal whit time in mysql very well and help me a lot to understand the time window trouble but mysql is a headache for me right now.

解决方案

With the caveat that I think your expected result is wrong (ie, showing wrong dates for certain groups, showing grouped ids that shouldn't be grouped together such as showing three values for timmy where only two fall within a two minute window), and also with the caveat that you want to show the first time from the group rather than the last, the following query will do it:

  select min(record_date) record_date, group_concat(id order by id asc) ids, record_user
    from (
      select if(((record_date < @last_record + interval 2 minute) and (@last_user = record_user)) or @last_record = '0000-00-00 00:00:00', 
               if((@last_record := record_date) and (@last_user := record_user), @group, @group),
               if((@last_record := record_date) and (@last_user := record_user), @group := @group + 1, @group := @group + 1)) subgroup, records.* 
        from records, (select @last_record := '0000-00-00 00:00:00', @last_user := '', @group := 1) q
        order by record_user, record_date asc
  ) q 
  group by subgroup
  order by min(record_date) asc;

As mysql does not have any windowing functionality, it's necessary to create a per group ranking with variables, and in order to force mysql to evaluate our variables in a certain order, we have to wrap them in a bunch of conditional statements. This makes the query ugly, but it functions.

results here

这篇关于Mysql的顺序记录由类似的时间和相同的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！