按组计算四分位数的方法是什么？

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代码。有什么建议吗？

- Johnny Bigoode

Answer 1

为什么不尝试使用partition by。

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

问候

- Alvaro Parra

---

那很容易。谢谢。 - Johnny Bigoode

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()。

- Gordon Linoff