case when 加 PERCENTILE_APPRO

引用提供了一个SQL查询，结果为指定时间范围内，指定事件的去重数量。引用提供了关于percentile_approx函数的说明，其中参数B控制了近似精度。引用则提供了一个Python代码示例。

问题: case when 加PERCENTILE_APPROX是什么意思？
回答: 在这个上下文中，case when 加PERCENTILE_APPROX可能是指在一个SQL查询中使用case when语句来计算一个近似百分位数。具体的语法和用法要根据具体的查询语句来确定，但通常情况下，case when语句用于根据条件返回不同的结果。而PERCENTILE_APPROX函数则用于计算近似的百分位数，其中可以设置参数B来控制精度。因此，case when 加PERCENTILE_APPROX可能是在根据条件计算一个近似百分位数的意思。

相关问题

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;

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

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;查询后JiJXH 字段中例如LX2004-EM，会成为LX2004，不完整，怎样修改代码使字段不被截取

可以在SELECT语句中使用SUBSTRING函数来截取JiJXH字段的前部分：

SELECT Xian, Name, XieYBH, FactoryName, BusinessName, JiJPM, FenDMC, SUBSTRING(JiJXH, 1, 7) AS 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;

在上面的代码中，用SUBSTRING函数将JiJXH截取前7个字符，可以根据具体需要调整截取的长度。