Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задача про скорость наполнения бассейна / 5 сообщений из 5, страница 1 из 1
27.12.2021, 01:53
    #40123244
aborigen
aborigen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задача про скорость наполнения бассейна
Всем привет,
Возможно, было здесь и много раз, но как конкретно искать не знаю, на start_of_group не похоже.
Может, поможете составить запрос.

Есть бассейн бесконечной вместимости. К нему могут подходить люди и каждый с момента start_time по end_time наполнять его водой со скоростью val. Мне необходимо узнать в каждый конкретный момент времени скорость наполнения бассейна.

Запрос следующего вида задачу, в общем-то, решает:
Код: 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.
with dat as(
  select timestamp'2021-12-24 05:00:00' as start_time, timestamp'2021-12-24 05:00:20' as end_time, 1 as val from dual
  union all
  select timestamp'2021-12-24 05:00:01' as start_time, timestamp'2021-12-24 05:00:21' as end_time, 2 as val from dual
  union all
  select timestamp'2021-12-24 05:00:02' as start_time, timestamp'2021-12-24 05:00:22' as end_time, 3 as val from dual
)
, times as(
  select
    distinct
    d.start_time as tm
  from
    dat d

  union

  select
    distinct
    d.end_time
  from
    dat d
)
select
  t.tm
  , sum(d.val)
from
  times t
    join dat d
    on t.tm between d.start_time and d.end_time
group by
  t.tm
order by
  1
;



Но на реальных данных (миллион записей, почти все даты различны) картезиан из JOIN-а получается неприятно длительным.

Возможно ли как-то достичь эквивалентного результата более производительно?
Оптимально - в один проход по исходной таблице.
Последняя идея наталкивает на мысль об аналитических функциях, но как их соорудить для интервалов (start_time, end_time) - не могу понять.
Чем ближе к ANSI, тем лучше: целевая БД хоть и сильно совместима с Oracle SQL, но всё же ряд свежих и/или экзотических фич отсутвует.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.12.2021, 02:26
    #40123245
aborigen
aborigen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задача про скорость наполнения бассейна
but wait ...
https://www.sql.ru/forum/actualsearch.aspx?search=?????????????? ?????????&sin=0&bid=3&a=&ma=0&dt=-1&s=1&so=1
Так и знал, что формулирование вопроса заставит переобдумать всё.
Надо будет полистать решения выше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.12.2021, 02:34
    #40123246
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задача про скорость наполнения бассейна
aborigen,

просто unpivot + sum()over():
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
with dat as(
  select timestamp'2021-12-24 05:00:00' as start_time, timestamp'2021-12-24 05:00:20' as end_time, 1 as val from dual
  union all
  select timestamp'2021-12-24 05:00:01' as start_time, timestamp'2021-12-24 05:00:21' as end_time, 2 as val from dual
  union all
  select timestamp'2021-12-24 05:00:02' as start_time, timestamp'2021-12-24 05:00:22' as end_time, 3 as val from dual
)
select dt, sum(val*x)over(order by dt) dt_val
from dat
unpivot (
  dt for x in (start_time as 1, end_time as -1)
);

DT                   DT_VAL
------------------- -------
2021-12-24 05:00:00       1
2021-12-24 05:00:01       3
2021-12-24 05:00:02       6
2021-12-24 05:00:20       5
2021-12-24 05:00:21       3
2021-12-24 05:00:22       0

6 rows selected.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.12.2021, 02:38
    #40123248
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задача про скорость наполнения бассейна
 
+ ansi
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with dat as(
  select timestamp'2021-12-24 05:00:00' as start_time, timestamp'2021-12-24 05:00:20' as end_time, 1 as val from dual
  union all
  select timestamp'2021-12-24 05:00:01' as start_time, timestamp'2021-12-24 05:00:21' as end_time, 2 as val from dual
  union all
  select timestamp'2021-12-24 05:00:02' as start_time, timestamp'2021-12-24 05:00:22' as end_time, 3 as val from dual
)
select 
   case x when 1 then start_time else end_time end as dt
 , sum(val*x)over(order by case x when 1 then start_time else end_time end) as dt_val
from dat
     cross join(
        select 1 x from dual
        union all
        select -1 from dual
     )

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2021, 17:37
    #40123668
aborigen
aborigen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задача про скорость наполнения бассейна
Sayan Malakshinov,

Спасибо.
Как ожидалось, ларчик просто открывался.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задача про скорость наполнения бассейна / 5 сообщений из 5, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 190ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1879640]:
 0ms
get settings:
 15ms
get forum list:
 5ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 36ms
get topic data:
 5ms
get forum data:
 1ms
get page messages:
 117ms
get tp. blocked users:
 2ms
others: 7ms
total:  190ms
x
x
Закрыть


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