基于trans_seq_num校验上下期pol_cancel_dt的SQL逻辑优化需求

需求描述 #

假设有上期（term A）和当前期（term B）两个保单期间，需完成以下校验逻辑：

• 校验上期的pol_cancel_dt是否为空；
• 判断上期最大trans_seq_num对应的记录中是否存在pol_cancel_dt，且该最大trans_seq_num需大于当前期所有trans_seq_num；
• 满足上述条件则执行对应业务逻辑。

现有问题 #

当前使用的SQL逻辑未得到预期结果，且trans_seq_num并非必然连续递减1。

现有SQL代码 #

WITH x AS (
  SELECT * FROM (
    SELECT
      pol_num
      ,term_start_dt
      ,term_end_dt,pol_cancel_dt
      ,trans_seq_num
      ,future_cancel_dt
      ,DENSE_RANK() OVER (PARTITION BY pol_num ORDER BY term_end_dt DESC) AS flag 
    FROM `gcp-ent-datalake-preprod.trns_prop_pol_hs_horison.prop_cost`
     --WHERE pol_num IN ('30766675','33896642')
    -- pol_num = '33288489' 
    ORDER BY term_start_dt, term_end_dt DESC
  ) 
)
SELECT
  *
  ,CASE
    WHEN prior_pol_cancel_dt IS NOT NULL AND current_trans_seq_num < prior_trans_seq_num THEN prior_pol_cancel_dt
    ELSE current_pol_cancel_dt
  END apply_cancelled_renewal_dt
FROM (
  SELECT
    MAX(a.pol_num) AS current_pol_num
    ,MAX(a.term_start_dt) AS current_term_start_dt
    ,a.term_end_dt AS current_term_ent_dt
    ,MAX(a.pol_cancel_dt) AS current_pol_cancel_dt
    ,MAX(a.trans_seq_num) AS current_trans_seq_num
    ,MAX(a.future_cancel_dt) AS current_future_cancel_dt
    ,MAX(a.flag) AS current_flag
    ,MAX(b.pol_num) AS prior_pol_num
    ,MAX(b.term_start_dt) AS prior_term_start_dt
    ,b.term_end_dt AS prior_term_end_dt
    ,MAX(b.pol_cancel_dt) AS prior_pol_cancel_dt
    ,MAX(b.trans_seq_num) AS prior_trans_seq_num
    ,MAX(b.future_cancel_dt) AS prior_future_cancel_dt
    ,MAX(b.flag) AS prior_flag
  FROM (
    SELECT * FROM x WHERE flag=1) a
  INNER JOIN(
    SELECT * FROM x WHERE flag = 2 ) b 
  ON a.pol_num = b.pol_num AND a.flag = b.flag - 1 
  WHERE a.pol_cancel_dt IS NOT NULL
  AND b.pol_cancel_dt IS NOT NULL
  AND greatest(a.trans_seq_num) < b.trans_seq_num 
  -- AND a.trans_seq_num = GREATEST(a.trans_seq_num) 
  -- AND b.trans_seq_num = GREATEST(b.trans_seq_num)
  GROUP BY a.term_end_dt, b.term_end_dt
)
  --WHERE a.term_start_dt < b.term_start_dt
  --if prior term GREATEST (trans_sewq num

修复后的SQL代码 #

WITH pol_terms AS (
  -- 按保单+期间分组，筛选每个期间内trans_seq_num最大的记录
  SELECT
    pol_num,
    term_start_dt,
    term_end_dt,
    pol_cancel_dt,
    trans_seq_num,
    future_cancel_dt,
    -- 标记保单期间顺序：1=当前期，2=上期
    DENSE_RANK() OVER (PARTITION BY pol_num ORDER BY term_end_dt DESC) AS term_rank
  FROM (
    SELECT
      *,
      -- 每个期间内取最新交易记录（trans_seq_num最大）
      ROW_NUMBER() OVER (PARTITION BY pol_num, term_end_dt ORDER BY trans_seq_num DESC) AS seq_rank
    FROM `gcp-ent-datalake-preprod.trns_prop_pol_hs_horison.prop_cost`
  )
  WHERE seq_rank = 1
),
current_prior_pair AS (
  -- 关联当前期与上期的有效记录
  SELECT
    curr.pol_num,
    -- 当前期字段
    curr.term_start_dt AS current_term_start_dt,
    curr.term_end_dt AS current_term_end_dt,
    curr.pol_cancel_dt AS current_pol_cancel_dt,
    curr.trans_seq_num AS current_max_trans_seq,
    curr.future_cancel_dt AS current_future_cancel_dt,
    -- 上期字段
    prior.term_start_dt AS prior_term_start_dt,
    prior.term_end_dt AS prior_term_end_dt,
    prior.pol_cancel_dt AS prior_pol_cancel_dt,
    prior.trans_seq_num AS prior_max_trans_seq,
    prior.future_cancel_dt AS prior_future_cancel_dt
  FROM pol_terms curr
  LEFT JOIN pol_terms prior
    ON curr.pol_num = prior.pol_num
    AND curr.term_rank = 1
    AND prior.term_rank = 2
  WHERE curr.term_rank = 1
)
-- 应用校验逻辑生成结果
SELECT
  *,
  CASE
    WHEN prior_pol_cancel_dt IS NOT NULL 
         AND prior_max_trans_seq > current_max_trans_seq
    THEN prior_pol_cancel_dt
    ELSE current_pol_cancel_dt
  END AS apply_cancelled_renewal_dt
FROM current_prior_pair
-- 可选：仅保留满足条件的记录用于后续业务逻辑
-- WHERE prior_pol_cancel_dt IS NOT NULL AND prior_max_trans_seq > current_max_trans_seq

关键修复说明 #

1. 精准获取期间最新交易：通过ROW_NUMBER()按保单+期间分组，确保每个期间只保留trans_seq_num最大的记录，避免取错pol_cancel_dt；
2. 正确关联期间配对：基于term_rank直接关联当前期（rank=1）和上期（rank=2），替代原SQL中错误的分组关联逻辑；
3. 严格匹配需求校验：直接用上期最大trans_seq_num和当前期最大trans_seq_num对比，同时校验上期pol_cancel_dt非空，完全符合需求逻辑；
4. 简化逻辑结构：去掉不必要的分组操作，避免同一保单产生多条无效配对记录。

内容的提问来源于stack exchange，提问作者Sai Prasanth Thota