Усреднение в sql [решено]

marat7256

Короче, туплю.
Задача тривиальная. Есть таблица в оракле, грубо две колонки: одна - дата/время, другая сами данные.
Время отличается на минуту (минутные данные). Хочется получить усреднения данных за пять минут.
Помогите тупице!

kill-still

Ну это детский сад. :)


SELECT o.*,

       (SELECT AVG (i.myvalue)

          FROM mytable i

         WHERE abs (datediff (minute, o.measuretime, i.measuretime <= 5 )

  FROM mytable o

Время отличается на минуту (минутные данные).
это значит, что точность измерения времени равняется одной минуте, или в одну минуту одно измерение? Если второе, можно оптимизировать.

marat7256

это значит, что точность измерения времени равняется одной минуте, или в одну минуту одно измерение? Если второе, можно оптимизировать.
Одно измерение в минуту.

stm5643616

select trunc(dt)+truncdt-trunc(dt*24*12)/24/12, avg(val) group by trunc(dt)+truncdt-trunc(dt*24*12)/24/12

kill-still

Ой, сори, я для MS SQL написал. :)
Сейчас исправлюсь.

kill-still



  SELECT     TRUNC (last_sys_date)

           + TRUNC last_sys_date - TRUNC (last_sys_date * 24 * 12) / 24 / 12,

           AVG (ID)

      FROM document

  GROUP BY   TRUNC (last_sys_date)

           + TRUNC last_sys_date - TRUNC (last_sys_date * 24 * 12) / 24 / 12

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

    

  Statement Id=4203172   Type=

  Cost=2,64018716311899E-308  TimeStamp=22-03-12::13::09:08

  

       (1)  SELECT STATEMENT  ALL_ROWS 

     Est. Rows: 302  Cost: 802

       (3)  HASH GROUP BY 

     Est. Rows: 302  Cost: 802

           (2)  TABLE TABLE ACCESS FULL AZKPLAN.DOCUMENT  [Analyzed] 

           (2)   Blocks: 3 604 Est. Rows: 112 202 of 112 202  Cost: 796 

                Tablespace: USERS





  SELECT o.LAST_SYS_DATE,

         (SELECT AVG (i.id)

            FROM document i

           WHERE abs (i.LAST_SYS_DATE - o.LAST_SYS_DATE) <= 0.00347 )

    FROM document o

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

    

  Statement Id=4203172   Type=

  Cost=2,64018716311899E-308  TimeStamp=22-03-12::13::09:47

  

       (1)  SELECT STATEMENT  ALL_ROWS 

     Est. Rows: 112 202  Cost: 796

       (3)  SORT AGGREGATE 

     Est. Rows: 1

           (2)  TABLE TABLE ACCESS FULL AZKPLAN.DOCUMENT  [Analyzed] 

           (2)   Blocks: 3 604 Est. Rows: 5 610 of 112 202  Cost: 800 

                Tablespace: USERS

       (4)  TABLE TABLE ACCESS FULL AZKPLAN.DOCUMENT  [Analyzed] 

       (4)   Blocks: 3 604 Est. Rows: 112 202 of 112 202  Cost: 796 

     Tablespace: USERS

Что ещё раз подтверждает, что не надо ничего без профилировщика оптимизировать. :)

kill-still

Твой запрос кстати немного другое делает. Он дробит таблицу на интервалы по 5 минут, и уже по этим отрезкам среднее считает. :)
UPD:
К тому же вот эта херь: group by trunc(dt)+truncdt-trunc(dt*24*12)/24/12 даже будучи запихнута в partition by поломает доступ по индексу и такой запрос будет раз в N дольше обрабатываться. (где N=количество строк в таблице)

stm5643616

Да я не понял, что ему надо, я так примерно написал с мобильного...

kill-still

Я поколдовал, оптимизировать получается только в случае отсутствия пропусков и то мизерно.
Не забудь индекс на колонку со временем добавить и обратить внимание на то, что null в AVG будет отбрасываться. (если его надо тоже считать, то тогда так: AVG (nvl(i.amount,0

marat7256

Спасибо, разобрался.
Но, блин, мееееедленно!

mbolik1

Учим аналитические функции:
 
SELECT d.LAST_DDL_TIME

, AVG ( d.object_id ) over ( order by d.LAST_DDL_TIME range BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT row ) av

FROM dba_objects d;

marat7256

Учим аналитические функции:
 
1
SELECT d.LAST_DDL_TIME
2
, AVG ( d.object_id ) over ( order by d.LAST_DDL_TIME range BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT row ) av
3
FROM dba_objects d;
Не совсем то. Получается, что выдаются все значения времени, а мне надо, чтобы число строк сократилось в 5 раз, то есть усреднение происходило бы в интервале и каждый интервал был одной строчкой в выдаче. То есть предыдущий вариант давал правильный результат. Вот тренировка на кошках:
 
 

select trunc"dt_record"-trunc("dt_record"*6)/6+trunc("dt_record") as "dt", avg("aeindex") as "ae" from "omni2"

group by trunc"dt_record"-trunc("dt_record"*6)/6+trunc("dt_record") 

order by trunc"dt_record"-trunc("dt_record"*6)/6+trunc("dt_record");

Тут часовые данные усредняю за 4 часа, при этом из каждых четырех строк остается одна.
Возможно поможет "partition", но пока не фтыкаю.
Переписал твой вариант, для тестовых прогонов:
 
 

select "dt_record", 

avg("aeindex") over (order by "dt_record" range between current row and interval '3' hour following) av 

from "omni2";

Выдает правильный результат в узловых точках, но есть еще промежуточные значения, которые хчется убрать.
Вот два вывода:
1963-01-01 00:00:00 94.75
1963-01-01 04:00:00 66
1963-01-01 08:00:00 72.5
1963-01-01 12:00:00 100
...
и
1963-01-01 00:00:00 94.75 <-
1963-01-01 01:00:00 88.75
1963-01-01 02:00:00 67.25
1963-01-01 03:00:00 63.75
1963-01-01 04:00:00 66 <-
1963-01-01 05:00:00 70.5
1963-01-01 06:00:00 75.25
1963-01-01 07:00:00 76.25
1963-01-01 08:00:00 72.5 <-
1963-01-01 09:00:00 55
1963-01-01 10:00:00 55.75
1963-01-01 11:00:00 79.5
1963-01-01 12:00:00 100 <-

mbolik1

а мне надо, чтобы число строк сократилось в 5 раз
Значит я не правильно понял, тогда вариант с group by.
 
Возможно поможет "partition", но пока не фтыкаю.

Не поможет, аналитические функции не уменьшают количества строк
Но, блин, мееееедленно!

Это с group by медленно?

marat7256

Это с group by медленно?
Да запроса, с group by и c over, именно те, что написаны в предыдущем посте: один выполняется 1.5 секунды, другой 0.22 секунды.

mbolik1

Тогда если данные плотные:


SELECT "dt_record"

, av

FROM

  (SELECT "dt_record"

  , AVG ( "aeindex" ) over ( order by "dt_record" range BETWEEN CURRENT row AND interval '3' hour following ) av

  FROM "omni2"

  )

WHERE TO_CHAR ( "dt_record", 'hh24' ) IN ( '00', '04', '08', '12', '16', '20' ) ;

marat7256

Йййййес!
Время выполнения 0.22 секунды!
Сам почти до этого дошел, чуток не хватило. Последний вариант был:
 
 

select "dt_record", "av" from

(select "dt_record", avg("aeindex") over ( 

order by "dt_record" range between current row and interval '3' hour following) "av"

from "omni2")

where "dt_record" in (select trunc"dt_record"-trunc("dt_record"*6)/6+trunc("dt_record") from "omni2");

mbolik1

В качестве дальнейших усовершенствований, должно чуть ускорить.
Будет работать если строго по 1 записи в час.
 
SELECT "dt_record"

, av

FROM

  (SELECT "dt_record"

  , AVG ( "aeindex" ) over ( order by "dt_record" rows BETWEEN CURRENT row AND 3 following ) av

  FROM "omni2"

  )

WHERE TO_CHAR ( "dt_record", 'hh24' ) IN ( '00', '04', '08', '12', '16', '20' ) ;

kill-still

ты крут, я только так умею:
 


SELECT o.ID, AVG (o.amaunt) OVER (PARTITION BY TRUNC (o.last_sys_date * 288

  FROM document o

Но спрашивается нафига :) : 


  SELECT   d.ID,

           AVG (d.amount) OVER (ORDER BY d.last_sys_date RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW)

                                                                             av

      FROM document d

  ORDER BY d.ID    

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

    

  Statement Id=4203172   Type=

  Cost=2,64018716311899E-308  TimeStamp=22-03-12::18::16:55

  

       (1)  SELECT STATEMENT  ALL_ROWS 

     Est. Rows: 112 202  Cost: 2 296

       (4)  SORT ORDER BY 

     Est. Rows: 112 202  Cost: 2 296

           (3)  WINDOW SORT 

                Est. Rows: 112 202  Cost: 2 296

               (2)  TABLE TABLE ACCESS FULL AZKPLAN.DOCUMENT  [Analyzed] 

               (2)   Blocks: 3 604 Est. Rows: 112 202 of 112 202  Cost: 796 

                    Tablespace: USERS
Оставить комментарий
Имя или ник:
Комментарий: