gifts2017

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

Опубликовал Сергей (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
98
.erf 6,08Kb 98 Скачать
СреднийДолг.erf
.erf 6,07Kb
29.09.11
38
.erf 6,07Kb 38 Скачать

См. также

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

Комментарии

1. Александр Рытов (Арчибальд) 30.09.11 10:27
Ну, что тут сказать... Остальное вы видели. ©
2. Ийон Тихий (cool.vlad4) 30.09.11 10:47
(0) Безусловно молодец, но только можешь объяснить выкладку из рисунка, че-то не соображу с утра никак почему
Прикрепленные файлы:
3. Сергей (ildarovich) 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) 30.09.11 11:57
(3) Ну, вот блин, а я время потратил, бумагу перевел, думаю почему у меня не сходится;-)
5. Сергей (ildarovich) 30.09.11 12:08
(4) Приношу извинения - торопился!
6. Ийон Тихий (cool.vlad4) 30.09.11 12:09
(5) да, не какие извинения, - молодец, очень ценная статья, а я плюс даже забыл поставить...исправился...
7. Алексей Константинов (alexk-is) 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) 30.09.11 17:33
Я как раз недавно возился с этой задачей, жаль не нашел вашего метода)
9. Владимир (hogik) 30.09.11 19:41
(0)
Сергей.
Пора открывать цикл публикаций "Мы пишем з..." с единым логотипом. ;-)
Восторгаюсь Вашей головой...
10. Александр Маляев (maljaev) 03.10.11 12:32
Формула шокировала :)
Надо будет все свои ТЗ к проектам снабжать аналогичными формулами вместо описания на "общечеловеческом" - для придания пущей важности проекту и уважения заказчика :)
igormiro; Светлый ум; kuzyara; Winstoncuk; +4 Ответить
11. bulpi bulpi (bulpi) 05.10.11 21:15
Объясните тупому (возможно)
Чем плохо посчитать остатки на каждый день, а потом сумму разделить на количество дней?
12. bulpi bulpi (bulpi) 05.10.11 21:17
За красоту идеи в любом случае + без разговоров, но все таки ?
13. Сергей (ildarovich) 05.10.11 22:14
(11) Дело в том, что посчитать остатки на каждый день в запросе просто не получится - остатки будут выдаваться только на периоды, по которым есть обороты. В запросе с итогами есть возможность "дополнить периодами", а в пакетном запросе - нет. В запросе люди выкручиваются как могут: гораздо более сложными способами. Примеры можно посмотреть:
v8: Полные остатки по периоду во вложенном запросе
v8: Полные остатки по периоду во вложенном запросе по начальному остатку
Отчет «Среднесписочная численность» для ЗУП 2.5.25
14. bulpi bulpi (bulpi) 06.10.11 00:33
(13)
Спасибо. Про запрос без итогов я не подумал.
15. Алексей Константинов (alexk-is) 15.10.11 01:53
Чем раскрашен запрос в публикации? Спрашиваю потому, что вижу это была не Разукрашка.
16. Сергей (ildarovich) 15.10.11 10:20
(15) Не знаю, почему Вы так решили, но я пользовался именно ей.
Как и для раскрашивания запросов в Порождающий запрос. Буду обновлять другие публикации - постараюсь сделать также. Если так удобнее читателям.
Прикрепленные файлы:
17. Алексей Константинов (alexk-is) 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) 13.04.12 17:55
(19) Думаю, да. Постараюсь в ближайшие дни дополнить статью данным примером.
Antony_2009; +1 Ответить 1
21. Сергей (ildarovich) 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) 06.06.13 00:38
Да уж. Действительно элементарно, Ватсон! Я бился над проблемой 3 часа, пока не нагуглил статью. Спасибо!
26. vlad kan (truba) 07.06.13 17:24
Спешу заметить про 3е ограничение метода. Если в данном примере надобно свернуть номенклатуру по объединяющему реквизиту. К примеру номенклатура1 и номенклатура2 по сути для анализирующего манагера ничем не отличаются, а метод даст движения (и средние) в разрезе каждой номенклатуры. В то время как среднее суммы не равно сумме среднего.
Путано написал, понимаю, но если вы с подобной задачей сталкивались то наверняка меня поняли.
27. dpagon (dpagon) 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) 29.09.13 19:40
29. Сергей (ildarovich) 29.03.14 20:38
Для случая, когда используется метод трапеции, то есть формула расчета среднего учитывает крайние значения с коэффициентом 1/2, данный метод следует слегка изменить. Как это сделать, описано в комментарии http://forum.infostart.ru/forum26/topic45819/message495419/#message495419.
30. Дмитрий Цыбульский (zmit) 15.06.16 10:11
ildarovich умница. Сколько огромных запросов-расчетов я видел, а это в 3 строки
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа