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

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

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

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

Пример

Имеем числовой ряд [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.

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

Так же в общем случае, не всегда есть порядковый номер: один и тот же признак может встречаться в выборке несколько раз и пронумеровать их можно будет только условно. Например, в статье //sale.itcity.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

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

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