如何为包含UNNEST展开重复记录操作的SQL语句生成对应关系代数

原SQL语句 #

SELECT
  id,
  (
  SELECT
    h.is_active
  FROM
    UNNEST(history.all_of_history) h
  WHERE
    start_date <= "2021-06-01" AND (end_date >= "2021-06-01" OR end_date IS NULL))
FROM
  `table`

前提说明 #

涉及的基础表table记为关系R，包含两个属性：

• id：主键/唯一标识列
• history：记录类型属性，其子属性all_of_history为重复记录集合，集合内每个元素包含三个属性：is_active、start_date、end_date

关系代数推导步骤 #

算子约定 #

• σ<条件>：选择算子，筛选出满足条件的行
• π<属性列表>：投影算子，仅保留指定的属性列
• ⟕<连接条件>：左外连接算子，保留左关系的所有行，匹配右关系的符合条件行
• µ<映射规则>：解嵌套算子，对应SQL的UNNEST操作，将指定的嵌套集合展开为多行，和原行做关联

分步推导 #

1. 解嵌套展开重复集合
   将R中每一行的history.all_of_history嵌套集合展开，得到展开后的临时关系R1：

R1 = µ<all_of_history = history.all_of_history>(R)

R1包含属性：id、history、is_active、start_date、end_date
2. 筛选符合日期条件的记录
对R1应用选择条件，筛选出2021-06-01生效的历史记录，得到临时关系R2：

R2 = σ<start_date ≤ "2021-06-01" ∧ (end_date ≥ "2021-06-01" ∨ end_date IS NULL)>(R1)

3. 投影需要的关联属性
   对R2投影后续连接需要的id和is_active字段，得到临时关系R3：

R3 = π<id, is_active>(R2)

4. 左外连接保留全量id，得到最终结果
   将原关系R和R3按id做左外连接，保证原表所有id都被保留，无匹配记录的is_active返回null，最后投影最终需要的两个字段：

R_final = π<id, is_active>(R ⟕<R.id = R3.id> R3)

特殊说明 #

原SQL中的子查询为标量子查询，默认每个id最多匹配1条符合条件的历史记录，如果同一个id存在多条匹配记录，SQL会抛出「标量子查询返回多行」错误，上述关系代数逻辑和SQL原生逻辑完全一致。如果业务允许同一个id有多条匹配记录，可以在R2步骤后增加分组聚合算子（如取最新记录的is_active）适配场景。

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