ReSQL.ru
     
powered by simpleCommunicator - 2.0.29     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Двухлинейное FIFO - в запросе.
9 сообщений из 9, страница 1 из 1
Двухлинейное FIFO - в запросе.
    #40103988
Jaffar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jaffar
Участник
Добрый день.

Прошу помочь решить задачу - кто знает или просто указать на готовое решение(где почитать).
Я убежден, что 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, 08:56
| Ответить | Цитировать | Написать  
Двухлинейное FIFO - в запросе.
    #40104003
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Jaffar,

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

ps
model

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


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

да, любые запросы подзапросы и т.п.
...
Рейтинг: 0 / 0
13.10.2021, 11:07
| Ответить | Цитировать | Написать  
Двухлинейное FIFO - в запросе.
    #40104029
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
Помогите с запросом с аналит. ф-циями
разбивка суммы на составляющие аналитическими функциями
очень похоже на то что надо
...
Рейтинг: 0 / 0
13.10.2021, 11:12
| Ответить | Цитировать | Написать  
Двухлинейное FIFO - в запросе.
    #40104068
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
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:01
| Ответить | Цитировать | Написать  
Двухлинейное FIFO - в запросе.
    #40104072
Jaffar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jaffar
Участник
Stax, спасибо.
...
Рейтинг: 0 / 0
13.10.2021, 12:16
| Ответить | Цитировать | Написать  
Двухлинейное FIFO - в запросе.
    #40104077
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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.
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, 12:36
| Ответить | Цитировать | Написать  
Двухлинейное FIFO - в запросе.
    #40104105
Никанор Кузьмич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Никанор Кузьмич
Участник
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
13.10.2021, 13:34
| Ответить | Цитировать | Написать  
Двухлинейное FIFO - в запросе.
    #40104374
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
на основе темы про "бутылочки"
реально работает :)

Код: 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
14.10.2021, 13:33
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Двухлинейное FIFO - в запросе.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=40104072&tid=1879822]:
 0ms
get settings:
 18ms
get forum list:
 5ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 34ms
get topic data:
 2ms
get forum data:
 1ms
get page messages:
 156ms
get tp. blocked users:
 0ms
others: 2304ms
total:  2522ms
созд. / загр.: 2522ms
x
x
Закрыть


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