假设我有一张表格
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代码。有什么建议吗?