当某些值可以为NULL时，通过带有GROUP BY的SQL通过MS Access在四分位数/百分位数中

Question

我正在寻找一个可以为NULL的字段的子组的百分位数。字段IU为1或为Null。具体来说：

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

我从以下内容开始（但我愿意采取更好的方法）：

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

到目前为止，我有这个：

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; 25％-0.706278906030414，-0.706278906030414

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

问题/问题/要求/注释：

1. 我想下面的值是四分位数25，其中IU = 1：IU 1; 25％-0.706278906030414
2. 查询速度很慢。
3. 我认为，正是这些子查询使我不胜其烦。

Answer 1

底部附近只是缺少的WHERE子句：

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;