Расчет средних по периодам в запросе - это элементарно!

Опубликовал Сергей (ildarovich) в раздел Программирование - Практика программирования

Распространено мнение, что расчет средних по периодам в запросе - это сложно. В статье приводится  прием, позволяющий опровергнуть это заблуждение. Приводится описание метода, его обоснование и ограничения использования. Как примеры прилагаются  два практичных отчета на СКД: для расчета среднедневных остатков товаров и для расчета среднедневной задолжности контрагентов. Отчеты созданы на платформе 8.1 для конфигурации УТ10.3 .

 

Легче всего объяснить предлагаемый прием на таком простом примере и конкретных числах: Пусть требуется посчитать среднее число рабов на плантации сотрудников некоторой компании за год. На начало года имеем  10 сотрудников. Предположив, что состав будет стабильным, оценим годовой бюджет рабочего времени компании в 3650 (10х365) человеко-дней. Если 13 мая в пятницу один из сотрудников уволится, то бюджет уменьшится на 232 дня (это число оставшихся дней года). А когда 17 июня на работу выйдет новенькая сотрудница, бюджет увеличится на 197 дней. Приём на работу 1-го сентября одного сотрудника увеличит бюджет на 121 дней, а увольнение 3-х сотрудников 30 сентября - уменьшит его на 376 (3х92) человеко-дней. В итоге получим бюджет 3650 - 232 + 197 + 121 - 376 = 3360. Разделив его на 365, получим в среднем примерно 9,2 работающих сотрудника. Заметьте, что при этом нам не пришлось учитывать ни интервалы работы сотрудников, ни интервалы между событиями!

То же самое можно описать формулой:

Формула расчета

И привести как пример реализации в запросе:

ВЫБРАТЬ
    
Номенклатура,
    
СУММА(ВЫБОР КОГДА Период &НачалоПериода ТОГДА КоличествоКонечныйОстаток ИНАЧЕ КоличествоОборот КОНЕЦ * (РАЗНОСТЬДАТ(Период&КонецПериодаДЕНЬ) + 1))
    / (
РАЗНОСТЬДАТ(&НачалоПериода&КонецПериодаДЕНЬ) + 1) КАК СреднийЗапас
ИЗ
    
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода&КонецПериодаДень, , )
СГРУППИРОВАТЬ ПО
    
Номенклатура

Основная "хитрость" предлагаемого метода состоит в замене суммирования остатков в формуле расчета среднего суммированием оборотов. В результате этого не требуется рассматривать периодов, в которых оборотов не было.

Ограничивает применение метода два обстоятельства:

1) Случай использования периодических регистров сведений, где в записи регистра не указана величина изменения, а только абсолютная величина. Это требует использования медленных тэта-соединений для определения предыдущего состояния и дельты, что сводит на нет преимущества метода.

2) Случай, когда расчет среднего производится "по рабочим дням" или похожими способами. При этом не просто (но возможно!) определить число оставшихся периодов.

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

 

Скачать файлы

Наименование Файл Версия Размер
СредниеОстатки.erf
.erf 6,08Kb
29.09.11
99
.erf 6,08Kb 99
СреднийДолг.erf
.erf 6,07Kb
29.09.11
39
.erf 6,07Kb 39

См. также

Комментарии
1. Александр Рытов (Арчибальд) 2652 30.09.11 10:27 Сейчас в теме
Ну, что тут сказать... Остальное вы видели. ©
2. Ийон Тихий (cool.vlad4) 41 30.09.11 10:47 Сейчас в теме
(0) Безусловно молодец, но только можешь объяснить выкладку из рисунка, че-то не соображу с утра никак почему
Прикрепленные файлы:
3. Сергей (ildarovich) 4845 30.09.11 11:38 Сейчас в теме
(2) Вот выкладка для числителя
s1 + s2 + s3 + ... + sn = (s0+d1) + (s1+d2) + (s2+d3) + ... + (sn-1 + dn) = 
= (s0 + d1) + (s0 + d1 + d2) + (s0 + d1 + d2 + d3) + ... + (s0 + d1 + d2 + d3 + ... + dn) = 
= s0 * n + d1 * n + d2 * (n-1) + d3 * (n - 2) + dn * 1
Из нее, кстати, следует, что в формуле (не в запросе) была неточность - забыл умножение s0 на n. Сейчас поправлю в статье.
Прикрепленные файлы:
4. Ийон Тихий (cool.vlad4) 41 30.09.11 11:57 Сейчас в теме
(3) Ну, вот блин, а я время потратил, бумагу перевел, думаю почему у меня не сходится;-)
5. Сергей (ildarovich) 4845 30.09.11 12:08 Сейчас в теме
(4) Приношу извинения - торопился!
6. Ийон Тихий (cool.vlad4) 41 30.09.11 12:09 Сейчас в теме
(5) да, не какие извинения, - молодец, очень ценная статья, а я плюс даже забыл поставить...исправился...
7. Алексей Константинов (alexk-is) 6086 30.09.11 16:34 Сейчас в теме
(0) Про быстрые тэта-соединения можно посмотреть здесь http://infostart.ru/public/71130/#Pro_hitrye_zaprosy
Пример 3

Расчет периода по рабочим дням легко производится по производственному календарю. Расчет периода по банковским дням можно посмотреть здесь http://infostart.ru/public/68269/
JohnyDeath; ildarovich; +2 Ответить
8. 22 2233 (losara1983) 6 30.09.11 17:33 Сейчас в теме
Я как раз недавно возился с этой задачей, жаль не нашел вашего метода)
9. Владимир (hogik) 415 30.09.11 19:41 Сейчас в теме
(0)
Сергей.
Пора открывать цикл публикаций "Мы пишем з..." с единым логотипом. ;-)
Восторгаюсь Вашей головой...
10. Александр Маляев (maljaev) 767 03.10.11 12:32 Сейчас в теме
Формула шокировала :)
Надо будет все свои ТЗ к проектам снабжать аналогичными формулами вместо описания на "общечеловеческом" - для придания пущей важности проекту и уважения заказчика :)
igormiro; Светлый ум; kuzyara; Winstoncuk; +4 Ответить
11. bulpi bulpi (bulpi) 106 05.10.11 21:15 Сейчас в теме
Объясните тупому (возможно)
Чем плохо посчитать остатки на каждый день, а потом сумму разделить на количество дней?
12. bulpi bulpi (bulpi) 106 05.10.11 21:17 Сейчас в теме
За красоту идеи в любом случае + без разговоров, но все таки ?
13. Сергей (ildarovich) 4845 05.10.11 22:14 Сейчас в теме
(11) Дело в том, что посчитать остатки на каждый день в запросе просто не получится - остатки будут выдаваться только на периоды, по которым есть обороты. В запросе с итогами есть возможность "дополнить периодами", а в пакетном запросе - нет. В запросе люди выкручиваются как могут: гораздо более сложными способами. Примеры можно посмотреть:
v8: Полные остатки по периоду во вложенном запросе
v8: Полные остатки по периоду во вложенном запросе по начальному остатку
Отчет «Среднесписочная численность» для ЗУП 2.5.25
14. bulpi bulpi (bulpi) 106 06.10.11 00:33 Сейчас в теме
(13)
Спасибо. Про запрос без итогов я не подумал.
15. Алексей Константинов (alexk-is) 6086 15.10.11 01:53 Сейчас в теме
Чем раскрашен запрос в публикации? Спрашиваю потому, что вижу это была не Разукрашка.
16. Сергей (ildarovich) 4845 15.10.11 10:20 Сейчас в теме
(15) Не знаю, почему Вы так решили, но я пользовался именно ей.
Как и для раскрашивания запросов в Порождающий запрос. Буду обновлять другие публикации - постараюсь сделать также. Если так удобнее читателям.
Прикрепленные файлы:
17. Алексей Константинов (alexk-is) 6086 15.10.11 11:39 Сейчас в теме
(16) Как почему? Конечно же по HTML-коду. Структура HTML-кода Разукрашки очень специфична, т.к. содержит большое количество различных способов оптимизации. Её очень легко узнать на тексте достаточно большого объема именно по наличию оптимизации.

Другое дело, если этот HTML-код прошел нескольно уровней преобразования разными редакторами. Тогда от оптимизации уже ничего не останется. Обязательно добавится много мусора.

Как минимум один уровень преобразования получается при вставке HTML-кода в публикацию на Infostart. Пусть незначительно, но код становится больше.
18. Дмитрий (sommid) 12.01.12 16:32 Сейчас в теме
19. Антон Харчевин (Antony_2009) 29.03.12 14:26 Сейчас в теме
Спасибо за публикацию! Скажите, пожалуйста, а возможно данный метод применить для платформы 1С 7.7?
20. Сергей (ildarovich) 4845 13.04.12 17:55 Сейчас в теме
(19) Думаю, да. Постараюсь в ближайшие дни дополнить статью данным примером.
Antony_2009; +1 Ответить 1
21. Сергей (ildarovich) 4845 22.04.12 12:34 Сейчас в теме
(19)(20) После более тщательного изучения данного вопроса
а возможно данный метод применить для платформы 1С 7.7?

выяснилось следующее:
1. В 7.7 нет возможности обработки данных в самом запросе, чтобы на выходе запроса сразу получить хронологическое среднее. Идеология 7.7 такова, что запрос является в основном средством получения данных, без их обработки, которая делается в коде.
2. При задании периода "День" (например), результат запроса будет содержать столько строк, сколько дней в периоде и покажет остатки на каждый день. То есть проблемы запросов восьмерки "получения остатков на дни периода, в которых не было движений", в 7.7 нет. Среднее получается элементарной обработкой полученной в запросе таблицы значений: делением итога колонки на число строк.
3. В то же время, есть ситуация, в которых имеет смысл применить прием, описанный в статье:
Если в анализируемом периоде движений заданной номенклатуры существенно меньше числа подпериодов.
Только в этом случае выгоднее использовать группировку "Документ" и метод, рассмотренный в статье.
Так, что с примером для 77 сейчас торопиться не буду.
22. Антон Харчевин (Antony_2009) 22.04.12 23:02 Сейчас в теме
Спасибо большое за ответ! Вы правы по пункту 3. Например при расчете средней дебеторской задолженности (не каждый день происходят движения), а на получение остатков на каждый день ооочень много времени уходит в запросе. Чтож, буду пробовать по документам.
23. Модератор раздела Артур Аюханов (artbear) 16.06.12 10:04 Сейчас в теме
У меня периодически возникают подобные задачи.
например, http://www.forum.mista.ru/topic.php?id=615738
Есть задача:
есть документ накладная, есть торговый представитель, через которого выполнена заявка и выписана эта накладная,
нужно через СКД получить итоговую таблицу за месяц или несколько месяцев, в которой
строки - ТП (Торговые представители)
колонки - Дни (от 1 до 30/31 - последний день месяца)
ресурс - количество Накладных в день
В итогах за месяц нужно получить среднее число накладных за день по каждому ТП.
Проблема именно с этим последним итогом, не могу придумать, как его подсчитать в СКД.
какие есть предложения?
==
я же количество документов за день также в СКД считаю.
Мне нужно посчитать среднее число по уже вычисленному полю через агрегатную функцию
т.е. что-то типа Среднее(Количество(Различные Регистратор))
СКД не дает сделать этого в чистом виде :(

хочется универсального решения для СКД
периодически возникают подобные задачи, когда нужно посчитать два раза агрегатные функции - типа Среднее(Количество(...)) или Сумма(Количество(...))
==
Что можете подсказать?
24. Модератор раздела Артур Аюханов (artbear) 16.06.12 10:08 Сейчас в теме
+(23) За день количество документов получить не проблема, проблема в СКД получить среднее число документов в день в итоге за месяц
например, пусть итоговый период состоит из 2-х дней
и
1 число - 2 документа
2 число - 4 документа
в итоге за период должно быть 3 документа.
как это сделать средствами СКД ?
25. Andrey Smirnov (dusha0020) 631 06.06.13 00:38 Сейчас в теме
Да уж. Действительно элементарно, Ватсон! Я бился над проблемой 3 часа, пока не нагуглил статью. Спасибо!
26. vlad kan (truba) 07.06.13 17:24 Сейчас в теме
Спешу заметить про 3е ограничение метода. Если в данном примере надобно свернуть номенклатуру по объединяющему реквизиту. К примеру номенклатура1 и номенклатура2 по сути для анализирующего манагера ничем не отличаются, а метод даст движения (и средние) в разрезе каждой номенклатуры. В то время как среднее суммы не равно сумме среднего.
Путано написал, понимаю, но если вы с подобной задачей сталкивались то наверняка меня поняли.
27. dpagon (dpagon) 6 25.09.13 01:40 Сейчас в теме
Спасибо за публикацию! Мне была поставлена аналогичная задача. Применил Ваш метод. Вроде бы есть одна неточность- приведу на примере:

День 1 2 3 Расчет средней
Остатки
1 договор 1000 1000 1000 1000=1000*3(колво дней с остатками)/3(колво дней за период)
2 договор 500 500 0 !!!500=500*3(колво дней с остатками-верно 2 дня)/3(колво дней за период)
3 договор 0 300 300 200=300*2(колво дней с остатками)/3(колво дней за период)

Думаю это из-за того, что и КоличествоКонечныйОстаток, и КоличествоОборот умножаем на РАЗНОСТЬДАТ(Период, &КонецПериода, ДЕНЬ) + 1)

Попробывал
СУММА(ВЫБОР КОГДА Период = &НачалоПериода
ТОГДА КоличествоКонечныйОстаток*(РАЗНОСТЬДАТ(&НачалоПериода, Период, ДЕНЬ) + 1)
ИНАЧЕ КоличествоОборот * (РАЗНОСТЬДАТ(Период, &КонецПериода, ДЕНЬ) + 1))
конец / (РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ) + 1)


Но так вообще не то...
28. Вячеслав Лонгинов (Longinoff) 47 29.09.13 19:40 Сейчас в теме
29. Сергей (ildarovich) 4845 29.03.14 20:38 Сейчас в теме
Для случая, когда используется метод трапеции, то есть формула расчета среднего учитывает крайние значения с коэффициентом 1/2, данный метод следует слегка изменить. Как это сделать, описано в комментарии http://forum.infostart.ru/forum26/topic45819/message495419/#message495419.
30. Дмитрий Цыбульский (zmit) 15.06.16 10:11 Сейчас в теме
ildarovich умница. Сколько огромных запросов-расчетов я видел, а это в 3 строки