Oracle SQL 四分位 上四分位 下四分位 中位数

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