VPF::Посчитать средднее значение за сутки

KAV2008

Дата 26.5.2011, 11:45 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 56
Регистрация: 26.6.2008

Репутация: нет
Всего: нет

Помогите доработать запрос
Есть таблица tag часовых значений
ID тега , значание val и дата revision, тип архивирования type_id (1 сумма, 2 - среднее)
необходимо в зависимости от type_id посчитать за сутки (с 8 до 8) сумму или среднее по часам
с учетом того что могут быть нулевые значения (сейча просто делиться на 24 часа)

Код


 select a1.tag_id
, round( SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
                          AND (a1.type_id = 1)  THEN val
                           ELSE val / 24 END), 2) "за сутки"
  from    tag a1
  where (a1.revision BETWEEN TO_DATE ('15.05.2011 8:00:00',
                                       'DD/MM/YYYY HH24:MI:SS'     )
                          AND TO_DATE ('16.05.2011 7:00:00',
                                       'DD/MM/YYYY HH24:MI:SS'   ) )

GROUP BY a1.tag_id

Т.Е. как изменить запрос чтобы val / 24 делить не на 24 часа а на количество ненулевых значений

Спасибо

Zloxa

Дата 26.5.2011, 12:29 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(KAV2008 @ 26.5.2011, 11:45

)

как изменить запрос чтобы val / 24 делить не на 24 часа а на количество ненулевых значений

вспомним математику школьного курса:

Код


a + b/24 + c + d/24 = (a+c) + (b/24+d/24) = (a+c) + (b+d)/24

по этому же принципу выражение

Код


SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
                          AND (a1.type_id = 1)  THEN val
                           ELSE val / 24 END)

меняем на

Код


SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
               AND (a1.type_id = 1)  
          THEN val
          ELSE 0
      END)
+SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
               AND (a1.type_id = 1)  
          THEN 0
          ELSE val
      END) / 24

Теперь нам не составит никакого труда заменить 24 на count(nullif(val,0)) - количество ненулевых

Это сообщение отредактировал(а) Zloxa - 26.5.2011, 12:30

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

KAV2008

Дата 26.5.2011, 13:11 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 56
Регистрация: 26.6.2008

Репутация: нет
Всего: нет

Спасибо за ответы
Вот готовый запрос

Код


select tt1.tag_id, tt1.type_id , 
, case when tt1.type_id = 1  then tt1.val_sum
else tt1.val_sum/tt1.kol
end "за сутки"
from
  
 (select a1.tag_id, a1.type_id 
 
, round( SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
                           THEN val
                           END), 2) val_sum
 
, round( SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
                          AND (a1.val>0 )  THEN 1
                           ELSE 0 END), 2) kol
                           
 
 
 from    tag a1
  where (a1.revision BETWEEN TO_DATE ('15.05.2011 8:00:00',
                                       'DD/MM/YYYY HH24:MI:SS'     )
                          AND TO_DATE ('16.05.2011 7:00:00',
                                       'DD/MM/YYYY HH24:MI:SS'   ) )

GROUP BY a1.tag_id , a1.type_id )tt1


ORDER BY 1;

KAV2008

Дата 26.5.2011, 13:34 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 56
Регистрация: 26.6.2008

Репутация: нет
Всего: нет

А если подставить вместо 24 выражение count(nullif(val,0))
выдает ошибку ORA-00937: групповая функция не является одногруппной
Исправить пока не получилось

Zloxa

Дата 26.5.2011, 13:40 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(KAV2008 @ 26.5.2011, 13:34

)

А если подставить вместо 24 выражение count(nullif(val,0))
выдает ошибку ORA-00937: групповая функция не является одногруппной

что я делаю не так? smile

Код


SQL> with tag as (select 1 tag_id,date '2011-05-16' revision, 1 val,1 type_id from dual)
  2   select a1.tag_id
  3  , round( SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
  4                 AND (a1.type_id = 1)
  5            THEN val
  6            ELSE 0
  7        END)
  8        +SUM (CASE WHEN (TO_NUMBER (TO_CHAR (a1.revision, 'HH24')) BETWEEN 0 AND 23 )
  9                       AND (a1.type_id = 1)
 10                  THEN 0
 11                  ELSE val
 12              END) / count(nullif(val,0))
 13          , 2) "за сутки"
 14    from    tag a1
 15    where (a1.revision BETWEEN TO_DATE ('15.05.2011 8:00:00',
 16                                         'DD/MM/YYYY HH24:MI:SS'     )
 17                            AND TO_DATE ('16.05.2011 7:00:00',
 18                                         'DD/MM/YYYY HH24:MI:SS'   ) )
 19  GROUP BY a1.tag_id
 20  ;
 
    TAG_ID   за сутки
---------- ----------
         1          1

Однако ж, судя по тому что написано в "готовом" запросе, Вам нужно совсем не то, что Вы попросили. smile

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »