Расчет медианы и квартилей для дискретного ряда запросом

Что такое медиана и квартили?

Медиана — числовое значение признака, которое делит упорядоченную по возрастанию совокупность на две равных части.

Квартили — числовые значения признака, которые делят упорядоченную по возрастанию совокупность на четыре равных части. 
Раз квартили делят совокупность на четыре части, то квартилей бывает три варианта: первый (нижний), второй(средний), третий (верхний). Второй квартиль это и есть медиана.

Пример

Имеем числовой ряд [1,3,5,7,9,11,13]:

• Медиана = 7 (делит исходный ряд пополам [1,3,5] + [9,11,13]);
• Квартиль1 = 3 (делит ряд [1,3,5] пополам);
• Квартиль2 = Медиана = 7;
• Квартиль3 = 11 (делит ряд [9,11,13] пополам).

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

Зачем все это нужно?

Медиана

Такой показатель как "среднее" знаком всем и ни у кого не возникает вопросов о его необходимости. Проблема со средним в том, что оно хорошо описывает данные, если они распределены нормально. Иначе, как правило, разумнее использовать медиану. 

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

Более близкий пример к 1С: средний чек. Если хочется оценить динамику этого показателя, то лучше использовать медиану. Причина: на среднее сильно влияют выбросы — очень маленькие покупки или наоборот очень большие.

Квартили

Например, есть задача оценки адекватности закупочной цены на товар на текущий момент. 

Один из вариантов — посмотреть по каким ценам продавался этот товар ранее и исходя из наценки рассчитать целевую закупочную цену. Но смотреть на среднее, как было сказано выше, плохая затея. Можно, например, попытаться определить такую цену, что 75% товара продавалось по ней или выше — это и будет 1-й квартиль. Все это, правда, будет работать, если у нас достаточно наблюдений — например, товар продавался хотя бы раз 30, чтобы было на основе чего все рассчитывать.
Безусловно, все это не может быть единственным критерием для определения разумной закупочной цены и нужно использоваться что-то еще.

Формулы

В общем случае можно посчитать порядковый номер медианы и квартилей:

• порядковый номер медианы = (n+1)/2;
• порядковый номер квартиля = номер квартиля*(n+1)/4.

В нашем примере:

• порядковый номер медианы = (7+1)/2 = 4;
• порядковый номер первого квартиля = 1*(7+1)/4 = 2;
• порядковый номер первого квартиля = 2*(7+1)/4 = 4;
• порядковый номер первого квартиля = 3*(7+1)/4 = 6.

В общем случае, все эти порядковые номера могут быть нецелыми (например, если количество элементов нечетно).
Если номер квартиля – нецелое число, то значением квартиля будет сумма, состоящая из значения элемента, для которого порядковый номер равен целому значению номера квартиля, и указанной части (нецелая часть номера квартиля) разности между значением этого элемента и значением следующего элемента.

Так же в общем случае, не всегда есть порядковый номер: один и тот же признак может встречаться в выборке несколько раз и пронумеровать их можно будет только условно. Например, в статье //infostart.ru/public/539316/ рассматривается как раз такой вариант решения.

В общем случае, можно использовать понятие накопленной частоты для каждого уникального элемента ряда. Подробнее про теорию можно почитать, например, тут: https://studfile.net/preview/5316597/page:3/#9. Там же есть хорошие примеры расчета.

Запрос

Постарался прокомментрировать все действия в самом запросе

//готовим саму выборку данных
//Значение — значение признака
//Частота — сколько раз данный признак встречается в выборке
ВЫБРАТЬ
Продажи.Номенклатура КАК Группировка,
СУММА(Продажи.КоличествоОборот) КАК Частота,
ВЫРАЗИТЬ(Продажи.СтоимостьОборот / Продажи.КоличествоОборот КАК ЧИСЛО(15, 0)) КАК Значение
ПОМЕСТИТЬ врВыборка
ИЗ
(ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.Регистратор КАК Регистратор,
ПродажиОбороты.КоличествоОборот КАК КоличествоОборот,
ПродажиОбороты.СтоимостьОборот КАК СтоимостьОборот
ИЗ
РегистрНакопления.Продажи.Обороты(, , Регистратор, ) КАК ПродажиОбороты
ГДЕ
ПродажиОбороты.КоличествоОборот > 0
И ПродажиОбороты.СтоимостьОборот > 0) КАК Продажи

СГРУППИРОВАТЬ ПО
Продажи.Номенклатура,
ВЫРАЗИТЬ(Продажи.СтоимостьОборот / Продажи.КоличествоОборот КАК ЧИСЛО(15, 0))
;

////////////////////////////////////////////////////////////////////////////////
//рассчитываем порядковые номера квартилей и медианы, отдельно целые части, отдельно дробные
//попутно рассчитываем средние, минимальные и максимальные значения
ВЫБРАТЬ
врВыборка.Группировка,
СУММА(врВыборка.Частота) КАК РазмерВыборки,
ВЫРАЗИТЬ(СУММА(врВыборка.Частота * врВыборка.Значение) / СУММА(врВыборка.Частота) КАК ЧИСЛО(15, 2)) КАК СреднееЗначениеПоВыборке,
МИНИМУМ(врВыборка.Значение) КАК МинимальноеЗначениеПоВыборке,
МАКСИМУМ(врВыборка.Значение) КАК МаксимальноеЗначениеПоВыборке,
ВЫРАЗИТЬ((СУММА(врВыборка.Частота) + 1) / 2 - 0.5 КАК ЧИСЛО(15, 0)) КАК ПорядковыйНомерМедианы,
(СУММА(врВыборка.Частота) + 1) / 2 - (ВЫРАЗИТЬ((СУММА(врВыборка.Частота) + 1) / 2 - 0.5 КАК ЧИСЛО(15, 0))) КАК СмещениеМедианыВправо,
ВЫРАЗИТЬ(1 * (СУММА(врВыборка.Частота) + 1) / 4 - 0.5 КАК ЧИСЛО(15, 0)) КАК ПорядковыйНомерКвартиля1,
1 * (СУММА(врВыборка.Частота) + 1) / 4 - (ВЫРАЗИТЬ(1 * (СУММА(врВыборка.Частота) + 1) / 4 - 0.5 КАК ЧИСЛО(15, 0))) КАК СмещениеКвартиля1Вправо,
ВЫРАЗИТЬ(3 * (СУММА(врВыборка.Частота) + 1) / 4 - 0.5 КАК ЧИСЛО(15, 0)) КАК ПорядковыйНомерКвартиля3,
3 * (СУММА(врВыборка.Частота) + 1) / 4 - (ВЫРАЗИТЬ(3 * (СУММА(врВыборка.Частота) + 1) / 4 - 0.5 КАК ЧИСЛО(15, 0))) КАК СмещениеКвартиля3Вправо
ПОМЕСТИТЬ врСвойстваВыборки
ИЗ
врВыборка КАК врВыборка

СГРУППИРОВАТЬ ПО
врВыборка.Группировка
;

////////////////////////////////////////////////////////////////////////////////
//рассчитываем порядковые номера для каждого признака
//при этом т.к. признак может повторяться, то рассчитываем и первые (левый) номер для данном значения признака и последний (правый)
//по сути это и есть накопленная частота
ВЫБРАТЬ
врВыборка.Группировка,
врВыборка.Частота,
врВыборка.Значение,
СУММА(врВыборкаВспомогательная.Частота) КАК НакопленнаяЧастотаПравогоЭлемента,
СУММА(ВЫБОР
КОГДА врВыборкаВспомогательная.Значение = врВыборка.Значение
ТОГДА 0
ИНАЧЕ врВыборкаВспомогательная.Частота
КОНЕЦ) + 1 КАК НакопленнаяЧастотаЛевогоЭлемента
ПОМЕСТИТЬ врВыборкаСНакопленнойЧастотой
ИЗ
врВыборка КАК врВыборка
ВНУТРЕННЕЕ СОЕДИНЕНИЕ врВыборка КАК врВыборкаВспомогательная
ПО врВыборка.Группировка = врВыборкаВспомогательная.Группировка
И врВыборка.Значение >= врВыборкаВспомогательная.Значение

СГРУППИРОВАТЬ ПО
врВыборка.Группировка,
врВыборка.Частота,
врВыборка.Значение
;

////////////////////////////////////////////////////////////////////////////////
//следующие два запроса выполняют сам расчет, их стоит рассматривать как одно целое
//разделены они для наглядности и простоты отладки
ВЫБРАТЬ
врВыборкаСНакопленнойЧастотой.Группировка,
врВыборкаСНакопленнойЧастотой.Частота,
врВыборкаСНакопленнойЧастотой.Значение,
ВЫБОР
КОГДА врСвойстваВыборки.ПорядковыйНомерМедианы >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерМедианы <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА врВыборкаСНакопленнойЧастотой.Значение
ИНАЧЕ 0
КОНЕЦ КАК МедианаЦелаяЦасть,
ВЫБОР
КОГДА врСвойстваВыборки.ПорядковыйНомерМедианы >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерМедианы <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерМедианы + 1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерМедианы + 1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА 0
КОГДА врСвойстваВыборки.ПорядковыйНомерМедианы + 1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерМедианы + 1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА врВыборкаСНакопленнойЧастотой.Значение
КОГДА врСвойстваВыборки.ПорядковыйНомерМедианы >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерМедианы <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА -врВыборкаСНакопленнойЧастотой.Значение
ИНАЧЕ 0
КОНЕЦ * врСвойстваВыборки.СмещениеМедианыВправо КАК МедианаДробнаяЧасть,
ВЫБОР
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА врВыборкаСНакопленнойЧастотой.Значение
ИНАЧЕ 0
КОНЕЦ КАК Квартиль1ЦелаяЦасть,
ВЫБОР
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля1 + 1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля1 + 1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА 0
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля1 + 1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля1 + 1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА врВыборкаСНакопленнойЧастотой.Значение
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА -врВыборкаСНакопленнойЧастотой.Значение
ИНАЧЕ 0
КОНЕЦ * врСвойстваВыборки.СмещениеКвартиля1Вправо КАК Квартиль1ДробнаяЧасть,
ВЫБОР
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля3 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля3 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА врВыборкаСНакопленнойЧастотой.Значение
ИНАЧЕ 0
КОНЕЦ КАК Квартиль3ЦелаяЦасть,
ВЫБОР
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля3 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля3 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля3 + 1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля3 + 1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА 0
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля3 + 1 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля3 + 1 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА врВыборкаСНакопленнойЧастотой.Значение
КОГДА врСвойстваВыборки.ПорядковыйНомерКвартиля3 >= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента
И врСвойстваВыборки.ПорядковыйНомерКвартиля3 <= врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента
ТОГДА -врВыборкаСНакопленнойЧастотой.Значение
ИНАЧЕ 0
КОНЕЦ * врСвойстваВыборки.СмещениеКвартиля3Вправо КАК Квартиль3ДробнаяЧасть,
врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаПравогоЭлемента,
врВыборкаСНакопленнойЧастотой.НакопленнаяЧастотаЛевогоЭлемента,
врСвойстваВыборки.РазмерВыборки,
врСвойстваВыборки.СреднееЗначениеПоВыборке,
врСвойстваВыборки.МинимальноеЗначениеПоВыборке,
врСвойстваВыборки.МаксимальноеЗначениеПоВыборке,
врСвойстваВыборки.ПорядковыйНомерМедианы,
врСвойстваВыборки.СмещениеМедианыВправо,
врСвойстваВыборки.ПорядковыйНомерКвартиля1,
врСвойстваВыборки.СмещениеКвартиля1Вправо,
врСвойстваВыборки.ПорядковыйНомерКвартиля3,
врСвойстваВыборки.СмещениеКвартиля3Вправо
ПОМЕСТИТЬ врИтоговаяТаблица
ИЗ
врВыборкаСНакопленнойЧастотой КАК врВыборкаСНакопленнойЧастотой
ВНУТРЕННЕЕ СОЕДИНЕНИЕ врСвойстваВыборки КАК врСвойстваВыборки
ПО врВыборкаСНакопленнойЧастотой.Группировка = врСвойстваВыборки.Группировка
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
врИтоговаяТаблица.Группировка,
врИтоговаяТаблица.РазмерВыборки,
врИтоговаяТаблица.СреднееЗначениеПоВыборке,
врИтоговаяТаблица.МинимальноеЗначениеПоВыборке,
врИтоговаяТаблица.МаксимальноеЗначениеПоВыборке,
СУММА(врИтоговаяТаблица.МедианаЦелаяЦасть) + СУММА(врИтоговаяТаблица.МедианаДробнаяЧасть) КАК Медиана,
СУММА(врИтоговаяТаблица.Квартиль1ЦелаяЦасть) + СУММА(врИтоговаяТаблица.Квартиль1ДробнаяЧасть) КАК Квартиль1,
СУММА(врИтоговаяТаблица.Квартиль3ЦелаяЦасть) + СУММА(врИтоговаяТаблица.Квартиль3ДробнаяЧасть) КАК Квартиль3
ИЗ
врИтоговаяТаблица КАК врИтоговаяТаблица

СГРУППИРОВАТЬ ПО
врИтоговаяТаблица.Группировка,
врИтоговаяТаблица.РазмерВыборки,
врИтоговаяТаблица.СреднееЗначениеПоВыборке,
врИтоговаяТаблица.МинимальноеЗначениеПоВыборке,
врИтоговаяТаблица.МаксимальноеЗначениеПоВыборке

Квантили

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

Ссылки

Немного теории (кривой сайт, но все понятно написано и хорошие примеры): https://studfile.net/preview/5316597/page:3/#9

Похожая разработка (но требующая уникальности): //infostart.ru/public/539316/

Видео-курс "Основы статистики": https://stepik.org/course/76/