How to calculate quartiles grouped by?

Question

Let's say I have a table

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

And I'd like to calculate the quartiles for each person.

I understand I can easily calculate those for a single person as such:

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

Will get me the desired results:

VAL    QUARTILE
1      1
2      2
3      3
4      4

Problem is, I'd like to do this for every person. I know something like this would do the job:

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

But what if there's a new person on the table? Then I'd have to change the SQL code. Any suggestions?

Answer 1

Why don't you try to use partition by.

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

Greetings