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

Откуда:
Сообщений: 47
У меня есть очень большая таблица состоящея из Obj_ID, DataTime(aeg), ModemMAC, Value(stat). Группы состоят из одинаковых Obj_ID которые в свою очередь делятся на промежутки DataTime в которых записана дата с интервалом по 15 минут. ModemMAC это идентификатор устройства Value соответственно его значение. На самом деле полей значений много, но это ничего не меняет.

Мне надо получить среднее количество модемов, оно может менятся в 15мин промежутках и также количество работяющих модемов stat =6.
Условный код:
SELECT TRUNC(sysdate) Paev, mon.obj_id,
(
SELECT avg(availability) availability
FROM (
SELECT sum(DECODE(stat,6,1,0)) availability
FROM Monitor
WHERE obj_id = MON.obj_id
AND aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
GROUP BY aeg
)
) availability,
(
SELECT Trunc(avg(Count(*)))
FROM Monitor
WHERE obj_id = MON.obj_id
AND aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
GROUP BY aeg
) num_modems
FROM (
SELECT obj_id, aeg, stat
FROM Monitor
WHERE aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
) MON
GROUP BY MON.obj_id;
Как я понимаю надо использовать аналитические функции, я пробовал чтото вроде:
SELECT TRUNC(sysdate) Paev, obj_id, Trunc(avg(Count(*) over (PARTITION BY aeg)) )
FROM Monitor
WHERE aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
GROUP BY obj_id;
Но не работает.
9 янв 07, 17:18    [3617678]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8130
Erik
У меня есть очень большая таблица состоящея из Obj_ID, DataTime(aeg), ModemMAC, Value(stat). Группы состоят из одинаковых Obj_ID которые в свою очередь делятся на промежутки DataTime в которых записана дата с интервалом по 15 минут. ModemMAC это идентификатор устройства Value соответственно его значение. На самом деле полей значений много, но это ничего не меняет.

Мне надо получить среднее количество модемов, оно может менятся в 15мин промежутках и также количество работяющих модемов stat =6.
Условный код:
	    SELECT TRUNC(sysdate) Paev, mon.obj_id, 
			 (
			  SELECT avg(availability) availability
			  FROM ( 
			 	 SELECT sum(DECODE(stat,6,1,0)) availability  
				 FROM Monitor 
				 WHERE obj_id = MON.obj_id
				 AND aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
	 			 GROUP BY aeg
				   )
			 ) availability,
		    (
		     SELECT  Trunc(avg(Count(*)))   
			 FROM Monitor 
			 WHERE obj_id = MON.obj_id
			 AND aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
			 GROUP BY aeg
			) num_modems 
        FROM (
			  SELECT obj_id, aeg, stat 
			  FROM Monitor
			  WHERE aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
			 ) MON
		GROUP BY MON.obj_id;     
Как я понимаю надо использовать аналитические функции, я пробовал чтото вроде:
SELECT TRUNC(sysdate) Paev, obj_id, Trunc(avg(Count(*) over (PARTITION BY aeg)) )  
FROM Monitor
WHERE aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
GROUP BY obj_id;
Но не работает.
Используйте тег SRC. Это придает вашему тексту читаемость! :-)
9 янв 07, 17:21    [3617709]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116070
Данных нет, поэтому посмотрите этот нетестированный псевдокод.
Правильно ли я понял ?

select obj_id, avg(cnt1), sum(cnt2)
from
(
select obj_id, datetime, (select count(*) over (partition by obj_id, datetime) cnt from tab1 
                where obj_id = t1.obj_id and stat = 6) cnt2,
       count(*) over (partition by obj_id, datetime) cnt1
       from tab1 t1       
)
group by obj_id    
9 янв 07, 17:30    [3617778]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Elic
Member

Откуда:
Сообщений: 29976
Бррр.. Не нужны здесь ни скалярные подзапросы, ни аналитика. А нужно просто два раза сгруппировать:
1) sum()...group by obj_id, eag
2) avg(sum)...group by obj_id
9 янв 07, 17:31    [3617786]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Erik
Member [заблокирован]

Откуда:
Сообщений: 47
Данные то есть:

OBJ_ID DPWR UPWR DSNR USNR AEG STAT SEADE_ID TIME1
10520 65457 525 354 319 08.01.2007 16:15:00 6 246539227 72
10520 65463 530 372 303 08.01.2007 16:15:00 6 247714865 138
10520 65 368 385 295 08.01.2007 16:15:00 6 253662345 86
10520 65501 483 379 299 08.01.2007 16:15:00 6 253804358 149
10520 65491 510 382 299 08.01.2007 16:15:00 6 249819579 95
10520 65511 485 383 299 08.01.2007 16:15:00 6 257617732 156
10520 65432 530 362 293 08.01.2007 16:15:00 6 260082542 7
10520 65432 473 366 287 08.01.2007 16:15:00 6 257753834 228
10520 65454 438 375 299 08.01.2007 16:15:00 6 272638145 29
10520 65490 555 347 303 08.01.2007 16:15:00 6 234921393 92
10520 65504 528 360 303 08.01.2007 16:15:00 6 229406062 197
........

Повторяются только с другим временем 16:30:00
9 янв 07, 17:55    [3617963]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Erik
Member [заблокирован]

Откуда:
Сообщений: 47
Косменические изменения в запросе, но запрос неработает. Возвращеет вольше одной строки в подзапросе.

select obj_id, avg(num_modems), sum(availability)
from
(
select obj_id,
(
select count(*) over (partition by obj_id, aeg) cnt from monitor
WHERE obj_id = t1.obj_id and stat = 6
AND aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
) availability,
count(*) over (partition by obj_id, aeg) num_modems
from monitor t1
WHERE aeg BETWEEN Trunc(sysdate) and Trunc(sysdate)+1-1/(24*60*60)
)
group by obj_id
9 янв 07, 18:06    [3618022]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Erik
Member [заблокирован]

Откуда:
Сообщений: 47
To dmidek
И снтаксически запрос неправильный, нельзя применять аналитические функции и гркпировку одновременно.
9 янв 07, 18:14    [3618062]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Elic
Member

Откуда:
Сообщений: 29976
Erik
Возвращеет вольше одной строки в подзапросе
Ты читаешь, что тебе пишут?
select obj_id, avg(num_modems), sum(availability)
  from
  ( select obj_id, count(decode(stat, 6, 0)) as availability, count(*) as num_modems
      from monitor
      where aeg between trunc(sysdate) and trunc(sysdate)+1-1/(24*60*60) 
      group by obj_id, aeg
  )
  group by obj_id
9 янв 07, 18:14    [3618067]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Elic
Member

Откуда:
Сообщений: 29976
Erik
нельзя применять аналитические функции и гркпировку одновременно.
Да ну
select owner, object_type, avg(min(object_id)) over (partition by owner) from all_objects
  group by owner, object_type;
9 янв 07, 18:22    [3618112]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Erik
Member [заблокирован]

Откуда:
Сообщений: 47
To Elic
Почти правильно, сейчас попровляю availability это среднее количество модемов в группе.
9 янв 07, 18:34    [3618155]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Erik
Member [заблокирован]

Откуда:
Сообщений: 47
To Elic
Все поправил и работает, большое спасибо.
9 янв 07, 18:36    [3618170]     Ответить | Цитировать Сообщить модератору
 Re: Как получить количество значений в группе и наложить гупировку с AVG?  [new]
Elic
Member

Откуда:
Сообщений: 29976
Erik
To Elic
Почти правильно, сейчас попровляю availability это среднее количество модемов в группе.
У тебя семь вторников на неделе: это твоя же формулировка :)
9 янв 07, 18:43    [3618188]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить