and with a "where" clause?
Cesar Salas, April 05, 2002 - 5:08 pm UTC
Hi tom,
What is wrong with this query?
exec my_pkg.gtotal := 0;
select n, s, rt
from
(select ename n, sal s, my_pkg.rtotal(sal) rt from
(select ename, sal from emp order by ename))
/
N S RT
---------- --------- ---------
ADAMS 1100 1100
ALLEN 1600 2700
BLAKE 2850 5550
CLARK 2450 8000
FORD 3000 11000
JAMES 950 11950
JONES 2975 14925
KING 5000 19925
MARTIN 1250 21175
MILLER 1300 22475
SCOTT 3000 25475
SMITH 800 26275
TURNER 1500 27775
WARD 1250 29025
--
-- Ok, but when I try...
--
exec my_pkg.gtotal := 0;
select n, s, rt
from
(select ename n, sal s, my_pkg.rtotal(sal) rt from
(select ename, sal from emp order by ename))
where rt < 6000
/
N S RT
---------- --------- ---------
ALLEN 1600 30625
SMITH 800 31425
WARD 1250 32675
I only want fetch records while rt < :x, Is it possible?
I can't use analytic functions...
Thanks!
April 06, 2002 - 11:03 am UTC
As I said:
To get the results. In 7.x -- 8.1.5, we can sometimes use a "trick"
You cannot -- you need to use the anlytic functions.
If sal is equal then not cumulated
dharma, August 01, 2002 - 8:01 pm UTC
The documentation says if two of the salaries were equal in subsequent rows, then the salary will not be cumulated, is there any way to overcome that?
for example when sal 130 comes continuously its not cumulated.
deptno empno sal cum_sal
10 1 100 100
10 3 120 220
10 2 130 350
10 5 130 350
10 4 150 500
August 05, 2002 - 9:57 am UTC
Depends on the WINDOW clause you use. In the above example I provided, what you say is inaccurate:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select ename, 100 sal from scott.emp;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> SELECT ename, sal,
2 SUM(sal) OVER
3 (ORDER BY ename RANGE UNBOUNDED PRECEDING) l_csum
4 FROM emp;
ENAME SAL L_CSUM
---------- ---------- ----------
ADAMS 100 100
ALLEN 100 200
BLAKE 100 300
CLARK 100 400
FORD 100 500
JAMES 100 600
JONES 100 700
KING 100 800
MARTIN 100 900
MILLER 100 1000
SCOTT 100 1100
SMITH 100 1200
TURNER 100 1300
WARD 100 1400
14 rows selected.
Now, if I change that JUST A BIT, the salaries are "accumulated" but not as you might desire them to be:
ops$tkyte@ORA817DEV.US.ORACLE.COM> SELECT ename, sal,
2 SUM(sal) OVER
3 (ORDER BY sal RANGE UNBOUNDED PRECEDING) l_csum
4 FROM emp;
ENAME SAL L_CSUM
---------- ---------- ----------
SMITH 100 1400
ALLEN 100 1400
WARD 100 1400
JONES 100 1400
MARTIN 100 1400
BLAKE 100 1400
CLARK 100 1400
SCOTT 100 1400
KING 100 1400
TURNER 100 1400
ADAMS 100 1400
JAMES 100 1400
FORD 100 1400
MILLER 100 1400
14 rows selected.
(set theory kicking in here!!! all of the rows are logically the same, they don't come before or after eachother hence the range unbounded preceding is the same set every time). Easy enough to fix:
ops$tkyte@ORA817DEV.US.ORACLE.COM> SELECT ename, sal,
2 SUM(sal) OVER
3 (ORDER BY sal, EMPNO RANGE UNBOUNDED PRECEDING) l_csum
4 FROM emp;
ENAME SAL L_CSUM
---------- ---------- ----------
SMITH 100 100
ALLEN 100 200
WARD 100 300
JONES 100 400
MARTIN 100 500
BLAKE 100 600
CLARK 100 700
SCOTT 100 800
KING 100 900
TURNER 100 1000
ADAMS 100 1100
JAMES 100 1200
FORD 100 1300
MILLER 100 1400
14 rows selected.
Just add something to make the SAL, <something> unique (eg: rowid would do it as well)
great solution
jigar, June 28, 2003 - 2:46 am UTC
brilliant answer as always
But what if the following.........
Neil Chapman, September 04, 2003 - 8:19 am UTC
I have the following records in a table
Account Cycle Val
999 1 0.11
999 2 0.18
999 3 0.27
999 4 0.35
999 5 0.52
999 6 0.61
999 7 -200
999 8 0.63
999 9 92.00
999 10 88.00
999 11 -400
999 12 0.8
And I want to produce a RUNNING TOTAL of the VAL column, but when it is a MINUS figure, then RESET the running total to 0.
I therefore want...........
Cycle Running Total
1 0.11
2 0.29
3 0.56
4 0.91
5 1.43
6 2.04
7 0
8 0.63
9 92.63
10 180.63
11 0
12 0.80
Can see how to do the running total, but not how to reset it to zero.
September 04, 2003 - 9:59 am UTC
what we can do is this. I'll build it iteratively, first we'll need to add something inside of account to subpartition account - to get our "breaks".
to do that i'll take the CYCLE column you have and take the max of the cycle in a sliding window -- based on "val" -- we'll return NULL if val is not negative -- so this decoded cycle only has a value when val is negative. taking the max of that in this window gives us something to "break" on:
ops$tkyte@ORA920LAP> select account, cycle, val,
2 max( decode(sign(val),-1,cycle,decode(cycle,1,1,null)) )
3 over (partition by account order by cycle) max_cycle
4 from t
5 /
ACCOUNT CYCLE VAL MAX_CYCLE
---------- ---------- ---------- ----------
999 1 .11 1
999 2 .18 1
999 3 .27 1
999 4 .35 1
999 5 .52 1
999 6 .61 1
999 7 -200 7
999 8 .63 7
999 9 92 7
999 10 88 7
999 11 -400 11
999 12 .8 11
12 rows selected.
Now, a running total by account/max_cycle is easy:
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select account, cycle, val,
2 sum(decode(sign(val),1,val,null))
3 over (partition by account, max_cycle order by cycle) running
4 from (
5 select account, cycle, val,
6 max( decode(sign(val),-1,cycle,decode(cycle,1,1,null)) )
7 over (partition by account order by cycle) max_cycle
8 from t
9 )
10 /
ACCOUNT CYCLE VAL RUNNING
---------- ---------- ---------- ----------
999 1 .11 .11
999 2 .18 .29
999 3 .27 .56
999 4 .35 .91
999 5 .52 1.43
999 6 .61 2.04
999 7 -200
999 8 .63 .63
999 9 92 92.63
999 10 88 180.63
999 11 -400
999 12 .8 .8
12 rows selected.
Roll your own ...
Adrian Billington, September 04, 2003 - 10:31 am UTC
I was looking at this and thought this would be a perfect use for a user-defined aggregate function. I've added my implementation of a function I've called "BREAKSUM" below:-
920> --
920> -- Create a TYPE to support our aggregate "BREAKSUM" function...
920> --
920> CREATE OR REPLACE TYPE typ_obj_breaksum AS OBJECT
2 (
3 sum NUMBER,
4
5 STATIC FUNCTION ODCIAggregateInitialize (
6 sctx IN OUT typ_obj_breaksum
7 ) RETURN NUMBER,
8
9 MEMBER FUNCTION ODCIAggregateIterate (
10 self IN OUT typ_obj_breaksum,
11 value IN NUMBER
12 ) RETURN NUMBER,
13
14 MEMBER FUNCTION ODCIAggregateTerminate (
15 self IN typ_obj_breaksum,
16 retval OUT NUMBER,
17 flags IN NUMBER
18 ) RETURN NUMBER,
19
20 MEMBER FUNCTION ODCIAggregateMerge (
21 self IN OUT typ_obj_breaksum,
22 ctx2 IN typ_obj_breaksum
23 ) RETURN NUMBER
24 );
25 /
Type created.
920>
920> CREATE OR REPLACE TYPE BODY typ_obj_breaksum IS
2
3 /************************** Initialisation ********************************/
4 STATIC FUNCTION ODCIAggregateInitialize (
5 sctx IN OUT typ_obj_breaksum
6 ) RETURN NUMBER IS
7 BEGIN
8 sctx := typ_obj_breaksum(0);
9 RETURN ODCIConst.Success;
10 END;
11
12 /************************** Iteration ********************************/
13 MEMBER FUNCTION ODCIAggregateIterate (
14 self IN OUT typ_obj_breaksum,
15 value IN NUMBER
16 ) RETURN NUMBER IS
17 BEGIN
18 self.sum := CASE
19 WHEN value > 0
20 THEN self.sum + value
21 ELSE 0
22 END;
23 RETURN ODCIConst.Success;
24 END;
25
26 /************************** Termination ********************************/
27 MEMBER FUNCTION ODCIAggregateTerminate (
28 self IN typ_obj_breaksum,
29 retval OUT NUMBER,
30 flags IN NUMBER
31 ) RETURN NUMBER IS
32 BEGIN
33 retval := self.sum;
34 RETURN ODCIConst.Success;
35 END;
36
37 /************************** Merge ********************************/
38 MEMBER FUNCTION ODCIAggregateMerge (
39 self IN OUT typ_obj_breaksum,
40 ctx2 IN typ_obj_breaksum
41 ) RETURN NUMBER IS
42 BEGIN
43 self.sum := self.sum + ctx2.sum;
44 RETURN ODCIConst.Success;
45 END;
46
47 END;
48 /
Type body created.
920> sho err
No errors.
920>
920> --
920> -- BREAKSUM user-defined aggregate function...
920> --
920> CREATE OR REPLACE FUNCTION breaksum (
2 input NUMBER
3 ) RETURN NUMBER
4 PARALLEL_ENABLE
5 AGGREGATE USING typ_obj_breaksum;
6 /
Function created.
920>
920> --
920> -- Create a table with the data in question...
920> --
920> CREATE TABLE accounts ( account NUMBER, cycle NUMBER, val NUMBER(5,2) );
Table created.
920>
920> ALTER SESSION SET CURSOR_SHARING = FORCE;
Session altered.
920>
920> INSERT INTO accounts VALUES ( 999, 1, 0.11 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 2, 0.18 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 3, 0.27 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 4, 0.35 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 5, 0.52 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 6, 0.61 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 7, -200 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 8, 0.63 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 9, 92.00 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 10, 88.00 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 11, -400 );
1 row created.
920> INSERT INTO accounts VALUES ( 999, 12, 0.8 );
1 row created.
920>
920> --
920> -- Now we can use our BREAKSUM function over the data...
920> --
920> SELECT account
2 , cycle
3 , val
4 , BREAKSUM ( val ) OVER
5 ( PARTITION BY account ORDER BY cycle ) AS running
6 FROM accounts;
ACCOUNT CYCLE VAL RUNNING
---------- ---------- ---------- ----------
999 1 .11 .11
999 2 .18 .29
999 3 .27 .56
999 4 .35 .91
999 5 .52 1.43
999 6 .61 2.04
999 7 -200 0
999 8 .63 .63
999 9 92 92.63
999 10 88 180.63
999 11 -400 0
999 12 .8 .8
12 rows selected.
Regards
Adrian
September 04, 2003 - 11:24 am UTC
interesting solution -- neat.
but, i tested it on a scaled up scenario, the plsql call (which can be removed) does add some amount of cpu overhead.
begin
for a in 1 .. 998
loop
for c in 1..15
loop
insert into accounts values ( a, c, decode(mod(c,5), 0, -1, 1 ) );
end loop;
end loop;
end;
/
commit;
SELECT account
, cycle
, val
, BREAKSUM ( val ) OVER
( PARTITION BY account ORDER BY cycle ) AS running
FROM accounts
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 30 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1000 1.71 1.70 0 38 0 14982
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1002 1.74 1.74 0 68 0 14982
********************************************************************************
select account, cycle, val,
sum(decode(sign(val),1,val,null))
over (partition by account, max_cycle order by cycle) running
from (
select account, cycle, val,
max( decode(sign(val),-1,cycle,decode(cycle,1,1,null)) )
over (partition by account order by cycle) max_cycle
from accounts
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1000 0.40 0.40 0 38 0 14982
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1002 0.40 0.40 0 38 0 14982
as always -- i go back to my mantra "if you can do it in a single sql statement"...
Mirjana, September 04, 2003 - 10:37 am UTC
It's a fair cop...
Adrian Billington, September 04, 2003 - 11:36 am UTC
Funny, because as you posted your followup I was just identifying the fact that the SQL solution was quicker when running at volume.
The only reason I would possibly consider the aggregate over the SQL would be to remove the complexity from the SQL itself if legibility was a real issue. The SQL-only solution already has one level of in-line view nesting which could easily turn into three or four when we scale up to a real life business-related reporting requirement or we are not dealing in "subpartitions" that start neatly at 1 but instead need to find different minimums for each partition. But if the people who support the app. are comfortable reading the SQL, then of course it makes sense to go for the quickest every time...
I hadn't considered the PL/SQL context switching when I wrote the aggregate. I assumed because we were plugging into the extensibility framework that a user-defined aggregate might work nearly as fast as a built-in. Alas not. Your SQL solution has 7 built-in function calls whereas mine has one user-defined function call, yet there's no comparison in performance.
Regards
Adrian
September 04, 2003 - 11:44 am UTC
I love inline views -- nested 3 or 4 deep sometimes. makes the sql so easy to read.
SQL can be commented just like code!!!
we can use LAG() easily to see if we are at "1", we just need to decode( lag(cycle) over (...), NULL, cycle, null ) (if first cycle -- spit it out)
I think people can parse the sql and if commented -- like code -- it would be as legible as anything else.
I always find a mixed code+sql implementation harder to read and maintain. I always prefer a pure SQL set based solution myself!
maybe if we stop trying to insulate people from "really hard scary stuff", they'll begin to understand the power that is there? and it'll become less scary and hard.
Wait'll you see the sql MODEL clause in the next release -- now that, that is going to be really cool and can change many things. iteration, recursion, dynamic subscripting -- alot like have a spreadsheet in sql. I really hope people wouldn't avoid using it cause "it looks strange" at first.
Bring it on...
Adrian Billington, September 04, 2003 - 11:59 am UTC
Tom
Speaking on behalf of the team I work in, absolutely not. We are very quick to embrace new "stuff". For example, I love analytics and my colleagues and I leapt on them as soon as they were available in 816 and now they are all over our systems - aggregations, derivations, data marts. We are looking to make use of all the whizz-bang stuff wherever it makes sense to do so and not because it makes our CVs look good ;o)
Can't wait for 10g - I notice the first articles on new features are starting to trickle through. It's a little unfair that certain non-Oracle stalwarts and beta-partners get access to information so early that they can write books / articles before the product is even on the market. Means we "everyday" people down here don't get a look in before it's too late...
Regards
Adrian
September 05, 2003 - 1:31 pm UTC
everyday people can write books too!! the people who write most of the books are in fact everyday people.
Yes, the people writing about the new features get a peek at them before hand. It is a mixed bag though, until very very very recently
10g was 10i....
the model clause was called spreadsheet...
and thousands of other changes from beta to beta :)
9i and 10g
Asim Naveed, September 06, 2003 - 12:55 am UTC
Hi,
Its really nice to know about MODEL Clause.
We are developing a BI front end product that will
have feature that it can support both SQL SERVER analysis
services and Oracle 9i rel. 2
(without OLAP option) at the backend.
In this product, I have made the metrics using MDX's
calculated members feature (i.e a spread sheet
within a database).
But I was unable to do that in pure SQL of ORACLE.
I have made some metrics using analytical functions, but
those are static queries not dynamic.
Can I say to my boss that if you want it in pure SQL then
wait for the MODEL clause of 10g.
Please answer the following,
1- Is MODEL clause providing the functionality something
like Calculated members provide in Analysis Services.
2- Is it correct that 9i rel.2 didnt have any feature
equivalent to MDX's calculated members. Note that
we can easily just define the formula, name it
and then store it. Then when
we select dimensions in the query , it applies that
formula to that dimensions, we do not have to
explicitly put the formula in the query.
Thanks
September 06, 2003 - 8:48 am UTC
1) i don't know what calculated members "provides" so cannot really comment.
2) aren't those called "views"
Cum. Sum
Jagjeet Singh Malhi, September 19, 2003 - 2:17 am UTC
hello Sir ..
This query is working with 8.1.5
SQL> select ename,sal from emp;
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
SQL> get a
1 Select ename,sal,
2 (select sum(sal) from (Select rownum r,ename,sal from
3 (select ename,sal from emp )) QUERY_REPEAT
4 where query_repeat.r <= main_query.r ) cum_sal
5 from
6 (Select rownum r,ename,sal from
7* (select ename,sal from emp )) MAIN_QUERY
SQL> /
ENAME SAL CUM_SAL
---------- ---------- ----------
SMITH 800 800
ALLEN 1600 2400
WARD 1250 3650
JONES 2975 6625
MARTIN 1250 7875
BLAKE 2850 10725
CLARK 2450 13175
SCOTT 3000 16175
KING 5000 21175
TURNER 1500 22675
ADAMS 1100 23775
JAMES 950 24725
FORD 3000 27725
MILLER 1300 29025
14 rows selected.
SQL> select ename,sal from emp order by sal;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
SCOTT 3000
FORD 3000
KING 5000
14 rows selected.
SQL> get a
1 Select ename,sal,
2 (select sum(sal) from (Select rownum r,ename,sal from
3 (select ename,sal from emp order by sal )) query_repeat
4 where query_repeat.r <= main_query.r ) cum_sal
5 from
6 (Select rownum r,ename,sal from
7* (select ename,sal from emp order by sal )) main_query
SQL> /
ENAME SAL CUM_SAL
---------- ---------- ----------
SMITH 800 800
JAMES 950 1750
ADAMS 1100 2850
WARD 1250 4100
MARTIN 1250 5350
MILLER 1300 6650
TURNER 1500 8150
ALLEN 1600 9750
CLARK 2450 12200
BLAKE 2850 15050
JONES 2975 18025
SCOTT 3000 21025
FORD 3000 24025
KING 5000 29025
14 rows selected.
SQL>
SQL> select ename,sal from emp order by ename;
ENAME SAL
---------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SCOTT 3000
SMITH 800
TURNER 1500
WARD 1250
14 rows selected.
SQL> get a
1 Select ename,sal,
2 (select sum(sal) from (Select rownum r,ename,sal from
3 (select ename,sal from emp order by ename )) query_repeat
4 where query_repeat.r <= main_query.r ) cum_sal
5 from
6 (Select rownum r,ename,sal from
7* (select ename,sal from emp order by ename )) main_query
SQL> /
ENAME SAL CUM_SAL
---------- ---------- ----------
ADAMS 1100 1100
ALLEN 1600 2700
BLAKE 2850 5550
CLARK 2450 8000
FORD 3000 11000
JAMES 950 11950
JONES 2975 14925
KING 5000 19925
MARTIN 1250 21175
MILLER 1300 22475
SCOTT 3000 25475
SMITH 800 26275
TURNER 1500 27775
WARD 1250 29025
14 rows selected.
SQL>
September 20, 2003 - 5:02 pm UTC
do it for a real sized set of data :)
How about this in versions before or equal to 8.1.5
Asim Naveed, September 21, 2003 - 4:18 am UTC
select a.empno, a.ename, a.sal, sum(b.sal)
from emp a, emp b
WHERE a.sal > b.sal
OR
(
a.sal= b.sal
and a.rowid >= b.rowid)
group by a.empno, a.ename, a.rowid,a.sal
order by a.sal, a.rowid
Just replase the a.sal column in WHERE clause and
ORDER BY clause of the above query with what ever column
you want to order by even if the column is not unique.
Tom , please comment on correctness and performance
of above query
September 21, 2003 - 10:12 am UTC
this is the power of analytics, they remove the need for messy, non-intuitive, SLOW self joins and other tricks.
that is a non-equi join of a table with itself. think about the work involved.
but a trivial benchmark shows how bad this is:
ops$tkyte@ORA920> drop table emp;
Table dropped.
ops$tkyte@ORA920> create table emp
2 as
3 select object_id empno, object_name ename, object_id/100 sal
4 from all_objects
5 where rownum <= 100
6 /
Table created.
ops$tkyte@ORA920> set autotrace traceonly statistics
ops$tkyte@ORA920> select a.empno, a.ename, a.sal, sum(b.sal)
2 from emp a, emp b
3 WHERE a.sal > b.sal
4 OR
5 (
6 a.sal= b.sal
7 and a.rowid >= b.rowid)
8 group by a.empno, a.ename, a.rowid,a.sal
9 order by a.sal, a.rowid
10 /
100 rows selected.
<b>
Elapsed: 00:00:00.07
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
303 consistent gets</b>
ops$tkyte@ORA920> select a.empno, a.ename, a.sal, sum(sal) over (order by sal, rowid) sum_sal
2 from emp a
3 /
100 rows selected.
<b>
Elapsed: 00:00:00.09
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets</b>
ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> delete from emp;
100 rows deleted.
ops$tkyte@ORA920> insert into emp
2 select object_id empno, object_name ename, object_id/100 sal
3 from all_objects
4 where rownum <= 1000
5 /
1000 rows created.
ops$tkyte@ORA920> set autotrace traceonly statistics
ops$tkyte@ORA920> select a.empno, a.ename, a.sal, sum(b.sal)
2 from emp a, emp b
3 WHERE a.sal > b.sal
4 OR
5 (
6 a.sal= b.sal
7 and a.rowid >= b.rowid)
8 group by a.empno, a.ename, a.rowid,a.sal
9 order by a.sal, a.rowid
10 /
1000 rows selected.
<b>Elapsed: 00:00:01.62
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9009 consistent gets</b>
ops$tkyte@ORA920> select a.empno, a.ename, a.sal, sum(sal) over (order by sal, rowid) sum_sal
2 from emp a
3 /
1000 rows selected.
<b>Elapsed: 00:00:00.17
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets</b>
ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> delete from emp;
1000 rows deleted.
ops$tkyte@ORA920> insert into emp
2 select object_id empno, object_name ename, object_id/100 sal
3 from all_objects
4 where rownum <= 10000
5 /
10000 rows created.
ops$tkyte@ORA920> set autotrace traceonly statistics
ops$tkyte@ORA920> select a.empno, a.ename, a.sal, sum(b.sal)
2 from emp a, emp b
3 WHERE a.sal > b.sal
4 OR
5 (
6 a.sal= b.sal
7 and a.rowid >= b.rowid)
8 group by a.empno, a.ename, a.rowid,a.sal
9 order by a.sal, a.rowid
10 /
10000 rows selected.
<b>Elapsed: 00:03:17.97
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
580058 consistent gets</b>
ops$tkyte@ORA920> select a.empno, a.ename, a.sal, sum(sal) over (order by sal, rowid) sum_sal
2 from emp a
3 /
10000 rows selected.
<b>Elapsed: 00:00:00.42
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
58 consistent gets</b>
ops$tkyte@ORA920> set autotrace off
<b>pretty compelling isn't it</b>
Compare with solutions in 8,.1.5 an prior
Asim Naveed, September 21, 2003 - 2:14 pm UTC
You are 100 percent right, that analytics are faster,
but I propose this solution for versions 8.1.5 and earlier,
those dont have analytic functions, so can you please
compare this solution with other solutions in version
8.1.5 or earlier.
Thanks
September 21, 2003 - 2:37 pm UTC
my suggestion would be to do this in the client, as you are fetching rows, in really old software like that.
the cost of doing it in SQL is exhorbinant.
Yet another try for versions before or equal to 8.1.5
Urs, September 22, 2003 - 8:24 am UTC
This one is still slower than analytics but scales better than pure SQL without
analytics. Following Tom's mantra: If you can't do it in SQL, use...
SQL> CREATE OR REPLACE
2 PACKAGE test_nocvs
3 IS
4 FUNCTION sum_up(
5 pRowNumber IN PLS_INTEGER,
6 pValue IN NUMBER)
7 RETURN NUMBER;
8
9 PRAGMA RESTRICT_REFERENCES(sum_up, WNDS, RNDS);
10 END;
11 /
Paket wurde erstellt.
Abgelaufen: 00:00:00.60
SQL>
SQL> CREATE OR REPLACE
2 PACKAGE BODY test_nocvs
3 IS
4 pckSum NUMBER;
5
6 FUNCTION sum_up(
7 pRowNumber IN PLS_INTEGER,
8 pValue IN NUMBER)
9 RETURN NUMBER
10 IS
11 BEGIN
12 IF pRowNumber = 1 THEN
13 pckSum := pValue;
14 ELSE
15 pckSum := pckSum + pValue;
16 END IF;
17
18 RETURN pckSum;
19 END;
20 END;
21 /
Paketrumpf wurde erstellt.
Abgelaufen: 00:00:00.60
SQL> create table emp as
2 select object_id empno, object_name ename, object_id/100 sal
3 from all_objects
4 where rownum <= 10000;
Tabelle wurde angelegt.
Abgelaufen: 00:00:01.42
SQL> set autotrace traceonly statistics
SQL> select a.empno, a.ename, a.sal, sum(sal) over (order by sal,rowid) sum_sal
2 from emp a;
3087 Zeilen ausgewõhlt.
Abgelaufen: 00:00:02.44
Statistiken
----------------------------------------------------------
0 recursive calls
12 db block gets
16 consistent gets
12 physical reads
0 redo size
122266 bytes sent via SQL*Net to client
14456 bytes received via SQL*Net from client
207 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3087 rows processed
SQL> select a.empno, a.ename, a.sal, test_nocvs.sum_up(rownum, a.sal) sum_sal
2 from emp a;
3087 Zeilen ausgewõhlt.
Abgelaufen: 00:00:02.54
Statistiken
----------------------------------------------------------
0 recursive calls
12 db block gets
221 consistent gets
0 physical reads
0 redo size
122822 bytes sent via SQL*Net to client
14456 bytes received via SQL*Net from client
207 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3087 rows processed
Is there a SUM - like function for characters
Raj, January 09, 2004 - 3:16 pm UTC
I love your responses with multiple solutions.
I am trying to make use of this window functions to append (concat) the column value for multiple rows.
ex:
create table emp (empid number, empname varchar2(1));
empid empname
1 R
1 A
1 J
2 B
2 I
2 L
2 L
How could I use OVER aggregates to come up with -
emplid Fullname
1 RAJ
2 BILL
Thanks !
January 09, 2004 - 3:23 pm UTC
OVER are analytics -- they are not aggregates at all.....
IF there is a known upper bound on the number of rows/empid -- eg:
select max(cnt) from ( select count(*) cnt from t group by empid )
is a well known, stable number -- then
ops$tkyte@ORA920PC> select deptno,
2 max(decode(rn,1,ename||',')) ||
3 max(decode(rn,2,ename||',')) ||
4 max(decode(rn,3,ename||',')) ||
5 max(decode(rn,4,ename||',')) ||
6 max(decode(rn,5,ename||','))
7 from (select deptno,ename,row_number() over(partition by deptno order by
8 ename) rn from emp ) emp
9 group by deptno
10 /
DEPTNO MAX(DECODE(RN,1,ENAME||','))||MAX(DECODE(RN,2,ENAME||',
---------- -------------------------------------------------------
10 CLARK,KING,MILLER,
20 ADAMS,FORD,JONES,SCOTT,SMITH,
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,
ops$tkyte@ORA920PC>
works.....
also, search this site for
stragg
Excellente
Raj, January 09, 2004 - 3:47 pm UTC
It's magical solution I would say.
Thank you !
cumulative sal
A reader, January 11, 2004 - 6:15 pm UTC
Reg. posting 1 above
>>In Oracle8i release 2 (8.1.6), we have analytic functions to perform this
>>operation. In that release, you can code:
>>SELECT ename, sal,
>> SUM(sal) OVER
>> (ORDER BY ename RANGE UNBOUNDED PRECEDING) l_csum
>> FROM emp;
In the above example, I would like to know how the following can be done
if I want to show dept no. along with the employee name and then show the totals per dept.
Like
DEPTNO ENAME SAL L_CSUM
----- ---------- ---------- ----------
10 ADAMS 1100 1100
10 ALLEN 1600 2700
10 BLAKE 2850 5550
Dept 10 total 5550
20 CLARK 2450 8000
20 FORD 3000 11000
20 JAMES 950 11950
20 JONES 2975 14925
20 KING 5000 19925
Dept 20 total 14375
30 MARTIN 1250 21175
30 MILLER 1300 22475
30 SCOTT 3000 25475
30 SMITH 800 26275
30 TURNER 1500 27775
30 WARD 1250 29025
Dept 30 total 9100
-------------------
DB Version: 9.2.0.4
Thank you
January 12, 2004 - 1:10 am UTC
scott@ORA9IR2> select decode( grouping(ename), 0, deptno ) deptno,
2 decode( grouping(ename), 0, ename, 'Dept ' || deptno || ' total' )
3 ename,
4 sal,
5 sum(sal) over (partition by deptno order by sal) l_csum
6 from emp
7 group by grouping sets ((deptno), (deptno,ename,sal))
8 /
DEPTNO ENAME SAL L_CSUM
---------- -------------------- ---------- ----------
10 MILLER 1300 1300
10 CLARK 2450 3750
10 KING 5000 8750
Dept 10 total 8750
20 SMITH 800 800
20 ADAMS 1100 1900
20 JONES 2975 4875
20 FORD 3000 10875
20 SCOTT 3000 10875
Dept 20 total 10875
30 JAMES 950 950
30 MARTIN 1250 3450
30 WARD 1250 3450
30 TURNER 1500 4950
30 ALLEN 1600 6550
30 BLAKE 2850 9400
Dept 30 total 9400
17 rows selected.
Cumulative sal
A reader, January 12, 2004 - 7:41 am UTC
Excellent answer. - Thank you
Cumulative sum
Moiz, January 20, 2004 - 12:46 am UTC
I want to do the following in a single query:
If I have a budget of only, say 7000, then I want to get a list of those employees who will be paid through this budget. Our companies policy is to do the following:
1. Sort the employees in ascending order of their salaries
2. Find the cumulative sum of the salaries
3. Put a "where" clause on this sum
I tried using the following query which is giving wrong result:
1 select ename, sal, deptno, total from
2 (select ename, sal, deptno,
3 sum(sal) over (order by rowid range unbounded preceding) Total
4 from emp)
5 where total < 7000
6* order by sal
SQL> /
ENAME SAL DEPTNO TOTAL
---------- ---------- ---------- ----------
SMITH 800 20 800
WARD 1250 30 3650
ALLEN 1600 30 2400
JONES 2975 20 6625
This is wrong because I am missing James whose sal is 950.
January 20, 2004 - 8:16 am UTC
well, you ordered by ROWID, not by salary.
sum(sal) over (order by sal, empno) total
is what you want.
grouping by bands
dxl, November 02, 2004 - 10:14 am UTC
create table t1 (name varchar2(40), age number(10));
insert into t1 values ('JOHN', 8);
insert into t1 values ('MARY', 75);
insert into t1 values ('MARK', 6);
insert into t1 values ('LUKE', 7);
insert into t1 values ('JO', 8);
insert into t1 values ('DONNA', 58);
insert into t1 values ('AMY', 55);
insert into t1 values ('DAVID', 78);
insert into t1 values ('SANDRA', 56);
insert into t1 values ('MELISSA', 28);
insert into t1 values ('ABBEY', 33);
From the above table data how can i create a query that looks like this:
AGEBAND COUNT
--------- -------------
UNDER10 4
10TO19 0
20TO29 1
30TO39 1
40TO49 0
50TO59 3
60TO69 0
70TO79 2
OVER80 0
ie creating agebands and grouping by them.
I would like to do it in one sql statement if possible,
i have been playing around with aggregates and sums but can't quite see how to get it right.
Are there any analytic functions i could use? or should it be sums and decodes?
(This is for 9.2.0.5)
Thanks
November 02, 2004 - 10:55 am UTC
select ageband, count(*)
from (
select case when age < 10 then 'under10'
when age between 10 and 19 then '10to19'
...
when age >= 80 'Over80' ageband
from t
)
group by ageband;
Another method
Glen Morris, November 03, 2004 - 8:36 pm UTC
Here is another possible method which makes use of a relatively new function (9i and 10g - not sure about 8i).
This would only work because the 'bands' are of the same size.
select case width_bucket(age,10,79,7)
when 0 then 'under10'
when 1 then '10to19'
when 2 then '20to29'
when 3 then '30to39'
when 4 then '40to49'
when 5 then '50to59'
when 6 then '60to69'
when 7 then '70to79'
when 8 then 'Over80'
end ageband
,count(*)
from t1
group by width_bucket(age,10,79,7)
Similar to bands
PK, October 22, 2006 - 10:26 am UTC
I have a table with following sample data:
Key Band Cost
-------------------------
1 A 100
2 B 1000
3 C 1100
Here Key is uniqe (it need not be a number). There are no pre-defined number of bands or number of rows.
Input is Cost.
If cost is less than or equal to 100, band should be A.
If cost is greater than 100 and less than or equal to 1000, band should be B
...and so on
If cost is greater than 1100, cost should be C.
How I can achieve this in one SQL query. I know that it is a bit easy to use PL/SQL block, but I want to achieve this using a one SQL query.
Any suggestions?
October 22, 2006 - 4:12 pm UTC
if cost is > 1100 cost should be C??????
and here I thought C would be 1000 > x <= 1100 ??? that does not seem to fit the logic, are you sure that is what you meant?
Similar to bands -Clarification
PK, October 23, 2006 - 6:07 am UTC
Sorry for the confusion. Please ignore the following for the time being:
If cost is greater than 1100, cost should be C.
Can you please suggest the query for the rest of scenarios?
Thanks.
October 23, 2006 - 10:16 am UTC
select * from t where cost = (select min(cost) from t where cost >= :x)
find the minimum cost greater than or equal to the cost you are looking at (:x)
get that record
Similar to bands - Thanks
PK, October 23, 2006 - 2:59 pm UTC
Thanks for the simple solution. I was thinking of some analytical functions....
what if we need a running "difference"
KK, November 21, 2006 - 11:44 pm UTC
Hi Tom,
What if the requirement is that we need a running "difference" not a running total. If the rows in the table are as follow:-
SLno: Ename Sal
-----------------
1 Jone 4000
2 Allan 7000
3 Tom 9000
4 Victor 1000
Now if I want to divide x dollars to employees shown in the above order and then get a balance column as follows:-
If x=13,000
Ename Sal Balance
--------------------
Jone 4000 0
Allan 7000 0
Tom 9000 6000
Victor 1000
If x=5,000
Ename Sal Balance
--------------------
Jone 4000 0
Allan 7000 6000
Tom 9000 0
Victor 1000
If x=21,000
Ename Sal Balance
--------------------
Jone 4000 0
Allan 7000 0
Tom 9000 0
Victor 1000 0
Could you please tell us how would the query look like?
November 24, 2006 - 12:27 pm UTC
I don't get "tom 9000 6000" - seems like 7000 not 6000
ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2> exec :x := 13000
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select slno, ename, sal,
2 sum(sal) over (order by slno) sumsal,
3 case when (sum(sal) over (order by slno)-sal > :x)
4 then to_number( null )
5 when (sum(sal) over (order by slno) < :x)
6 then 0
7 else sum(sal) over (order by slno)-:x
8 end sumsal
9 from t
10 order by slno
11 /
SLNO ENAME SAL SUMSAL SUMSAL
---------- ---------- ---------- ---------- ----------
1 jone 4000 4000 0
2 allan 7000 11000 0
3 tom 9000 20000 7000
4 victor 1000 21000
ops$tkyte%ORA10GR2> exec :x := 5000
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> /
SLNO ENAME SAL SUMSAL SUMSAL
---------- ---------- ---------- ---------- ----------
1 jone 4000 4000 0
2 allan 7000 11000 6000
3 tom 9000 20000
4 victor 1000 21000
ops$tkyte%ORA10GR2> exec :x := 21000
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> /
SLNO ENAME SAL SUMSAL SUMSAL
---------- ---------- ---------- ---------- ----------
1 jone 4000 4000 0
2 allan 7000 11000 0
3 tom 9000 20000 0
4 victor 1000 21000 0
A reader, December 05, 2006 - 2:22 pm UTC
You are unbelievable....
Cumulative Total
Tennyson Dias, March 26, 2007 - 7:57 am UTC
Hi TOM,
Thank you for your wonderful site, now got a small enquiry below
STOCK TABLE
------------
CODE DOC_DATE QTY
A1 15/12/06 3
A1 15/01/07 5
A1 15/01/07 -1
A1 08/02/07 -2
A1 15/02/07 6
A2 10/01/07 10
so i am interested code wise month wise opening balance and closing balance
Now suppose i run query for the period for Jun and Feb, i want output like this
CODE MONTH OP BAL CLBAL
A1 01/07 3 7
A1 02/07 7 5
A2 01/07 10 10
--
--
can we achive this using analytical function as i dont want use procedure
any help/ suggestion on this is highly appriciated.
Thank you
Tennyson
March 26, 2007 - 11:08 am UTC
no create, in inserts, no lookie - and no promises
Cumulative Total... Try this query
Jignesh, March 27, 2007 - 12:05 pm UTC
SELECT code, to_char(mnth, qty,
(sum(qty) over
(ORDER BY code, mnth RANGE UNBOUNDED PRECEDING))-qty op_bal,
sum(qty) over (ORDER BY code, mnth RANGE UNBOUNDED PRECEDING) cl_bal
from
(select code, trunc(doc_date,'MM') mnth, sum(qty) qty
from <YourTableName>
group by code, trunc(doc_date,'MM')
)
Cumulative sum with Reset
Soumadip, September 04, 2012 - 5:34 am UTC
Hi Tom,
I have a similar query with one of the post before with slight changes.
My Data is like this :-
ID Tot_sum
---------------
1 20
2 40
3 -50
4 70
5 10
6 -100
7 30
8 50
9 -90
10 20
I want the cumulative sum of tot_sum and want to reset the counter when the sum reaches negative and shows the sum as zero like this :-
ID Tot_sum Running_sum
-------------------------------
1 20 20
2 40 60
3 -50 10
4 70 80
5 10 90
6 -100 0
7 30 30
8 50 80
9 -90 0
10 20 20
Scripts :-
create table S1
(
ID NUMBER,
TOT_SUM NUMBER
)
;
insert into S1 (ID, TOT_SUM)
values (1, 20);
insert into S1 (ID, TOT_SUM)
values (2, 40);
insert into S1 (ID, TOT_SUM)
values (3, -50);
insert into S1 (ID, TOT_SUM)
values (4, 70);
insert into S1 (ID, TOT_SUM)
values (5, 10);
insert into S1 (ID, TOT_SUM)
values (6, -100);
insert into S1 (ID, TOT_SUM)
values (7, 30);
insert into S1 (ID, TOT_SUM)
values (8, 50);
insert into S1 (ID, TOT_SUM)
values (9, -90);
insert into S1 (ID, TOT_SUM)
values (10, 20);
commit;
Please let me know can we do it using only SQL statement.
I am using Oracle 10g.
CUMULATIVE SALARY CALUCULATION
chari, April 14, 2013 - 5:59 am UTC
eno,ename,sal
1,A,100
2,B,200
3,C,300
IN THIS WE CALCULATE CUMULATIVE SAL..JUST A SIMPLE QUERY IS :
WE DONT USING ANALYTICAL QUERY....>
"SELECT E.ENO,E.ENAME,E.SAL,(SELECT SUM(SAL) FROM TABLE D WHERE D.ENO<=E.ENO) CUME_SAL FROM TABLE E ORDER BY ENO;"
O/P IS ---->:ENO,ENAME,SAL,CUME_SAL
1, A ,100, 100
2, B ,200, 300
3, C ,300, 600
MAY I EXPECT SURE IT WILL EXECUTE... SAME AS ONE THING I HAVE A DOUBT...IN OUT PUT TABLE I WANT LIKE THIS QUERY..
ENO,ENAME,SAL
1, A ,600
2, B ,600
3, C ,600
PLEASE LET ME A QUERY..ASAP..
April 22, 2013 - 6:56 pm UTC
YOUR CAPSLOCK IS STUCK
yes, you could get your running total using a scalar subquery - it would be about the slowest, least efficient, most IO intensive approach you could take - but it would "work"
I would not suggest this ever - not with analytic windowing functions.
need your clarification
Ranjan, September 26, 2013 - 10:20 pm UTC
Hi Tom,
I think there is a bug in your below logic posted for "Neil Chapman" in september4,2003.
But by "ODCIAggregate" technique it gives the correct result
select account, cycle, val,
2 sum(decode(sign(val),1,val,null))
3 over (partition by account, max_cycle order by cycle) running
4 from (
5 select account, cycle, val,
6 max( decode(sign(val),-1,cycle,decode(cycle,1,1,null)) )
7 over (partition by account order by cycle) max_cycle
8 from t
9 )
10 /
ACCOUNT CYCLE VAL RUNNING
---------- ---------- ---------- ----------
999 1 .11 .11
999 2 .18 .29
999 3 .27 .56
999 4 .35 .91
999 5 .52 1.43
999 6 .61 2.04
999 7 -200
999 8 .63 .63
999 9 92 92.63
999 10 88 180.63
999 11 -400
999 12 .8 .8
If we will update the value for cycle 11 from -400 to -150.63 , then the
corresponding running for cycle 11 should be 180-150.63=30 and then for cycle 12
it should add up 30+.8=30.8 but it is giving null for cycle 11 and .8 for cycle 12.
AFTER UPDATING
_______________
select account, cycle, val,
2 sum(decode(sign(val),1,val,null))
3 over (partition by account, max_cycle order by cycle) running
4 from (
5 select account, cycle, val,
6 max( decode(sign(val),-1,cycle,decode(cycle,1,1,null)) )
7 over (partition by account order by cycle) max_cycle
8 from t
9 )
10 /
ACCOUNT CYCLE VAL RUNNING
---------- ---------- ---------- ----------
999 1 .11 .11
999 2 .18 .29
999 3 .27 .56
999 4 .35 .91
999 5 .52 1.43
999 6 .61 2.04
999 7 -200
999 8 .63 .63
999 9 92 92.63
999 10 88 180.63
999 11 -150.63
999 12 .8 .8
I think it seems very difficult to do with single sql without that "user defined aggregate" technique.
Could you please say about my findings.
Thanks as always,