请求将带标量子查询的SQL Server语句转换为Snowflake语句

需求描述 #

需要将使用标量子查询的SQL Server查询转换为Snowflake兼容版本，尝试用LEFT JOIN改写未得到正确结果。

表结构与测试数据 #

registration表

CREATE TABLE registration 
(
    familyMemberGraduated     VARCHAR(1),       -- 'Y' or 'N'
    MaxfamilyMemberGraduated  VARCHAR(1),       -- Nullable
    FirstInFamily             VARCHAR(1),       -- 'Y' or 'N'
    MaxFirstInFamily          VARCHAR(1),       -- Nullable
    RegYear                   INT,              -- Registration year
    personOID                 NUMBER(15, 5)     -- Assuming decimal with precision
);

INSERT INTO registration (familyMemberGraduated, MaxfamilyMemberGraduated,
    FirstInFamily, MaxFirstInFamily, RegYear, personOID)
VALUES ('N', NULL, 'N', NULL, 1995, 3621.12585),
       ('N', NULL, 'N', NULL, 2005, 3621.12585),
       ('N', NULL, 'N', NULL, 2009, 3621.12585),
       ('N', NULL, 'Y', NULL, 2012, 3621.12585);

enrolment表

CREATE TABLE enrolment 
(
    StudentBK       NUMBER(15,5),   -- Student ID
    AcademicYear    INT,            -- Academic year
    dept            VARCHAR(10),    -- Department code
    EnrolmentItemID NUMBER(15,6)    -- Unique enrolment item identifier
);

INSERT INTO enrolment (StudentBK, AcademicYear, dept, EnrolmentItemID) 
VALUES (3621.12585, 2005, 'EDUC', 2907.1264),
       (3621.12585, 2005, 'LACL', 2907.1265),
       (3621.12585, 2006, 'HACA', 2907.2563),
       (3621.12585, 2006, 'LACL', 2907.1265),
       (3621.12585, 2012, 'CPSP', 2907.943567),
       (3621.12585, 2012, 'CPSP', 2907.943568),
       (3621.12585, 2012, 'CPSP', 2907.943569),
       (3621.12585, 2012, 'STED', 2907.943560),
       (3621.12585, 2012, 'STED', 2907.943561),
       (3621.12585, 2012, 'STED', 2907.943562),
       (3621.12585, 2012, 'STED', 2907.943563),
       (3621.12585, 2012, 'STED', 2907.943564),
       (3621.12585, 2012, 'STED', 2907.943565),
       (3621.12585, 2012, 'STED', 2907.943566);

SQL Server原查询 #

SELECT
    esf.StudentBK,
    esf.AcademicYear,
    ISNULL((SELECT TOP 1 ISNULL(familyMemberGraduated, MaxfamilyMemberGraduated) 
            FROM registration RE (NOLOCK) 
            WHERE RE.personOID = esf.StudentBK 
              AND RE.RegYear <= esf.AcademicYear
            ORDER BY RegYear DESC) , '?') COLLATE SQL_Latin1_General_CP1_CS_AS AS 'FamilyMemberGraduated',
    ISNULL((SELECT TOP 1 ISNULL(FirstInFamily, MaxFirstInFamily) 
            FROM registration RE (NOLOCK) 
            WHERE RE.personOID = esf.StudentBK
              AND RE.RegYear <= esf.AcademicYear
            ORDER BY RegYear DESC), '?') COLLATE SQL_Latin1_General_CP1_CS_AS AS 'FirstInFamily',
    CASE 
        WHEN YearFirstTertiary = 0 THEN '?'
        WHEN YearFirstTertiary = AcademicYear THEN 'Y' 
        ELSE 'N' 
    END COLLATE SQL_Latin1_General_CP1_CS_AS AS FirstYearTertiaryStudy
FROM 
    Enrolment esf

预期结果 #

StudentBK   AcademicYear    FamilyMemberGraduated   FirstInFamily   FirstYearTertiaryStudy
3621.12585  2012    N   Y   N
3621.12585  2005    N   N   N
3621.12585  2005    N   N   N
3621.12585  2012    N   Y   N
3621.12585  2012    N   Y   N
3621.12585  2012    N   Y   N
3621.12585  2012    N   Y   N
3621.12585  2006    N   N   N
3621.12585  2012    N   Y   N
3621.12585  2012    N   Y   N
3621.12585  2012    N   Y   N
3621.12585  2006    N   N   N
3621.12585  2012    N   Y   N
3621.12585  2012    N   Y   N

Snowflake兼容改写方案 #

原查询核心是为每条enrolment记录匹配同一学生、注册年份小于等于当前学年的最新registration记录，取对应字段值。以下是改写后的Snowflake查询：

WITH latest_registration AS (
    SELECT
        re.personOID,
        re.RegYear,
        ISNULL(re.familyMemberGraduated, re.MaxfamilyMemberGraduated) AS FamilyMemberGraduated,
        ISNULL(re.FirstInFamily, re.MaxFirstInFamily) AS FirstInFamily,
        ROW_NUMBER() OVER (PARTITION BY re.personOID ORDER BY re.RegYear DESC) AS rn
    FROM registration re
)
SELECT
    esf.StudentBK,
    esf.AcademicYear,
    COALESCE(lr.FamilyMemberGraduated, '?') COLLATE 'SQL_Latin1_General_CP1_CS_AS' AS FamilyMemberGraduated,
    COALESCE(lr.FirstInFamily, '?') COLLATE 'SQL_Latin1_General_CP1_CS_AS' AS FirstInFamily,
    CASE 
        WHEN YearFirstTertiary = 0 THEN '?'
        WHEN YearFirstTertiary = esf.AcademicYear THEN 'Y' 
        ELSE 'N' 
    END COLLATE 'SQL_Latin1_General_CP1_CS_AS' AS FirstYearTertiaryStudy
FROM enrolment esf
LEFT JOIN latest_registration lr 
    ON esf.StudentBK = lr.personOID
    AND lr.RegYear <= esf.AcademicYear
    AND lr.rn = 1;

关键说明 #

1. 窗口函数筛选最新记录：通过ROW_NUMBER()按学生分组，注册年份倒序排序，标记最新记录为rn=1，确保每个学生只保留最新的registration信息。
2. LEFT JOIN关联逻辑：将enrolment与预处理后的最新registration记录关联，同时过滤年份条件，保证匹配的是当前学年之前的有效记录。
3. 函数与语法适配：用COALESCE替代ISNULL（Snowflake中两者功能兼容，COALESCE通用性更强）；排序规则用COLLATE 'SQL_Latin1_General_CP1_CS_AS'指定，与原SQL Server逻辑一致；移除Snowflake不需要的NOLOCK提示。

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