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

Откуда:
Сообщений: 301
Здравия коллеги!

автор
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production


Помогите с запросом, что-т блин идей, кроме громоздких в голову не приходит.
Значит есть таблица - история событий. Упрощённо такая:
автор
ID | INSERTTS | Param1 | Param2 |
---------------------------------------------------------------------------
10 | 12-DEC-17 11.10.25.989000000 PM | 0 | 1 |
11 | 12-DEC-17 11.20.25.989000000 PM | 1 | 0 |
12 | 12-DEC-17 11.30.25.989000000 PM | 1 | 0 |
13 | 12-DEC-17 12.12.25.989000000 PM | 1 | 1 |
14 | 12-DEC-17 12.34.25.989000000 PM | 0 | 1 |
15 | 12-DEC-17 12.50.25.989000000 PM | 0 | 1 |
16 | 12-DEC-17 12.55.25.989000000 PM | 1 | 1 |
...
nm | 12-DEC-17 13.50.25.989000000 PM | 1 | 1 |


Задача:
1. Найти суммарное количество строк в каждом часе с Param1 =1. (То есть сгруппировать по часам, дням, месяцам и годам)
2. Найти суммарное количество строк в каждом часе с Param2 =1.
3. Поделить количество строк в пункте 2 на количество строк в пункте 1. То есть на выходе мне нужен результат деления также сгруппированный соответственно.

Мой запрос, который не пашет, так как псевдонимы нельзя использовать там где хочется:

SELECT s1.INSERTTS,
to_char(s1.INSERTTS, 'YYYY') as Year,
to_char(s1.INSERTTS, 'MM') as Month,
to_char(s1.INSERTTS, 'DD') as Day,
to_char(s1.INSERTTS, 'HH24')as Hour,
COUNT(to_number(to_char(s1.INSERTTS, 'ddmmyyyyhh24')))/COUNT(to_number(to_char(s2.INSERTTS, 'ddmmyyyyhh24'))) 
FROM DM_PARCEL s1
INNER JOIN 
(
SELECT INSERTTS,
to_char(INSERTTS, 'YYYY') as Year,
to_char(INSERTTS, 'MM') as Month,
to_char(INSERTTS, 'DD') as Day,
to_char(INSERTTS, 'HH24')as Hour,
COUNT(to_number(to_char(INSERTTS, 'ddmmyyyyhh24'))) as "Total"
FROM DM_PARCEL 
WHERE INSERTTS >= to_date('13-12-2017 18.00.00','dd-mm-yyyy hh24:mi:ss')
AND INSERTTS <= to_date('14-12-2017 02.00.00','dd-mm-yyyy hh24:mi:ss')
AND Param2=1
GROUP BY 
to_number(to_char(INSERTTS, 'ddmmyyyyhh24'))
,to_char(INSERTTS, 'YYYY'),
to_char(INSERTTS, 'MM'),
to_char(INSERTTS, 'DD'),
to_char(INSERTTS, 'HH24')
) s2
ON  s1.Year=s2.Year and s1.Month=s2.Month and s1.Day=s2.Day and s1.Hour=s2.Hour
WHERE s1.INSERTTS >= to_date('13-12-2017 18.00.00','dd-mm-yyyy hh24:mi:ss')
AND s1.INSERTTS <= to_date('14-12-2017 02.00.00','dd-mm-yyyy hh24:mi:ss')
AND s1.Param1=1
GROUP BY s1.INSERTTS, (to_number(to_char(s1.INSERTTS, 'ddmmyyyyhh24')) )
, to_char(s1.INSERTTS, 'YYYY'),
to_char(s1.INSERTTS, 'MM'),
to_char(s1.INSERTTS, 'DD'),
to_char(s1.INSERTTS, 'HH24')
ORDER BY to_char(s1.INSERTTS, 'DD');


по отдельности SELECTы работают.
Проблема в части:
ON  s1.Year=s2.Year and s1.Month=s2.Month and s1.Day=s2.Day and s1.Hour=s2.Hour

Так как через псевдонимы Year, Month... я не могу сослаться на колонки, а точнее даже на кусок даты в колонке.
Но, блин, мне нужно группировать по часам с одной стороны, а с другой надо как-то сослаться на поле по которому надо "сджоиниться" двум выборкам. А join у них как раз по дате до уровня часа.

Помогите идеей, как реализовать.

P.S.: уменьшал базовый запрос, может где-то есть опечатки, сорри.
14 дек 17, 22:11    [21035158]     Ответить | Цитировать Сообщить модератору
 Re: Объединить результаты 2-х запросов к одной таблице и выполнить мат. действие  [new]
Усталый разработчик
Member

Откуда: до горизонта протянуть надежды рвущуюся нить
Сообщений: 142
Param1 и Param2 могут принимать только значения 1 или 0?
14 дек 17, 23:29    [21035328]     Ответить | Цитировать Сообщить модератору
 Re: Объединить результаты 2-х запросов к одной таблице и выполнить мат. действие  [new]
rtyts
Member

Откуда:
Сообщений: 301
Усталый разработчик, нет это просто для примера я упростил. Да и кроме того, самих параметров больше. То есть SELECTы строятся более, чем на основе одного параметра.
14 дек 17, 23:37    [21035343]     Ответить | Цитировать Сообщить модератору
 Re: Объединить результаты 2-х запросов к одной таблице и выполнить мат. действие  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
+
tst> create table t1 as
  2  select sysdate-rownum*15/60/24 dt, mod(rownum, 2) p1, sign(mod(rownum, 3)) p2
  3  from dual connect by level <= 15;

Table created.

tst> select * from t1 order by 1;

DT                          P1         P2
------------------- ---------- ----------
15-12-2017 04:59:24          1          0
15-12-2017 05:14:24          0          1
15-12-2017 05:29:24          1          1
15-12-2017 05:44:24          0          0
15-12-2017 05:59:24          1          1
15-12-2017 06:14:24          0          1
15-12-2017 06:29:24          1          0
15-12-2017 06:44:24          0          1
15-12-2017 06:59:24          1          1
15-12-2017 07:14:24          0          0
15-12-2017 07:29:24          1          1
15-12-2017 07:44:24          0          1
15-12-2017 07:59:24          1          0
15-12-2017 08:14:24          0          1
15-12-2017 08:29:24          1          1

15 rows selected.

tst> with int as (select trunc(dt, 'hh24') dt_hour, sum(p1) p1_one, sum(p2) p2_one
  2               from t1 group by trunc(dt, 'hh24')
  3  )
  4  select dt_hour, p1_one, p2_one, decode(p1_one, 0, 0, p2_one/p1_one) div
  5  from int order by 1;

DT_HOUR                 P1_ONE     P2_ONE        DIV
------------------- ---------- ---------- ----------
15-12-2017 04:00:00          1          0          0
15-12-2017 05:00:00          2          3        1.5
15-12-2017 06:00:00          2          3        1.5
15-12-2017 07:00:00          2          2          1
15-12-2017 08:00:00          1          2          2
15 дек 17, 01:47    [21035475]     Ответить | Цитировать Сообщить модератору
 Re: Объединить результаты 2-х запросов к одной таблице и выполнить мат. действие  [new]
rtyts
Member

Откуда:
Сообщений: 301
Вячеслав Любомудров, спасибо! И главное я ж подумал ещё, что в MS SQL SRV я в таких случаях использую CTE. Но почему-то поспешно решил с ORACLE это не прокатит. Безосновательно, как оказалось.
15 дек 17, 09:52    [21035826]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить