按规则关联Segment_info与Baggage_Info的SQL不符合预期，求修正

数据表结构与数据 #

Segment_info表 #

包含字段：BookingID、SegmentID、PassengerID、SSRCode、SSRCreatedUTC，数据如下：

Baggage_Info表 #

包含字段：SegmentID、BaggageID、Weight，数据如下：

关联规则 #

• 当SegmentID下存在SSRCode为AB20/AB25/AB30/AB40的记录时，将同SegmentID的所有行李信息关联到该类SSR记录
• 若不存在AB类SSR，但存在PB20/PB25/PB30/PB40类SSR，将行李关联到同SegmentID下该类中SSRCreatedUTC最早的SSR记录
• 若前两类都不存在，存在WCI/MCI类SSR时，将行李关联到同SegmentID下该类中SSRCreatedUTC最早的SSR记录

预期结果 #

现有问题代码 #

WITH 

segment_info AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY SegmentID ORDER BY
      CASE WHEN SSRCode IN ('AB20', 'AB25', 'AB30', 'AB40') THEN 1
      WHEN SSRCode IN ('PB20', 'PB25', 'PB30', 'PB40') THEN 2  
      WHEN SSRCode IN ('WCI', 'MCI') THEN 3
      ELSE 4 END,
      SSRCreatedUTC ASC
    ) AS rn
  FROM
    `Segment_info`
),

final_output AS (SELECT

    SI.BookingID,
    SI.SegmentID,
    SI.PassengerID,

    SI.SSRCode,
    SI.SSRCreatedUTC,

    BI.BaggageID,
    BI.Weight
FROM
  segment_info SI
LEFT JOIN
  `Baggage_Info` BI ON SI.SegmentID = BI.SegmentID
WHERE
  SI.rn = 1)

SELECT * FROM final_output

问题分析与修正代码 #

问题点 #

1. 原代码仅保留了每个SegmentID中排第一的SSR记录，丢失了其他SSR记录，不符合展示所有SSR的需求
2. 关联逻辑错误：直接将所有行李关联到rn=1的SSR，未处理其他SSR行李字段为NULL的情况，也未实现"仅目标SSR关联所有行李"的核心逻辑

修正后的SQL代码 #

WITH 
-- 为每个SegmentID筛选符合优先级的目标SSR记录
target_ssr AS (
  SELECT
    BookingID,
    SegmentID,
    PassengerID,
    SSRCode,
    SSRCreatedUTC,
    -- 按规则优先级排序，取每个SegmentID的第一条作为目标SSR
    ROW_NUMBER() OVER (PARTITION BY SegmentID ORDER BY
      CASE WHEN SSRCode IN ('AB20', 'AB25', 'AB30', 'AB40') THEN 1
           WHEN SSRCode IN ('PB20', 'PB25', 'PB30', 'PB40') THEN 2  
           WHEN SSRCode IN ('WCI', 'MCI') THEN 3
           ELSE 4 END,
      SSRCreatedUTC ASC
    ) AS rn
  FROM `Segment_info`
  -- 仅保留规则涉及的SSR类型
  WHERE SSRCode IN ('AB20','AB25','AB30','AB40','PB20','PB25','PB30','PB40','WCI','MCI')
),
-- 关联目标SSR与对应SegmentID的所有行李
ssr_with_baggage AS (
  SELECT
    ts.BookingID,
    ts.SegmentID,
    ts.PassengerID,
    ts.SSRCode,
    ts.SSRCreatedUTC,
    bi.BaggageID,
    bi.Weight
  FROM target_ssr ts
  LEFT JOIN `Baggage_Info` bi ON ts.SegmentID = bi.SegmentID
  WHERE ts.rn = 1
)
-- 将所有原始SSR记录与关联结果匹配，仅目标SSR显示行李信息
SELECT
  si.BookingID,
  si.SegmentID,
  si.PassengerID,
  si.SSRCode,
  si.SSRCreatedUTC,
  swb.BaggageID,
  swb.Weight
FROM `Segment_info` si
LEFT JOIN ssr_with_baggage swb 
  ON si.BookingID = swb.BookingID
  AND si.SegmentID = swb.SegmentID
  AND si.PassengerID = swb.PassengerID
  AND si.SSRCode = swb.SSRCode
  AND si.SSRCreatedUTC = swb.SSRCreatedUTC
ORDER BY si.BookingID, si.SSRCode, swb.BaggageID;

逻辑说明 #

1. target_ssr CTE：按规则优先级为每个SegmentID的SSR排序，筛选出需要关联行李的目标SSR记录
2. ssr_with_baggage CTE：将目标SSR与同SegmentID的所有行李关联，得到目标SSR的完整行李匹配结果
3. 最终查询：保留所有原始SSR记录，仅当原始SSR是目标SSR时匹配行李信息，其他SSR的行李字段自动为NULL，完全符合预期输出要求

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