Data Warehousing Conference

The tinyurl for this posting is: http://tinyurl.com/23p4yj3

The question seemed simple: “I want to [to group] rows [so that] the sum of col1 is


WITH t as (
select 0 a, 0 col1 from dual where 0=1
union all select 1,10 from dual
union all select 2,90 from dual
union all select 3,1 from dual
union all select 4,5 from dual
union all select 5,5 from dual
union all select 6,50 from dual
union all select 7,99 from dual
union all select 8,2 from dual
union all select 9,10 from dual
union all select 10,50 from dual
)
select a,col1, grp
from t
... -- order by a
/
A COL1 GRP
--- ---------- ----------
1 10 1
2 90 1
3 1 2
4 5 2
5 5 2
6 50 2
7 99 3
8 2 4
9 10 4
10 50 4


Note: This is not simply TRUNC’ing the rolling sum of COL1.

Frankly, I didn’t understand Charles Heilig’s query, which used the SQL Model Clause, although I’ve written about the Model Clause a few times before. However, that was a couple of years ago, and it’s hardly something I use frequently.

So I looked at using the hoary CONNECT BY and 11gR2′s recursive CTE (factored subfactor). However, they seem to be limited to the equivalent of the LAG analytic function.

Finally, I just built my own solution using the Model Clause: You can see how I “build up” the answer, calculating the RATCHET (cumulative sum for the GRP) and GRP.


...
select a,col1, ratchet, grp
from t
model
dimension by ( a)
MEASURES
( col1, 0 as ratchet, 0 as grp )
rules automatic order
(ratchet[1] = col1[CV()]
,ratchet[a>1] = CASE WHEN col1[CV()]+ratchet[CV()-1] THEN col1[CV()]+ratchet[CV()-1]
ELSE col1[CV()]
END
,grp[1] = 1
,grp[a>1] = CASE WHEN col1[CV()]+ratchet[CV()-1] THEN grp[CV()-1]
ELSE grp[CV()-1]+1
END
)
order by a
/

A COL1 RATCHET GRP
—– ———- ———- ———-
1 10 10 1
2 90 100 1
3 1 1 2
4 5 6 2
5 5 11 2
6 50 61 2
7 99 99 3
8 2 2 4
9 10 12 4
10 50 62 4

It may be a little easier to see if you compare the formulas from a spreadsheet:


A COL1 RATCHET GRP
-- ---- --------------------------------- ------------------------------
1 10 =B2 =1
2 90 =IF((B3+C2)3 1 =IF((B4+C3)4 5 =IF((B5+C4)5 5 =IF((B6+C5)6 50 =IF((B7+C6)7 99 =IF((B8+C7)8 2 =IF((B9+C8)9 10 =IF((B10+C9)10 50 =IF((B11+C10)

Sure, in Oracle I used a CASE statement instead of an IF. And named references instead of “B1″, but you can see the affinity between the spreadsheet formula used for most of the RATCHET, e.g:


IF((B3+C2)
and the generalized MODEL formula:

CASE WHEN col1[CV()]+ratchet[CV()-1] THEN col1[CV()]+ratchet[CV()-1]
ELSE col1[CV()]
END


Yes, better than a spreadsheet!

**************************************************************************************
ENDNOTE: I noticed that posting my query on the original forum posting demonstrated how “+” were getting dropped. Knowing that, I was able to reconstruct Mr. Heilig’s MODEL rules if you want to compare his approach:


( flag[1] = col1[cv()]
, flag[a > 1 ] =
case when ceil(trunc((col1[cv()]
+ flag[cv()-1])/100))*100
= ceil(trunc((flag[cv()-1])/100))*100
or mod( col1[cv()]+ flag[cv()-1],100)=0
then col1[cv()]+ flag[cv()-1]
else ceil(trunc((col1[cv()]
+ flag[cv()-1])/100))*100
+ col1[cv()]
end
, grp[1] = 1
, grp[a > 1] = ceil(flag[cv()]/100)