Just the other day I received a request to aggregate the sum for each user_id for each month as a sum of all counters for all previous days starting from the beginning of the month.
The table used was:
create table test_report
(
user_id number,
curr_date date,
total_day number,
total_month number,
constraint pk_report primary key (user_id,curr_date));
The test data in this table had the total_day=10 for each day for each user_id.
The view created to retrieve the data in the requested format, where dynamic_total_month is the automatic generated sum:
CREATE VIEW test_report_view AS
SELECT user_id,
curr_date,
total_day,
SUM(SUM(total_day)) over (partition BY user_id extract(MONTH FROM curr_date),extract(YEAR FROM curr_date) order by
curr_date) dynamic_total_month,
total_month total,
extract(MONTH FROM curr_date) month_ex,
extract(YEAR FROM curr_date) year_ex
FROM test_report
GROUP BY user_id,
curr_date,
total_day,
total_month,
extract(MONTH FROM curr_date),
extract(YEAR FROM curr_date);
Here is a sample output:
select * from test_report_view
where user_id=1 and curr_date between '25-dec-2013' and
'5-jan-2014' order by curr_date;
1 25-DEC-13 01.17.24 10 250 12 2013
1 26-DEC-13 01.17.25 10 260 12 2013
1 27-DEC-13 01.17.25 10 270 12 2013
1 28-DEC-13 01.17.26 10 280 12 2013
1 29-DEC-13 01.17.26 10 290 12 2013
1 30-DEC-13 01.17.26 10 300 12 2013
1 31-DEC-13 01.17.27 10 310 12 2013
1 01-JAN-14 01.17.27 10 10 1 2014
1 02-JAN-14 01.17.27 10 20 1 2014
1 03-JAN-14 01.17.28 10 30 1 2014
1 04-JAN-14 01.17.28 10 40 1 2014
