Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
Простой вопрос:

Нужно сделать параметризованый запрос, который будет возвращать данные по датам, причём записи нужны по всем датам, даже если на какую-нибудь дату данных нет, т.е. такой запрос:

select trunc(tb.DAT) DAT,
       sum(nvl(tb.AMOUNT,0)) AMOUNT
from test_tbl tb
where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND to_date('05.01.2007','dd.mm.yyyy')
group by trunc(tb.DAT)

возвратит следующее:

01.01.2007  111
03.01.2007  333
05.01.2007  555

а хочется чтобы возвращал так:

01.01.2007  111
02.01.2007  0
03.01.2007  333
04.01.2007  0
05.01.2007  555

как изменить запрос?
23 янв 07, 17:29    [3682441]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
tru55
Member

Откуда: СПб
Сообщений: 19790
ищи на форуме по слову pivot
23 янв 07, 17:35    [3682506]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Stax.
Guest
пример с pivot-ом (токо where надо добавить)
......
stax
23 янв 07, 17:40    [3682549]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116351
Например сюда

Получить селектом диапазон дат
23 янв 07, 17:42    [3682577]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
блин, фигня какая-то! Неужели так сложно?
23 янв 07, 18:21    [3682798]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Stax.
Guest
verter
блин, фигня какая-то! Неужели так сложно?

что сложного?
упрощаем условия и упростится сложность,
напр нужно ли в разрезе "клиента"
.....
stax
23 янв 07, 18:28    [3682854]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
я получил нужный результат сделав так как написано, т.е. вот так:

select dd.DAT,
         ss.AMOUNT
from
      (
       select trunc(tb.DAT) DAT,
               sum(nvl(tb.AMOUNT,0)) AMOUNT
       from test_tbl tb
       where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND 
                                                 to_date('05.01.2007','dd.mm.yyyy')
       group by trunc(tb.DAT)
      ) ss,
     (
      select (startdate + rownum - 1) DAT
      from all_objects, 
            all_objects,
            (
             select to_date('01.01.2007','dd.mm.yyyy') startdate,
                      to_date('05.01.2007','dd.mm.yyyy') enddate
             from dual
            )
      where rownum <= enddate - startdate + 1
     ) dd
where ss.DAT (+)= dd.DAT 

всё получилось, НО! ОЧЕНЬ и ОЧЕНЬ МЕДЛЕННО!
23 янв 07, 18:30    [3682871]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Встречный вопрос
Guest
verter
всё получилось, НО! ОЧЕНЬ и ОЧЕНЬ МЕДЛЕННО!


А

       select trunc(tb.DAT) DAT,
               sum(nvl(tb.AMOUNT,0)) AMOUNT
       from test_tbl tb
       where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND 
                                                 to_date('05.01.2007','dd.mm.yyyy')
       group by trunc(tb.DAT)

Работает быстро?
23 янв 07, 18:34    [3682897]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
Встречный вопрос


       select trunc(tb.DAT) DAT,
               sum(nvl(tb.AMOUNT,0)) AMOUNT
       from test_tbl tb
       where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND 
                                                 to_date('05.01.2007','dd.mm.yyyy')
       group by trunc(tb.DAT)

Работает быстро?


да!
23 янв 07, 18:38    [3682924]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
test_tbl вообще пустая для начала, так вот с этой пустой таблицей тот хитрый запрос дающий диапазон дат соединяется безумно долго.
23 янв 07, 18:39    [3682943]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
tru55
Member

Откуда: СПб
Сообщений: 19790
А зачем в pivot два all_objects, одного не хватило?
23 янв 07, 18:43    [3682962]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
кхе
23 янв 07, 18:45    [3682971]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Stax.
Guest
Stax.
что сложного?
упрощаем условия

select * from (
select trunc(tb.DAT) DAT,
       sum(nvl(tb.AMOUNT,0)) AMOUNT
from test_tbl tb
where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND to_date('05.01.2007','dd.mm.yyyy')
group by trunc(tb.DAT) ) t
right outer join
 (select to_date('01.01.2007','dd.mm.yyyy')+x-1 d from  pivot where x<=(to_date('05.01.2007','dd.mm.yyyy')-to_date('01.01.2007','dd.mm.yyyy'))+1) p  
on (dat=d)
order by d
/

DAT           AMOUNT D
---------- --------- ----------
01.01.2007       111 01.01.2007
                     02.01.2007
03.01.2007       333 03.01.2007
                     04.01.2007
05.01.2007       555 05.01.2007
.....
stax
23 янв 07, 18:48    [3682995]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
tru55
А зачем в pivot два all_objects, одного не хватило?


да, с одним тоже работает. Честно говоря, не понимаю этот запрос. Какую роль тут играет этот all_objects? И что вообще за таблица такая? Только не отсылайте к докам, плз.
23 янв 07, 18:51    [3683006]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
Stax.
Stax.
что сложного?
упрощаем условия

select * from (
select trunc(tb.DAT) DAT,
       sum(nvl(tb.AMOUNT,0)) AMOUNT
from test_tbl tb
where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND to_date('05.01.2007','dd.mm.yyyy')
group by trunc(tb.DAT) ) t
right outer join
 (select to_date('01.01.2007','dd.mm.yyyy')+x-1 d from  pivot where x<=(to_date('05.01.2007','dd.mm.yyyy')-to_date('01.01.2007','dd.mm.yyyy'))+1) p  
on (dat=d)
order by d
/

DAT           AMOUNT D
---------- --------- ----------
01.01.2007       111 01.01.2007
                     02.01.2007
03.01.2007       333 03.01.2007
                     04.01.2007
05.01.2007       555 05.01.2007
.....
stax



right outer join в 8-ке не работает, а у меня 8-ой оракл
23 янв 07, 18:52    [3683017]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Elic
Member

Откуда:
Сообщений: 29991
verter
Только не отсылайте к докам, плз.
При нежелании познавать, придётся верить
23 янв 07, 18:55    [3683029]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Stax.
Guest
verter

right outer join в 8-ке не работает, а у меня 8-ой оракл

(+)

select 
to_date('01.01.2007','dd.mm.yyyy')+x-1 d
,amount
 from (
select trunc(tb.DAT) DAT,
       sum(nvl(tb.AMOUNT,0)) AMOUNT
from test_tbl tb
where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND to_date('06.01.2007','dd.mm.yyyy')
group by trunc(tb.DAT) ) t
,pivot p where
x<=(to_date('06.01.2007','dd.mm.yyyy')-to_date('01.01.2007','dd.mm.yyyy'))+1
and dat(+)=to_date('01.01.2007','dd.mm.yyyy')+x-1
order by 1
/
SQL> /

D             AMOUNT
---------- ---------
01.01.2007       111
02.01.2007
03.01.2007       333
04.01.2007
05.01.2007       555
06.01.2007

6 rows selected.
......
stax
23 янв 07, 18:57    [3683040]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
Stax.


stax


не видит pivot и что за "x"?
23 янв 07, 18:59    [3683049]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
pivot
Guest
select DAT,
         sum(AMOUNT)
from
      (
       select trunc(tb.DAT) DAT,
               sum(nvl(tb.AMOUNT,0)) AMOUNT
       from test_tbl tb
       where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND 
                                                 to_date('05.01.2007','dd.mm.yyyy')
       group by trunc(tb.DAT)
      
      union
      
      select (startdate + LEVEL - 1) DAT, 0
      from (select to_date('01.01.2007','dd.mm.yyyy') startdate,
                      to_date('05.01.2007','dd.mm.yyyy') enddate
             from dual
            )
      CONNECT BY LEVEL <= enddate - startdate + 1
     ) 
group by dat
23 янв 07, 19:02    [3683070]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Stax.
Guest
Elic


+ вариант (для меня основной)
пивот ето
любая (специальная) таблица/вю в которой гарантировано есть n-строк

......
stax
23 янв 07, 19:03    [3683071]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Stax.
Guest
verter

не видит pivot и что за "x"?

SQL> desc pivot
 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        NUMBER(38)

SQL> 
select rownum x from ЛЮБАЯ_ТБЛИЦА_В_КОТОРОЙ_СТРОК_БОЛЬШЕ_ДИАПАЗОНА
where rownum<=<=(to_date('06.01.2007','dd.mm.yyyy')-to_date('01.01.2007','dd.mm.yyyy'))+1

.....
stax
23 янв 07, 19:09    [3683093]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
stax.
Guest
пример любой
select 
to_date('01.01.2007','dd.mm.yyyy')+x-1 d
,amount
 from (
select trunc(tb.DAT) DAT,
       sum(nvl(tb.AMOUNT,0)) AMOUNT
from test_tbl tb
where trunc(tb.DAT) BETWEEN to_date('01.01.2007','dd.mm.yyyy') AND to_date('06.01.2007','dd.mm.yyyy')
group by trunc(tb.DAT) ) t
,(select rownum x 
from test_tbl,test_tbl pivot 
where
rownum<=(to_date('06.01.2007','dd.mm.yyyy')-to_date('01.01.2007','dd.mm.yyyy'))+1) p
where dat(+)=to_date('01.01.2007','dd.mm.yyyy')+p.x-1
order by 1
/
SQL> /

D             AMOUNT
---------- ---------
01.01.2007       111
02.01.2007
03.01.2007       333
04.01.2007
05.01.2007       555
06.01.2007

6 rows selected.
ps
рука дрогнула преждевременно
.....
stax
23 янв 07, 19:19    [3683127]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
verter
Member

Откуда: Санкт-Петербург
Сообщений: 1018
спасибо! Теперь понятно. Пример с connect by работает отлично - то что доктор прописал!
24 янв 07, 12:01    [3686115]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
клю
Guest
почти по теме.
нужна помощь.
как две даты вычесть? они у меня забиваются в таблицу полностью, с точным временем. т.е. моя задача - выцепить пару данных из таблицы (из столбца from взять дату и время остановки техники и вычесть из них дату и время столбца to). и чтобы все точно было. даты могут отличаться или быть одинаковыми, время различается обязательно. надо, чтобы вы давался ответ примерно так:
64:25:13
т.е. простой длился 64 часа, 25 минут, 13 секунд, в днях считать не надо.
кто-нить может подсказать?
24 янв 07, 12:10    [3686230]     Ответить | Цитировать Сообщить модератору
 Re: запрос возвращающий диапазон дат  [new]
Бабичев Сергей
Member

Откуда:
Сообщений: 2498
клю
почти по теме.
нужна помощь.
как две даты вычесть? они у меня забиваются в таблицу полностью, с точным временем. т.е. моя задача - выцепить пару данных из таблицы (из столбца from взять дату и время остановки техники и вычесть из них дату и время столбца to). и чтобы все точно было. даты могут отличаться или быть одинаковыми, время различается обязательно. надо, чтобы вы давался ответ примерно так:
64:25:13
т.е. простой длился 64 часа, 25 минут, 13 секунд, в днях считать не надо.
кто-нить может подсказать?
Да каждый первый тебе подскажет:
with my_v 
  as (
        select sysdate as d2,
               sysdate - 1 - 5/24/60 - 20/24/60/60 as d1 
          from dual
      )
-- Описание макета данных завершенно
--
-- Непосредственно сам запрос:
select my_v.*,
       trunc((d2 - d1) * 24)||' ч. '||
       to_char(trunc(d2) + (d2 - d1),'mi "мин." ss "сек."') as delta_time
from my_v

Query finished, retrieving results...

         D2                      D1               DELTA_TIME                          
--------------------    --------------------    --------------------
24-янв-2007 16:26:01    23-янв-2007 16:20:41    24 ч. 05 мин. 20 сек.                                       

1 row(s) retrieved 
24 янв 07, 12:29    [3686414]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Oracle Ответить