Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
fr13
Guest
Есть задача, упрощенный вариант которой выглядит так

my_object_table (OBJECT_ID number, START_RANGE date, END_RANGE date) - это означает, что OBJECT_ID имеется для любой даты из диапазона между START_RANGE и END_RANGE.
(Сколько всего строк, сейчас не скажу, поскольку пишу не с работы.)

Надо подсчитать, сколько для каждого дня за последние, скажем, 2-3 года имеется OBJECT_ID (уникальных или нет - не существенно).

Делаю я это вот так

-- Стандартный способ получения списка дат
with days as (select :DATE_FROM + rownum - 1 DAY_IN_LIST
from dual
connect by level <= :DATE_TO - :DATE_FROM +1),

-- Соединение с предметной таблицей для получения списка объектов для каждого дня
lst as (select DAY_IN_LIST, OBJECT_ID
from my_object_table
join days on DAY_IN_LIST between START_RANGE and END_RANGE)

select DAY_IN_LIST, count(*) from lst group by DAY_IN_LIST;

Все просто, только, к сожалению, медленно. Есть возможность вызывать не это, а материализованное представление. Но тогда оно обновляться будет медленно. Это тоже не устраивает.

Вопрос не в том, чтобы найти еще какой-то альтернативный вариант трансформации интервала в список строк, а в том, чтобы любым способом быстрее получить результат (сейчас при списке дат за последние два месяца все сносно, а за два года очень медленно). Есть ли идеи?
13 ноя 15, 01:38    [18409878]     Ответить | Цитировать Сообщить модератору
 Re: Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
Добрый Э - Эх
Guest
fr13,

ну, так-то в году всего 36[5|6] дней. Даже за 10 лет объем календаря будет невелик. Или у тебя данные о миллиардах объектах хранятся?


Как вариант маленькой оптимизации: разворот в по-дневной список делать не для каждого отдельно взятого объекта, а для посчитанного их количества по пересекающимся периодам жизненных циклов объектов.
13 ноя 15, 04:46    [18409964]     Ответить | Цитировать Сообщить модератору
 Re: Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
Добрый Э - Эх
Guest
fr13,

в качестве направления для дальнейших поисков:
+ <<<< Немного пятничного бреда

with
--
-- Исходные данные:
  my_object_table as 
    (
      select 1 OBJECT_ID, date'2015-01-01' START_RANGE, date'2015-01-05' END_RANGE from dual union all
      select 1 OBJECT_ID, date'2015-01-07' START_RANGE, date'2015-01-10' END_RANGE from dual union all
      select 1 OBJECT_ID, date'2015-01-15' START_RANGE, date'2015-01-20' END_RANGE from dual union all
      select 2 OBJECT_ID, date'2015-01-02' START_RANGE, date'2015-01-08' END_RANGE from dual union all
      select 2 OBJECT_ID, date'2015-01-10' START_RANGE, date'2015-01-11' END_RANGE from dual union all
      select 2 OBJECT_ID, date'2015-01-15' START_RANGE, date'2015-01-25' END_RANGE from dual union all
      select 3 OBJECT_ID, date'2015-01-05' START_RANGE, date'2015-01-10' END_RANGE from dual union all
      select 3 OBJECT_ID, date'2015-01-16' START_RANGE, date'2015-01-21' END_RANGE from dual union all
      select 3 OBJECT_ID, date'2015-01-26' START_RANGE, date'2015-01-31' END_RANGE from dual union all
      select 4 OBJECT_ID, date'2015-01-10' START_RANGE, date'2015-01-14' END_RANGE from dual union all
      select 4 OBJECT_ID, date'2015-01-16' START_RANGE, date'2015-01-18' END_RANGE from dual union all
      select 4 OBJECT_ID, date'2015-01-23' START_RANGE, date'2015-01-28' END_RANGE from dual union all
      select 5 OBJECT_ID, date'2015-01-04' START_RANGE, date'2015-01-09' END_RANGE from dual union all
      select 5 OBJECT_ID, date'2015-01-19' START_RANGE, date'2015-01-29' END_RANGE from dual union all
      select 6 OBJECT_ID, date'2015-01-01' START_RANGE, date'2015-01-15' END_RANGE from dual union all
      select 7 OBJECT_ID, date'2015-01-16' START_RANGE, date'2015-01-31' END_RANGE from dual 
    )
--
-- Небольшая предобработка исходных данных: 
, range_transform as
   (-- Сворачивания точек смены состояний в интервалы состояний:
     select dt s_dt, lead(dt,1,dt + 1) over(order by dt) e_dt, cnt
       from ( -- Расчет "эрлангов":
              select distinct sum(flag) over(order by dt) as cnt, dt
                from ( -- Преобразование исходных интервалов в точки смены состояния:
                       select  1 as flag, START_RANGE as dt from my_object_table t
                        union all
                       select -1 as flag,   END_RANGE as dt from my_object_table t
                     )
             )
   )
--
-- Основной запрос:
select t.s_dt + p.column_value - 1 as "Дата", t.cnt as "Кол-во объектов"
  from range_transform t
     , table
        (-- Разложение интервалов в календарные дни:
          cast
            (
              multiset
                (
                  select rownum from dual connect by level <= t.e_dt - t.s_dt
                ) as sys.odcinumberlist
            )
        ) p;
13 ноя 15, 07:39    [18410099]     Ответить | Цитировать Сообщить модератору
 Re: Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
Elic
Member

Откуда:
Сообщений: 29990
Добрый Э - Эх
<<<< Немного пятничного бреда
Ну почему же. Хотя немного витиеватее и недетерминированей, чем в Задача с пересекающимися диапазонами
13 ноя 15, 08:20    [18410170]     Ответить | Цитировать Сообщить модератору
 Re: Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
stax..
Guest
fr13,

а если так?
SQL> /

  1  with
  2    t as
  3      (
  4        select 1 OBJECT_ID, date'2015-01-01' START_RANGE, date'2015-01-05' END_RANGE from dual union all
  5        select 1 OBJECT_ID, date'2015-01-07' START_RANGE, date'2015-01-10' END_RANGE from dual union all
  6        select 1 OBJECT_ID, date'2015-01-15' START_RANGE, date'2015-01-20' END_RANGE from dual union all
  7        select 2 OBJECT_ID, date'2015-01-02' START_RANGE, date'2015-01-08' END_RANGE from dual union all
  8        select 2 OBJECT_ID, date'2015-01-10' START_RANGE, date'2015-01-11' END_RANGE from dual union all
  9        select 2 OBJECT_ID, date'2015-01-15' START_RANGE, date'2015-01-25' END_RANGE from dual
 10      )
 11  select start_range+to_number(column_value) dd
 12  ,count(OBJECT_ID) cj,count(unique OBJECT_ID) cu
 13  from t,xmltable('0 to xs:integer(.)' passing (END_RANGE-START_RANGE)+0)
 14  group by start_range+to_number(column_value)
 15* order by 1
SQL> /

DD               CJ         CU
-------- ---------- ----------
01.01.15          1          1
02.01.15          2          2
03.01.15          2          2
04.01.15          2          2
05.01.15          2          2
06.01.15          1          1
07.01.15          2          2
08.01.15          2          2
09.01.15          1          1
10.01.15          2          2
11.01.15          1          1
15.01.15          2          2
16.01.15          2          2
17.01.15          2          2
18.01.15          2          2
19.01.15          2          2
20.01.15          2          2
21.01.15          1          1
22.01.15          1          1
23.01.15          1          1
24.01.15          1          1
25.01.15          1          1

22 rows selected.


......
stax
13 ноя 15, 16:23    [18413177]     Ответить | Цитировать Сообщить модератору
 Re: Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
fr13
Guest
Как иногда бывает, выяснилось, что реальный запрос тормозился не из-за превращения интервала в список дат (в таблице, конечно, не миллиарды строк). Но пока я это выяснял, наткнулся на вот это, имеющее отношение к топику (запрос в самом конце). Так что надо будет как-нибудь опробовать.
https://community.oracle.com/thread/1101756?start=0&tstart=0
13 ноя 15, 17:01    [18413409]     Ответить | Цитировать Сообщить модератору
 Re: Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
без латерал
Guest
fr13
Но пока я это выяснял, наткнулся на вот это, имеющее отношение к топику (запрос в самом конце). Так что надо будет как-нибудь опробовать.
https://community.oracle.com/thread/1101756?start=0&tstart=0
"это" для однострочного источника, а у тебя множество OBJECT_ID
13 ноя 15, 17:09    [18413465]     Ответить | Цитировать Сообщить модератору
 Re: Быстрейший способ превращения интервала (одна строка) в набор строк  [new]
fr13
Guest
Ты не дочитал обсуждение по ссылке до конца. Наверно, торопился! )))
13 ноя 15, 19:15    [18414165]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить