SELECT FactoryName,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 FactoryName, JiJXH, sale;提示在当前兼容模式下不允许使用 PERCENTILE_CONT 函数。仅在 110 模式或更高模式下允许使用它。怎么修改

这个问题可以通过将数据库的兼容性级别提高到110或更高来解决。可以使用以下命令来更改数据库的兼容性级别：

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 110;

在这里，将“DatabaseName”替换为你的数据库名称。将兼容性级别设置为110将允许使用PERCENTILE_CONT函数。

相关问题

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';

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

SELECT Xian,Name,XieYBH,FactoryName,BusinessName,JiJPM,FenDMC,JiJXH,sale, CASE WHEN OutlierFlag = 1 THEN 'Outlier' ELSE 'Normal' END AS OutlierIndicator FROM ( SELECT *, 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 1 ELSE 0 END AS OutlierFlag FROM tmp_apply_Outlier ) tmp WHERE OutlierFlag = 1; DROP TABLE tmp_apply_Outlier;运行后JiJXH字段不完整

根据你提供的 SQL 语句来看，可能是因为你在 SELECT 语句中没有将 JiJXH 字段加入到 PARTITION BY 子句中，导致对于相同 FactoryName 和 JiJXH 的记录没有被正确地分组。你可以尝试修改为以下语句：

SELECT Xian, Name, XieYBH, FactoryName, BusinessName, JiJPM, FenDMC, JiJXH, sale, 
  CASE
    WHEN OutlierFlag = 1 THEN 'Outlier'
    ELSE 'Normal'  
  END AS OutlierIndicator 
FROM (
  SELECT *, 
    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 1 
      ELSE 0 
    END AS OutlierFlag 
  FROM tmp_apply_Outlier
) tmp 
WHERE OutlierFlag = 1;

如果还有问题，可以提供更多的信息，我会尽力帮助你解决。