我有一个名为user_dates的邂逅表，按'user'和'start'排序，如下所示。我想创建一个列，显示30天内是否有另一次相遇。基本上我要逐行检查是否&;encounter_stop&;在"邂逅"开始后30天内
user | encounter_start | encounter_stop
A | 4-16-1989 | 4-20-1989
A | 4-24-1989 | 5-1-1989
A | 6-14-1993 | 6-27-1993
A | 12-24-1999 | 1-2-2000
A | 1-19-2000 | 1-24-2000
B | 2-2-2000 | 2-7-2000
B | 5-27-2001 | 6-4-2001
我想要一张这样的表:
user | encounter_start | encounter_stop | subsequent_encounter_within_30_days
A | 4-16-1989 | 4-20-1989 | 1
A | 4-24-1989 | 5-1-1989 | 0
A | 6-14-1993 | 6-27-1993 | 0
A | 12-24-1999 | 1-2-2000 | 1
A | 1-19-2000 | 1-24-2000 | 0
B | 2-2-2000 | 2-7-2000 | 1
B | 5-27-2001 | 6-4-2001 | 0
您可以select..., exists <select ... criteria>，这将返回一个布尔值(总是true或false)，但如果真的想要1或0，只需将结果转换为整数:true=>1和false=>0。看到演示
select ts1.user_id
, ts1.encounter_start
, ts1. encounter_stop
, (exists ( select null
from test_set ts2
where ts1.user_id = ts2.user_id
and ts2.encounter_start
between ts1.encounter_stop
and (ts1.encounter_stop + interval '30 days')::date
)::integer
) subsequent_encounter_within_30_days
from test_set ts1
order by user_id, encounter_start;
差异:上述(和演示)与您的预期结果不一致:
B | 2-2-2000 | 2-7-2000| 1
子ent_encounter(最后一列)应该是0。这个条目开始并结束于2000年2月，另一个B条目开始于2001年5月。请解释这些是如何在30天内(除了一个简单的打字错误)。
注意:不要使用用户作为列名。它既是Postgres保留字，也是SQL标准保留字。有时候你可以用双引号。如果你双引号，你必须总是这样做。最大的问题是它有一个预定义的含义(运行select user;)，如果你忘记双引号是没有必要产生错误或异常;更糟糕的是——错误的结果。