Одним из распространенных статистических вычислений, которые встречаются даже при администрировании баз данных, является процентиль.
Процентиль — это статистическая мера, которая позволяет нам разделить набор данных на равные части сегментов. Роль процентилей заключается в том, чтобы дать представление о распределении данных, благодаря чему мы понимаем, как распределяются значения.
В этом уроке мы узнаем, как можно рассчитать процентили в SQL, чтобы разделить данные на различные сегменты.
Пример таблицы
Давайте начнем с создания базовой таблицы, содержащей образцы данных для демонстрационных целей. Это помогает нам проиллюстрировать, как ведут себя различные методы расчета процентилей и получаемые результаты.
Давайте создадим таблицу под названием «продукты», содержащую информацию о продуктах. Предложение «создать таблицу» выглядит следующим образом:
СОЗДАТЬ ТАБЛИЦУ продуктов (
product_id INT ПЕРВИЧНЫЙ КЛЮЧ AUTO_INCREMENT,
имя_продукта VARCHAR( 255 ),
категория ВАРЧАР( 255 ),
цена ДЕСЯТИЧНАЯ( 10 , 2 ),
количество INT,
истечение_даты ДАТА,
штрих-код БИГИНТ
);
После того, как мы создали таблицу, мы можем продолжить и добавить в нее образец данных. Мы можем использовать следующие операторы «вставки»:
вставлятьв
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Шляпа шеф-повара 25см' ,
'пекарня' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Яйца перепелиные - консервированные' ,
'кладовая' ,
17.99 ,
67 ,
'29 сентября 2023 г.' ,
1708039594250 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Кофе - Эгг Ног Капучино' ,
'пекарня' ,
92,53 ,
10 ,
'22 сентября 2023 г.' ,
8704051853058 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Груша - Колючая' ,
'пекарня' ,
65,29 ,
48 ,
'2023-08-23' ,
5174927442238 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Паста - Волосы ангела' ,
'кладовая' ,
48,38 ,
59 ,
'2023-08-05' ,
8008123704782 );
вставлять
в
продукты (имя_продукта,
категория,
цена,
количество,
Дата окончания срока,
штрих-код)
ценности ( 'Вино - Просекко Вальдобьядене' ,
'производить' ,
44,18 ,
3 ,
'2023-03-13' ,
6470981735653 );
В итоге у вас должна получиться следующая таблица:
SQL-процентиль
Как вы можете догадаться, способ расчета процентиля может отличаться в зависимости от ядра базы данных. Однако наиболее распространенным методом является использование функций PERCENTILE_DISC() и PERCENTILE_CONT().
Эти функции являются частью стандартной спецификации SQL (2003 г.). Следовательно, он обязательно будет поддерживаться PostgreSQL и Oracle.
ПРОЦЕНТИЛЬ_КОНТ()
Начнем с функции PERCENTILE_CONT(). Эта функция позволяет нам рассчитать значения процентиля как часть набора данных.
Функция возвращает интерполированные значения, которые могут неточно соответствовать конкретной точке данных в вашем наборе данных.
Синтаксис функции следующий:
PERCENTILE_CONT(процентиль) ВНУТРИ ГРУППА ( ЗАКАЗ ПО имя_столбца) НАД ();Функция принимает следующие параметры:
- Процентиль – указывает желаемое значение процентиля (от 0,0 до 1,0).
- имя_столбца – обозначает столбец, для которого мы хотим вычислить процентиль.
- OVER () — устанавливает оконную функцию для указания всего набора данных.
Пример использования этой функции следующий:
ВЫБИРАТЬPERCENTILE_CONT( 0,5 ) В ПРЕДЕЛАХ ГРУППА ( ЗАКАЗ ПО цене) БОЛЬШЕ () КАК медиана
ОТ
продукты;
Примечание. Данный запрос работает только в PostgreSQL, поскольку MySQL не поддерживает использование Within GROUP.
Это вычисляет 50 й процентиль предоставленных данных.
ПРОЦЕНТИЛЬ_ДИСК()
Мы можем использовать функцию PERCENTILE_DISC() для расчета значения процентиля как дискретного значения непосредственно из набора данных.
Функция возвращает значение, соответствующее фактической точке данных.
Синтаксис функции следующий (PostgreSQL):
PERCENTILE_DISC(процентиль) ВНУТРИ ГРУППА ( ЗАКАЗ ПО имя_столбца) НАД ();Пример вывода выглядит следующим образом:
ВЫБИРАТЬПРОЦЕНТИЛЬ_ДИСК( 0,25 ) В ПРЕДЕЛАХ ГРУППА ( ЗАКАЗ ПО цене) НАД () КАК процентиль_25
ОТ
продукты;
Это должно вычислить 25 й процентиль данных.
Заключение
В этом руководстве рассказывается, как использовать различные функции для расчета процентилей в базах данных SQL.