Расширение Перцентилей

1. Обзор

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

2. Получение и компиляция расширения Перцентилей

Код расширения перцентилей находится в файле ext/misc/percentile.c основного дерева исходного кода SQLite. Его можно скомпилировать в загружаемое расширение SQLite с помощью команды:

gcc -g -fPIC -shared percentile.c -o percentile.so

В качестве альтернативы, файл percentile.c может быть скомпилирован в приложение. В этом случае, для регистрации расширения с каждой новой базой данных следует вызвать следующую функцию:

int sqlite3_percentile_init(sqlite3 *db, void*, void*);

Первый аргумент, который должен быть передан, — это обработчик базы данных для регистрации расширения. Второй и третий аргументы должны быть оба равны 0.

Расширение Перцентилей включено в большинство сборок оболочки командной строки.

3. Функции агрегирования, реализованные расширением Перцентилей

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

3.1. Функция агрегирования median(Y)

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

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

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

3.2. Функция агрегирования percentile(Y,P)

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

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

3.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.

3.4. Функция агрегирования percentile_disc(Y,P)

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

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

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

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

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). Другими словами, дробное значение во втором аргументе находится в диапазоне от 0 до 1 вместо от 0 до 100.

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) могут использоваться как функции окон.

Эта страница была последняя изменена 2024-09-18 15:57:24 UTC