用窗口函数代替两个窗口函数

Question

我有一个有两列的表：time和id。我们认为行先按id排序，然后按时间排序。

╔════════╦══════════╗
║ time   ║ id       ║
╠════════╬══════════╣
║ 9:10   ║  1       ║
║ 9:20   ║  1       ║
║ 10:10  ║  1       ║
║ 11:30  ║  1       ║
║ 11:50  ║  1       ║
║ 10:20  ║  2       ║
║ 10:30  ║  2       ║
║ 11:20  ║  3       ║
║ 11:50  ║  3       ║
╚════════╩══════════╝

我只想从中选择它们的id与“上一行”id相同的行，并且与前一行的时间差小于一个小时。

这可以通过以下方法实现:首先创建一个表，其中前一行有第3列时间差，第4列是id差异，并且只选择id_diff为0而time_diff大于1小时的行。

但是，这个方法似乎不优雅，因为我想分别查看每个id和每个id内部，查看其时间，并检查连续的差值是否超过一个小时。这将更好地反映分别查看每个id的逻辑，因为它们是不同的实体。

那么，如何对id进行分组，而不是使用两次窗口函数呢？我知道GROUP BY的存在。

具有两个窗口函数的工作代码：

SELECT auxiliary_table_with_lag_diffs.*
FROM (
    select info.*,
        time-lag(time) over (Order by id, time ) as diff_time,
        id-lag(id) over (order by id, time) as diff_id
    from info
    )auxiliary_table_with_lag_diffs
WHERE diff_time>'01:00:00'
    AND diff_id=0
ORDER BY id, time;

Answer 1

这里只需要一个解析函数调用:获取相同ID内的前一次。

SELECT *
FROM 
(
  select info.*,
    time - lag(time) over (partition by id order by time) as diff_time
  from info
) auxiliary_table_with_lag_diffs
WHERE diff_time > interval '1 hour';
ORDER BY id, time;

Answer 2

如果您想查看前面的行，那么为什么要先按id排序呢？

SELECT i.*
FROM (select i.*, lag(time) over (order by time) as prev_time,
             lag(id) over (order by time) as prev_id
     from info i
     ) i 
WHERE time < prev_time + interval '1 hour' and id = prev_id
ORDER BY id, time;

如果不希望在输出中使用prev_time和prev_id，只需显式地选择所需的列即可。

注意:您可能需要>而不是<，这取决于您的实际需求(问题是不明确的)。