mysql PERCENTILE_CONT
时间: 2023-11-06 10:02:59 浏览: 147
PERCENTILE_CONT函数是MySQL中用于计算给定百分位数的连续分布的函数。
该函数的语法如下:
PERCENTILE_CONT ( percentile_value ) WITHIN GROUP (ORDER BY column_name)
参数说明:
- percentile_value:要计算的百分位数,取值范围为0到1之间。
- column_name:用于排序的列名。
该函数将返回与指定百分位数对应的连续值。如果在给定数据集中找不到精确匹配的值,该函数将使用线性插值来计算结果。
例如,假设我们有一个包含销售数据的表sales,其中有一个列price表示商品的价格。我们想计算价格中位数,可以使用PERCENTILE_CONT函数:
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price
FROM sales;
相关问题
hive PERCENTILE_CONT
PERCENTILE_CONT是Hive中的一个聚合函数,用于计算指定列的百分位数。它的语法如下:
```
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col)
```
其中,p是一个介于0和1之间的实数,表示要计算的百分位数;col是要计算百分位数的列。
举个例子,如果我们要计算一个表中某一列的中位数,可以这样写:
```
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) FROM table;
```
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;
```
如果还有问题,可以提供更多的信息,我会尽力帮助你解决。