MS SQL转Snowflake SQL：OUTER APPLY改写为LATERAL遇问题求助

将旧MS SQL项目中的查询改写为Snowflake SQL时，处理三个OUTER APPLY子查询结果遇到问题。原MS SQL通过TOP 1仅返回子查询第一行，尝试用Snowflake的LATERAL结合QUALIFY ROW_NUMBER()实现，需确认改写是否正确。

原MS SQL查询 #

SELECT 
CASE WHEN c.[Committee Date] is not NULL THEN 1 ELSE 0 end [C_DATE]
, CASE WHEN r1.[roster] = 1 THEN 1
    ELSE CASE WHEN coalesce(r2.[R-NID],0) = 1 AND coalesce(r3.[R-TID],0) = 0 THEN 2 
    ELSE CASE WHEN coalesce(r2.[R-NID],0) = 0 AND coalesce(r3.[R-TID],0) = 1 THEN 3 
    ELSE CASE WHEN coalesce(r2.[R-NID],0) = 1 AND coalesce(r3.[R-TID],0) = 1 THEN 4 
    ELSE 10
    END END END END [Roster_IND]
FROM Stage os
OUTER APPLY (SELECT TOP 1 1 [roster]
        FROM [Master_R] m
        where m.[N_ID] = s.N_ID
        AND m.[T_ID] = s.T_ID
        )r1
OUTER APPLY (SELECT TOP 1 1 [R-NID]
        FROM [Master_R] m
        where m.[N_ID] = s.N_ID
        AND m.[T_ID] <> s.T_ID
        )r2
OUTER APPLY (SELECT TOP 1 1 [R-TID]
        FROM [Master_R] m
        where m.[N_ID] <> s.N_ID
        AND m.[T_ID] = s.T_ID
        )r3
OUTER APPLY (select TOP 1 [Committee Date]
            FROM C_SOURCE r
            WHERE r.N_ID = s.N_ID
            )c 
WHERE s.load_date = '2023-07-17';

第一次改写的Snowflake SQL #

with MR_CTR  as (
  SELECT T_ID ,N_ID
  FROM Master_R 
  QUALIFY ROW_NUMBER() OVER 
    (PARTITION BY T_ID, N_ID ORDER BY N_ID) = 1
)
select 
 CASE WHEN r1.N_ID is not NULL and r1.T_ID is not NULL THEN 1
 ELSE CASE WHEN r2.N_ID is not null and r3.T_ID is NULL then 2
 ELSE CASE WHEN r2.N_ID is null and r3.T_ID is NOT NULL then 3
 ELSE CASE WHEN r2.N_ID is not null and r3.T_ID is NOT NULL then 4
ELSE 10
END END END END OPS_Roster  
FROM Roster_Stage as s,
LEFT JOIN LATERAL (
 select * 
 from MR_CTR r
 where r.N_ID  = s.N_ID and 
 r.T_ID = s.T_ID ) as r1 
LEFT JOIN LATERAL (
 select *  
 from MR_CTR r 
 where r.N_ID  = s.N_ID and 
r.T_ID <> s.T_ID ) as r2 
LEFT JOIN LATERAL (
  select *  
  from MR_CTR r 
  where r.N_ID  <> s.N_ID and 
  r.T_ID = s.T_ID ) as r3
LEFT JOIN (
  select TOP 1 Committee_Date 
  FROM C_SOURCE r 
  WHERE r.N_ID = s.N_ID) c 
WHERE s.load_date = '2023-07-17';

第二次改写的Snowflake SQL #

with MR_CTR  as (
SELECT T_ID ,N_ID
        FROM Master_R 
    QUALIFY ROW_NUMBER() OVER (PARTITION BY T_ID, N_ID ORDER BY N_ID) = 1
)
select
CASE WHEN r1.N_ID is not NULL and r1.T_ID is not NULL THEN 1
    ELSE CASE WHEN r2.N_ID is not null and r3.T_ID is NULL then 2
    ELSE CASE WHEN r2.N_ID is null and r3.T_ID is NOT NULL then 3
    ELSE CASE WHEN r2.N_ID is not null and r3.T_ID is NOT NULL then 4
    ELSE 10
    END END END END OPS_Roster
FROM Roster_Stage as s,
LEFT JOIN LATERAL (select * from  MR_CTR  r where r.N_ID  = s.N_ID and r.T_ID = s.T_ID ) as r1
LEFT JOIN LATERAL (select *  from MR_CTR r where r.N_ID  = s.N_ID and r.T_ID <> s.T_ID ) as r2
LEFT JOIN LATERAL (select *  from MR_CTR r where r.N_ID  <> s.N_ID and r.T_ID = s.T_ID ) as r3
LEFT JOIN (select TOP 1 Committee_Date FROM C_SOURCE r WHERE r.N_ID = s.N_ID)c 
WHERE s.load_date = '2023-07-17';

改写问题分析与修正 #

两次改写存在以下问题：

1. 语法错误：FROM Roster_Stage as s, LEFT JOIN中多了逗号，会导致SQL解析失败，必须删除。
2. 遗漏LATERAL：最后一个C_SOURCE的子查询引用了外部表s.N_ID，必须用LEFT JOIN LATERAL，否则无法关联外部表。
3. 遗漏原列：原查询中的C_DATE列在两次改写中均未保留，需补回。
4. 排序逻辑差异：原MS SQL的TOP 1未指定排序，返回任意匹配行；Snowflake的ROW_NUMBER()必须指定ORDER BY，用ORDER BY NULL可模拟无排序的随机返回。
5. CASE逻辑冗余：嵌套CASE可简化为多分支WHEN结构，更清晰且逻辑一致。

修正后的Snowflake SQL #

WITH MR_CTR AS (
    SELECT T_ID, N_ID
    FROM Master_R
    QUALIFY ROW_NUMBER() OVER (PARTITION BY T_ID, N_ID ORDER BY NULL) = 1
)
SELECT 
    CASE WHEN c.Committee_Date IS NOT NULL THEN 1 ELSE 0 END AS C_DATE,
    CASE 
        WHEN r1.N_ID IS NOT NULL THEN 1
        WHEN r2.N_ID IS NOT NULL AND r3.T_ID IS NULL THEN 2
        WHEN r2.N_ID IS NULL AND r3.T_ID IS NOT NULL THEN 3
        WHEN r2.N_ID IS NOT NULL AND r3.T_ID IS NOT NULL THEN 4
        ELSE 10
    END AS Roster_IND
FROM Roster_Stage AS s
LEFT JOIN LATERAL (
    SELECT * FROM MR_CTR r 
    WHERE r.N_ID = s.N_ID AND r.T_ID = s.T_ID
) AS r1 ON TRUE
LEFT JOIN LATERAL (
    SELECT * FROM MR_CTR r 
    WHERE r.N_ID = s.N_ID AND r.T_ID <> s.T_ID
) AS r2 ON TRUE
LEFT JOIN LATERAL (
    SELECT * FROM MR_CTR r 
    WHERE r.N_ID <> s.N_ID AND r.T_ID = s.T_ID
) AS r3 ON TRUE
LEFT JOIN LATERAL (
    SELECT TOP 1 Committee_Date FROM C_SOURCE r 
    WHERE r.N_ID = s.N_ID
) AS c ON TRUE
WHERE s.load_date = '2023-07-17';

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