当某些值可以为 NULL 时，通过 SQL 使用 GROUP BY 在 MS Access 中的四分位数/百分位数 [英] Quartile/Percentile in MS Access via SQL with a GROUP BY when some values can be NULL

问题描述

我正在寻找可以为 NULL 的字段计算子组的百分位数.字段 IU 为 1 或 Null.具体:

I am looking to calculate a percentile of a subgroup for a field that can be NULL. Field IU is either 1 or Null. Specifically:

*my table: tblFirst250
*group by: IU = 1 (which is Nullable)
*percentile of: GM (which is Nullable)

我从以下开始(但我愿意接受更好的方法):

I am starting with the following (but I am open to better ways of doing this):

select T.groupField, 0.75*(select max(myField) from myTable where
myTable.myField in (select top 25 percent myField from myTable where
myTable.groupField = T.groupField order by myField)) + 0.25*(select
min(myField) from myTable where myTable.myField in (select top 75 percent
myField from myTable where myTable.groupField = T.groupField order by
myField desc)) AS 25Percentile from myTable AS T group by T.groupField

直接取自这里:http:///blogannath.blogspot.com/2010/03/microsoft-access-tips-tricks-statistics.html#ixzz3dEf9ZJSq

到目前为止，我有这个:

So far I have this:

SELECT T.IU, 0.75*(SELECT Max(GM) FROM tblFirst250 WHERE tblFirst250.GM IN
(SELECT TOP 25 PERCENT GM FROM tblFirst250
WHERE tblFirst250.IU = 1 AND GM Is Not Null ORDER BY GM)) + 0.25*
(SELECT Min(GM) FROM tblFirst250 WHERE tblFirst250.GM IN
(SELECT TOP 75 PERCENT GM FROM tblFirst250 
WHERE tblFirst250.IU = 1 AND GM Is Not Null ORDER BY GM desc)) AS 25Percentile 
FROM tblFirst250 AS T 
GROUP BY T.IU;

产生:IU , 1 ;25Percentile -0.706278906030414, -0.706278906030414

Which yields: IU , 1 ;25Percentile -0.706278906030414, -0.706278906030414

...看起来像是分配给所有内容的所有内容的四分位数.

...which looks like the quartile of everything assigned to everything.

问题/问题/请求/备注:

Issues/questions/requests/notes:

1. 我想要以下内容，其中值是 IU = 1 的四分位数 25:IU 1;25% -0.706278906030414
2. 查询速度很慢.
3. 我认为是子查询让我感到困惑.

推荐答案

底部附近缺少一个 WHERE 子句:

Just a missing WHERE clause near the bottom:

SELECT T.IU, 0.75*(SELECT Max(GM) FROM tblFirst250 
WHERE tblFirst250.GM IN (SELECT TOP 25 PERCENT GM FROM tblFirst250 WHERE tblFirst250.IU = 1 AND GM Is Not Null ORDER BY GM)) + 0.25*(SELECT Min(GM) FROM tblFirst250 WHERE tblFirst250.GM IN (SELECT TOP 75 PERCENT GM FROM tblFirst250 WHERE tblFirst250.IU = 1 AND GM Is Not Null ORDER BY GM DESC))  AS 25Percentile 
FROM tblFirst250 AS T
WHERE T.IU = 1
GROUP BY T.IU;

这篇关于当某些值可以为 NULL 时，通过 SQL 使用 GROUP BY 在 MS Access 中的四分位数/百分位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！