SSMS中基于记录类型与源优先级筛选事实表数据的方案问询

实现思路 #

• 分类匹配规则：针对两种记录类型分别执行筛选逻辑：
  • actuals类型：直接筛选SOURCE_ID=2的记录
  • projections类型：优先选取SOURCE_ID=4的记录，按APPROVED_ON降序取最新条目；若SOURCE_ID=4无匹配记录，则选取SOURCE_ID=1的记录
• 窗口函数精准排序：使用ROW_NUMBER()窗口函数，按业务维度主键（示例中为GUID_ID）+RECORD_TYPE分组，根据优先级定义排序规则，最终保留每组中排名第1的记录，确保每个业务实体仅返回符合要求的唯一记录
• 边界场景处理：兼容APPROVED_ON为NULL的情况，排序时将NULL值置于非NULL值之后，保证最新的已批准记录优先被选中

示例代码 #

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY GUID_ID, RECORD_TYPE
            ORDER BY 
                -- 按记录类型和SOURCE_ID定义优先级
                CASE RECORD_TYPE
                    WHEN 'actuals' THEN 1
                    WHEN 'projections' THEN 
                        CASE SOURCE_ID
                            WHEN 4 THEN 1
                            WHEN 1 THEN 2
                            ELSE 3
                        END
                END ASC,
                -- projections类型下，SOURCE_ID=4的记录按审批时间降序取最新
                CASE WHEN RECORD_TYPE = 'projections' AND SOURCE_ID = 4 THEN APPROVED_ON END DESC,
                -- 处理APPROVED_ON为NULL的情况，非NULL记录优先
                CASE WHEN APPROVED_ON IS NOT NULL THEN 1 ELSE 2 END ASC
        ) AS rn
    FROM mytable
    -- 先过滤不符合基础规则的记录，减少计算量
    WHERE 
        (RECORD_TYPE = 'actuals' AND SOURCE_ID = 2)
        OR (RECORD_TYPE = 'projections' AND SOURCE_ID IN (4, 1))
)
-- 选取每组中优先级最高的记录
SELECT 
    ID, SCENARIO_ID, GUID_ID, RECORD_TYPE, VALUE, APPROVED_ON, SOURCE_ID, IS_APPROVED
FROM ranked_records
WHERE rn = 1
ORDER BY GUID_ID, RECORD_TYPE;

代码说明 #

1. 前置过滤：通过WHERE条件提前筛掉不符合基础规则的记录，提升查询效率
2. 排序逻辑：
   • 对于actuals类型，所有SOURCE_ID=2的记录优先级一致，若存在多条同维度记录，可根据实际业务需求补充额外排序字段
   • 对于projections类型，先锁定SOURCE_ID=4的记录，再按APPROVED_ON降序确保最新记录排名第一；若SOURCE_ID=4无记录，SOURCE_ID=1的记录自动成为组内排名第1的结果
3. 分区字段：示例中使用GUID_ID作为业务维度主键，可根据实际业务的唯一标识（如SCENARIO_ID）调整分区逻辑

扩展说明 #

如果业务中“可用记录”特指已批准（IS_APPROVED='APPROVED'）的记录，可在WHERE条件中添加过滤规则：

WHERE 
    ((RECORD_TYPE = 'actuals' AND SOURCE_ID = 2) AND (IS_APPROVED IS NULL OR IS_APPROVED = 'APPROVED'))
    OR ((RECORD_TYPE = 'projections' AND SOURCE_ID IN (4, 1)) AND (IS_APPROVED IS NULL OR IS_APPROVED = 'APPROVED'))

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