时间戳

首页 > 解决方案 > 在 select where 语句中展开查询结果

mysql - 在 select where 语句中展开查询结果

问题描述

我需要在同一张表上获取两个日期之间的所有值，但还要包括最早的值之前的值：

更清楚地说，我有下表（表 1。大约有 4600 万行）：

Table 1. (Table I have)
updatetime            | value
        .                   .
        .                   .
2018-01-01 08:32:02   |     2
2018-01-01 09:23:12   |     5
2018-01-01 10:45:00   |     8 * value to include on the result.
2018-01-01 11:10:44   |    10 * <-- earliest date.
2018-01-01 13:11:54   |    12 *
2018-01-01 16:14:57   |    16 *
2018-01-01 20:00:55   |    22 *
2018-01-01 22:34:43   |    23 *
2018-01-01 23:55:23   |    23 * <-- latest date.
2018-01-02 01:03:57   |    25
2018-01-02 03:39:07   |    28
        .                   .
        .                   .

我有累积值，因此，下一个值将始终等于或大于前一个值。我需要获取 2018-01-01 11:10:44 和 2018-01-02 01:03:57 之间的所有值，但是，我还需要最早日期之前的值，这意味着我需要这个结果：（表 2.)

Table 2. (Result I need)
2018-01-01 10:45:00   |     8
2018-01-01 11:10:44   |    10
2018-01-01 13:11:54   |    12
2018-01-01 16:14:57   |    16
2018-01-01 20:00:55   |    22
2018-01-01 22:34:43   |    23
2018-01-01 23:55:23   |    23

正如您在表 1 中看到的，日期空间不一样，因此，最早日期的前一个日期对我来说是未知的，并且值增量也是随机的。

我已经尝试了这两个查询，但我对性能有疑问：

第一个只是两次查询一次，由于表顺序的变化，性能很差。

SELECT * FROM his 
WHERE updatetime>=(
  SELECT updatetime 
  FROM definition 
  WHERE updatetime<"2018-01-01 11:10:44" 
  ORDER BY updatetime DESC 
  LIMIT 1
  ) and updatetime<="2018-01-02 01:03:57";

第二个应该比第一个慢，但是如果我将列定义为增量以使 MySQL 从条件的最后一个值遍历表而不是遍历它？

SELECT * FROM his 
WHERE updatetime>=(
  SELECT MAX(updatetime) 
  FROM definition 
  WHERE updatetime<"2018-01-01 11:10:44" 
  LIMIT 1
  ) AND updatetime<="2018-01-02 01:03:57";

标签： mysqlsql

解决方案

首先，为了让这些查询高效执行，您需要在 column 上建立一个索引updatetime：

CREATE INDEX idx_updatetime ON mytable(updatetime);

WHERE一种方法是在选择前一条记录的子句中使用相关子查询：

SELECT t.*
FROM mytable t
WHERE 
    t.updatetime < '2018-01-02 01:03:57'
    AND NOT EXISTS (
      SELECT 1 
      FROM mytable t1 
      WHERE t1.updatetime < '2018-01-01 11:10:44' AND t1.updatetime > t.updatetime
    )

DB Fiddle 上的演示：

| updatetime          | value |
| ------------------- | ----- |
| 2018-01-01 11:10:44 | 10    |
| 2018-01-01 13:11:54 | 12    |
| 2018-01-01 16:14:57 | 16    |
| 2018-01-01 20:00:55 | 22    |
| 2018-01-01 22:34:43 | 23    |
| 2018-01-01 23:55:23 | 23    |

另一种选择是计算updatetime子查询中的前一条记录，然后使用它来过滤表。这可能比第一个查询执行得更好。

SELECT t.*
 FROM mytable t
 INNER JOIN (
     SELECT MAX(updatetime) updatetime 
     FROM mytable 
     WHERE updatetime < '2018-01-01 11:10:44'
 ) x ON t.updatetime >= x.updatetime AND t.updatetime < '2018-01-02 01:03:57';

DB Fiddle 上的演示

推荐阅读