oracle take mean value of one column across different dates

Question

There is one column called Price, and another column called Date_1, 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_1  | 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.

Thanks! This problem solved. And I cam across an additional one:

There is another table that contains date information :

Location_id |  Ex_start_date  | Ex_end_date 
------------+-----------------+--------------
   L_1      | 08-JUN-16       |   30-AUG-16
   L_1      | 21-SEP-16       |   25-SEP-16
   L_1      | 08-MAY-17       |   12-MAY-17
   L_2      | 08-AUG-16       |   21-AUG-16
   L_2      | 24-OCT-16       |   29-OCT-16
   L_2      | 15-MAR-17       |   19-MAR-17

Beyond "Ex_Start_date" and "Ex_End_date" is 'Non_Ex' period. After I obtain average information of 2 weeks and 6 months period, I would like to I would like to add one more column, to obtain mean price for 'Non_Ex' and 'Ex' conditions as above.

Hopefully, a table as below can be obtained:

Location_id |  Period        | Ex_Condition    |   Average_Price 
------------+----------------+----------------------------------
   L_1      | 2 weeks        |   Ex period     |   ...
   L_1      | 2 weeks        |   Non-Ex period |   ...
   L_1      | 6 months       |   Ex period     |   ...
   L_1      | 6 months       |   Non-Ex period |   ...                                                 
   L_2      | 2 weeks        |   Ex period     |   ...
   L_2      | 2 weeks        |   Non-Ex period |   ...
   L_2      | 6 months       |   Ex period     |   ...
   L_2      | 6 months       |   Non-Ex period |   ...

The average price will return 'null' if there is no dates falling in EX Period or Non-Ex Period.

And how can I make it happen? Thanks!

data like: Location_id Date Price L_1 20-JUL-2016 105 ... ... ... L_1 16-MAY-2017 103 L_2 20-JUL-2016 99 ... ... ... L_2 16-MAY-2017 120 to get Location_id Period Average_Price L_1 2 weeks ... L_1 6 months ... L_2 2 weeks ... L_2 6 months ... — Crubal Chenxi Li, Jul 20 '16 at 18:19
@Crubal Chenxi Li Please edit your original post with this information. — Gary_W, Jul 20 '16 at 19:40
@CrubalChenxiLi It's still not clear what *exact* results you want to get. Please [edit] your question and add the expected Average_Price and the missing rows in your input data. — Frank Schmitt, Jul 20 '16 at 20:17

trincot · Accepted Answer · 2016-07-21T13:57:42.247

0

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.

edited Jul 21 '16 at 13:57

answered Jul 20 '16 at 19:49

trincot

317,000
35
244
286

Thanks! May I ask what is "dual" and "localprice"? – Crubal Chenxi Li Jul 21 '16 at 02:33
This may help explain dual: http://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle – zundarz Jul 21 '16 at 03:36
*localprce* is just your table (you did not provide its name in your question). *dual* is a standard object that exists in Oracle, which can be used to introduce rows in a query. Alternatively, you could create a table with all periods that interest you (2 weeks, 4 weeks, ...) and use that instead of the `union` select on *dual*. – trincot Jul 21 '16 at 07:06
Thanks! I just tried, it seems that mean value of "6 months" is the same as that of "2 weeks". I guess is only the first observation returned? – Crubal Chenxi Li Jul 21 '16 at 13:24
I don't understand what you are saying. The value *could* of course be the same for a certain data set, but in general it is not (I tested this). Make sure you have data that has dates further in the future than 2 weeks. – trincot Jul 21 '16 at 13:46
Yes, I made that mistake as you mentioned. Thank you! – Crubal Chenxi Li Jul 21 '16 at 13:55
You're welcome. I had just added some "proof" by creating a fiddle :) – trincot Jul 21 '16 at 13:58

score 0 · Answer 2 · answered Jul 20 '16 at 21:32

NOT TESTED because OP didn't provide input data in usable format.

You probably want something along the lines of

select   location_id, '2 weeks' as period, avg(price) as average_price
from     base_table
where    price is not null
         and
         "date" between SYSDATE and SYSDATE + 13  
                                -- or however you want to define the two week interval
group by location_id
union all
select   location_id, '6 months' as period, avg(price) as average_price
from     base_table
where    price is not null
         and
         "date" between SYSDATE and add_months(SYSDATE, 6) - 1  
                                -- or however you want to define the six month interval
group by location_id
;

Note that date is a reserved Oracle keyword which should not be used as a column name; if you do, you'll have to use double-quotes, match case (upper and lower) exactly, and you may still run into various problems later. Better to only use table and column names that are not reserved words.

Thanks! I would avoid that. By the way, for 'base_table', I have to refer every that I use "select and union all" function, right? — Crubal Chenxi Li, Jul 21 '16 at 13:06
I am not sure to understand your question. Do you mean, do you have to refer to "base_table" (instead, use your actual table or view name here!) twice, because there is a UNION ALL? Then the answer is yes; when you use UNION ALL, both parts of the UNION are full "SELECT statements" with a FROM clause where you must reference the base table. — , Jul 21 '16 at 14:10

score 0 · Answer 3 · answered Jul 20 '16 at 23:27

This is a re-phrased version of the @trincot answer. It should be faster over a bigger dataset.

Rows which are unwanted are skipped, not zeroed and used. You won't get a result row any more if there no localprice which match the intervals criteria.
It still only scans localprice once unlike the @mathguy answer.
If the real local price has a highly selective index on date then it can be used.
Un-commenting the line in the WHERE clause will help discard lines early i.e. before the intervals table is considered. The ORDERED hint may well be unnecessary in real life but it demonstrates the correct explain plan when using this line with this data.
Use UNION ALL rather that UNION when gluing rows which are going to be unique.

As usual, don't believe any answer until you've proved it in your circumstances.

WITH localprice AS ( SELECT 'L_1' Location_id, TO_DATE('20-JUN-2016') "DATE", 105 Price FROM DUAL UNION ALL SELECT 'L_1' Location_id, TO_DATE('16-MAY-2017') "DATE", 103 Price FROM DUAL UNION ALL SELECT 'L_2' Location_id, TO_DATE('20-JUN-2016') "DATE", 99 Price FROM DUAL UNION ALL SELECT 'L_2' Location_id, TO_DATE('16-MAY-2017') "DATE", 120 Price FROM DUAL ), intervals AS ( SELECT '2 weeks' AS period, 14 AS days FROM dual UNION ALL SELECT '6 months', 183 FROM dual ) SELECT /*+ ORDERED */ location_id, period, AVG(price) AS avg_price FROM localprice CROSS JOIN intervals WHERE "DATE" >= SYSDATE - days -- AND "DATE" >= SYSDATE - (SELECT MAX(days) FROM intervals) GROUP BY location_id, period

oracle take mean value of one column across different dates

3 Answers3