Home » SQL & PL/SQL » SQL & PL/SQL » Query help with cumulative sum to be broken into sections (Oracle 10.1)
Query help with cumulative sum to be broken into sections [message #366226] |
Fri, 12 December 2008 03:26 |
acantoni
Messages: 9 Registered: December 2008
|
Junior Member |
|
|
Greetings all!
I've come into a problem with a database query than on surface seems to be easy but is troubling me since yesterday.
I've putted some effort to provide some valid sql to reproduce the starting point and the desired output
drop table test_fatt;
create table test_fatt (
id VARCHAR2(10),
month NUMBER(10),
usd NUMBER(10)) ;
INSERT INTO test_fatt VALUES ('A', 1, 3);
INSERT INTO test_fatt VALUES ('A', 2, 5);
INSERT INTO test_fatt VALUES ('A', 3, 2);
INSERT INTO test_fatt VALUES ('A', 4, 8);
INSERT INTO test_fatt VALUES ('A', 5, 13);
INSERT INTO test_fatt VALUES ('A', 6, 3);
INSERT INTO test_fatt VALUES ('B', 1, 2);
INSERT INTO test_fatt VALUES ('B', 2, 15);
INSERT INTO test_fatt VALUES ('B', 3, 23);
INSERT INTO test_fatt VALUES ('B', 4, 8);
INSERT INTO test_fatt VALUES ('B', 5, 3);
INSERT INTO test_fatt VALUES ('B', 6, 5);
INSERT INTO test_fatt VALUES ('C', 1, 5);
INSERT INTO test_fatt VALUES ('C', 2, 15);
INSERT INTO test_fatt VALUES ('C', 3, 7);
INSERT INTO test_fatt VALUES ('C', 6, 2);
INSERT INTO test_fatt VALUES ('D', 1, 12);
INSERT INTO test_fatt VALUES ('D', 2, 5);
INSERT INTO test_fatt VALUES ('D', 3, 3);
INSERT INTO test_fatt VALUES ('D', 4, 4);
INSERT INTO test_fatt VALUES ('D', 5, 2);
INSERT INTO test_fatt VALUES ('D', 6, 8);
commit;
This reproduce the starting point in which you have an id for a company (A-B-C-D) 6 month of data (you can have no data for a month in this case the row is missing) and a number that represents the usd of sales
The output should be as follow:
drop table test_cum_fatt;
create table test_cum_fatt (
id VARCHAR2(10),
month NUMBER(10),
usd NUMBER(10),
cumulative_invoice NUMBER(10),
invoice_month NUMBER(10)) ;
INSERT INTO test_cum_fatt VALUES ('A', 1, 3, 3, 3);
INSERT INTO test_cum_fatt VALUES ('A', 2, 5, 8, 3);
INSERT INTO test_cum_fatt VALUES ('A', 3, 2, 10, 3);
INSERT INTO test_cum_fatt VALUES ('A', 4, 8, 8,5 );
INSERT INTO test_cum_fatt VALUES ('A', 5, 13, 21,5);
INSERT INTO test_cum_fatt VALUES ('A', 6, 3, 3,'' );
INSERT INTO test_cum_fatt VALUES ('B', 1, 2,2,2);
INSERT INTO test_cum_fatt VALUES ('B', 2, 15,17,2);
INSERT INTO test_cum_fatt VALUES ('B', 3, 23,23,3);
INSERT INTO test_cum_fatt VALUES ('B', 4, 8,8,5);
INSERT INTO test_cum_fatt VALUES ('B', 5, 3,11,5);
INSERT INTO test_cum_fatt VALUES ('B', 6, 5,5,'');
INSERT INTO test_cum_fatt VALUES ('C', 1, 5,5,2);
INSERT INTO test_cum_fatt VALUES ('C', 2, 15,20,2);
INSERT INTO test_cum_fatt VALUES ('C', 3, 7,7,'');
INSERT INTO test_cum_fatt VALUES ('C', 6, 2,9,'');
INSERT INTO test_cum_fatt VALUES ('D', 1, 12,12,1);
INSERT INTO test_cum_fatt VALUES ('D', 2, 5,5,4);
INSERT INTO test_cum_fatt VALUES ('D', 3, 3,8,4);
INSERT INTO test_cum_fatt VALUES ('D', 4, 4,12,4);
INSERT INTO test_cum_fatt VALUES ('D', 5, 2,2,6);
INSERT INTO test_cum_fatt VALUES ('D', 6, 8,10,6);
commit;
In which we have the same structure of the starting point in the first 3 colums, then we have 2 other colums:
4th colums is a cumulative sum of the usd breaken up by the id of the company, this cumulative sum should reset back to 0 when the company change and when the cumulative sum reaches 10 or more
5th colums indicates for each row when the usd cumulative amount has reached at least 10
Lets consider company A for example:
First month was 3, so not reached 10
Second month was 5, cumulative is 8 not reached 10
Third month was 2, cumulative is 10 so the minimum of 10 is reached.
Invoice is sent for the amount of 10 and for each month should bee noted that it was on the third month that amount is reached
Forth month amount is 8, so not reached 10
Fifth is 13, so total is 21, reached at least 10. Invoice is sent and forth-fifth month reached the amount on the fifth month
Sixth month amount is 3 , so not reached 10
I hope this is clear enough!
Any help is appreciated!
Thank you
Fabio
[Updated on: Tue, 16 December 2008 08:53] by Moderator Report message to a moderator
|
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #366234 is a reply to message #366232] |
Fri, 12 December 2008 03:57 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It's nested analytic functions time!
There's almost certainly a better solution using the MODEL clause.
select id
,month
,usd
,cum_tot
,min(inv_month) over
(partition by id order by month
rows between current row and unbounded following) x
from (select id
,month
,usd
,cum_tot
,case when nvl(lag(tens) over (partition by id order by month),tens)
!= tens then month end inv_month
from (select id
,month
,usd
,sum(usd) over (partition by id order by month) cum_tot
,floor(sum(usd) over (partition by id order by month)/10) tens
from test_fatt
) )
order by id,month;
You can (and I'd say should) have a play with this - run the innermost query, and then the inner two together and see what they produce.
The innermost subquery generates the cumulative total for each month..
The next query out spots where the cumulative total goes over a multiple of 10, and flags these up with the month in which it happened.
The outermost query looks forwards from each row to see what the smallest month ahead in which the invoice month changed is, and back fills the current row with that value.
Results:ID MONTH USD CUM_TOT INV_MONTH
---------- ---------- ---------- ---------- ----------
A 1 3 3 3
A 2 5 8 3
A 3 2 10 3
A 4 8 18 5
A 5 13 31 5
A 6 3 34
B 1 2 2 2
B 2 15 17 2
B 3 23 40 3
B 4 8 48 5
B 5 3 51 5
B 6 5 56
C 1 5 5 2
C 2 15 20 2
C 3 7 27
C 6 2 29
D 1 12 12 3
D 2 5 17 3
D 3 3 20 3
D 4 4 24 6
D 5 2 26 6
D 6 8 34 6
22 rows selected.
[Updated on: Tue, 16 December 2008 08:52] by Moderator Report message to a moderator
|
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #366241 is a reply to message #366226] |
Fri, 12 December 2008 04:26 |
|
Michel Cadot
Messages: 68707 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Is this what you want?
SQL> break on id dup skip 1
SQL> select id, month, usd, cum_tot, flag
2 from test_fatt
3 model
4 partition by (id)
5 dimension by (month)
6 measures (usd, 0 cum_tot, 0 flag)
7 rules update sequential order (
8 cum_tot[ANY] order by month =
9 case when nvl(cum_tot[cv()-1],0) >= 10
10 then usd[cv()]
11 else nvl(cum_tot[cv()-1],0)+usd[cv()]
12 end,
13 flag[ANY] order by month =
14 case when cum_tot[cv()] >= 10 then 1 else 0 end
15 )
16 order by 1, 2
17 /
ID MONTH USD CUM_TOT FLAG
---------- ---------- ---------- ---------- ----------
A 1 3 3 0
A 2 5 8 0
A 3 2 10 1
A 4 8 8 0
A 5 13 21 1
A 6 3 3 0
B 1 2 2 0
B 2 15 17 1
B 3 23 23 1
B 4 8 8 0
B 5 3 11 1
B 6 5 5 0
C 1 5 5 0
C 2 15 20 1
C 3 7 7 0
C 6 2 2 0
D 1 12 12 1
D 2 5 5 0
D 3 3 8 0
D 4 4 12 1
D 5 2 2 0
D 6 8 10 1
22 rows selected.
Regards
Michel
|
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #366246 is a reply to message #366243] |
Fri, 12 December 2008 04:55 |
|
Michel Cadot
Messages: 68707 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Regrouping each month involve in an invoice number:
SQL> select id, month, usd, cum_tot, invoice_nb, flag
2 from test_fatt
3 model
4 partition by (id)
5 dimension by (month)
6 measures (usd, 0 cum_tot, 0 invoice_nb, 0 flag)
7 rules update sequential order (
8 cum_tot[ANY] order by month =
9 case when nvl(cum_tot[cv()-1],0) >= 10
10 then usd[cv()]
11 else nvl(cum_tot[cv()-1],0)+usd[cv()]
12 end,
13 invoice_nb[ANY] order by month =
14 nvl(invoice_nb[cv()-1],1) +
15 case when nvl(cum_tot[cv()-1],0) >= 10
16 then 1
17 else 0
18 end,
19 flag[ANY] order by month =
20 case when cum_tot[cv()] >= 10 then 1 else 0 end
21 )
22 order by 1, 2
23 /
ID MONTH USD CUM_TOT INVOICE_NB FLAG
---------- ---------- ---------- ---------- ---------- ----------
A 1 3 3 1 0
A 2 5 8 1 0
A 3 2 10 1 1
A 4 8 8 2 0
A 5 13 21 2 1
A 6 3 3 3 0
B 1 2 2 1 0
B 2 15 17 1 1
B 3 23 23 2 1
B 4 8 8 3 0
B 5 3 11 3 1
B 6 5 5 4 0
C 1 5 5 1 0
C 2 15 20 1 1
C 3 7 7 2 0
C 6 2 2 1 0
D 1 12 12 1 1
D 2 5 5 2 0
D 3 3 8 2 0
D 4 4 12 2 1
D 5 2 2 3 0
D 6 8 10 3 1
22 rows selected.
Regards
Michel
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #366248 is a reply to message #366246] |
Fri, 12 December 2008 04:59 |
|
Michel Cadot
Messages: 68707 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Doh! this does not work when some months are missing, here is a fixed one:
SQL> break on id dup skip 1
SQL> select id, month, usd, cum_tot, invoice_nb, flag
2 from ( select id, month, usd,
3 row_number() over (partition by id order by month) rn
4 from test_fatt
5 )
6 model
7 partition by (id)
8 dimension by (rn)
9 measures (month, usd, 0 cum_tot, 0 invoice_nb, 0 flag)
10 rules update sequential order (
11 cum_tot[ANY] order by month =
12 case when nvl(cum_tot[cv()-1],0) >= 10
13 then usd[cv()]
14 else nvl(cum_tot[cv()-1],0)+usd[cv()]
15 end,
16 invoice_nb[ANY] order by month =
17 nvl(invoice_nb[cv()-1],1) +
18 case when nvl(cum_tot[cv()-1],0) >= 10
19 then 1
20 else 0
21 end,
22 flag[ANY] order by month =
23 case when cum_tot[cv()] >= 10 then 1 else 0 end
24 )
25 order by 1, 2
26 /
ID MONTH USD CUM_TOT INVOICE_NB FLAG
---------- ---------- ---------- ---------- ---------- ----------
A 1 3 3 1 0
A 2 5 8 1 0
A 3 2 10 1 1
A 4 8 8 2 0
A 5 13 21 2 1
A 6 3 3 3 0
B 1 2 2 1 0
B 2 15 17 1 1
B 3 23 23 2 1
B 4 8 8 3 0
B 5 3 11 3 1
B 6 5 5 4 0
C 1 5 5 1 0
C 2 15 20 1 1
C 3 7 7 2 0
C 6 2 9 2 0
D 1 12 12 1 1
D 2 5 5 2 0
D 3 3 8 2 0
D 4 4 12 2 1
D 5 2 2 3 0
D 6 8 10 3 1
22 rows selected.
Regards
Michel
|
|
|
|
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #376217 is a reply to message #376208] |
Tue, 16 December 2008 08:47 |
acantoni
Messages: 9 Registered: December 2008
|
Junior Member |
|
|
Thanks michel
I'd update the previous threads if i knew how to do it
so MODEL is out of the question
Sorry if i wasted your time, i still hope someone else may benefit from this.
@JRowbottom
I'm providing a new little test case to see the problems
drop table test_fatt;
create table test_fatt (id VARCHAR2(10),month NUMBER,usd NUMBER) ;
INSERT INTO test_fatt VALUES ('A', 1, 3);
INSERT INTO test_fatt VALUES ('A', 2, 5);
INSERT INTO test_fatt VALUES ('A', 3, 2);
INSERT INTO test_fatt VALUES ('A', 4, 8);
INSERT INTO test_fatt VALUES ('A', 5, 13);
INSERT INTO test_fatt VALUES ('A', 6, 3);
INSERT INTO test_fatt VALUES ('B', 1, 2);
INSERT INTO test_fatt VALUES ('B', 2, 15);
INSERT INTO test_fatt VALUES ('B', 3, 23);
INSERT INTO test_fatt VALUES ('B', 4, 8);
INSERT INTO test_fatt VALUES ('B', 5, 3);
INSERT INTO test_fatt VALUES ('B', 6, 5);
INSERT INTO test_fatt VALUES ('C', 1, 5);
INSERT INTO test_fatt VALUES ('C', 2, 15);
INSERT INTO test_fatt VALUES ('C', 3, 7);
INSERT INTO test_fatt VALUES ('C', 6, 2);
INSERT INTO test_fatt VALUES ('D', 1, 12);
INSERT INTO test_fatt VALUES ('D', 2, 5);
INSERT INTO test_fatt VALUES ('D', 3, 3);
INSERT INTO test_fatt VALUES ('D', 4, 4);
INSERT INTO test_fatt VALUES ('D', 5, 2);
INSERT INTO test_fatt VALUES ('D', 6, 8);
INSERT INTO test_fatt VALUES ('E', 1, 38.2);
INSERT INTO test_fatt VALUES ('E', 2, 9.9);
INSERT INTO test_fatt VALUES ('E', 3, 9.2);
INSERT INTO test_fatt VALUES ('E', 4, 7.4);
INSERT INTO test_fatt VALUES ('E', 5, 2.7);
INSERT INTO test_fatt VALUES ('E', 6, 8.6);
INSERT INTO test_fatt VALUES ('F', 1, 8.2);
INSERT INTO test_fatt VALUES ('F', 2, 9.9);
INSERT INTO test_fatt VALUES ('F', 3, 9.2);
INSERT INTO test_fatt VALUES ('F', 4, 7.4);
INSERT INTO test_fatt VALUES ('F', 5, 2.7);
INSERT INTO test_fatt VALUES ('F', 6, 8.6);
commit;
I'm using the code you posted
select id
,month
,usd
,cum_tot
,min(inv_month) over
(partition by id order by month
rows between current row and unbounded following) x
from (select id
,month
,usd
,cum_tot
,case when nvl(lag(tens) over (partition by id order by month),tens)
!= tens then month end inv_month
from (select id
,month
,usd
,sum(usd) over (partition by id order by month) cum_tot
,floor(sum(usd) over (partition by id order by month)/10) tens
from test_fatt
) )
order by id,month;
And here is the results
ID,MONTH,USD,CUM_TOT,X
A,1,3,3,3
A,2,5,8,3
A,3,2,10,3
A,4,8,18,5
A,5,13,31,5
A,6,3,34,null
B,1,2,2,2
B,2,15,17,2
B,3,23,40,3
B,4,8,48,5
B,5,3,51,5
B,6,5,56,null
C,1,5,5,2
C,2,15,20,2
C,3,7,27,null
C,6,2,29,null
D,1,12,12,3 -> SHOULD BE 1
D,2,5,17,3 -> SHOULD BE 4
D,3,3,20,3 -> SHOULD BE 4
D,4,4,24,6 -> SHOULD BE 4
D,5,2,26,6
D,6,8,34,6
E,1,38,2,38,2,2 -> SHOULD BE 1
E,2,9,9,48,1,2 -> SHOULD BE 3
E,3,9,2,57,3,3
E,4,7,4,64,7,4 -> SHOULD BE 5
E,5,2,7,67,4,6 -> SHOULD BE 5
E,6,8,6,76,6 -> SHOULD BE NULL
F,1,8,2,8,2,2
F,2,9,9,18,1,2
F,3,9,2,27,3,3 -> SHOULD BE 4
F,4,7,4,34,7,4
F,5,2,7,37,4,6
F,6,8,6,46,6
As you can see it seems there are some problem with decimals and most strange when the very first record for a company alone can surpass the 10 threshold
[Updated on: Tue, 16 December 2008 08:51] by Moderator Report message to a moderator
|
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #376638 is a reply to message #376218] |
Thu, 18 December 2008 02:45 |
acantoni
Messages: 9 Registered: December 2008
|
Junior Member |
|
|
Since there is still not a solution (nor we're close to one in this enviroment: oracle 9.1) i still accept any ideas, it is fine also to do something that can be run in a procedure/package or also use temporary tables and do the thing in more than one passage.
@michel
When you told me to look for invoice_nb it does the job of showing what invoice it is but not in what months it has been issued.
For instance for ID A, month 1-2-3 it is invoice_nb 1 but what i need its to be shown as "invoiced in month 3"
Here you go with the Fixed colums expected ideal results
(i have no idea how to paste here with fixed columms, is is fine in the editor but in the preview its screwed up.
i'll upload the results in a txt in case here doesnt work):
What is important is having the "invoice_month" column correct, the cumulative_invoice is not important
ID MONTH USD CUMULATIVE_INVOICE INVOICE_MONTH
A 1 3 3 3
A 2 5 8 3
A 3 2 10 3
A 4 8 8 5
A 5 13 21 5
A 6 3 3 null
B 1 2 2 2
B 2 15 17 2
B 3 23 23 3
B 4 8 8 5
B 5 3 11 5
B 6 5 5 null
C 1 5 5 2
C 2 15 20 2
C 3 7 7 null
C 6 2 9 null
D 1 12 12 1
D 2 5 5 4
D 3 3 8 4
D 4 4 12 4
D 5 2 2 6
D 6 8 10 6
E 1 38,2 38,2 1
E 2 9,9 9,9 3
E 3 9,2 19,1 3
E 4 7,4 7,4 5
E 5 2,7 10,1 5
E 6 8,6 8,6 null
F 1 8,2 8,2 2
F 2 9,9 18,1 2
F 3 9,2 9,2 4
F 4 7,4 16,6 4
F 5 2,7 2,7 6
F 6 8,6 11,3 6
As usual any help is really appreciated
|
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #376654 is a reply to message #376649] |
Thu, 18 December 2008 03:20 |
acantoni
Messages: 9 Registered: December 2008
|
Junior Member |
|
|
According to your schema the flag columm flags the month in which the limit has been surpassed, it does not flag all the months that contributed to surpassing the threshold nor it immediatly shows which month it is.
Now i do agree that having exactly the result you provided (so the use of invoice_nb and the flag columm) with some adjustment can be workable using your result as a starting point for what i really need.
But since the code you kindly provided cant be used in this case i see no point in discussing it further, if you do have ideas to provide the exact same results without using the MODEL clause however i'm all hears but consider the final result i'm looking for is different =)
As usual i thank you for your input and contribution
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #376658 is a reply to message #376638] |
Thu, 18 December 2008 03:31 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This should fix the month 1 invoice month problem.select id
,month
,usd
,cum_tot
,min(inv_month) over
(partition by id order by month
rows between current row and unbounded following) x
from (select id
,month
,usd
,cum_tot
,case when nvl(lag(tens) over (partition by id order by month),0)
!= tens then month end inv_month
from (select id
,month
,usd
,sum(usd) over (partition by id order by month) cum_tot
,floor(sum(usd) over (partition by id order by month)/10) tens
from test_fatt
) )
order by id,month;
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #376670 is a reply to message #376658] |
Thu, 18 December 2008 04:12 |
acantoni
Messages: 9 Registered: December 2008
|
Junior Member |
|
|
Thank you Jrowbottom! really
It does fix the 1st line problem,
It does not fix the other major problem, i attach the example to show it:
ID MONTH USD CUM_TOT X
D 1 12 12 1 ok
D 2 5 17 3 no should be 4!
D 3 3 20 3 no! should be 4 see the problem?
the cum_tot is 20 and so it get flagged but it shouldnt!
first invoice was at 12, here we're at 8 (5+3) it shouldnt!
D 4 4 24 6 should be 4, because here 5+3+4 we surpass the threshold
D 5 2 26 6 ok
D 6 8 34 6 ok
E 1 38,2 38,2 1 ok
E 2 9,9 48,1 2 see? its < 10, it does not surpass 10, but since before we had 38.2
which is way higher than 10 it keeps flagging as surpasses. that should be 3
E 3 9,2 57,3 3 ok
E 4 7,4 64,7 4 we have still the same problem
E 5 2,7 67,4 6
E 6 8,6 76 6
Basically when the script divide by 10 it doesnt count that it can be surpassed by a lot by previous results!
[Updated on: Thu, 18 December 2008 04:47] Report message to a moderator
|
|
|
Re: Query help with cumulative sum to be broken into sections [message #377286 is a reply to message #376670] |
Mon, 22 December 2008 04:14 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The other problem is rather intractable - I don't know if it can be done without either Pl/Sql or the Model clause.
I've managed to get a partial success with this query:select id,month,usd,cum_tot,inv_month
from (
select id,month,usd,cum_tot,inv_month
,min(case when inv_month = 0 then null else inv_month end) over (partition by id,month) act_inv_month
from (
select id,month,rw,usd,prev_usd,cum_tot
,sum(usd) over (partition by id,rw order by month rows between current row and rw following) x
,case when sum(usd) over (partition by id,rw order by month rows between current row and rw following) >= 10 then month+rw else 0 end inv_month
from (select id,month,usd
,sum(usd) over (partition by id order by month) cum_tot
,nvl(lag(usd) over (partition by id order by month),0) prev_usd
from test_fatt)
,(select level-1 rw from dual connect by level <=4)
)
) where inv_month = act_inv_month;
This works by taking each value and looking n months into the future and seeing when an invoice will need to be generated.
If you look through the results starting from month one it will tell you then next month that invoices will be generateed in. if you then move to that month+1, you can see the following invoice month, and so on.
I tried to create a Connect By loop version of the query to implement this logic, but it seems to get into some sort of internal loop, and creates me an arbitrarily large Temp tablespace until I decide to kill it - it might work for you.
The query is of this form:select *
from (query goes here)
start with month=1
connect by prior inv_month+1 = month and prior id = id;
|
|
|
|
Goto Forum:
Current Time: Thu Nov 07 16:01:08 CST 2024
|