如何优雅实现有序Foo表与Bar表的关联匹配？

问题背景 #

存在两类事件：Foo和Bar，且Bar始终跟随Foo（Foo→Bar）。需要将两个有序事件表按对应顺序关联，合并出包含双方字段的结果表。

现有表结构 #

Foo事件表 #

|----|---------------|------|
| id | ordering-foo  | other|
|----|---------------|------|
|1   |1              |X     |
|1   |2              |Y     |
|----|---------------|------|
|2   |1              |X     |
|----|---------------|------|
|3   |2              |X     |
|----|---------------|------|
|4   |1              |X     |
|4   |2              |Y     |
|----|---------------|------|

其中ordering-foo表示每个id下Foo事件的发生顺序。

Bar事件表 #

|----|---------------|-------|
| id | ordering_bar  | other |
|----|---------------|-------|
|1   |A              |XX     |
|1   |B              |YY     |
|----|---------------|-------|
|3   |B              |XX     |
|----|---------------|-------|
|4   |A              |XX     |
|----|---------------|-------|

注意事项 #

• Foo和Bar均为有序表，但排序规则不同，无法直接通过排序字段关联；实际场景中排序字段为时间戳，满足foo.ordering < bar.ordering，但对关联帮助有限。
• 排序序列可能不完整，比如存在id为3的Bar记录ordering_bar为B，但无A的记录。
• 可能存在仅有Foo记录而无对应后续Bar记录的情况，如id为2、4的部分记录。

期望结果表 #

|----|----------|-----------|-----------|
| id | ordering | other-foo | other-bar |
| 1  | 1        | X         | XX        |
| 1  | 2        | Y         | YY        |           
|----|----------|-----------|-----------|
| 2  | 1        | X         | null      |           
|----|----------|-----------|-----------|
| 3  | 2        | X         | XX        |
|----|----------|-----------|-----------|
| 4  | 1        | X         | XX        |
| 4  | 2        | Y         | null      |           
|----|----------|-----------|-----------|

现有方案的问题 #

针对每个id最多两类事件的场景，曾尝试用CASE语句实现关联逻辑：

case
  when count(*) over (partition by foo.id) = 1 and count(*) over (partition by bar.id) = 1 then foo.ordering_foo
  when count(*) over (partition by foo.id) = 2 and count(*) over (partition by bar.id) = 1 then 1
  when count(*) over (partition by foo.id) = 2 and count(*) over (partition by bar.id) = 2 and max(bar.ordering_bar) over (partition by bar.id) = bar.ordering_bar then 2
  when count(*) over (partition by foo.id) = 2 and count(*) over (partition by bar.id) = 2 and min(bar.ordering_bar) over (partition by bar.id)= bar.ordering_bar then 1
  else -1
end as ordering, 

但该方案存在以下问题：

• 可读性差，逻辑复杂难以理解
• 维护困难，扩展到更多事件类型时需要修改大量条件
• 灵活性不足，无法适配更复杂的序列缺失场景
• 难以直接获取对应的other字段，需要额外处理

通用优雅解决方案 #

核心思路是为每个id下的Foo和Bar分别生成统一的组内行号，再通过id和行号进行关联，这样无论原排序规则是什么，都能按顺序一一对应。

具体SQL实现 #

WITH foo_ranked AS (
    SELECT 
        id,
        ordering_foo AS ordering,
        other AS other_foo,
        -- 按id分组，根据Foo的排序字段生成行号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY ordering_foo) AS row_num
    FROM foo
),
bar_ranked AS (
    SELECT 
        id,
        other AS other_bar,
        -- 按id分组，根据Bar的排序字段生成行号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY ordering_bar) AS row_num
    FROM bar
)
-- 以Foo表为主表，通过id和行号关联Bar表
SELECT 
    f.id,
    f.ordering,
    f.other_foo,
    b.other_bar
FROM foo_ranked f
LEFT JOIN bar_ranked b 
    ON f.id = b.id 
    AND f.row_num = b.row_num
ORDER BY f.id, f.ordering;

方案优势 #

• 通用性强：适配每个id下任意数量的事件记录，不受原排序规则限制
• 可读性高：逻辑分层清晰，通过CTE拆分步骤，便于理解和维护
• 灵活性好：天然支持序列缺失、部分记录无对应事件等场景
• 易于扩展：新增其他关联事件表时，只需添加对应的ranked CTE并按相同逻辑关联即可

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