Crubal Chenxi Li Crubal Chenxi Li - 4 months ago 12
SQL Question

oracle take mean value of one column across different dates

There is one column called

Price
, and another column called
Date
, which include data from now to about one year later.
I want to find the mean value of
Price
across different dates. Ex, 2 weeks from now, 1 month from now, 6 months from now...

Can I use
Case When
function to do it?

Given:

Location_id | Date | Price
------------+-------------+------
L_1 | 20-JUL-2016 | 105
L_1 | 21-JUL-2016 | 117
... | ... | ...
L_1 | 16-MAY-2017 | 103
L_2 | 20-JUL-2016 | 99
L_2 | 21-JUL-2016 | 106
... | ... | ...
L_2 | 16-MAY-2017 | 120


To get:

Location_id | Period | Average_Price
------------+----------+--------------
L_1 | 2 weeks | ...
L_1 | 6 months | ...
L_1 | 1 year | ...
L_2 | 2 weeks | ...
L_2 | 6 months | ...
L_2 | 1 year | ...


Where in "Period", '2 weeks' means 2 weeks from start date (sysdate). And "Average_Price" is the mean value of price across that period.

Answer

You could do it like this:

select   location_id,
         period,
         sum(in_period * price) / nullif(sum(in_period), 0) as avg_price
from     (select location_id,
                 price,
                 period,
                 case when mydate - days < sysdate then 1 else 0 end in_period
          from   localprice,
                 ( select '2 weeks' as period, 14 as days from dual
                   union 
                   select '6 months', 183 from dual
                 ) intervals
         ) detail
group by location_id, 
         period

Replace localprice with the name of your table (you did not provide its name in your question).

Replace mydate with the actual name of your date column. I don't expect you called it date, as that is a reserved word and would require you to always quote it -- don't do that: choose another name.

dual is a standard object available in Oracle, which can be used to introduce rows in a query - rows which you don't have in a table somewhere.

Alternatively, you could create a table with all periods that interest you (2 weeks, 4 weeks, ..., together with the number of days they represent) and use that instead of the union select on dual.

Here is an SQL fiddle. Note that it runs on Postgres, because the Oracle instance is not available at this moment. For that reason I created dual explicitly and used current_date instead of sysdate. But for the rest it is the same.