如何在SQL中针对每组多时间范围执行非等值连接（non-equi join）？

需要在SQL中执行非等值连接，判断表A的breakfast_date是否落在表B的日期范围内。但表B采用长格式存储：每个person_id对应多个period，每个period包含起始和结束两个日期，且无法预知每个用户的period数量。

示例数据 #

表A #

表B #

表B说明：每个person_id可能有一个或多个period，编写查询时无法预知每个用户的period数量。

期望输出 #

SQL方言 #

使用基于Trino SQL的AWS Athena。

可复现数据 #

WITH

table_a AS (
  SELECT * FROM (VALUES
    (1, DATE('2023-03-12'), 'banana'),
    (1, DATE('2023-03-25'), 'apple'),
    (1, DATE('2023-04-01'), 'orange'),
    (1, DATE('2023-04-05'), 'kiwi'),
    (1, DATE('2023-04-22'), 'grapefruit'),
    (2, DATE('2024-12-15'), 'strawberry'),
    (2, DATE('2024-01-11'), 'blueberry'),
    (2, DATE('2024-02-12'), 'mango'),
    (2, DATE('2024-02-29'), 'watermelon'),
    (2, DATE('2024-03-10'), 'pear')
  ) AS t(person_id, breakfast_date, fruit_eaten_for_breakfast)
),

table_b AS (
  SELECT * FROM (VALUES
    (1, DATE('2023-03-15'), 1),
    (1, DATE('2023-03-30'), 1),
    (1, DATE('2023-04-02'), 2),
    (1, DATE('2023-04-10'), 2),
    (1, DATE('2023-04-12'), 3),
    (1, DATE('2023-04-20'), 3),
    (2, DATE('2024-01-01'), 1),
    (2, DATE('2024-01-05'), 1),
    (2, DATE('2024-02-10'), 2),
    (2, DATE('2024-02-13'), 2)
  ) AS t(person_id, period_start_and_end, period)
)

已尝试的方法 #

目前没太多进展。熟悉的常规非等值连接是把起始和结束日期放在不同列，但当前场景下每个person_id有多个数量未知的period，转宽格式也处理不了这种情况。

解决方案 #

先将表B转换为每个period对应一行、包含起始和结束日期的格式，再和表A做非等值连接。具体步骤：

1. 对表B按person_id和period分组，提取每个组的最小日期作为period_start，最大日期作为period_end；
2. 将转换后的表B与表A按person_id关联，同时筛选breakfast_date落在period_start和period_end之间的记录。

完整SQL查询：

WITH

table_a AS (
  SELECT * FROM (VALUES
    (1, DATE('2023-03-12'), 'banana'),
    (1, DATE('2023-03-25'), 'apple'),
    (1, DATE('2023-04-01'), 'orange'),
    (1, DATE('2023-04-05'), 'kiwi'),
    (1, DATE('2023-04-22'), 'grapefruit'),
    (2, DATE('2024-12-15'), 'strawberry'),
    (2, DATE('2024-01-11'), 'blueberry'),
    (2, DATE('2024-02-12'), 'mango'),
    (2, DATE('2024-02-29'), 'watermelon'),
    (2, DATE('2024-03-10'), 'pear')
  ) AS t(person_id, breakfast_date, fruit_eaten_for_breakfast)
),

table_b AS (
  SELECT * FROM (VALUES
    (1, DATE('2023-03-15'), 1),
    (1, DATE('2023-03-30'), 1),
    (1, DATE('2023-04-02'), 2),
    (1, DATE('2023-04-10'), 2),
    (1, DATE('2023-04-12'), 3),
    (1, DATE('2023-04-20'), 3),
    (2, DATE('2024-01-01'), 1),
    (2, DATE('2024-01-05'), 1),
    (2, DATE('2024-02-10'), 2),
    (2, DATE('2024-02-13'), 2)
  ) AS t(person_id, period_start_and_end, period)
),

-- 转换表B为宽格式，每个period对应一行起止日期
transformed_b AS (
  SELECT
    person_id,
    period,
    MIN(period_start_and_end) AS period_start,
    MAX(period_start_and_end) AS period_end
  FROM table_b
  GROUP BY person_id, period
)

-- 非等值连接匹配日期范围
SELECT
  a.person_id,
  a.breakfast_date,
  a.fruit_eaten_for_breakfast,
  b.period
FROM table_a a
JOIN transformed_b b
  ON a.person_id = b.person_id
  AND a.breakfast_date BETWEEN b.period_start AND b.period_end
ORDER BY a.person_id, a.breakfast_date;

说明：通过分组聚合将表B的长格式转为标准的起止日期格式，后续的非等值连接就和常规场景一致了，无需关心每个用户的period数量。

内容的提问来源于Stack Exchange，提问作者Emman