Добрый день.

Есть таблица с данными
Дата - просто день месяца
Мощность - мощность станка т.е. сколько данный станок может произвести продукции в штуках за этот день
Требуется - соответственно сколько штук надо произвести

Дата Мощность Требуется
1     100        0
2     100        150
3     100        0
4     100        50
5     100        0
6     100        175
7     50         10
8     100        0
9     100        0

Необходимо получить следующую таблицу

Дата Мощность Требуется Перенесено  
1     100        0         0
2     100        150       0
3     100        0         50
4     100        50        0
5     100        0         0
6     100        175       0
7     50         10        75
8     100        0         35
9     100        0         0

Колонка [Перенесено] считается так. Суть.
- Например 2 числа надо было сделать 150 штук а мощность станка не позволяет это сделать , значит 3 числа перенесено будет будет 50
- Например 6 числа надо сделать 175 штук, а мощность станка 100, значит перенесено будет 7 число 75
- Далее 7 числа мощность станка упала и стала 50 и сделать надо 10 штук и плюс 75 с предыдущего числа , можно сделать только 40 , значит на 8ое число перенесется 75 - (50-10) = 35 штук
- 8 числа мощность станка 100, перенесено 35, значит на 9 число ничего не надо переносить.

Вопрос, можно ли это сделать запросом ?
Помогите запросом если это возможно, или подскажите направление.
что то не могу придумать как это сделать. Чем то похоже на распределение по фифо, но это не совсем то.
Спасибо.

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

а в ТЧ все элементарно. при добавлении строк.

Я бы циклом сделал... и в цикле бы раскидал по строках лишние данные, где недостает

(2) ну я так и написал выше. делаешь обработку, туда тч. заполнил как надо и циклом раскидал.
Как сделать в виде отчета отчетом-запросом.. я бы не мудрил

(0) Соединить левым соединением эту таблицу (Т) с этой же таблицей (Т2) по Т.Дата > Т2.Дата , сгруппировать по Т.Дата, посчитав СУММА(Т2.Мощность-Т2.Требуется) КАК Перенесено, потом через ВЫБОР убрать отрицательные значения Перенесено

(4) мощность будет накапливаться, если ничего не требуется это будет нарастающий итог, а мне не надо так

(0) Почему именно запросом?
Выгрузить в ТЗ, обработай с конца.

(6) ну это непринципиально, можно и через тз. Наверно не получится, так и сделаю.

(0) Запросом тоже можно. Но на 1С без оконных функций будет весело.

(6) Только не с конца, а с начала, т.к. там накопительный итог будет, а если с конца смотреть, то сколько накопилось ранее - неизвестно.

(9) с начала , это я понимаю. Не удобно конечно ( сейчас придётся выгружать в тз потом пересчёт, потом опять загружать , в набор данных

А вообще чисто теоретически это можно сделать, пусть даже сложно. Или нельзя запросом  или неизвестно?)

(11) точно можно. Но скорее всего действительно не стоит)

Посчитать перенесено как разность требуется и мощности. Получить в виде отдельной таблицы Дата/Перенесено. Потом ко всем датам в полученной табличке сделать +1 день. Присоединить к исходной таблице по дате.

СКД так может
а (4) пробовали? должно быть ок

(14) если Требуется будет = 0 то Перенесено  будет зависеть от мощности и будет нарастающий итог

в (4) не выйдет, будет нарастающий итоге. запросом можно но в 4 раза геморойнее и надо шаманить.
проще циклом перебрать и вы вести

(0) Можно, но не нужно. Это как раз один из надежных признаков профессионализма. Понимание - где нужно использовать запросы, а где не нужно.

(17) Да ему уже многие так и ответили ;) просто человек видимо хочет знать как сделать такой запрос.. Но тут затраченное время и мозги не соответсвуют результату. Смысла тупо нету, когда по другому в 5 раз легче и быстрее. еще уверен что и отработает быстрее чем запросом, хотя обычному пользователю будет глазом не заметно скорее всего.

(11) Можно сделать. Несложно. Соединяешь таблицу с самой собой по условию дата из второй таблицы меньше даты из первой. Группируешь по дате из первой таблицы, суммируешь. Вот, собственно, и все

(19) Получил ты нарастающие итоги, это действительно не проблема. Дальше что?

Проблема посчитать недозагруз производства: то что нужно выпустить сегодня выпустить вчера ты уже не сможешь. Поэтому одних нарастающих итогов мало.

(18) Да, интерес чисто академический. Задачка поинтереснее ФИФО в запросе

если что я уже переделал на перебор тз ) всем спасибо

(0) Никто так и не написал с помощью запроса.

Не на 1С это можно сделать например как ниже для SQLite.

Можно и рекурсивным запросом, но у него обычно есть ограничение - не более 100 вложений. Если строк много, и есть вероятность уйти в минус более чем 100 строк подряд, то будет ошибка. И рекурсивный запрос на 1С я не знаю как переписать :-)
Написал через несколько вложенных запросов и помещений во временные таблицы, для понимания логики. Можно и короче записать. Можно вместо CASE использовать IIF и т.д.

Если переписывать на 1С, то надо все используемые оконные функции заменить несколькими JOIN.

Входные данные заменил, так как не хватать может и в двух строках подряд. И закрыться может не одной следующей строкой, а несколькими.
И может быть вариант что не закрыться избыточными, а переходит на следующие недостающие.

Немного пояснений:
WITH - в 1С аналог Поместить, в примере создаются несколько таких таблиц.
LAG() - предыдущая запись в 1С левое соединение таблицы сама по условию
sum(t.d) OVER (PARTITION by t.gr ORDER by t.npp) - сумма нарастающим итогом по группировке поля t.gr и в порядке поля t.npp
IfNULL - аналог в 1С ЕстьNULL
CASE - аналог в 1С ВЫБОР КОГДА... ТОГДА... ИНАЧЕ... КОНЕЦ
------------

WITH vt_data as (
select 1 as npp, 100 as mo, 30 as tr
UNION ALL  select 2, 100, 20
UNION ALL  select 3, 100, 150
UNION ALL  select 4, 100, 120
UNION ALL  select 5, 100, 110
UNION ALL  select 6, 50, 20
UNION ALL  select 7, 100, 60
UNION ALL  select 8, 100, 150
UNION ALL  select 9, 100, 10
UNION ALL  select 10, 100, 130
UNION ALL  select 11, 100, 50
UNION ALL  select 12, 100, 80
)
, vt_delta as (
SELECT
     t.*
    ,t.mo-t.tr as d
    ,CASE
        WHEN LAG(t.mo-t.tr) OVER(ORDER BY t.npp) > 0
                and (t.mo-t.tr) < 0 THEN
            1
        ELSE
            0
     END as fl
FROM
    vt_data as t
)
, vt_data2 as (
SELECT
     t.*
    ,CASE
        WHEN LAG(t.s) OVER(ORDER BY t.npp) > 0
                and (t.s) < 0 THEN
            1
        ELSE
            0
     END as fl2
FROM
    (SELECT
         t.*
         ,sum(t.d) OVER (PARTITION by t.gr ORDER by t.npp) as s
    FROM
        (SELECT
             t.*
             ,(    SELECT
                    ifnull(max(t1.npp), 1)
                FROM
                    vt_delta as t1
                WHERE
                        t1.fl = 1
                    and t1.npp <= t.npp
              ) as gr
                    
        FROM
            vt_delta as t
    ) as t
) as t
)

SELECT
     /*
     t.*
     */
     t.npp
    ,t.mo
    ,t.tr
    
    ,IfNULL(LAG(t.ss) OVER(ORDER BY t.npp), 0) as per
FROM
    (SELECT
         t.*
        ,CASE
            WHEN t.s2 > 0 THEN
                0
            ELSE
                -t.s2
         END as ss
    FROM
        (SELECT
             t.*
            ,sum(t.d) OVER (PARTITION by t.gr2 ORDER by t.npp) as s2
        FROM
            (SELECT
                  t.*
                 ,(    SELECT
                        ifnull(max(t1.npp), 1)
                    FROM
                        vt_data2 as t1
                    WHERE
                            t1.fl2 = 1
                        and t1.npp <= t.npp
                  ) as gr2
            FROM
                vt_data2 as t
            ) as t
        ) as t
    ) as t

-----------
npp    mo    tr    per
1    100    30    0
2    100    20    0
3    100    150    0
4    100    120    50
5    100    110    70
6    50    20    80
7    100    60    50
8    100    150    10
9    100    10    60
10    100    130    0
11    100    50    30
12    100    80    0

-------
Result: 12 строк возвращено за 33мс

------
Если все поля смотреть, что бы логику понять, то в итоговом запросе раскомментировать "т.*", три входные наоборот закомментировать. Будет так:

------
npp    mo    tr    d    fl    gr    s    fl2    gr2    s2    ss    per
1    100    30    70    0    1    70    0    1    70    0    0
2    100    20    80    0    1    150    0    1    150    0    0
3    100    150    -50    1    3    -50    1    3    -50    50    0
4    100    120    -20    0    3    -70    0    3    -70    70    50
5    100    110    -10    0    3    -80    0    3    -80    80    70
6    50    20    30    0    3    -50    0    3    -50    50    80
7    100    60    40    0    3    -10    0    3    -10    10    50
8    100    150    -50    1    8    -50    0    3    -60    60    10
9    100    10    90    0    8    40    0    3    30    0    60
10    100    130    -30    1    10    -30    1    10    -30    30    0
11    100    50    50    0    10    20    0    10    20    0    30
12    100    80    20    0    10    40    0    10    40    0    0

Последняя таблица сильно поехала...

npp    mo    tr    d    fl    gr     s     fl2     gr2     s2     ss     per
1     100     30     70     0     1     70     0     1     70     0     0
2     100     20     80     0     1     150     0     1     150     0     0
3     100     150     -50     1     3     -50     1     3     -50     50     0
4     100     120     -20     0     3     -70     0     3     -70     70     50
5     100     110     -10     0     3     -80     0     3     -80     80     70
6     50     20     30     0     3     -50     0     3     -50     50     80
7     100     60     40     0     3     -10     0     3     -10     10     50
8     100     150     -50     1     8     -50     0     3     -60     60     10
9     100     10     90     0     8     40     0     3     30     0     60
10     100     130     -30     1     10     -30     1     10     -30     30     0
11     100     50     50     0     10     20     0     10     20     0     30
12     100     80     20     0     10     40     0     10     40     0     0

npp           mo           tr           d              fl           gr            s            fl2          gr2            s2            ss            per
1            100            30            70            0            1            70             0            1             70             0             0
2            100            20            80            0            1            150            0            1            150             0             0
3            100           150           -50            1            3            -50            1            3            -50            50             0
4            100           120           -20            0            3            -70            0            3            -70            70            50
5            100           110           -10            0            3            -80            0            3            -80            80            70
6             50            20            30            0            3            -50            0            3            -50            50            80
7            100            60            40            0            3            -10            0            3            -10            10            50
8            100           150           -50            1            8            -50            0            3            -60            60            10
9            100            10            90            0            8            40             0            3             30             0            60
10           100           130           -30            1           10            -30            1           10            -30            30             0
11           100            50            50            0           10            20             0           10             20             0            30
12           100            80            20            0           10            40             0           10             40             0             0

(0) А нужно ли это делать запросом? В набор данных СКД таблицу значений сунуть и мозг не тревожьте.

(25) Автора в (11) интересовало, а теоретически возможно решить данную задачу с помощью запроса. Выше одно из решений.

К (24) пояснения

npp - входная колонка номер по порядку или как у автора в (0) дата месяца, уникальная и не повторяется.
mo - входная колонка мощность станка.
tr - входная колонка Требуется - соответственно сколько штук надо произвести.
per - Перенесено - выходная колонка, которую необходимо посчитать.

d - дельта между mo и tr, т.е. фактически (mo-tr)
fl - признак очередной первой отрицательной строки дельта "d". Строка считается "первой отрицательной", если у неё дельта "d" отрицательная и предыдущая строка с положительной дельтой "d".
gr - группировочное поле равное npp ближайшей предыдущей строки, у которой fl = 1. Если такой строки нет то значение 1.
s - сумма нарастающим итогом для каждого значения gr в направлении npp
fl2 - признак очередной первой отрицательной строки дельта суммы нарастающим итогом "s". Строка считается "первой отрицательной", если у неё "s" отрицательная и предыдущая строка с положительной суммой нарастающим итогом "s". По сути если мощности следующих за первой отрицательной и всех в ходящих в её группу не хватает закрыть требуемое количество, то недостаток "переедет" в следующую группу. В итоге мы за одну итерацию выявим все такие случаи и объединим в максимально большие отрезки групп.
gr2 - группировочное поле равное npp ближайшей предыдущей строки, у которой fl2 = 1. Если такой строки нет то значение 1.
s2 - сумма нарастающим итогом для каждого значения gr2 в направлении npp
ss - рассчитывается из колонки s2. Если s2>=0, то 0 иначе -s2 (минус s2).
per - та же колонка ss, но смещенная на одну позицию вниз. В первой строке соответственно 0. Если в последней строке в поле ss есть не нулевое значение, то оно исчезает. Его по условию задачи не где показывать. А надо было бы генерировать дополнительную строку со следующим номером npp и mo=0, tr=0. Но этого в условии нет.

(17) "Можно, но не нужно" - не известно нужно или нет. Зависит от объема данных.
В 1С скорее не нужно - столько JOIN лепить придется, мама не горюй. Поэтому производительность на больших объемах, скорее всего будет умирать или умрет. А собственно из-за больших объемов SQL может быть более выгодным в использовании.

В 1С проще как автор уже сделал. Но если не в 1С, то делать проще напрямую с использованием SQL и без JOIN это выполняется очень быстро.

(20) "Да, интерес чисто академический. Задачка поинтереснее ФИФО в запросе".
Стандартная задача, на самом деле.

(28) >> В 1С скорее не нужно - столько JOIN лепить придется, мама не горюй.

В пакете 3 запроса (включая помещение данных в ВТ), всего 2 джойна.

(29) Стандартная задача
А вот тут не согласен. "Стандартная задача" подразумевает, что любой может решить.

(27) Спасибо за пояснение. Но не затруднит ли вас выдать готовый запрос для ТС, на языке запросов 1С?

(31)20$ уже предлагали?

(31) "ТС" - транспортное средство?

(31) Какие сложности вызывает перевод запроса в 1С, кроме как кучи кода на километр?

(30) "всего 2 джойна" - не знаю как всего двумя обойтись.

(30) "Стандартная задача" подразумевает, что любой может решить." - да ладно. 2*3 не каждый решит - есть же дошкольники.
Стандартная задача - это типовая задача, которую или подобную ей приходится решать достаточно часто, а не то что её каждый решит.

Тут при решении задачи нет каких-то сложных алгоритмов. Если задача просто решается на бумаге и нет вопросов как считать, то в 98% на SQL не должно возникать сложностей.

(33) Топик Стартер - автор ветки

(36) ..98% на SQL не должно возникать сложностей
Ну вот тебе и пример алгоритмически простейшей задачи, которая на SQL в твою оценку 98% не попала. Под "каждый" естественно понимался спец, который подобные задачи решает.

(35) а такое решение точно есть)

(38) "а такое решение точно есть" - покажи - не стесняйся.
Если и есть, то там другое решение этой же задачи.

(39) выложу обязательно, но не сегодня уже наверное.
Мелкую укладываю, потом лень комп включать будет. Вроде нормальная отмазка чтобы потянуть интригу)) Может ещё решатели найдутся "стандартной" задачи

(40) А можно всех посмотреть? :-)

ВЫБРАТЬ
    ТЗ.Дата,
    СУММА(ТЗ1.Требуется - ТЗ1.Мощность) КАК ОстатокНИ
ПОМЕСТИТЬ ВТ
ИЗ
    ТЗ КАК ТЗ
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТЗ КАК ТЗ1
        ПО ТЗ.Дата >= ТЗ1.Дата

СГРУППИРОВАТЬ ПО
    ТЗ.Дата
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Дата,
    ВТ.ОстатокНИ - ВЫБОР
        КОГДА МИНИМУМ(ВТ1.ОстатокНИ) < 0
            ТОГДА МИНИМУМ(ВТ1.ОстатокНИ)
        ИНАЧЕ 0
    КОНЕЦ КАК Перенесено
ИЗ
    ВТ КАК ВТ
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ КАК ВТ1
        ПО ВТ.Дата >= ВТ1.Дата

СГРУППИРОВАТЬ ПО
    ВТ.Дата,
    ВТ.ОстатокНИ

(42) не верю что так просто, завтра проверю

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

(43) данный запрос неправильно отрабатывает при вот таких входных данных

             1    2    3    4
мощность    100    100    100    0
надо             0    150    75    100
перенести     0    0    50    125

он выдает

Дата    Перенесено
1    
2    50
3    25
4    125

(45) запрос как раз правильно выдает. Во второй день отработаем 100, перенесем 50

по логике здесь 50 штук надо перенести на предыдущий день, почему переносится на следующий, непонятно.

(47) если бы можно было перенести на вчера был бы банальный нарастающий итог и второй запрос с поправкой не нужен был. Поправка как раз на неиспользованные ранее мощности

(45) кажется понял о чем  речь:
запрос считает сколько будет перенесено на следующий день,
а ты ожидаешь увидеть сколько было перенесено с предыдущего дня.

(49) "а ты ожидаешь увидеть сколько было перенесено с предыдущего дня." - ну на следующий же день доделывать заказ нужно на станке.
(42) Запрос - для какой-то другой задачи.

Автор кинул почитал и уже ушел из темы)) а люди продолжают фантазировать запросы в плоть на SQL ))

(51) я здесь,  на следующий день надо переносить остаток что надо доделать с предыдущих дней , с учетом полной загрузки мощностей предыдущего дня.

(50) сдвинуть на день можно двумя способами:
1. вычесть текущий день,
2. считать итоги без текущего

второй вариант

ВЫБРАТЬ
    ТЗ.Дата,
    СУММА(ТЗ1.Требуется - ТЗ1.Мощность) КАК ОстатокНИ
ПОМЕСТИТЬ ВТ
ИЗ
    ТЗ КАК ТЗ
        ЛЕВОЕ СОЕДИНЕНИЕ ТЗ КАК ТЗ1
        ПО ТЗ.Дата > ТЗ1.Дата

СГРУППИРОВАТЬ ПО
    ТЗ.Дата
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Дата,
    ВТ.ОстатокНИ - ВЫБОР
        КОГДА МИНИМУМ(ВТ1.ОстатокНИ) < 0
            ТОГДА МИНИМУМ(ВТ1.ОстатокНИ)
        ИНАЧЕ 0
    КОНЕЦ КАК Перенесено
ИЗ
    ВТ КАК ВТ
        СОЕДИНЕНИЕ ВТ КАК ВТ1
        ПО ВТ.Дата >= ВТ1.Дата

СГРУППИРОВАТЬ ПО
    ВТ.Дата,
    ВТ.ОстатокНИ

(53) про сдвиг я понял, извиняюсь. запрос вроде правильный результат выдает.

(49) я на 4ый посчитал неправильно. Надо 25, а у меня 125 написано, ошибся.

ну что прикольно, спасибо. сохраню в коллекцию )

Проверял на таблице

           1    2    3    4    5    6    7    8    9
мощность    100    100    100    0    0    200    200    200    200
надо            0    150    75    100            250        
перенести    0    0    50    25    125    125    0    50    0

результат запроса

Дата    Перенесено
1    
2    
3    50
4    25
5    125
6    125
7    
8    50
9    

Все ОК

(56) ещё раз замечу, что (12) остается в силе.
Использовать это в проде не рекомендую даже если никаких проблем с применением на реальных данных нет.

Задачка чисто на академический или спортивный интерес.

(58) Хорошо

(53) В ЕстьNULL надо обернуть ещё поле "Перенесено"

ЕстьNULL(СУММА(ТЗ1.Требуется - ТЗ1.Мощность),0) КАК ОстатокНИ

Иначе первая строка будет содержать NULL

(60) во втором запрос внутреннее соединение по ">=", нуллей там быть не должно

(61) Я про первый запрос - там строгое ">", а потом только во 2-м из этого NULL в первой строчке вычитается, то где не может быть NULL так как там ">=".

(62) ОК. Согласен, если в первой строке хотим видеть 0, то нужно обернуть. Ничего кроме 0 там быть не может по условиям задачи

Ребят всём спасибо ещё раз.