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

Без теории не обойтись.

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

Алгоритм вычисления медианы.

Для примера возьмём два числовых ряда:

{13, 5, -3, 18, 2, 5, 0, 9, 2}

{6, 3, 8, 1, 3, 5}

1. Упорядочим числовой ряд по возрастанию.

{-3, 0, 2, 2, 5, 5, 9, 13, 18}

{1, 3, 3, 5, 6, 8}

2. Выберем число, которое находится непосредственно по середине получившегося ряда.

• Если количество элементов нечётное, то берём средний элемент числового ряда:

{-3, 0, 2, 2, 5, 5, 9, 13, 18}

median = 5

• Если количество элементов чётное, то мы можем в принципе выбрать любое число из интервала [x; y], где x и y – два средних значения, но в основном используют среднее арифметическое этих двух чисел (в дальнейшем буду использовать именно этот вариант):

{1, 3, 3, 5, 6, 8}

median = (3 + 5) / 2 = 4

И сразу пример.

Задача: Необходимо с помощью одного запроса получить медианы цен номенклатуры по одному типу цен и вывести результат в таблицу из двух колонок: «Номенклатура» и «МедианаЦены».

Используемые данные: периодический регистр «Цены номенклатуры» (например типовой регистр «Цены номенклатуры» УТ 10.3).

Решение:

ВЫБРАТЬ
     ЦеныНоменклатуры.Цена * 10000000000 + ГОД(ЦеныНоменклатуры.Период) * 10000 + МЕСЯЦ(ЦеныНоменклатуры.Период) * 100 + ДЕНЬ(ЦеныНоменклатуры.Период) КАК Ключ,
     ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
     ЦеныНоменклатуры.Цена КАК Цена
ПОМЕСТИТЬ втЦеныНоменклатуры
ИЗ
     РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
     ЦеныНоменклатуры.ТипЦен = &ТипЦен

ИНДЕКСИРОВАТЬ ПО
     Номенклатура,
     Ключ
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
     ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
     ВЫРАЗИТЬ((КОЛИЧЕСТВО(ЦеныНоменклатуры.Цена) + 1) / 2 КАК ЧИСЛО(10, 0)) КАК СреднийНомерСправа,
     ВЫРАЗИТЬ(КОЛИЧЕСТВО(ЦеныНоменклатуры.Цена) / 2 КАК ЧИСЛО(10, 0)) КАК СреднийНомерСлева
ПОМЕСТИТЬ втСредниеНомера
ИЗ
     РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
     ЦеныНоменклатуры.ТипЦен = &ТипЦен

СГРУППИРОВАТЬ ПО
     ЦеныНоменклатуры.Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
     втЦеныНоменклатуры.Номенклатура КАК Номенклатура,
     втЦеныНоменклатуры.Цена КАК Цена,
     КОЛИЧЕСТВО(втЦеныНоменклатуры1.Номенклатура) КАК Номер
ПОМЕСТИТЬ втПоПорядку
ИЗ
     втЦеныНоменклатуры КАК втЦеныНоменклатуры
          ВНУТРЕННЕЕ СОЕДИНЕНИЕ втЦеныНоменклатуры КАК втЦеныНоменклатуры1
          ПО втЦеныНоменклатуры.Ключ >= втЦеныНоменклатуры1.Ключ
               И втЦеныНоменклатуры.Номенклатура = втЦеныНоменклатуры1.Номенклатура

СГРУППИРОВАТЬ ПО
     втЦеныНоменклатуры.Номенклатура,
     втЦеныНоменклатуры.Ключ,
     втЦеныНоменклатуры.Цена
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
     втСредниеНомера.Номенклатура КАК Номенклатура,
     ВЫРАЗИТЬ(СРЕДНЕЕ(втПоПорядку.Цена) КАК ЧИСЛО(15, 2)) КАК МедианаЦены
ИЗ
     втСредниеНомера КАК втСредниеНомера
          ВНУТРЕННЕЕ СОЕДИНЕНИЕ втПоПорядку КАК втПоПорядку
          ПО втСредниеНомера.Номенклатура = втПоПорядку.Номенклатура
               И (втСредниеНомера.СреднийНомерСправа = втПоПорядку.Номер
                    ИЛИ втСредниеНомера.СреднийНомерСлева = втПоПорядку.Номер)

СГРУППИРОВАТЬ ПО
     втСредниеНомера.Номенклатура

Почему это работает.

Основная сложность, с которой разработчик может столкнуться при решении этой задачи, это однозначно определить каждую запись цены по каждой номенклатуре. В пакетном запросе (втПоПорядку) с нарастающим итогом я не мог использовать поле «Цена» в качестве ключа сравнения в соединении, потому что значения цен в пределах номенклатуры могли повторяться. Поэтому я, воспользовавшись свойством периодичности регистра сведений «Цены номенклатуры», сгенерировал собственное поле, состоящее из значения цены, расширенного числовым представлением даты записи, и назвал его «Ключ». Это рукотворное поле позволило мне качествено упорядочить и пронумеровать каждый ряд цен для какой-либо конкретной номенклатуры. Обращаю внимание, что значение цены я умножаю на 10^10, чтобы копейки не смешались с числовой датой, то есть 2 разряда под копейки, остальные 8 под расширение.

Итак. В таблице «втЦеныНоменклатуры» находятся цены, номенклатура и ключ, имеющий уникальное значение в пределах каждой отдельной номенклатуры. В таблице «втСредниеНомера» содержатся номера средних элементов ряда цен для каждой отдельной номенклатуры (в случае с нечётным количество элементов СреднийНомерСправа = СреднийНомерСлева). В таблице «втПоПорядку» содержатся пронумерованные записи цен по каждой номенклатуре. В финальном пакете я соединил «втСредниеНомера» с «втПоПорядку» по номенклатуре и номерам записей и получил таким образом значение средней записи ряда цен для каждой номенклатуры.

Плюсы / минусы.

Плюсы.

Универсальность. Во-первых, подобный запрос справляется с крайними случаями, когда элементов в ряду меньше 3. Во-вторых, можно осуществлять как расчёт медианы по нескольким измерениям (в нашем примере, добавить тип цен), так и расчёт медиан нескольких полей в одном запросе.

Минусы.

Придётся заморочиться по поводу поля «Ключ».  Ключ должен иметь тип, подлежащий сравнению в запросе. Чаще всего Число. И есть ограничение, количество разрядов этого числа (целой части + дробной) должно быть не более 38.

Чтобы генерировать ключ по типам, которые не сопоставимы и не подходят для сравнения, мне порой приходилось содержать специальные таблицы ключей объектов. Но это уже совсем другая история…

Больше минусов не нашёл.

Ссылки.

Как вычислить среднее значение, медиану, моду (статистика)

Медиана (статистика)