how to find average for consecutive four months for preceding 12 months in oracle 12c

Question

I need to find 4 consecutive months for preceding 12 months using Oracle 12c

Original Output:

Month     Consumption
01/04/17  10000
01/05/17  10000
01/06/17  10000
01/07/17  13000
01/08/17  11000
01/09/17  12000
01/10/17  10000
01/11/17  12000

Example:(Expected Output)

Consecutive Months  Avg. of 4 Months Consumption
04/17 - 07/17         10750
05/17 - 08/17         11250
06/17 - 09/17         11750
07/17 - 10/17         12250
08/17 - 11/17         12750
09/17 - 12/17         13250
10/17 - 01/18         13750
11/17 - 02/18         14250
12/17 - 03/18         14750

Show us your expected output and the query you have tried so far. — Kaushik Nayak, Jul 27 '18 at 07:02
hi, i edit the question as original output and expected output.kindly check from your end — Poovendan, Jul 27 '18 at 07:42
yes. we have group the data and get single data for each month — Poovendan, Jul 27 '18 at 09:55

Wei Lin · Accepted Answer · 2018-07-27T08:28:41.007

try it use connect by level get 12 months data

then group + avg get expected data

with CTE as (
    select 
        TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- (level+3)),'YYYY/MM/')||'01','yyyy/mm/dd') sDate
        ,TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- level),'YYYY/MM/')||'01','yyyy/mm/dd') eDate
        ,level from dual 
    connect by level <=12 
)
,CTE2 as (
  select * 
  from CTE T1
  left join T T2 on cast(T2."Month" as date) between T1.sDate and T1.eDate
)
select TO_CHAR(sDate,'MM/YY') || ' - ' || TO_CHAR(eDate,'MM/YY') as "Consecutive Months"
  ,round(avg("Consumption")) as "Avg. of 4 Months Consumption"
from CTE2
group by sDate,eDate
order by eDate

Result

| Consecutive Months | Avg. of 4 Months Consumption |
|--------------------|------------------------------|
|      04/17 - 07/17 |                        10750 |
|      05/17 - 08/17 |                        11000 |
|      06/17 - 09/17 |                        11500 |
|      07/17 - 10/17 |                        11500 |
|      08/17 - 11/17 |                        11250 |
|      09/17 - 12/17 |                        11333 |
|      10/17 - 01/18 |                        11000 |
|      11/17 - 02/18 |                        12000 |
|      12/17 - 03/18 |                       (null) |
|      01/18 - 04/18 |                       (null) |
|      02/18 - 05/18 |                       (null) |
|      03/18 - 06/18 |                       (null) |

Test DDL:

CREATE TABLE T
    ("Month" timestamp, "Consumption" int)
;

INSERT ALL 
    INTO T ("Month", "Consumption")
         VALUES ('01-Apr-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-May-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Jun-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Jul-2017 12:00:00 AM', 13000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Aug-2017 12:00:00 AM', 11000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Sep-2017 12:00:00 AM', 12000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Oct-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Nov-2017 12:00:00 AM', 12000)
SELECT * FROM dual
;

SQL Fiddle

score 0 · Answer 2 · answered Jul 27 '18 at 08:21

You can use the analytical version of avg, and use lag to make sure the window indeed has four months:

SELECT   * 
FROM     (SELECT LAG(mnth, 4) AS start_month,
                 mnth AS end_month,
                 AVG(consumption) OVER 
                    (ORDER BY mnth ROWS BETWEEN CURRENT ROW AND 4 PRECEDING) AS avg_consumption
          FROM   mytable) t
WHERE    start_month IS NOT NULL
ORDER BY 1 ASC

how to find average for consecutive four months for preceding 12 months in oracle 12c

2 Answers2

SQL Fiddle