Как рассчитать квартили по группам?

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