gifts2017

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

Опубликовал Дюша Дюшистый (SpaceOfMyHead) в раздел Программирование - Практика программирования

В моей практике возникла задача поиска медиан множества числовых рядов средствами платформы «1С:Предприятие 8». Было принято решение использовать запрос. Так как толковых вариантов с ходу найти в Интернете не удалось, то пришлось решать задачу самостоятельно с нуля. В этой статье делюсь своими наработками и соображениями.

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

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

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

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

{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}

  1. Выберем число, которое находится непосредственно по середине получившегося ряда.
  • Если количество элементов нечётное, то берём средний элемент числового ряда:

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

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

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

Ссылки.

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

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

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Дмитрий Царапкин (NeviD) 27.07.16 16:45
Основная сложность присвоить порядковый номер в запросе. Интересное решение через свое поле Ключ, но этот вариант применим, если есть такие поля, по которым можно его посчитать. Если просто передавать в запрос только список чисел, то так сделать уже не получится.
Ради интереса написал запрос, который выполняет нумерацию заданных значений. В нем есть заранее установленное ограничение на максимальное количество одинаковых чисел (в примере 256).

ВЫБРАТЬ
	0 КАК Ч
ПОМЕСТИТЬ Ц

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	1
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	Ц.Ч + Ц2.Ч * 2 + Ц4.Ч * 4 + Ц8.Ч * 8 + Ц16.Ч * 16 + Ц32.Ч * 32 + Ц64.Ч * 64 + Ц128.Ч * 128 КАК Ч
ПОМЕСТИТЬ ЧЧ
ИЗ
	Ц КАК Ц,
	Ц КАК Ц2,
	Ц КАК Ц4,
	Ц КАК Ц8,
	Ц КАК Ц16,
	Ц КАК Ц32,
	Ц КАК Ц64,
	Ц КАК Ц128
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	Числа.Число КАК Число
ПОМЕСТИТЬ Числа
ИЗ
	&Числа КАК Числа
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	Числа.Число,
	КОЛИЧЕСТВО(*) КАК КоличествоЧисел
ПОМЕСТИТЬ ЧислаСКолвом
ИЗ
	Числа КАК Числа

СГРУППИРОВАТЬ ПО
	Числа.Число
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ЧислаСКолвом.Число,
	ЧислаСКолвом.КоличествоЧисел,
	СУММА(ЕСТЬNULL(ЧислаСКолвом1.КоличествоЧисел, 1) - 1) КАК Доп
ПОМЕСТИТЬ ЧислаСКолвомДоп
ИЗ
	ЧислаСКолвом КАК ЧислаСКолвом
		ЛЕВОЕ СОЕДИНЕНИЕ ЧислаСКолвом КАК ЧислаСКолвом1
		ПО ЧислаСКолвом.Число > ЧислаСКолвом1.Число

СГРУППИРОВАТЬ ПО
	ЧислаСКолвом.Число,
	ЧислаСКолвом.КоличествоЧисел
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ЧислаСКолвомДоп.Число,
	ЧислаСКолвомДоп.Доп,
	ЧЧ.Ч КАК НомерЧисла
ПОМЕСТИТЬ ЧислаСНомером
ИЗ
	ЧЧ КАК ЧЧ
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЧислаСКолвомДоп КАК ЧислаСКолвомДоп
		ПО ЧЧ.Ч < ЧислаСКолвомДоп.КоличествоЧисел
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ЧислаСНомером.Число,
	КОЛИЧЕСТВО(*) + ЧислаСНомером.Доп КАК ПорядковыйНомер
ИЗ
	ЧислаСНомером КАК ЧислаСНомером
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЧислаСНомером КАК ЧислаСНомером1
		ПО ЧислаСНомером.Число >= ЧислаСНомером1.Число
			И (ВЫБОР
				КОГДА ЧислаСНомером.Число = ЧислаСНомером1.Число
					ТОГДА ЧислаСНомером.НомерЧисла >= ЧислаСНомером1.НомерЧисла
				ИНАЧЕ ЧислаСНомером1.НомерЧисла = 0
			КОНЕЦ)

СГРУППИРОВАТЬ ПО
	ЧислаСНомером.Число,
	ЧислаСНомером.НомерЧисла,
	ЧислаСНомером.Доп
...Показать Скрыть
2. Дюша Дюшистый (SpaceOfMyHead) 28.07.16 10:09
(1) NeviD, Согласен. Если мы ищем медиану числового ряда, написанного на бумаге или отрисованного на мониторе, то каждый элемент уникален хотя бы своим физическим расположением на носителе (бумага, монитор), даже если содержит одинаковые значения. Поэтому не прокатит
просто передавать в запрос список чисел.
Сильно важно это понимать.

Вариант с ключом применим в принципе всегда. Просто иногда придётся содержать служебные таблицы (регистр сведений, например) с полями: "Объект", "Ключ". Тип объекта составной, тип ключа - число. ok.
3. bulpi bulpi (bulpi) 28.07.16 11:18
Вместо ЦеныНоменклатуры.Регистратор.Дата лучше использовать ЦеныНоменклатуры.Период, быстрее будет.
4. Дюша Дюшистый (SpaceOfMyHead) 28.07.16 11:53
(3) bulpi, верно, упустил! Благодарю, поправил
5. Serg (serg_infostart) 28.07.16 14:32
Да, интересный ход! Плюсую.
6. Сергей (ildarovich) 02.08.16 15:51
Идея составления ключа интересная, работающая. Тот же прием (сложение числа из периода со значением) используется, например, при решении задачи 4 в статье Минимализмы.

Но в этой задаче, кажется, легче было разделить записи при равенстве цены дополнительной проверкой, то есть записать условие в виде
ПО (втЦеныНоменклатуры.Цена > втЦеныНоменклатуры1.Цена ИЛИ втЦеныНоменклатуры.Цена = втЦеныНоменклатуры1.Цена И втЦеныНоменклатуры.Период >= втЦеныНоменклатуры1.Период)
               И втЦеныНоменклатуры.Номенклатура = втЦеныНоменклатуры1.Номенклатура


Вообще, если цен много, то запрос получится (из-за использования знака неравества) трудоемким. Простого решения этой проблемы в общем случае нет, но вот если бы номенклатура была единственной, то медиану цены можно было бы найти таким приемом: записать таблицу N/2 пустых записей ПЕРЕД таблицей цен, а затем выбрать ПОСЛЕДНЮЮ 1 ИЗ ПЕРВЫХ N записей.

Для иллюстрации приведу текст запроса, с которым можно поэкспериментировать в консоли. В зависимости от переданного в запрос номера он выбирает соответствующую запись из НЕПРОНУМЕРОВАННОЙ таблицы Дано:
ВЫБРАТЬ
	0 КАК Х
ПОМЕСТИТЬ Бит

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	1
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	NULL КАК Х
ПОМЕСТИТЬ Сдвиг
ИЗ
	Бит КАК Б0,
	Бит КАК Б1,
	Бит КАК Б2,
	Бит КАК Б3,
	Бит КАК Б4,
	Бит КАК Б5,
	Бит КАК Б6,
	Бит КАК Б7
ГДЕ
	Б0.Х + 2 * (Б1.Х + 2 * (Б2.Х + 2 * (Б3.Х + 2 * (Б4.Х + 2 * (Б5.Х + 2 * (Б6.Х + 2 * Б7.Х)))))) < 256 - &Номер
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	1 КАК Х
ПОМЕСТИТЬ Дано

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	2

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	3

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	4

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	5

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	6

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	7

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	8

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	9

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	10
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ ПЕРВЫЕ 2
	ВЗ.Х
ИЗ
	(ВЫБРАТЬ ПЕРВЫЕ 256
		ВЗ.Х
	ИЗ
		(ВЫБРАТЬ
			Сдвиг.Х
		ИЗ
			Сдвиг КАК Сдвиг
		
		ОБЪЕДИНИТЬ ВСЕ
		
		ВЫБРАТЬ
			Дано.Х
		ИЗ
			Дано КАК Дано) КАК ВЗ
	
	УПОРЯДОЧИТЬ ПО
		Х) КАК ВЗ

УПОРЯДОЧИТЬ ПО
	Х УБЫВ
...Показать Скрыть
7. Michael Cher (mmch) 03.08.16 10:25
В свое время рассчитал медиану средствами СКД..., может кому этот путь покажется проще..

ВЫБОР 
    КОГДА ВычислитьВыражение("Количество(Медиана)",,, "Первая", "Последняя") % 2 = 0 ТОГДА
         ВычислитьВыражение("Среднее(Медиана)",,, "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая", "Последняя")-1) / 2, "ЧДЦ=0; ЧГ=0")+")", "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая",  "Последняя")+1) / 2, "ЧДЦ=0; ЧГ=0")+")")
    ИНАЧЕ
     ВычислитьВыражение("Среднее(Медиана)",,, "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая", "Последняя")+1) / 2, "ЧДЦ=0; ЧГ=0")+")", "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая",    "Последняя")+1) / 2, "ЧДЦ=0; ЧГ=0")+")") 
КОНЕЦ
...Показать Скрыть
8. Michael Cher (mmch) 03.08.16 10:29
но тут есть ограничение... обязательная сортировка по параметру для которого считается медиана
10. Олег Родионов (Ovrfox) 03.08.16 10:55
(6) Проще Выбрать максимум из n/2 первых, отсортированных по возрастанию, ничего не добавляя в исходные данные
Но Вот Вопрос - как выбрать первые n/2 записей, если это значение не известно?
В чистом SQL для этого можно было бы воспользоваться процедурой sp_executesql
11. Сергей (ildarovich) 03.08.16 14:58
(10) Ovrfox, понятно, что первые n/2 решают эту задачу. Динамическое построение запроса в 1С тоже поможет.
Я предложил добавить искусственную таблицу перед основной, чтобы запрос не переформировывать, чтобы можно было твердо написать ПЕРВЫЕ 256, например.
Записал сюда как идею, чтобы ее не забыть. Для меня это пример приема использования искусственных таблиц в запросах.
Задача с медианой не очень жизненная. Другой задачи на этот прием пока в голову не приходит. Может, задача и найдется со временем. А прием уже вот, готовый.
Если бы задача с медианой действительно была актуальной, ее можно было бы быстро решить (в запросе) последовательным делением пополам, но это - из пушки по воробьям.
12. Олег Родионов (Ovrfox) 03.08.16 16:37
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа