PostgreSQL按分组日期范围过滤JOIN关联数据的实现问题

现有数据表说明 #

表1 事件发生记录表 #

存储事件发生记录，每个唯一事件对应唯一的event_id，同时包含字段：事件发生日期date_event、人员标识person、人员性别gender，表结构样例如下：

person| event_id| date_event | gender|
----- |---------|------------|-------|
a     |  86i    | 2012-01-25 |   m   |
a     |  87i    | 2012-05-30 |   m   |
a     |  88i    | 2012-09-20 |   m   |
a     |  89i    | 2012-12-20 |   m   |
b     |  15i    | 2015-04-06 |   f   |
b     |  16i    | 2016-07-06 |   f   |
b     |  17i    | 2016-04-30 |   f   |
b     |  18i    | 2016-11-28 |   f   |
----- |---------|------------|-------|

表2 人员项目参与区间表 #

存储人员参与项目的时间区间，包含字段：人员标识person、项目参与开始日期date_start、项目参与结束日期date_end，表结构样例如下：

person| date_start | date_end   |
----- |------------|------------|
a     | 2012-02-05 | 2012-03-30 |
a     | 2012-06-26 | 2012-08-28 |
a     | 2012-09-15 | 2012-12-31 |
b     | 2015-01-24 | 2015-03-30 |
b     | 2016-07-01 | 2016-10-01 |
b     | 2016-11-25 | 2016-12-30 |
----- |------------|------------|

需求说明 #

过滤表1，仅保留事件发生日期date_event落在对应人员任意一段项目参与时间区间[date_start, date_end]内的记录，期望结果样例如下：

person| event_id| date_event | gender|
----- |---------|------------|-------|
a     |  88i    | 2012-09-20 |   m   |
a     |  89i    | 2012-12-20 |   m   |
b     |  16i    | 2016-07-06 |   f   |
b     |  18i    | 2016-11-28 |   f   |
----- |---------|------------|-------|

原有写法错误原因 #

之前的写法错误主要出在GROUP BY person这一步，该操作会强制每个人员仅返回一条记录，丢失符合条件的其他事件。此外先JOIN再过滤的写法在数据量极大的场景下会产生大量冗余中间行，效率偏低。

最优实现方案（适配大数据量场景） #

推荐使用EXISTS子查询实现，该写法不需要产生冗余的JOIN中间结果，执行效率更高，PostgreSQL优化器对这类查询的优化效果很好：

CREATE TABLE desiredresult AS
SELECT t1.*
FROM table1 t1
WHERE EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t2.person = t1.person
      AND t1.date_event BETWEEN t2.date_start AND t2.date_end
);

如果希望用JOIN写法实现，需要去掉错误的GROUP BY，同时对结果去重（避免同一个事件匹配到多个项目区间时产生重复行）：

CREATE TABLE desiredresult AS
SELECT DISTINCT t1.*
FROM table1 t1
JOIN table2 t2 
  ON t1.person = t2.person
  AND t1.date_event BETWEEN t2.date_start AND t2.date_end;

内容的提问来源于stack exchange，提问作者mkpcr