如何计算分组依据的四分位数?

Question

假设我有一个表

VAL     PERSON
1          1
2          1
3          1
4          1
2          2
4          2
6          2
3          3
6          3
9          3
12         3
15         3

我想计算每个人的四分位数。

我知道我可以很容易地为一个人计算这些：

SELECT 
VAL,
NTILE(4) OVER(ORDER BY VAL) AS QUARTILE
WHERE PERSON = 1;

会给我想要的结果：

VAL    QUARTILE
1      1
2      2
3      3
4      4

问题是，我想为每个人做这件事。我知道这样的事情可以完成这项工作：

SELECT 
PERSON,
VAL,
NTILE(4) OVER(ORDER BY VAL) AS QUARTILE
WHERE PERSON = 1
UNION
SELECT 
PERSON,
VAL,
NTILE(4) OVER(ORDER BY VAL) AS QUARTILE
WHERE PERSON = 2
UNION
SELECT 
PERSON,
VAL,
NTILE(4) OVER(ORDER BY VAL) AS QUARTILE
WHERE PERSON = 3
UNION
SELECT 
PERSON,
VAL,
NTILE(4) OVER(ORDER BY VAL) AS QUARTILE
WHERE PERSON = 4

但是，如果桌子上有一个新人呢？然后我必须更改SQL代码。有什么建议吗？

Answer 1

你为什么不尝试使用分区。

SELECT 
PERSON,
VAL,
NTILE(4) OVER(PARTITION BY PERSON ORDER BY VAL) AS QUARTILE;
FROM TABLE 

问候

Answer 2

ntile()

不能很好地处理领带。 您可以通过示例轻松看到这一点：

select v.x, ntile(2) over (order by x) as tile
from (values (1), (1), (1), (1)) v(x);

返回：

x tile
1   1
1   1
1   2
1   2

相同的值。 不同的磁贴。 如果您跟踪值所在的磁贴，情况会变得更糟。 不同的行可以在同一查询的不同运行中具有不同的磁贴 - 即使数据没有更改也是如此。

通常，您希望具有相同值的行具有相同的四分位数，即使切片大小不同也是如此。 出于这个原因，我建议使用rank()进行显式计算：

select t.*,
((seqnum - 1) * 4 / cnt) + 1 as quartile
from (select t.*,
rank() over (partition by person order by val) as seqnum,
count(*) over (partition by person) as cnt
from t
) t;

如果确实希望在磁贴之间拆分值，请使用row_number()而不是rank()。