Без теории не обойтись.
Медианой числового ряда называется такое число, что ровно половина из элементов ряда больше него, а другая половина меньше него.
Алгоритм вычисления медианы.
Для примера возьмём два числовых ряда:
{13, 5, -3, 18, 2, 5, 0, 9, 2}
{6, 3, 8, 1, 3, 5}
- Упорядочим числовой ряд по возрастанию.
{-3, 0, 2, 2, 5, 5, 9, 13, 18}
{1, 3, 3, 5, 6, 8}
- Выберем число, которое находится непосредственно по середине получившегося ряда.
- Если количество элементов нечётное, то берём средний элемент числового ряда:
{-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.
Чтобы генерировать ключ по типам, которые не сопоставимы и не подходят для сравнения, мне порой приходилось содержать специальные таблицы ключей объектов. Но это уже совсем другая история...
Больше минусов не нашёл.
