访问间隔超过3个月- sql

Question

我正在尝试识别两次访问间隔超过3个月的id

​

​

CREATE TABLE Data (
    ID int,
    visit datetime);

INSERT INTO data (ID,visit)
VALUES
    (1,'1998-05-18 00:00:00.000'),
    (1,'1995-05-18 00:20:00.000'),
    (1,'1994-12-18 01:00:00.000'),
    (1,'1994-007-18 00:00:00.000'),
    (2,'2000-06-29 13:30:00.000'),
    (2,'2000-05-29 13:45:00.000'),
    (2,'2000-04-29 10:30:00.000'),
    (3,'2009-09-29 13:30:00.000'),
    (3,'2009-01-29 13:30:00.000'),
    (3,'2008-12-29 13:30:00.000');

Answer 1

您可以使用lag()。date/time函数在不同的数据库中有很大的不同，但其思想是：

select distinct id
from (select d.*, lag(visit) over (partition by id order by visit) as prev_visit
      from data d
     ) d
where prev_visit < visit - interval '3 month';

Here是一个db<>fiddle。它使用SQL Server，因此where子句是：

where prev_visit < dateadd(month, -3, visit);