如何编写以下两条语句中的一条更好的sql以提高性能

Question

如果匹配有多个 ID，请选择带有 max(date) 的 id。 如果上述最长日期有多个 ID，请选择 max(ID) 转至表 1 获取最新 IDS

第 1 步：识别具有最长日期的 ID

CREATE MULTISET VOLATILE TABLE TEST_VT 
AS 
(                                       
SELECT
TABLE1.ID ,
TABLE1.KEY1,
TABLE1.STRT_DT,
TABLE1.TERM_DT,
Rank() OVER (PARTITION BY  TABLE1.KEY1 ORDER BY TABLE1.STRT_DT DESC , TABLE1.ID DESC) RNK
FROM TABLE2          
INNER JOIN TABLE1         
ON TABLE2.KEY1=TABLE1.KEY1    
WHERE  TABLE1.TERM_DT='8888-12-31'
QUALIFY RNK=1
GROUP BY 
1,2,3,4
)
WITH DATA PRIMARY INDEX(ID,KEY1) ON COMMIT PRESERVE ROWS;

转至表 1 获取与上述步骤中的 IDS 关联的最新 KEY1S 第 2 步：如果存在多个最大日期，请从上一步中的所有匹配记录中取出最大 ID

SELECT                                      
TABLE1.ID ,
TABLE1.KEY1,
TABLE1.STRT_DT,
TABLE1.TERM_DT,                             
RANK() OVER (PARTITION BY TABLE1.KEY1 ORDER BY TABLE1.STRT_DT DESC , TABLE1.ID DESC) AS RNK                 
FROM  TABLE1    
INNER JOIN TEST_VT                                      
ON TEST_VT.ID=TABLE1.ID 
INNER JOIN TABLE3                   
ON TABLE3.KEY1=TABLE1.KEY1    
WHERE  TABLE1.TERM_DT='8888-12-31'  
QUALIFY RNK=1 
GROUP BY    
1,2,3,4