MySql选择相对相似的日期 [英] MySql select relative similar dates

问题描述

我尝试从同一时间窗口中的表中选择记录，例如

I trying to select records from a table that are in the same time window, for example

record_date            |   record_data
4/20/2015 5:00:00 PM   |  23
4/20/2015 5:08:00 PM   |   3
4/20/2015 5:09:00 PM   |  98

如果我设置2分钟窗口将导致：

if i set 2 minutes window will be result in:

4/20/2015 5:08:00 PM   |   3
4/20/2015 5:09:00 PM   |  98

但是，如果我选择15分钟的窗口必须得到：

but, if i choose 15 minutes window must get:

4/20/2015 5:00:00 PM   |  23
4/20/2015 5:08:00 PM   |   3
4/20/2015 5:09:00 PM   |  98

该怎么做？ BETWEEN或DATEDIFF语句需要与之进行比较的绝对日期，在这种情况下，比较必须与其他记录值相对而不是外部时间。

how can be done that ? BETWEEN or DATEDIFF statements needs a absolute date to compare with, and in this case the comparison must be relative to other records values and not to an external time.

推荐答案

Mysql不是很好（这意味着没有内置的）。

Mysql isn't very good at this (which means - there's nothing built in for it).

我们需要做的是根据 record_date 订单将每个行的排名相关联。即第一行得到1的等级，按日期排列的最近的下一行得到2的等级，等等。

What we need to do, is associate a rank with each row, based on record_date order. ie, first row gets a rank of 1, the immediate next row by date gets a rank of 2, and so forth.

如果我们这样做，我们可以比较一行到下一个相当容易（即比较等级到等级+ 1 ）

If we do that, we can then compare one row to the next fairly easily (ie compare rank to rank + 1)

这将给我们配对的行和按照日期排列的直接下一行，然后我们可以筛选那些被您选择的窗口分开的行。

This will give us pairings of rows, and the immediate next row by date, which we can then filter for those that are separated by your window of choice.

为了将该结果转换为两列结果，我们需要重复查询， union 结果，将第一个日期和数据放在第一个查询，第二个查询中的第二个日期和数据。

In order to transform that result, into a two column result, we need to repeat the query, union the results together, taking the first date and data in the first query, and the second date and data in the second query.

union 默认情况下 distinct ，所以我们不会得到任何重复的行。这给我们以下查询：

union is by default distinct, so we dont get any repeated rows. This gives us the following query:

select * from (
  select t1.record_date, t1.record_data from
  (select @rank := @rank + 1 as rank, records.*
    from records, (select @rank := 0) q order by record_date asc) t1
  left join
  (select @rank2 := @rank2 + 1 as rank, records.*
    from records, (select @rank2 := 0) q order by record_date asc) t2
  on t1.rank + 1 = t2.rank
  where t2.record_date < t1.record_date + interval 2 minute
union
select t2.record_date, t2.record_data from
  (select @rank := @rank + 1 as rank, records.*
    from records, (select @rank := 0) q order by record_date asc) t1
  left join
  (select @rank2 := @rank2 + 1 as rank, records.*
    from records, (select @rank2 := 0) q order by record_date asc) t2
  on t1.rank + 1 = t2.rank
  where t2.record_date < t1.record_date + interval 2 minute
) q
order by record_date asc;

与演示这里

或者，您可以使用这种稍微不那么高的方式： / p>

Or alternatively, you can do it in a slightly less over-the-top manner like this:

select *
  from (
    select r1.record_date, r1.record_data
      from records r1
        inner join records r2
          on r2.record_date = (select min(record_date) from records where record_date > r1.record_date)
      where r2.record_date < r1.record_date + interval 2 minute
    union
    select r2.record_date, r2.record_data
      from records r1
        inner join records r2
          on r2.record_date = (select min(record_date) from records where record_date > r1.record_date)
      where r2.record_date < r1.record_date + interval 2 minute
  ) q
  order by record_date asc;

我们不同于排名，只需通过查找的子查询直接连接到下一个连续日期

Where we dont both with ranking, and just join directly to the next sequential date via a subquery that finds it.

更新演示此处

这篇关于MySql选择相对相似的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！