如何提升Data Vault架构下仅日期聚合差异SQL查询的性能

现有查询需要按公共维度分组取每组的最小、最大日期，当前实现存在明显的冗余逻辑：

• 两次编写完全相同的7表关联、过滤逻辑，分别计算最小日期、最大日期
• 两个子查询计算完成后，再通过维度字段做自关联拼接结果
• 重复的表扫描、关联计算会产生不必要的性能开销，数据量越大损耗越明显

同分组维度下的MIN()、MAX()聚合可以在同一次分组查询中同时计算，不需要拆成两个子查询重复跑关联逻辑：

1. 仅保留一份多表关联、过滤逻辑
2. 在SELECT子句中同时写最小日期、最大日期的聚合计算
3. 去掉两个子查询的自关联步骤，直接输出单次聚合的结果

这种写法可以把多表关联的执行次数从2次降到1次，同时省掉子查询自连接的开销，性能提升非常明显。

SELECT 
  ACTIVITY_CODE,
  FORM_ID_STRING, 
  PROJECT_CODE, 
  DATE(MIN_START_DATE) AS MIN_START_DATE, 
  DATE(MAX_END_DATE) AS MAX_END_DATE
FROM (
  SELECT 
    HO.FORM_ID_STRING, 
    HO.LOCATION_NAME, 
    HPC.PROJECT_CODE, 
    HA.ACTIVITY_CODE,
    MIN(SO.OBSERVATION_START_DT) AS MIN_START_DATE,
    MAX(SO.OBSERVATION_START_DT) AS MAX_END_DATE
  FROM DATA_VAULT.HUB_OBSERVATION HO 
  JOIN DATA_VAULT.SAT_OBSERVATION SO 
    ON HO.OBSERVATION_HKEY = SO.OBSERVATION_HKEY
  JOIN DATA_VAULT.SAT_OBSERVATION_REVIEW SOR 
    ON SOR.OBSERVATION_HKEY = HO.OBSERVATION_HKEY
  JOIN DATA_VAULT.LNK_OBSERVATION_PROJECT_CODE LOPC 
    ON LOPC.OBSERVATION_HKEY = HO.OBSERVATION_HKEY
  JOIN DATA_VAULT.HUB_PROJECT_CODE HPC 
    ON HPC.PROJECT_CODE_HKEY = LOPC.PROJECT_CODE_HKEY
  JOIN DATA_VAULT.LNK_OBSERVATION_COUNTRY_ACTIVITY LOCA 
    ON LOCA.OBSERVATION_HKEY = HO.OBSERVATION_HKEY
  JOIN DATA_VAULT.HUB_ACTIVITY HA 
    ON HA.ACTIVITY_HKEY = LOCA.ACTIVITY_HKEY
  WHERE 
    SO.LOAD_DT = (
      SELECT MAX(LOAD_DT) 
      FROM DATA_VAULT.SAT_OBSERVATION 
      WHERE OBSERVATION_HKEY = HO.OBSERVATION_HKEY
    )
    AND SOR.LOAD_DT = (
      SELECT MAX(LOAD_DT) 
      FROM DATA_VAULT.SAT_OBSERVATION_REVIEW 
      WHERE OBSERVATION_HKEY = HO.OBSERVATION_HKEY
    )
    AND SOR.REVIEW_STATUS NOT IN ('REJECTED', 'DELETED')
  GROUP BY  
    HO.FORM_ID_STRING, 
    HO.LOCATION_NAME, 
    HPC.PROJECT_CODE, 
    HA.ACTIVITY_CODE
) aggregated_result

注：优化后的SQL完全保留原有业务逻辑，返回结果和原SQL一致。

如果后续数据量持续增长，还可以做以下调整进一步提升性能：

• 替换现有取最新版本记录的相关子查询：如果所用数据库支持窗口函数，可以用ROW_NUMBER() OVER (PARTITION BY OBSERVATION_HKEY ORDER BY LOAD_DT DESC)的方式标记每个观测的最新版本记录，过滤rn=1的数据即可，避免关联时反复执行子查询匹配。
• 检查索引配置：确认关联字段OBSERVATION_HKEY、过滤字段LOAD_DT、REVIEW_STATUS上建有对应索引，减少全表扫描开销。

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