sql 逻辑 ORACLE优化

1.查最新

有2个站点 CR_OPE_ID_FK  IN  ('M310','M330')，查询A站点NG的PRD_SEQ_ID,并显示2个站点同ID的最新状况（如果也是NG）

SELECT 
    CR_OPE_ID_FK,
    MDL_ID_FK,
    WO_ID_FK,
    PRD_SEQ_ID,
    substr(PRD_GRADE,1,1) PRD_GRADE_GP,
    PRD_GRADE,
    EVT_CATE,
    LOGOF_USR,
    EXT_9,
    EVT_TIMESTAMP
FROM HIS_RET_PRD_INFO  
WHERE 
    (PRD_SEQ_ID,CR_OPE_ID_FK,EVT_TIMESTAMP) IN (
	SELECT PRD_SEQ_ID,CR_OPE_ID_FK,MAX(EVT_TIMESTAMP)--查询 M330 NG的ID对应的M330和310所有的最新的信息  
	FROM HIS_RET_PRD_INFO
	WHERE PRD_SEQ_ID IN (
		SELECT PRD_SEQ_ID--查询 最新的ID和时间里，EXT_9=NG的信息
		FROM HIS_RET_PRD_INFO 
		WHERE 
                    (PRD_SEQ_ID,EVT_TIMESTAMP)IN 
			(
			SELECT  PRD_SEQ_ID,MAX(EVT_TIMESTAMP)--输入站点，查询最新的ID和时间
			FROM HIS_RET_PRD_INFO 
    		        WHERE CR_OPE_ID_FK='M330'
            	            AND EVT_TIMESTAMP >= to_date('2018-01-04 08:30:00','yyyy-mm-dd hh24:mi:ss') 
            	            AND EVT_TIMESTAMP < to_date('2018-01-05 08:30:00','yyyy-mm-dd hh24:mi:ss') 
            	            AND EVT_CATE='LGOF'
            	            AND MDL_ID_FK='G1548FH111GG-001'
            	            AND WO_ID_FK='3000575'
            	        GROUP BY PRD_SEQ_ID
			)
                    AND EXT_9='NG'
	)
	    AND CR_OPE_ID_FK IN ('M310','M330')
	    AND EVT_TIMESTAMP >= to_date('2018-01-04 08:30:00','yyyy-mm-dd hh24:mi:ss') 
	    AND EVT_TIMESTAMP < to_date('2018-01-05 08:30:00','yyyy-mm-dd hh24:mi:ss') 
	    AND EVT_CATE='LGOF'
	    AND MDL_ID_FK='G1548FH111GG-001'
	    AND WO_ID_FK='3000575'
	GROUP BY PRD_SEQ_ID,CR_OPE_ID_FK
)
AND EXT_9='NG'

2.分组查最新

SELECT  ID,NAME，TYPE,TIME 
FROM (
    SELECT  ID,NAME，TYPE,TIME,ROW_NUMBER()
    OVER(PARTITION BY TYPE ORDER BY TIME) AS code_id
    FROM HIS_RET_PRD_INFO 
    WHERE NAME LIKE '%A'
)WHERE code_id =1

误区：这里是先进行WHERE条件，再排序取最新。

3.ORACLE 优化 利用lag(),lead()函数

原SQL：要查2次表

SELECT PRD_SEQ_ID
FROM HIS_RET_PRD_INFO 
WHERE (PRD_SEQ_ID,EVT_TIMESTAMP)IN 
(
	SELECT  PRD_SEQ_ID,MAX(EVT_TIMESTAMP)
	FROM HIS_RET_PRD_INFO 
    WHERE EVT_TIMESTAMP >= to_date('2018-01-04 08:30:00','yyyy-mm-dd hh24:mi:ss') 
    AND EVT_TIMESTAMP < to_date('2018-01-05 08:30:00','yyyy-mm-dd hh24:mi:ss') 
    and CR_OPE_ID_FK IN ('M330')
    AND EVT_CATE='LGOF'
    and MDL_ID_FK IN ('G1548FH111GG-001')
    and WO_ID_FK IN ('3000575')
    GROUP BY PRD_SEQ_ID
)
and EXT_9 IN ('NG')

利用lag(),lead()函数分析，效率上也有优化

SELECT PRD_SEQ_ID FROM (
	SELECT 
		PRD_SEQ_ID,EXT_9, 
        LAG(EXT_9,1,0) OVER(partition BY PRD_SEQ_ID ORDER BY EVT_TIMESTAMP DESC) MAX_VALUE,
        EVT_TIMESTAMP,
        LEAD(EXT_9,1,0)  OVER(partition BY PRD_SEQ_ID ORDER BY EVT_TIMESTAMP DESC) MIN_VALUE
   	FROM HIS_RET_PRD_INFO 
    	WHERE EVT_TIMESTAMP >= to_date('2018-01-04 08:30:00','yyyy-mm-dd hh24:mi:ss') 
        AND EVT_TIMESTAMP < to_date('2018-01-05 08:30:00','yyyy-mm-dd hh24:mi:ss') 
        and CR_OPE_ID_FK IN ('M330')
        AND EVT_CATE='LGOF'
        and MDL_ID_FK IN ('G1548FH111GG-001')
        and WO_ID_FK IN ('3000575')
)WHERE MAX_VALUE='0' AND EXT_9='NG'

partition 类似 group by 先分组后排序。

max_value='0'代表他是分组排序中的第一个，min_value='0'代表他是分组排序中的最后一个，不为0是，显示的是他上一位的EXT_9