0. SpaceOfMyHead 11 26.07.16 21:22 Сейчас в теме

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

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

Перейти к публикации

Комментарии
Избранное Подписка Сортировка: Древо
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 11 28.07.16 10:09 Сейчас в теме
(1) NeviD, Согласен. Если мы ищем медиану числового ряда, написанного на бумаге или отрисованного на мониторе, то каждый элемент уникален хотя бы своим физическим расположением на носителе (бумага, монитор), даже если содержит одинаковые значения. Поэтому не прокатит
просто передавать в запрос список чисел.
Сильно важно это понимать.

Вариант с ключом применим в принципе всегда. Просто иногда придётся содержать служебные таблицы (регистр сведений, например) с полями: "Объект", "Ключ". Тип объекта составной, тип ключа - число. ok.
3. bulpi 156 28.07.16 11:18 Сейчас в теме
Вместо ЦеныНоменклатуры.Регистратор.Дата лучше использовать ЦеныНоменклатуры.Период, быстрее будет.
4. SpaceOfMyHead 11 28.07.16 11:53 Сейчас в теме
(3) bulpi, верно, упустил! Благодарю, поправил
5. serg_infostart 311 28.07.16 14:32 Сейчас в теме
Да, интересный ход! Плюсую.
6. ildarovich 6714 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
		ВЗ.Х
	ИЗ
		(ВЫБРАТЬ
			Сдвиг.Х
		ИЗ
			Сдвиг КАК Сдвиг
		
		ОБЪЕДИНИТЬ ВСЕ
		
		ВЫБРАТЬ
			Дано.Х
		ИЗ
			Дано КАК Дано) КАК ВЗ
	
	УПОРЯДОЧИТЬ ПО
		Х) КАК ВЗ

УПОРЯДОЧИТЬ ПО
	Х УБЫВ
Показать
10. Ovrfox 14 03.08.16 10:55 Сейчас в теме
(6) Проще Выбрать максимум из n/2 первых, отсортированных по возрастанию, ничего не добавляя в исходные данные
Но Вот Вопрос - как выбрать первые n/2 записей, если это значение не известно?
В чистом SQL для этого можно было бы воспользоваться процедурой sp_executesql
11. ildarovich 6714 03.08.16 14:58 Сейчас в теме
(10) Ovrfox, понятно, что первые n/2 решают эту задачу. Динамическое построение запроса в 1С тоже поможет.
Я предложил добавить искусственную таблицу перед основной, чтобы запрос не переформировывать, чтобы можно было твердо написать ПЕРВЫЕ 256, например.
Записал сюда как идею, чтобы ее не забыть. Для меня это пример приема использования искусственных таблиц в запросах.
Задача с медианой не очень жизненная. Другой задачи на этот прием пока в голову не приходит. Может, задача и найдется со временем. А прием уже вот, готовый.
Если бы задача с медианой действительно была актуальной, ее можно было бы быстро решить (в запросе) последовательным делением пополам, но это - из пушки по воробьям.
12. Ovrfox 14 03.08.16 16:37 Сейчас в теме
7. mmch 122 03.08.16 10:25 Сейчас в теме
В свое время рассчитал медиану средствами СКД..., может кому этот путь покажется проще..

ВЫБОР 
    КОГДА ВычислитьВыражение("Количество(Медиана)",,, "Первая", "Последняя") % 2 = 0 ТОГДА
         ВычислитьВыражение("Среднее(Медиана)",,, "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая", "Последняя")-1) / 2, "ЧДЦ=0; ЧГ=0")+")", "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая",  "Последняя")+1) / 2, "ЧДЦ=0; ЧГ=0")+")")
    ИНАЧЕ
     ВычислитьВыражение("Среднее(Медиана)",,, "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая", "Последняя")+1) / 2, "ЧДЦ=0; ЧГ=0")+")", "Первая("+Формат((ВычислитьВыражение("Количество(Медиана)",,, "Первая",    "Последняя")+1) / 2, "ЧДЦ=0; ЧГ=0")+")") 
КОНЕЦ
8. mmch 122 03.08.16 10:29 Сейчас в теме
но тут есть ограничение... обязательная сортировка по параметру для которого считается медиана
9. mmch 122 03.08.16 10:42 Сейчас в теме
13. legionne 28.09.18 09:59 Сейчас в теме
Тема старая, но может кто-то ответит. А как посчитать 25,75 и любые другие перцентили по массиву непоименованных значений?
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Ведущий программист 1С
Санкт-Петербург
зарплата от 130 000 руб.
Полный день

Программист 1С
Москва
зарплата от 130 000 руб. до 200 000 руб.
Полный день

Бизнес-архитектор 1С, ведущий консультант
Санкт-Петербург
Полный день

Руководитель проектов 1С
Санкт-Петербург
Полный день

Программист 1C
Москва
зарплата от 100 000 руб. до 150 000 руб.
Полный день