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

Есть данные вида date- amount

select to_date('01/12/2010', 'DD/MM/YYYY') + level - 1 created, level amount from dual
        connect by level < 100


01/01/2011 - avg( sum(amount) for 01/01/2011, sum(amount) from 03/12/2010 to 31/12/2010 )
02/01/2011 - avg( 01/01/2011 результат, sum(amount) from 04/12/2010 to 31/12/2010 )
03/01/2011 - avg( 01/01/2011 результат, 02/01/2011 результат, sum(amount) from 05/12/2010 to 31/12/2010 )

дебильная логика, она сейчас в Excel реализована, надо перенести в Oracle...
1 ноя 11, 15:07    [11533513]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
AmKad
Member

Откуда:
Сообщений: 5222
англикот
sum(amount) from 03/12/2010 to 31/12/2010 )
sum(amount) from 04/12/2010 to 31/12/2010 )
sum(amount) from 05/12/2010 to 31/12/2010 )

Аналитическая sum + windowing clause.
1 ноя 11, 15:22    [11533664]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
AmKad
Member

Откуда:
Сообщений: 5222
англикот,

Далее
04/01/2011 - avg( 01/01/2011 результат, 02/01/2011 результат, 03/01/2011 результат, sum(amount) from 06/12/2010 to 31/12/2010 )
или
04/01/2011 - avg( 02/01/2011 результат, 03/01/2011 результат, sum(amount) from 06/12/2010 to 31/12/2010 )
1 ноя 11, 16:07    [11534065]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
AmKad
Member

Откуда:
Сообщений: 5222
англикот,

?
1 ноя 11, 16:33    [11534298]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
AmKad,

далее
04/01/2011 - avg( 01/01/2011 результат, 02/01/2011 результат, 03/01/2011 результат, sum(amount) from 06/12/2010 to 31/12/2010 )

даже не знаю как подступиться... уже стал думать использовать APEX коллекцию для хранения промежуточных результатов, но это как то не айс...
1 ноя 11, 16:53    [11534470]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
Ramin Hashimzade
Member

Откуда: Азербайджан, Баку
Сообщений: 9979
Блог
про AVG понял...
можно так:
AVG(amount) OVER (ORDER BY 1 ROWS UNBOUNDED PRECEDING) AVG
но про sum честно говоря не понял...

sum(amount) for 01/01/2011, sum(amount) from 03/12/2010 to 31/12/2010
что означает?
1 ноя 11, 17:09    [11534572]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
Ramin
про AVG понял...
можно так:
AVG(amount) OVER (ORDER BY 1 ROWS UNBOUNDED PRECEDING) AVG
но про sum честно говоря не понял...

sum(amount) for 01/01/2011, sum(amount) from 03/12/2010 to 31/12/2010
что означает?


дело в том, что для каждого дня может быть несколько строк, в моем примере там генерится одна строка для каждого дня.
1 ноя 11, 17:23    [11534680]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
Ramin Hashimzade
Member

Откуда: Азербайджан, Баку
Сообщений: 9979
Блог
англикот
Ramin
про AVG понял...
можно так:
AVG(amount) OVER (ORDER BY 1 ROWS UNBOUNDED PRECEDING) AVG
но про sum честно говоря не понял...

sum(amount) for 01/01/2011, sum(amount) from 03/12/2010 to 31/12/2010
что означает?


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


тоесть например если есть:


01.01.2011 5
01.01.2011 3
01.01.2011 4
02.01.2011 5
02.01.2011 1
03.01.2011 10
03.01.2011 11


нужно

01.01.2011 12
02.01.2011 avg(12,6) = 9
03.01.2011 avg(12,9,22) = 14.3

так? правильно понял?!
1 ноя 11, 17:31    [11534754]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
Ramin
англикот
пропущено...


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


тоесть например если есть:


01.01.2011 5
01.01.2011 3
01.01.2011 4
02.01.2011 5
02.01.2011 1
03.01.2011 10
03.01.2011 11


нужно

01.01.2011 12
02.01.2011 avg(12,6) = 9
03.01.2011 avg(12,9,22) = 14.3

так? правильно понял?!


почти верно, за исключением

01.01.2011 avg(12, сумма того что с 3 до 31 декабря)
02.01.2011 avg(12, 6, сумма того что с 4 до 31 декабря)
03.01.2011 avg(12, 6, 22, сумма того что с 5 до 31 декабря)
1 ноя 11, 17:42    [11534843]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
03.01.2011 avg(12, 6, 21, сумма того что с 5 до 31 декабря)
1 ноя 11, 17:44    [11534856]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
AmKad
Member

Откуда:
Сообщений: 5222
англикот,

+
avg(amount) over (partition by trunc(created, 'month') order by created range between unbounded preceding and current row) f1,
sum(amount) over (partition by trunc(created, 'month') order by created range between 2 following and unbounded following) f2
Далее (f1 + f2) / 2
1 ноя 11, 18:35    [11535298]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
AmKad,

select 
    created,
    avg(amount) over (partition by trunc(created, 'month') order by created  range between unbounded preceding and current row) f1,
    sum(amount) over (partition by trunc(created, 'month') order by created  range between 2 following and unbounded following) f2
from (
select to_date('01/12/2010', 'DD/MM/YYYY') + level - 1 created, level amount from dual
connect by level < 100
) 

промежуточные результаты не похожи на правду :(
1 ноя 11, 19:58    [11535648]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
я наверно запутал всех

на самом деле мне надо среднее значение 30 дней назад, начиная с 1 января

например
1.01.2011 - avg(с 3 декабря по 1 января)
2.01.2011 - avg(с 4 декабря по 2 января)
3.01.2011 - avg(с 5 декабря по 3 января)
и т.д.

надо использовать avg + windowing clause, но пока не получается
1 ноя 11, 20:40    [11535796]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
вот так почти работает
select 
    created,
    avg(amount) over (order by created  range between unbounded preceding and current row) f1
from (
select to_date('01/12/2010', 'DD/MM/YYYY') + level - 1 created, level amount from dual
connect by level < 100
) 

толкьо я не знаю, как указать, чтобы unbounded preceding были 30 дней назад, а не все
1 ноя 11, 21:03    [11535865]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
AmKad
Member

Откуда:
Сообщений: 5222
англикот
толкьо я не знаю, как указать, чтобы unbounded preceding были 30 дней назад, а не все
Кое где я уже показал как сделать "2 дня вперед".
1 ноя 11, 21:30    [11535928]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
AmKad
англикот
толкьо я не знаю, как указать, чтобы unbounded preceding были 30 дней назад, а не все
Кое где я уже показал как сделать "2 дня вперед".


спасибо AmKad, так получилось
round(avg(new_depositors) over (order by report_date  range between 29 preceding and  current row))

+1 в репу :)
1 ноя 11, 21:44    [11535961]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
Ramin Hashimzade
Member

Откуда: Азербайджан, Баку
Сообщений: 9979
Блог
англикот
я наверно запутал всех

на самом деле мне надо среднее значение 30 дней назад, начиная с 1 января

например
1.01.2011 - avg(с 3 декабря по 1 января)
2.01.2011 - avg(с 4 декабря по 2 января)
3.01.2011 - avg(с 5 декабря по 3 января)
и т.д.

надо использовать avg + windowing clause, но пока не получается

блин это вообще обратное первого поста...
2 ноя 11, 09:27    [11536894]     Ответить | Цитировать Сообщить модератору
 Re: опять аналитика - 30 day rolling average  [new]
англикот
Guest
Ramin
блин это вообще обратное первого поста...


это тоже самое, но еще раз извиняюсь что коряво написал.
просто посмотрел на формулу в Экселе... а она через одно место сделана
2 ноя 11, 11:40    [11537759]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить