Проблема:
Иногда нам нужно найти первые или последние n% записей из набора данных на основе определенного столбца. Например, учитель хочет узнать, какие студенты находятся в нижней 25% по оценкам. В таких ситуациях помогает функция SQL NTILE(). Мы воспользуемся этим советом, чтобы получить первые или последние n% записей с использованием NTILE() в SQL Server.
Решение:
Функция NTILE в SQL Server – это мощный инструмент, который позволяет разделить записи из набора данных на указанное количество групп на основе указанного порядка. Это позволяет более эффективно анализировать данные и выполнять различные операции и вычисления над разделенными данными.
Функция NTILE делит набор на равные диапазоны, каждое из которых состоит примерно из равного количества записей/строк, что крайне полезно, когда вы хотите разделить данные на группы на основе процентилей, таких как квартили, децили или любое количество ntiles, которое вам нужно. Это позволяет выявлять тенденции/шаблоны в ваших данных и выполнять операции с каждой группой отдельно. Поэтому этот инструмент мощен для создания гистограмм, анализа ранжирования или даже разделения данных для параллельной обработки.
Синтаксис NTILE:
NTILE(Количество групп) OVER (PARTITION BY выражение_разбиения ORDER BY выражение_порядка)
Количество групп определяет количество групп, на которые нужно разделить ваши данные; поэтому количество групп должно быть целым числом, большим нуля. PARTITION BY – это необязательная часть, которая позволяет разделить данные на подмножества перед применением функции NTILE к набору данных. Затем NTILE будет применяться к каждому отдельному подмножеству. ORDER BY – это обязательная часть, используемая для указания столбца(ов), по которым происходит ранжирование/упорядочивание записей в каждом подмножестве. По сути, это критерий, по которому набор данных разделяется на группы.
В этом совете наш основной фокус будет на использовании функции NTILE для ранжирования записей на основе определенных критериев. Теперь, когда мы дали краткий обзор функции NTILE и ее использования, пришло время узнать, как использовать эту SQL-функцию.
Создание таблиц и данных для тестирования:
Прежде чем мы сможем использовать функцию NTILE, нам нужен набор данных, к которому ее применить. Давайте создадим новую базу данных для тестирования:
CREATE DATABASE Информация; GO; USE Информация;
Затем создайте две таблицы в этой базе данных, одну для записей о сотрудниках и другую для записей о студентах. Поскольку мы используем функцию NTILE, каждая таблица должна содержать по крайней мере один столбец, по которому мы можем ранжировать наши записи. Для ранжирования в таблице Employees мы можем использовать столбец Salary, а в таблице Students – столбец Final_Marks. Нам также понадобится столбец для разделения данных на несколько подмножеств, чтобы продемонстрировать, как использовать необязательную часть PARTITION BY. Для этого добавим столбец Department в таблицу Employees и столбец Course в таблицу Students.
Вот код для создания таблицы Employee_Information:
CREATE TABLE Employee_Information ( Employee_ID INT PRIMARY KEY IDENTITY(1,1), Department VARCHAR(255), Salary INT );
Вот код для создания таблицы Student_Information:
CREATE TABLE Student_Information
(
Student_ID INT PRIMARY KEY IDENTITY(1,1),
Course VARCHAR(255),
Final_Marks INT
);
Теперь, когда обе таблицы созданы, давайте заполним их, начиная с таблицы Employee_Information:
INSERT INTO Employee_Information (Department, Salary) VALUES
('IT', 60000),
('IT', 50000),
('IT', 36000),
('IT', 29000),
('IT', 72000),
('IT', 52000),
('Sales', 23000),
('Sales', 53000),
('Sales', 45000),
('Sales', 56000),
('Sales', 54000),
('Sales', 46000),
('Sales', 57000),
('Sales', 55000),
('Sales', 47000),
('HR', 62500),
('HR', 58000),
('HR', 49000),
('HR', 61000),
('HR', 81000),
('HR', 59000),
('HR', 30000),
('HR', 62000),
('HR', 64000),
('HR', 60500),
('HR', 51000),
('HR', 63000);
Теперь заполните таблицу Student_Information:
INSERT INTO Student_Information (Course, Final_Marks) VALUES
('Math', 92),
('Math', 80),
('Math', 84),
('Math', 66),
('Math', 40),
('Math', 51),
('Math', 83),
('Math', 69),
('Math', 76),
('Math', 40),
('Math', 39),
('Math', 30),
('English', 91),
('English', 89),
('English', 76),
('English', 53),
('English', 96),
('English', 65),
('English', 74),
('English', 69),
('English', 48),
('English', 35),
('English', 77),
('English', 54);
Примеры использования NTILE на данных о сотрудниках:
Предположим, что вам нужно повысить зарплаты сотрудников, зарплата которых находится в первом квартиле, и снизить зарплаты сотрудников в четвертом квартиле на определенные значения, независимо от их отделов. Для этого вам необходимо получить сотрудников в первом и четвертом квартиле, прежде чем вы сможете обновить данные. Итак, сначала вам нужно разделить набор данных Employees на четыре равные группы. Вспомните синтаксис NTILE:
NTILE(Количество групп) OVER (ORDER BY выражение_порядка)
В этом случае Количество групп будет равно 4 (для 4 квартилей). Мы не будем использовать часть PARTITION BY для этой задачи. Поскольку мы ранжируем наших сотрудников по зарплате, мы будем использовать ORDER BY, и выражение_порядка будет зарплата. Чтобы применить функцию к таблице Employees, используйте следующий код:
SELECT *, NTILE(4) OVER (ORDER BY Salary) AS Emp_Ranking FROM Employee_Information;
Код выше будет отображать зарплаты сотрудников, разделенные на четыре группы, ранжированные в порядке возрастания. Чем выше зарплата, тем выше номер. Однако это происходит потому, что часть ORDER BY по умолчанию упорядочивает столбец по возрастанию. Вы можете добавить параметр DESC, например ORDER BY выражение_порядка DESC, чтобы сделать порядок убывающим. Вы также можете отфильтровать результаты, чтобы получить только нужные вам квартили, как показано ниже:
SELECT * FROM (SELECT *, NTILE(4) OVER (ORDER BY Salary) AS Emp_Ranking FROM Employee_Information) AS Temp_Table WHERE Emp_Ranking = 4;
Код выше вернет записи о сотрудниках, зарплата которых находится в четвертом или самом высокооплачиваемом квартиле. Аналогично, код ниже вернет записи о сотрудниках, зарплата которых находится в первом или самом низкооплачиваемом квартиле.
SELECT * FROM (SELECT *, NTILE(4) OVER (ORDER BY Salary) AS Emp_Ranking FROM Employee_Information) AS Temp_Table WHERE Emp_Ranking = 1;
Примеры использования NTILE на таблице Student:
Предположим, у вас есть итоговые оценки студентов, и вы хотите оценить их в соответствии с относительной системой оценок: A – Четвертый квартиль, B – Третий квартиль, C – Второй квартиль, F – Первый квартиль. Сначала вам нужно разделить оценки ваших студентов на четыре группы с помощью функции NTILE, где параметр buckets равен 4, как показано ниже:
SELECT *, NTILE(4) OVER (ORDER BY Final_Marks) AS Student_Grade FROM Student_Information;
Обратите внимание, что наш набор данных состоит из двух курсов; однако мы не использовали часть PARTITION BY, поэтому квартили были сделаны независимо от того, какие оценки были введены для каждого курса. Это было сделано для подчеркивания важности части PARTITION BY. Данные могут не всегда иметь смысл без разделения по другим столбцам, как показано на изображении выше, и чтобы исправить эту проблему, мы теперь будем использовать часть PARTITION BY. Традиционно, меньшее число используется для представления более высокого ранга, поэтому мы будем использовать DESC в части ORDER BY:
SELECT *, NTILE(4) OVER (PARTITION BY Course ORDER BY Final_Marks DESC) AS Student_Grade FROM Student_Information;
Этот запрос возвращает желаемый результат. Теперь столбец Student_Grade имеет смысл, так как каждый курс оценивается отдельно. Давайте отфильтруем наши результаты, чтобы мы могли назначить оценки позже.
Студенты, получающие оценку 'F', это: SELECT * FROM (SELECT *, NTILE(4) OVER (PARTITION BY Course ORDER BY Final_Marks DESC) AS Student_Grade FROM Student_Information) AS Temp_Table WHERE Student_Grade = 4; Студенты, получающие оценку 'C', это: SELECT * FROM (SELECT *, NTILE(4) OVER (PARTITION BY Course ORDER BY Final_Marks DESC) AS Student_Grade FROM Student_Information) AS Temp_Table WHERE Student_Grade = 3; Студенты, получающие оценку 'B', это: SELECT * FROM (SELECT *, NTILE(4) OVER (PARTITION BY Course ORDER BY Final_Marks DESC) AS Student_Grade FROM Student_Information) AS Temp_Table WHERE Student_Grade = 2; Студенты, получающие оценку 'A', это: SELECT * FROM (SELECT *, NTILE(4) OVER (PARTITION BY Course ORDER BY Final_Marks DESC) AS Student_Grade FROM Student_Information) AS Temp_Table WHERE Student_Grade = 1;
Вывод:
Вы узнали, как функция NTILE группирует записи и разделяет данные. Теперь вы можете применять эту функцию к своим наборам данных в соответствии с вашей конкретной проблемой и достигать желаемых результатов. Затем вы можете обновлять и изменять свои данные и выполнять любые операции с вашим набором данных. Вы также можете анализировать полученную информацию.
Статья обновлена: 2023-07-05