SQL — групповые значения по рейтингу процентиля/слияния

У меня есть несколько таблиц, содержащих название компании/атрибута и рейтинг.

введите здесь описание изображения

Я хотел бы написать фрагмент кода, который позволяет помещать ряд оценок в определенные группы на основе процентиля оценки по отношению к таблице Score total. Я предоставил очень простой пример использования, чтобы продемонстрировать то, что я ищу, разбив группу из 10 компаний на 5 групп, но я хотел бы масштабировать это, чтобы применить 5 групп к наборам данных с большим количеством строк БЕЗ необходимости указывать значения в операторе CASE.

введите здесь описание изображения


sql tsql ranking scoring
person hansolo    schedule 17.08.2016    source источник

Ответы (3)


arrow_upward
4
arrow_downward

NTILE(5) OVER(ORDER BY score) может фактически помещать строки с одинаковым значением в разные квантили (вероятно, это не то, что вам нужно, по крайней мере, мне это никогда не нравилось).

Это очень похоже на

5 * (row_number() over (order by score) - 1) / count(*) over ()

но если число строк нельзя разделить поровну, оставшиеся строки добавляются к первым квантилям при использовании NTILE и случайным образом для ROW_NUMBER.

Чтобы присвоить все строки с одинаковым значением одному и тому же квантилю, вам нужно выполнить собственный расчет:

5 * (rank() over (order by score) - 1) / count(*) over ()
person dnoeth    schedule 17.08.2016
comment
попробовал это, и есть одно явное отличие от предложения vkp. В предложении vkp группы 1-5, в вашем предложении группы 0-4 ... Есть ли способ воспроизвести ваш метод с группами 1-5 вместо 0-4? - person hansolo; 17.08.2016
comment
Добавить +1 к результату? - person dnoeth; 17.08.2016
comment
5 * (rank() over (order by [Account Score]) + 1) / count(*) over () as grp2 дает мне от 0 до 5, только два элемента из 7000 имеют 5 ... может быть, я просто неправильно понимаю решение - person hansolo; 17.08.2016
comment
Для тех, кто видит это решение, использующее версию SQL, которая не выполняет целочисленное деление с помощью /, например HIVE, вы можете использовать DIV для выполнения целочисленного деления следующим образом: 10 * (RANK() OVER (ORDER BY score) - 1) / COUNT(*) OVER () + 1 AS дециль - person Nic Scozzaro; 04.04.2019

arrow_upward
5
arrow_downward

Вы можете использовать NTILE, чтобы разделить данные на 5 сегментов на основе оценки. Однако, если данные не могут быть разделены на равное количество бинов или если есть связи, одна из групп будет иметь больше членов.

SELECT t.*, NTILE(5) OVER(ORDER BY score) as grp
FROM tablename t

Подробнее о NTILE читайте здесь

person Vamsi Prabhala    schedule 17.08.2016
comment
Или, если есть связи, то NTILE() ячейки не будут одинакового размера. - person Gordon Linoff; 17.08.2016
comment
включил это в ответ @GordonLinoff - person Vamsi Prabhala; 17.08.2016
comment
NTILE может фактически помещать строки с одинаковым значением в разные квантили. Если вы этого не хотите, вы можете использовать этот расчет: 5 * (rank() over (order by score) - 1) / count(*) over () - person dnoeth; 17.08.2016
comment
@dnoeth .. вы должны опубликовать это как ответ - person Vamsi Prabhala; 17.08.2016
comment
Только что использовал NTILE, который предложил @vkp, и, кажется, он работает. Однако я могу упускать из виду некоторые из проблем, которые были упомянуты. Есть ли предпочтение между ответом vkp и dnoeth? - person hansolo; 17.08.2016
comment
@hansolo: я добавил ответ (как предложил vkp), предпочтения должны основываться на ваших потребностях. - person dnoeth; 17.08.2016

arrow_upward
2
arrow_downward

Вы можете попробовать использовать ROW_NUMBER() и CEILING():

SELECT t.name,t.score,
       CEILING(ROW_NUMBER() OVER(ORDER BY t.score)/2) as group
FROM YourTable t

Это разделит каждую группу из двух на одну группу, используя результат ROW_NUMBER().

person sagi    schedule 17.08.2016
comment
Но тогда вам нужно знать количество строк в результате, чтобы иметь возможность создать n квантилей. - person dnoeth; 17.08.2016