Расширение Percentile

1. Обзор

Расширение percentile предоставляет четыре агрегатные функции, которые вычисляют процентиль и/или медианное значение распределения.

1.1. Доступность

Расширение percentile входит в состав амальгамации начиная с SQLite версии 3.51.0 (2025-11-04). Однако расширение percentile по умолчанию отключено, и его необходимо активировать во время компиляции с помощью параметра компиляции -DSQLITE_ENABLE_PERCENTILE.

До SQLite 3.51.0 расширение percentile находилось в отдельном файле исходного кода, который требовалось компилировать независимо и использовать как загружаемое расширение.

2. Агрегатные функции, реализованные в Percentile

Расширение percentile реализует описанные ниже агрегатные функции SQL. Алгоритмы всех этих функций используют O(N) памяти и O(NlogN) времени, где N — количество входных значений, не равных NULL.

2.1. Агрегатная функция median(Y)

Функция median(Y) — это агрегатная функция, вычисляющая медианное значение всех входных значений Y, не равных NULL. Если хотя бы одно входное значение Y для median() не равно NULL и не является числовым значением, возникает ошибка. Если числовых входных значений, не равных NULL, нет, функция median() возвращает NULL.

Медиана — это значение центрального элемента, если все входные значения отсортированы и их количество нечётно. Если количество входных значений чётно, медиана равна среднему арифметическому двух центральных значений.

Функция median(Y) эквивалентна percentile(Y,50).

2.2. Агрегатная функция percentile(Y,P)

Агрегатная функция percentile(Y,P) вычисляет значение X, которое больше или равно P процентам входных значений, не равных NULL, и меньше или равно 100-P процентам входных значений. Параметр P должен быть числом от 0.0 до 100.0. Значение P должно быть одинаковым для всех членов агрегата и не может быть NULL. Входные значения Y должны быть либо NULL, либо числовыми. Значения Y, равные NULL, игнорируются. Если какое-либо входное значение Y, не равное NULL, не является числовым, возникает ошибка.

Функция percentile() сортирует входные значения, не равные NULL, а затем вычисляет одно или несколько значений, ближайших к P процентам пути от первого значения к последнему. Возвращаемое значение — это взвешенное среднее двух ближайших значений.

2.3. Агрегатная функция percentile_cont(Y,P)

Функция percentile_cont(Y,P) работает как percentile(Y,P), за исключением того, что значение P находится в диапазоне от 0.0 до 1.0, а не от 0.0 до 100.0. Таким образом, результат percentile_cont(Y,P) совпадает с результатом percentile(Y,P*100).

Функция percentile_cont() определена стандартами SQL. Однако вместо простого вызова функции "percentile_cont(Y,P)" синтаксис стандарта SQL выглядит так:

SELECT percentile_cont(P) WITHIN GROUP (ORDER BY Y) FROM tab;

Это довольно многословный способ выразить то же самое, что и:

SELECT percentile_cont(Y,P) FROM tab;

SQLite поддерживает синтаксис стандарта SQL, но только если сборка выполнена (из канонических исходников, а не из амальгамации) с параметром компиляции -DSQLITE_ENABLE_ORDERED_SET_AGGREGATES=1. Без этого параметра компиляции поддерживается только более простая форма "percentile_cont(Y,P)". Поскольку у многословного формата стандарта SQL нет преимуществ, зато есть существенные недостатки с точки зрения читаемости, а параметр компиляции SQLITE_ENABLE_ORDERED_SET_AGGREGATES увеличивает размер библиотеки SQLite, этот параметр не используется в большинстве сборок.

Автор считает, что суффикс "_cont" в имени этой функции — это сокращение от "continuous" («непрерывный»), отражающее тот факт, что возвращаемое значение является взвешенным средним двух входных значений, ближайших к фактическому рангу процентиля. Имя задано стандартом SQL, а не разработчиками SQLite.

2.4. Агрегатная функция percentile_disc(Y,P)

Функция percentile_disc(Y,P) работает как percentile_cont(Y,P), за исключением того, что вместо вычисления взвешенного среднего ближайших доступных входных значений она всегда возвращает одно из входных значений — меньшее из двух возможных. Функция percentile_disc(Y,P) определена стандартами SQL. Как и для percentile_cont(), требуется многословный синтаксис агрегатной функции с упорядоченным набором, но SQLite поддерживает этот синтаксис только при компиляции с параметром SQLITE_ENABLE_ORDERED_SET_AGGREGATES.

Автор считает, что суффикс "_disc" в имени этой функции — это сокращение от "discrete" («дискретный»). Имя задано стандартом SQL, а не разработчиками SQLite.

3. Требования к проектированию

Следующие требования определяют расширение percentile.

1. Функция percentile(Y,P) — это агрегатная функция, принимающая ровно два аргумента.

2. Если аргумент P функции percentile(Y,P) различается для разных строк агрегата, возникает ошибка. В предыдущем предложении под словом «одинаковый» подразумевается, что значения отличаются менее чем на 0.001.

3. Если аргумент P функции percentile(Y,P) принимает значение, отличное от числа в диапазоне от 0.0 до 100.0 включительно, возникает ошибка.

4. Если какой-либо аргумент Y функции percentile(Y,P) принимает значение, которое не равно NULL и не является числовым, возникает ошибка.

5. Если какой-либо аргумент Y функции percentile(Y,P) принимает значение плюс или минус бесконечность, возникает ошибка. (SQLite всегда интерпретирует значения NaN как NULL.)

6. Аргументы Y и P функции percentile(Y,P) могут быть произвольными выражениями, включая выражения CASE WHEN.

7. Агрегатная функция percentile(Y,P) способна обрабатывать входные данные как минимум из одного миллиона (1,000,000) строк.

8. Если для Y нет значений, не равных NULL, функция percentile(Y,P) возвращает NULL.

9. Если для Y есть ровно одно значение, не равное NULL, функция percentile(Y,P) возвращает это значение Y.

10. Если для Y имеется N значений, не равных NULL, где N равно двум или больше, и значения Y упорядочены от наименьшего к наибольшему, а график строится от 0 до N-1 так, что высота графика в точке J равна J-му значению Y и между соседними значениями Y проводятся прямые линии, то функция percentile(Y,P) возвращает высоту графика в точке P*(N-1)/100.

11. Функция percentile(Y,P) всегда возвращает число с плавающей запятой или NULL.

12. Функция percentile(Y,P) реализована в виде отдельного файла исходного кода C99, который компилируется в разделяемую библиотеку или DLL, загружаемую в SQLite с помощью интерфейса sqlite3_load_extension().

13. Отдельная функция median(Y) эквивалентна percentile(Y,50).

14. Отдельная функция percentile_cont(Y,P) возвращает тот же результат, что и percentile(Y,P*100.0). Другими словами, дробное значение второго аргумента percentile_cont() должно находиться в диапазоне от 0 до 1, а не от 0 до 100, как для percentile().

15. Отдельная функция percentile_disc(Y,P) похожа на percentile_cont(Y,P), за исключением того, что вместо взвешенного среднего двух ближайших входных значений она возвращает следующее меньшее значение. Таким образом, percentile_disc(Y,P) всегда возвращает значение, которое было одним из входных.

16. Функции median(), percentile(Y,P), percentile_cont(Y,P) и percentile_disc(Y,P) можно использовать как оконные функции.

Последнее обновление этой страницы: 2026-02-25 22:25:39Z