SELECT Xian,Name,XieYBH,FactoryName,BusinessName,JiJPM,FenDMC,JiJXH,sale, CASE WHEN sale < (PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) - 1.5 * (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH))) OR sale > (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) + 1.5 * (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH))) THEN 'Outlier' ELSE 'Normal' END AS OutlierIndicator FROM tmp_apply GROUP BY Xian,Name,XieYBH,FactoryName,BusinessName,JiJPM,FenDMC,JiJXH,sale;如何取出异常值明细

要取出异常值明细，可以在上述SQL语句的基础上，加一个WHERE子句来筛选出OutlierIndicator为'Outlier'的记录，即：

SELECT Xian,Name,XieYBH,FactoryName,BusinessName,JiJPM,FenDMC,JiJXH,sale, CASE      
    WHEN sale < (PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) - 1.5 * (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH)))          
        OR sale > (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) + 1.5 * (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH) - PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sale) OVER (PARTITION BY FactoryName, JiJXH)))     
    THEN 'Outlier'     
    ELSE 'Normal'  
END AS OutlierIndicator 
FROM tmp_apply 
GROUP BY Xian,Name,XieYBH,FactoryName,BusinessName,JiJPM,FenDMC,JiJXH,sale
WHERE OutlierIndicator = 'Outlier';

这样就可以只取出异常值的记录了。