Postgresql -选择4天前的行

Question

我在userBuyTable中有这个数据

+-------+------------+
|  ID   |    Date    |
+-------+------------+
| 11111 | 2019-11-02 |
| 11111 | 2019-11-05 |
| 22222 | 2019-11-22 |
| 33333 | 2019-11-22 |
| 33333 | 2019-11-23 |
+-------+------------+ 

我想知道像这样4天前有数据的ID在哪里

+-------+------------+
|  ID   |    Date    |
+-------+------------+
| 11111 | 2019-11-02 |
| 11111 | 2019-11-05 |
+-------+------------+

(2019-11-05 - 2019-11-02) =3

+-------+------------+
|  ID   |    Date    |
+-------+------------+
| 33333 | 2019-11-22 |
| 33333 | 2019-11-23 |
+-------+------------+

(2019-11-23 - 2019-11-22 )=1

这是意料之中的结果

+-------+------------+
|  ID   |    Date    |
+-------+------------+
| 11111 | 2019-11-05 |
| 33333 | 2019-11-23 |
+-------+------------+

我试着选择

select 
ID
,(select ID from userBuyTable ubtIn where ubtIn.ID = ubt.ID and (ubt.Date - ubtIn.Date)<4 ) 
from userBuyTable ubt where ubt.Date between '2019-11-01' and '2019-11-30'

Answer 1

使用EXISTS

select u.* from userBuyTable u
where u."Date" between '2019-11-01' and '2019-11-30'
and exists (
  select 1 from userBuyTable
  where u."Date" > "Date" and u."Date" - "Date" < 4 
) 

请参阅demo。

如果您只对具有相同ID的行感兴趣，则使用以下命令：

select u.* from userBuyTable u
where u."Date" between '2019-11-01' and '2019-11-30'
and exists (
  select 1 from userBuyTable
  where u."ID" = "ID" and u."Date" > "Date" and u."Date" - "Date" < 4 
)

请参阅demo。

结果：

| ID    | Date       |
| ----- | ---------- |
| 11111 | 2019-11-05 |
| 33333 | 2019-11-23 |  

Answer 2

如果需要原始行，我可以简单地使用lag()和lead()：

select ubt.*
from (select ubt.*,
             lag(date) over (partition by id order by date) as prev_date,
             lead(date) over (partition by id order by date) as next_date
      from userBuyTable ubt
     ) ubt
where (prev_date > date - 4 * interval '1 day') or
      (next_date < date + 4 * interval '1 day');

如果您只需要与条件匹配的最新行，则：

select ubt.*
from (select ubt.*,
             lag(date) over (partition by id order by date) as prev_date
      from userBuyTable ubt
     ) ubt
where prev_date > date - 4 * interval '1 day';

或者：

select ubt.id, max(ubt.date)
from userBuyTable ubt
where exists (select 1
              from userBuyTable ubt2
              where ubt2.id = ubt.id and ubt2.date > date - 4 * interval '1 day'
             )
group by ubt.id;