Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Временной срез  [new]
Захарка
Guest
Добрый день!

Задача такая: есть таблица

ID_ST NUMBER NOT NULL,
ID_TURNED NUMBER NOT NULL,
DATE_BEGIN DATE

Здесь:
ID_ST - ID аппарата,
ID_TURNED - состояние аппарата, принимает значения 1 - вкл, 0 -выкл,
DATE_BEGIN - дата смены состояния

Состояния каждого аппарата чередуются. Последнее состояние - состояние по текущий момент.

Необходимо найти:
1. количество включённых аппаратов на конец каждых суток,когда включен хотя бы один аппарат (COUNT_ST);
2. количество аппаратов, работавших в эти сутки(COUNT_ST1). Аппарат, несколько раз включавшийся в течении суток, считается одним.

Пример:

ID_STID_TURNEDDATE_BEGIN
1101.01.2011 10:00:00
1001.01.2011 12:00:00
1101.01.2011 14:00:00
1005.01.2011 15:00:00
1125.08.2011 12:00:00
2103.01.2011 14:00:00
2006.01.2011 18:00:00


Результат:

DATE_COUNT_STCOUNT_ST1
01.01.201111
02.01.201111
03.01.201122
04.01.201122
05.01.201112
06.01.201101
25.08.201111
26.08.201111


Последняя строка - текущая дата.
26 авг 11, 14:01    [11185105]     Ответить | Цитировать Сообщить модератору
 Re: Временной срез  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
за партой рядом сидите?
26 авг 11, 14:09    [11185185]     Ответить | Цитировать Сообщить модератору
 Re: Временной срез  [new]
Захарка
Guest
Vint,

если б сидел - списал.
26 авг 11, 14:15    [11185237]     Ответить | Цитировать Сообщить модератору
 Re: Временной срез  [new]
Захарка
Guest
По первому пункту:

with tbl_turned as
(select 1 id_st, 1 id_turned, To_date('01.01.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 1 id_st, 0 id_turned, To_date('01.01.2011 12:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 1 id_st, 1 id_turned, To_date('01.01.2011 14:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 1 id_st, 0 id_turned, To_date('05.01.2011 15:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 1 id_st, 1 id_turned, To_date('25.08.2011 12:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 2 id_st, 1 id_turned, To_date('03.01.2011 14:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 2 id_st, 0 id_turned, To_date('06.01.2011 18:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual),
t as (select id_st,
                     date_begin,
                     id_turned,
                     nvl(lead(date_begin) over (partition by id_st order by date_begin), sysdate) date_end
             from tbl_turned),
t1 as (select trunc(date_begin + level -1) dt
           from t connect by level <= (date_end-date_begin)+1
           and id_turned <> 0)
select distinct dt,  
 (select count(*) from t where date_begin <= dt+1 and date_end > dt+1 and id_turned = 1) count_st
from t1

Вопрос: а можно упростить
(select count(*) from t where date_begin <= dt+1 and date_end > dt+1 and id_turned = 1)
, используя аналитические функции?
29 авг 11, 11:39    [11193895]     Ответить | Цитировать Сообщить модератору
 Re: Временной срез  [new]
AmKad
Member

Откуда:
Сообщений: 5222
+
select trunc(sysdate) dt from dual;

DT
----------
29.08.2011

with s as
(select 1 id_st, 1 id_turned, To_date('01.01.2011 10:00:00', 'dd.mm.yyyy hh24:mi:ss') dt         from dual union all
 select 1 id_st, 0 id_turned, To_date('01.01.2011 12:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 1 id_st, 1 id_turned, To_date('01.01.2011 14:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 1 id_st, 0 id_turned, To_date('05.01.2011 15:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 1 id_st, 1 id_turned, To_date('25.08.2011 12:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 2 id_st, 1 id_turned, To_date('03.01.2011 14:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual union all
 select 2 id_st, 0 id_turned, To_date('06.01.2011 18:00:00', 'dd.mm.yyyy hh24:mi:ss') date_begin from dual)
select trunc(dt) dt, 
count(distinct decode(pr, 1, id_st)) cnt_1,
count(distinct decode(lv, 1, id_st)) cnt_2
from
 (select id_st, id_turned, dt, lv,
  last_value(lv ignore nulls) over (partition by id_st, trunc(dt) order by dt range between unbounded preceding and unbounded following) pr
  from
   (select id_st, id_turned, dt,
    last_value(id_turned ignore nulls) over (partition by id_st order by dt) lv
    from
      (select *
       from s
       model
       partition by (id_st)
       dimension by (-row_number() over (partition by id_st order by dt) rn)
       measures(id_turned, dt, trunc(sysdate) mx)
       rules
       (dt[for rn from 0 to mx[-1] - trunc(dt[-1]) increment 1] = trunc(dt[-1]) + cv(rn)
       )
      ) 
    )   
  )
group by trunc(dt)
having count(distinct decode(pr, 1, id_st)) + count(distinct decode(lv, 1, id_st)) > 0
order by dt;

DT              CNT_1      CNT_2
---------- ---------- ----------
01.01.2011          1          1
02.01.2011          1          1
03.01.2011          2          2
04.01.2011          2          2
05.01.2011          1          2
06.01.2011          0          1
25.08.2011          1          1
26.08.2011          1          1
27.08.2011          1          1
28.08.2011          1          1
29.08.2011          1          1

11 строк выбрано.
29 авг 11, 16:17    [11196064]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить