sql - 在连续 block 中查询具有相同值的最后一条记录

Question

在SQLite中，我具有以下格式的表datatable：

+-----------------------+-----+-----+
|       timestamp       |  x  |  y  |
+-----------------------+-----+-----+
| "2015-01-30 23:00:00" |  1  |  1  |
| "2015-01-30 22:00:00" |  2  |  2  |
| "2015-01-30 21:00:00" |  2  |  2  |
| "2015-01-30 20:00:00" |  2  |  2  |
| "2015-01-30 19:00:00" |  3  |  3  |
| "2015-01-30 18:00:00" |  4  |  4  |
| "2015-01-30 17:00:00" |  2  |  2  |
+-----------------------+-----+-----+

我想在一个连续的块中提取最旧的记录（按时间戳记），其中 x,y值与第二个最新条目的 x,y值匹配。我有一个有效的查询（请参阅文章末尾），但是它在多个子查询中效率很低。我知道一定有更好的方法。

使用上面的示例表：


搜索坐标 x,y必须与最近的第二个条目中的 2,2匹配（时间戳='2015-01-30 22:00:00'）
记录必须来自相同 x,y（ 22:00- 20:00）的连续块，但不能来自也具有坐标 2,2（即 17:00）的任何较早的记录
期望值是此 2,2块或 20:00中最早的记录


这是我到目前为止的查询。它可以工作，但是对于大型表可能会很慢-尤其是在使用字符串连接时。

-- find oldest time in continuous block that matches coordinates of interest
select min(timestamp) from datatable
where timestamp > (
    -- find most recent time that does not match coordinates of interest
    select max(timestamp) from datatable
    where timestamp < '2015-01-30 23:00:00'
    and x || ' | ' || y != (
        -- find coordinates of interest (2nd most recent record)
        select x || ' | ' || y
        from datatable
        where timestamp < '2015-01-30 23:00:00'
        order by timestamp
        limit 1
        -- returns 2 | 2
    )
    -- returns '2015-01-30 19:00:00
)
-- returns '2015-01-30 20:00:00 (which is the expected result)

Answer 1

可以删除字符串串联：

select min(timestamp), x, y
from datatable
where timestamp > (select max(timestamp)
                   from datatable
                   join (select x, y
                         from datatable
                         order by timestamp desc
                         limit 1 offset 1) as second
                   on datatable.x <> second.x
                   or datatable.y <> second.y
                   where timestamp < (select timestamp
                                      from datatable
                                      order by timestamp desc
                                      limit 1 offset 1))

使用 timestamp上的索引，两个查询应该都不错。

最快的方法可能是在应用程序中搜索块的末尾，即读取此查询的结果：

select timestamp, x, y
from datatable
order by timestamp desc
limit -1 offset 1

并在 x,y值更改时停止。