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,不完整,怎样修改代码使字段不被截取
时间: 2023-12-05 19:03:10 浏览: 25
可以在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个字符,可以根据具体需要调整截取的长度。