how to aggregate data base per month and previous data with oracle sql

Question

let's say we have a simple table "example" like this:

id  time 
1  2-2-20 
2  3-1-20
3  15-2-20
4  20-3-20

i want to count the rows by each month containing previous month data and get the average data(divide by rows for the specified month) per month:

month rows_num avg_per_day
1-20    1        1/31
2-20    2+1      2/29
3-20    2+1+1    1/31

i was considering if i can use sql like this but will not contain the previous month data and have no idea how to deal with the average value:

select count(*) from example group by trunc(to_date(trunc(time)),'MONTH')

Could anyone kindly help me?

stefan stefan · Accepted Answer · 2020-06-23T18:16:30

Although the output of your query looks rather simple, it may be an advantage to write an inline view for generating values that can be utilised in an outer SELECT eg (using your example table)

Table and data

create table example ( id,  day_ )
as
select 1, date '2020-02-02' from dual union all --  2-2-20
select 2, date '2020-01-03' from dual union all --  3-1-20
select 3, date '2020-02-15' from dual union all -- 15-2-20
select 4, date '2020-03-20' from dual           -- 20-3-20
;

First step ( use this for the "inline view")

  select
    to_char( day_, 'MM-YY') month_
  , to_char( extract ( day from last_day( day_ ) ) ) lastday_
  , count(*) over ( order by to_char( day_, 'MM-YY') ) runningtotal_
  , row_number() over ( partition by  to_char( day_, 'MM-YY')  order by day_ ) rn_
  from example ;

-- result
+------+--------+-------------+---+
|MONTH_|LASTDAY_|RUNNINGTOTAL_|RN_|
+------+--------+-------------+---+
|01-20 |31      |1            |1  |
|02-20 |29      |3            |1  |
|02-20 |29      |3            |2  |
|03-20 |31      |4            |1  |
+------+--------+-------------+---+

Final query

select
  month_
, runningtotal_ rows_num
, round( max( rn_ )  / lastday_, 5 ) avg_per_day
, to_char( max( rn_ )  ) || '/' || to_char( lastday_ ) avg_per_day
from (
  select
    to_char( day_, 'MM-YY') month_
  , to_char( extract ( day from last_day( day_ ) ) ) lastday_
  , count(*) over ( order by to_char( day_, 'MM-YY') ) runningtotal_
  , row_number() over ( partition by  to_char( day_, 'MM-YY')  order by day_ ) rn_
  from example
)
group by month_, runningtotal_, lastday_
order by month_
;

-- result 
+------+--------+-----------+-----------+
|MONTH_|ROWS_NUM|AVG_PER_DAY|AVG_PER_DAY|
+------+--------+-----------+-----------+
|01-20 |1       |0.03226    |1/31       |
|02-20 |3       |0.06897    |2/29       |
|03-20 |4       |0.03226    |1/31       |
+------+--------+-----------+-----------+

DBfiddle here.

Note: It was not quite clear (to me) which form of "AVG_PER_DAY" you require. Just delete the line that you do not need from the outer SELECT.

how to aggregate data base per month and previous data with oracle sql

2 Answers