Oracle SQL 四分位 上四分位 下四分位 中位数 平均值 方差 最大值 最小值
------------------------SQL 四分位 上四分位 下四分位 中位数---------------------- SELECT PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_SIZE ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY CT ASC) OVER(PARTITION BY PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI,LOT_SIZE) AS CT0_25 ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CT ASC) OVER(PARTITION BY PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI,LOT_SIZE) AS CT0_5 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY CT ASC) OVER(PARTITION BY PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_SIZE) AS CT0_75 FROM (SELECT PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_ID ,dlh.STEP_OUT_TIME-dlh.STEP_IN_TIME AS CT ,CASE WHEN STEP_OUT_WAFER_QTY >=13 THEN 'BIG' ELSE 'SMALL' END AS LOT_SIZE FROM DWT_LOT_HISTORY dlh WHERE 1=1 AND dlh.STEP_IN_TIME>= TO_DATE('2023-02-07 08:00:00','yyyy-mm-dd hh24:mi:ss') AND dlh.STEP_IN_TIME<= TO_DATE('2023-02-08 08:00:00','yyyy-mm-dd hh24:mi:ss') AND LOT_TYPE IN ('PP','PC','PE','PR','Q1','Q2','Q3','P','L') )AA --------------------------------------------------------------------------------- SELECT PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_SIZE ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY CT ASC) AS CT0_25 ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CT ASC) AS CT0_5 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY CT ASC) AS CT0_75 FROM (SELECT PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_ID ,dlh.STEP_OUT_TIME-dlh.STEP_IN_TIME AS CT ,CASE WHEN STEP_OUT_WAFER_QTY >=13 THEN 'BIG' ELSE 'SMALL' END AS LOT_SIZE FROM DWT_LOT_HISTORY dlh WHERE 1=1 AND dlh.STEP_IN_TIME>= TO_DATE('2023-02-07 08:00:00','yyyy-mm-dd hh24:mi:ss') AND dlh.STEP_IN_TIME<= TO_DATE('2023-02-08 08:00:00','yyyy-mm-dd hh24:mi:ss') AND LOT_TYPE IN ('PP','PC','PE','PR','Q1','Q2','Q3','P','L'))AA GROUP BY PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_SIZE
汇总计算：
SELECT PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_SIZE ,COUNT(LOT_ID) CNT ,AVG(CT) AVG_CT ,VARIANCE(CT) FC_CT --,MEDIAN(CT) MED_CT ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY CT ASC) AS CT0_25 ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CT ASC) AS CT0_5 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY CT ASC) AS CT0_75 ,MAX(CT) MAX_CT ,MIN(CT) MIN_CT FROM (SELECT PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_ID ,dlh.STEP_OUT_TIME-dlh.STEP_IN_TIME AS CT ,CASE WHEN STEP_OUT_WAFER_QTY >=13 THEN 'BIG' ELSE 'SMALL' END AS LOT_SIZE FROM DWT_LOT_HISTORY dlh WHERE 1=1 AND dlh.STEP_IN_TIME>= TO_DATE('2023-02-07 08:00:00','yyyy-mm-dd hh24:mi:ss') AND dlh.STEP_IN_TIME<= TO_DATE('2023-02-08 08:00:00','yyyy-mm-dd hh24:mi:ss') AND LOT_TYPE IN ('PP','PC','PE','PR','Q1','Q2','Q3','P','L')
)AA GROUP BY PROD_ID ,STAGE_ID ,STEP_ID ,RECIPE_ID ,LOT_PRI ,LOT_SIZE