Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Двухлинейное FIFO - в запросе. / 9 сообщений из 9, страница 1 из 1
13.10.2021, 08:56
    #40103988
Jaffar
Jaffar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
Добрый день.

Прошу помочь решить задачу - кто знает или просто указать на готовое решение(где почитать).
Я убежден, что 100% есть такая математическая задачка, но я не знаю ее общеупотребительное название.
Какое-нибудь "двойное(двухлинейное) FIFО".

Задача.
Есть приходы(таблица PRIHOD), отсортированные по порядку поступления(номер по порядку(N) и сумма(SUMM)).
так же Есть расходы(таблица RASHOD), отсортированные по порядку поступления(номер по порядку(N) и сумма(SUMM)).
Суть:
нужно связать расходы(по методу FIFO) с приходами(по методу FIFO) по порядку и иметь сумму связи.
какой приход использовался для покрытия какого расхода и сумма покрытия.

Чтобы проще понять - придумал аналогию с перегрузкой.
Допустим, есть пронумерованные вагоны заполненные кирпичами(мы знаем номер и кол-во кирпичей в каждом вагоне) - это приходы.
И так же есть пронумерованные фуры пустые (мы знаем номер и сколько кирпичей поместится в фуру) - это расходы.
Емкости вагонов и фур у всех разные.
Нужно: сколько можно перегрузить кирпичи из вагонов в фуры.
По следующему алгоритму(2 линии FIFO):
Первой загружаем первую фуру кирпичами из первого вагона.
Если первая фура загружена полностью - начинаем грузить вторую из того же первого вагона до его полного опустошения и т.д.
Если же первая фура еще не заполнена, а первый вагон уже пуст - то грузим в нее из следующего по порядку вагона и т.д.
И самое главное записать в какую фуру из какого вагона сколько кирпичей перегружено
Напр.
в 1 фуру из 1 вагона 1000 кирп.
в 2 фуру из 1 вагона 500 кирп.
в 2 фуру из 2 вагона 300 кирп.
в 2 фуру из 3 вагона 600 кирп.
в 3 фуру из 3 вагона 400 кирп.
и т.д.


Конечно можно написать цикл, но хотелось бы одном запросом.
Можно использовать аналитические(оконные) функции и т.п.
Сервер: Oracle Database 11g

Для удобства есть пример таблицы приходов и расходов и что должно получиться.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
create table PRIHOD as
select p.N, p.SUMM
from (    select 1 N, 100 SUMM from dual
union all select 2 N, 110 SUMM from dual
union all select 3 N, 130 SUMM from dual
union all select 4 N, 150 SUMM from dual
union all select 5 N, 200 SUMM from dual
union all select 6 N,  20 SUMM from dual  ) p
order by p.N asc;

create table RASHOD as
select r.N, r.SUMM
from (    select  1 N,  50 SUMM from dual
union all select  2 N,  40 SUMM from dual
union all select  3 N, 130 SUMM from dual
union all select  4 N,  60 SUMM from dual
union all select  5 N,  20 SUMM from dual
union all select  6 N,  80 SUMM from dual 
union all select  7 N, 300 SUMM from dual
union all select  8 N,  40 SUMM from dual
union all select  9 N,  70 SUMM from dual
union all select 10 N, 120 SUMM from dual
union all select 11 N,  10 SUMM from dual  ) r
order by r.N asc;

---результат:
select o.R "Расход", o.P "Приход", o.SS "Сумма связи"
from(     select 1 R, 1 P,  50 SS from dual
union all select 2  , 1  ,  40    from dual
union all select 3  , 1  ,  10    from dual
union all select 3  , 2  , 110    from dual
union all select 3  , 3  ,  10    from dual
union all select 4  , 3  ,  60    from dual
union all select 5  , 3  ,  20    from dual
union all select 6  , 3  ,  40    from dual
union all select 6  , 4  ,  40    from dual
union all select 7  , 4  , 110    from dual
union all select 7  , 5  , 190    from dual
union all select 8  , 5  ,  10    from dual
union all select 8  , 6  ,  20    from dual ) o



Кто прочел до конца - спасибо за терпение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2021, 09:57
    #40104003
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
Jaffar,

1) сумарные емкости вагонов и фур равны?
2) декартовое обьеденение допускается?

ps
model

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2021, 11:07
    #40104026
Jaffar
Jaffar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
автор1) суммарные емкости вагонов и фур равны?
- это не оговаривается.
кирпичей в вагонах может быть больше чем места в фурах,
а может быть места в фурах больше чем кирпичей в вагонах.
а может быть и поровну.


автор2) декартовое обьеденение допускается?

да, любые запросы подзапросы и т.п.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2021, 11:12
    #40104029
MazoHist
MazoHist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
Помогите с запросом с аналит. ф-циями
разбивка суммы на составляющие аналитическими функциями
очень похоже на то что надо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2021, 12:01
    #40104068
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
Jaffar,

от декартового надо отказаться,
будет чутку сложнее условие в
union all (form t,prihod p, rashod r where ...)

начал с декартового и ...

если не сможете, я поправлю


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
with  
PRIHOD (N, SUMM) as
(    select 1 N, 100 SUMM from dual
union all select 2 N, 110 SUMM from dual
union all select 3 N, 130 SUMM from dual
union all select 4 N, 150 SUMM from dual
union all select 5 N, 200 SUMM from dual
union all select 6 N,  20 SUMM from dual  
)
,RASHOD (N, SUMM) as
(    select  1 N,  50 SUMM from dual
union all select  2 N,  40 SUMM from dual 
union all select  3 N, 130 SUMM from dual
union all select  4 N,  60 SUMM from dual
union all select  5 N,  20 SUMM from dual
union all select  6 N,  80 SUMM from dual 
union all select  7 N, 300 SUMM from dual
union all select  8 N,  40 SUMM from dual
union all select  9 N,  70 SUMM from dual
union all select 10 N, 120 SUMM from dual
union all select 11 N,  10 SUMM from dual  
) 
,tt as (
select 
  row_number() over (order by p.n,r.n) n
 ,p.n pn
 ,p.summ psumm
 ,r.n rn
 ,r.summ rsumm
from prihod p,rashod r
--where p.n=1 and r.n=1
)
, t (n,pn,psumm,rn,rsum,z,s,summ) as (
select n,pn,psumm,rn,rsumm
 ,abs(psumm-rsumm) z
 ,sign(psumm-rsumm) s
 ,least(psumm-rsumm) summ
from tt where n=1
union all
select tt.n,tt.pn,tt.psumm,tt.rn,tt.rsumm
 ,abs(decode(t.s,1,t.z,tt.psumm)-decode(t.s,-1,t.z,tt.rsumm))
 ,sign(decode(t.s,1,t.z,tt.psumm)-decode(t.s,-1,t.z,tt.rsumm)) s
 ,least(decode(t.s,1,t.z,tt.psumm),decode(t.s,-1,t.z,tt.rsumm))
from t,tt where tt.pn=t.pn+decode(t.s,1,0,1) and tt.rn=t.rn+decode(t.s,-1,0,1)
)
select rn,pn,summ from t
/
SQL> /

        RN         PN       SUMM
---------- ---------- ----------
         1          1         50
         2          1         40
         3          1         10
         3          2        110
         3          3         10
         4          3         60
         5          3         20
         6          3         40
         6          4         40
         7          4        110
         7          5        190
         8          5         10
         8          6         20

13 rows selected.


сильно не не стировал, допилете

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2021, 12:16
    #40104072
Jaffar
Jaffar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
Stax, спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2021, 12:36
    #40104077
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
with  
PRIHOD (N, SUMM) as
(    select 1 N, 100 SUMM from dual
union all select 2 N, 110 SUMM from dual
union all select 3 N, 130 SUMM from dual
union all select 4 N, 150 SUMM from dual
union all select 5 N, 200 SUMM from dual
union all select 6 N,  20 SUMM from dual  
)
,RASHOD (N, SUMM) as
(    select  1 N,  50 SUMM from dual
union all select  2 N,  40 SUMM from dual 
union all select  3 N, 130 SUMM from dual
union all select  4 N,  60 SUMM from dual
union all select  5 N,  20 SUMM from dual
union all select  6 N,  80 SUMM from dual 
union all select  7 N, 300 SUMM from dual
union all select  8 N,  40 SUMM from dual
union all select  9 N,  70 SUMM from dual
union all select 10 N, 120 SUMM from dual
union all select 11 N,  10 SUMM from dual  
) 
, t (n,pn,psumm,rn,rsum,z,s,summ) as (
select 1 n,p.n pn,p.summ psumm,r.n rn,r.summ rn
 ,abs(p.summ-r.summ) z
 ,sign(p.summ-r.summ) s
 ,least(p.summ-r.summ) summ
from prihod p,rashod r
  where p.n=1 and r.n=1
union all
select t.n+1,p.n, p.summ,r.n,r.summ
 ,abs(decode(t.s,1,t.z,p.summ)-decode(t.s,-1,t.z,r.summ))
 ,sign(decode(t.s,1,t.z,p.summ)-decode(t.s,-1,t.z,r.summ)) s
 ,least(decode(t.s,1,t.z,p.summ),decode(t.s,-1,t.z,r.summ))
from t,prihod p,rashod r where p.n=t.pn+decode(t.s,1,0,1) and r.n=t.rn+decode(t.s,-1,0,1)
)
select 
*
--rn,pn,summ 
from t
/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2021, 13:34
    #40104105
Никанор Кузьмич
Никанор Кузьмич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
Stax,

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
with PRIHOD as (
        select 1 N, 100 SUMM from dual
        union all select 2 N, 110 SUMM from dual
        union all select 3 N, 130 SUMM from dual
        union all select 4 N, 150 SUMM from dual
        union all select 5 N, 200 SUMM from dual
        union all select 6 N,  20 SUMM from dual  ),
     RASHOD as (
        select  1 N,  50 SUMM from dual
        union all select  2 N,  40 SUMM from dual
        union all select  3 N, 130 SUMM from dual
        union all select  4 N,  60 SUMM from dual
        union all select  5 N,  20 SUMM from dual
        union all select  6 N,  80 SUMM from dual 
        union all select  7 N, 300 SUMM from dual
        union all select  8 N,  40 SUMM from dual
        union all select  9 N,  70 SUMM from dual
        union all select 10 N, 120 SUMM from dual
        union all select 11 N,  10 SUMM from dual  ),
    running_rashod as (
        select n, summ, sum(summ) over (order by n) rs
          from rashod),
    running_prihod as (
        select t.*, lag(rs, 1, 0) over (order by n) prev_prs
          from (select n, summ, sum(summ) over (order by n) rs
                  from prihod) t)
select rn, pn,
       case when prs >= rrs and (prev_prs - prev_rrs) >= 0 then rsumm
            when prs >= rrs and (prev_prs - prev_rrs) <= psumm then prev_rrs - prev_prs
            when prs >= prev_rrs then prs - prev_rrs
            else psumm end itog
  from (select lag(prs, 1, 0) over (order by pn, rn) prev_prs,
               lag(rrs, 1, 0) over (order by pn, rn) prev_rrs, t.*
          from (select *
                  from (select p.n pn, p.summ psumm, p.rs prs,
                               r.n rn, r.summ rsumm, r.rs rrs,
                               min(r.n) keep (dense_rank first order by 
                                     case when p.rs > r.rs then null else p.rs - r.rs end desc nulls last)
                                     over (partition by p.n) rn_max 
                          from running_prihod p, running_rashod r
                         where r.rs > p.prev_prs)
                 where rn <= rn_max) t
        )
 order by pn, rn


Надо еще посмотреть повнимательнее решения, на которые давали ссылки выше. Уж больно они короткие и простые, чувствую себя двоечником
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.10.2021, 13:33
    #40104374
MazoHist
MazoHist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Двухлинейное FIFO - в запросе.
на основе темы про "бутылочки"
реально работает :)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select *
  from (select last_value(pn ignore nulls) over(order by svol desc) as pn,
               svol - lead(svol, 1, 0) over(order by svol desc) as p_sum,
               last_value(SUMM ignore nulls) over(order by svol desc) as r_sum,
               last_value(rn ignore nulls) over(order by svol desc) as rn
          from (select n as rn,
                       null as pn,
                       SUMM,
                       sum(SUMM) over(order by n) as svol
                  from r
                union all
                select null, n, null, sum(SUMM) over(order by n)
                  from p))
 where r_sum > 0
   and pn is not null
 order by pn, rn
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Двухлинейное FIFO - в запросе. / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 2761ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1879822]:
 0ms
get settings:
 23ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 45ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 254ms
get tp. blocked users:
 2ms
others: 2403ms
total:  2761ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]