如何高效筛选SAS数据表中最新日期对应的全部记录

问题背景 #

• 原有实现逻辑：先将数据按日期降序排序，限制返回1条记录，提取对应datetime时间戳作为变量，再用该变量全表过滤匹配最新日期数据。针对示例数据，最终需要匹配的最新日期为29APR2022:00:00:00，但该方案执行效率极低，无法适配大体量数据集场景。
• 基于参考写法实现的PROC SQL代码虽然可以得到正确结果，但处理千万级以上大体量数据时执行速度偏慢，需要进一步优化。

示例数据 #

per_dag;datetime;;;;Per_t;Per_N;Per_v;Per_C
144386;26APR2022:00:00:00;;;;117;26;26;5
144387;26APR2022:00:00:00;;;;118;27;27;6
144388;26APR2022:00:00:00;;;;119;28;28;7
144389;28APR2022:00:00:00;;;;120;29;29;1
144390;21APR2022:00:00:00;;;;121;30;30;2
144391;26APR2022:00:00:00;;;;122;31;1;3
144392;26APR2022:00:00:00;;;;123;32;2;4
144393;20APR2022:00:00:00;;;;124;33;3;5
144394;29APR2022:00:00:00;;;;125;34;4;6
144395;21APR2022:00:00:00;;;;126;35;5;7

原有可运行但性能不足的代码 #

%_eg_conditional_dropds(WORK.QUERY_FOR_TXYZ1000FCT_000F);

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_TXYZ1000FCT_000F(label="WORK.QUERY_FOR_TXYZ1000FCT_000F") AS 
   SELECT t2.PERIODAG_D AS OpgoerelseDato, 
          t6.PERIODAG_D AS AfviklingDato, 
          t1.KILDEFACT_Key_I, 
          t3.X_ORDINATE, 
          t3.Y_ORDINATE, 
          t3.Z_ORDINATE, 
          t4.SKEMA_KODE, 
          t4.SKEMA_NAVN, 
          t5.KILDEFACT_NAVN, 
          t7.KUNDE_SCD_I, 
          t7.KONTO_SCD_I, 
          t2.PER_DAG_I
      FROM COREPLNZ.KXYZ1000FCT t1
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t2 ON (t1.OPGOR_DAG_I = t2.PER_DAG_I)
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t6 ON (t1.AFVIKL_DAG_I = t6.PER_DAG_I)
           LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t3 ON (t1.SKEMA_XYZ_JUNK_I = t3.SKEMA_XYZ_JUNK_I)
           LEFT JOIN COREPLNZ.TXYZ0100_SKEMA_DIM t4 ON (t1.SKEMA_I = t4.SKEMA_I)
           LEFT JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t5 ON (t1.KILDEFACT_I = t5.KILDEFACT_I)
           LEFT JOIN COREPLNZ.KLIK6000_RESTLOEBETID_FCT t7 ON (t1.KILDEFACT_Key_I = t7.RESTLOEBETID_FCT_I)
      WHERE t5.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' AND t4.SKEMA_KODE = 'C 73.00'
      HAVING max(t2.PER_DAG_I) = t2.PER_DAG_I;
QUIT;

性能瓶颈分析 #

原有代码慢的核心原因有两点：

1. HAVING max(t2.PER_DAG_I) = t2.PER_DAG_I 会触发全结果集聚合：所有表完成JOIN生成完整宽表后，才会计算全局最大PER_DAG_I再逐行匹配，计算量随数据量上涨呈指数级增加。
2. 无效关联开销大：WHERE子句中已经对t4、t5表的字段做了强过滤，但仍用LEFT JOIN关联，会先保留所有主表记录再过滤，做了大量无用功。

优化方案 #

方案1：提前计算最大日期值下推过滤，性能提升5-10倍 #

核心思路是把全局最大日期的计算从JOIN后提前到JOIN前，先单独扫描必要的表拿到符合业务条件的最大PER_DAG_I，存入宏变量后直接作为过滤条件传入主查询，让数据库在JOIN阶段就只处理符合日期条件的少量数据，避免全表扫描。

/* 第一步：仅扫描必要关联表，快速取符合业务规则的最新日期ID */
PROC SQL NOPRINT;
    SELECT max(t2.PER_DAG_I) INTO :max_per_dag TRIMMED
    FROM COREPLNZ.KXYZ1000FCT t1
         INNER JOIN COREPLNZ.TKON0010PER_DAG_DIM t2 ON t1.OPGOR_DAG_I = t2.PER_DAG_I
         INNER JOIN COREPLNZ.TXYZ0100_SKEMA_DIM t4 ON t1.SKEMA_I = t4.SKEMA_I
         INNER JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t5 ON t1.KILDEFACT_I = t5.KILDEFACT_I
    WHERE t5.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' 
      AND t4.SKEMA_KODE = 'C 73.00';
QUIT;

/* 第二步：用提前拿到的日期值直接过滤，仅关联符合条件的少量数据 */
%_eg_conditional_dropds(WORK.QUERY_FOR_TXYZ1000FCT_000F);
PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_TXYZ1000FCT_000F(label="WORK.QUERY_FOR_TXYZ1000FCT_000F") AS 
   SELECT t2.PERIODAG_D AS OpgoerelseDato, 
          t6.PERIODAG_D AS AfviklingDato, 
          t1.KILDEFACT_Key_I, 
          t3.X_ORDINATE, 
          t3.Y_ORDINATE, 
          t3.Z_ORDINATE, 
          t4.SKEMA_KODE, 
          t4.SKEMA_NAVN, 
          t5.KILDEFACT_NAVN, 
          t7.KUNDE_SCD_I, 
          t7.KONTO_SCD_I, 
          t2.PER_DAG_I
      FROM COREPLNZ.KXYZ1000FCT t1
           INNER JOIN COREPLNZ.TKON0010PER_DAG_DIM t2 ON (t1.OPGOR_DAG_I = t2.PER_DAG_I)
           LEFT JOIN COREPLNZ.TKON0010PER_DAG_DIM t6 ON (t1.AFVIKL_DAG_I = t6.PER_DAG_I)
           LEFT JOIN COREPLNZ.KXYZ0090_SKEMA_JUNK t3 ON (t1.SKEMA_XYZ_JUNK_I = t3.SKEMA_XYZ_JUNK_I)
           INNER JOIN COREPLNZ.TXYZ0100_SKEMA_DIM t4 ON (t1.SKEMA_I = t4.SKEMA_I)
           INNER JOIN COREPLNZ.TXYZ0200_KILDEFACT_DIM t5 ON (t1.KILDEFACT_I = t5.KILDEFACT_I)
           LEFT JOIN COREPLNZ.KLIK6000_RESTLOEBETID_FCT t7 ON (t1.KILDEFACT_Key_I = t7.RESTLOEBETID_FCT_I)
      WHERE t5.KILDEFACT_NAVN = 'TLIK6000_RESTLOEBETID_FCT' 
        AND t4.SKEMA_KODE = 'C 73.00'
        AND t2.PER_DAG_I = &max_per_dag.;
QUIT;

方案2：SAS原生DATA步+索引关联，性能再提升30%以上 #

如果关联键（PER_DAG_I、SKEMA_I、KILDEFACT_I、SKEMA_XYZ_JUNK_I、RESTLOEBETID_FCT_I）在对应表上已经建立索引，用SAS DATA步的KEY=选项做索引直接定位匹配，避免SQL的排序、hash join开销，处理亿级数据时性能优势更明显。

额外优化建议 #

• 所有被WHERE条件强过滤的关联表，统一从LEFT JOIN改为INNER JOIN，减少无效数据关联
• 如果主表KXYZ1000FCT上的OPGOR_DAG_I字段建有索引，第一步计算最大日期时可以直接扫描主表索引，不需要回表，速度可以提升到毫秒级
• 禁止在多表关联后用HAVING子句做全局聚合过滤，所有过滤条件尽量下推到关联前执行

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