Skip to Main Content
  • Questions
  • Analytical Functions- a brief introduction

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: November 11, 2001 - 1:03 pm UTC

Last updated: September 12, 2022 - 3:09 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom

One of our main dissapointment is that , we cannot use analytical functions in sql from within a procedure, as they are not compatible with the pl/sql engine.

Further, when I try to update my knowledge with the analytical functions, I get the doubt as to the total number of analytical functions available with 8i.

Can you give a brief introduction to the analytical functions, giving the simplest example possible for each analytical function( as you have done in case of CASE).

As far as I am concerned, the total number of analytical functions are
1.group by
2.rollbup
3.cube
4.partition
5.Rollover

Tom, kindly give the simples of examples possible, so that we understand the concept first, and can then use them to reosolve complex functionalities.

( If we can use these above functions in pl/sql , I am curious, how much of coding it would reduce, I guess a lot, a lot lot infact.

Thank you

and Tom said...

You can use them in PLSQL, you can either:

o use a view
o use native dynamic sql

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:186812348071
for example


I don't agree with your list of analytic functions.  There is

o group by rollup
o group by cube
o analytic functions such as sum, avg, lag, lead, min, max, and so on (they are documented here:
http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76989/functio2.htm#81409

You can search for group by rollup or group by cube on this site to see some examples of that type of statement.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1512805503041 <code>
is a good example of them.

Search for

partition over nulls

to see some analytic functions. If you are really interested in this -- I can recommand a book (mine) that has a complete chapter on nothing but how to use analytic functions with a comparision between using and NOT using them performance wise (they can be truly amazing at speeding many operations up as well as doing things that are otherwise impossible or very awkward to do in SQL)


Rating

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

analytical functions

vinay, December 26, 2001 - 5:56 am UTC

Helped me a lot. Thanx

how can I do this?

A reader, October 17, 2002 - 6:22 pm UTC

Hi

I would like to know if this is possible, I am using table emp

I want to show the top 3 sal and sum the rest, something like

select ename, sal
from (select ename, sal
from emp
order by sal desc)
where rownum <= 3
union
select 'REST', sum(sal)
from (select ename, sal, rownum numrow
from emp
order by sal desc)
where numrow > 3
order by 2

is this possible with analityc functions?

Tom Kyte
October 17, 2002 - 7:41 pm UTC

scott@ORA920.US.ORACLE.COM> select decode( rn, 1, ename, 2, ename, 3, ename, 'REST' ), sum(sal)
2 from ( select row_number() over ( order by sal desc ) rn, ename, sal
3 from emp
4 )
5 group by decode( rn, 1, ename, 2, ename, 3, ename, 'REST' )
6 order by 2
7 /

DECODE(RN, SUM(SAL)
---------- ----------
ford 3000
scott 3000
king 5000
REST 18025



here

A reader, October 17, 2002 - 7:00 pm UTC

select numrow, deptno, sal, max(total) over () rest
from (
select
numrow,
deptno,
sal,
sum(sal) over (order by numrow range between 3 FOLLOWING and unbounded following) total
from
(select
empno,
deptno,
sal,
sum(sal) over (order by empno) cumulative,
dense_rank() over (order by sal desc) numrow
from emp) b)
where numrow <= 3
/


Tom Kyte
October 17, 2002 - 7:42 pm UTC

see above, could be "shorter"

Partition analytical functions

IB, October 18, 2002 - 12:47 am UTC

I have read many examples from Tom's reply but still i am not very sure how exactly it (partition)works ,when it can be used .If i know the exact use of it i feel it will be very usefull.Could you please explain me with some good examples

Tom Kyte
October 18, 2002 - 8:52 am UTC

I'll have to refer you to my book "Expert one on one Oracle". I have a lengthy chapter on this topic in there with pictures and everything.

Or:
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/analysis.htm#1020 <code>






for IB

A reader, October 18, 2002 - 1:28 am UTC

Hi IB

do you know how break works in SQL*PLUS? It's probably a good point to start

Distributing tablespace equally

steve, January 21, 2003 - 5:02 pm UTC

Hi Tom,

we are developing a hot backup solution. We want to
backup each tablespace once a week and perform backups
every day. We plan to create a table like:

create table backup_schedule (
ts varchar2(40), -- tablespace to backup
day number(4,0) -- day on which to backup 1..7
)

The goal is that we want to populate the table so that the amount of data we backup each day is more or less the same each day. For example, if the total database size is 700G,
then we should backup 100G (more or less) every day.

Can you suggest (perhaps using analytical functions) a
query that would populate this table? I hope my question
makes sense.

BTW: Oracle 9.2.0.1

Thanks

Steve

Tom Kyte
January 21, 2003 - 7:00 pm UTC

sorry -- I cannot think of a way to do a bin fitting problem in SQL -- even with the analytics.

does steve win a prize

Josh, January 21, 2003 - 7:14 pm UTC

I suggest Steve wins a prize for stumping Tom.

Winner Winner Chicken Dinner

awk

Steve, January 22, 2003 - 5:37 am UTC

Hi Tom,

Due to lacck of time, we threw something together in awk.
Maybe useful to post (??):

First, we created an input file as follows:

select sum(bytes) total, ceil(sum(bytes)/7)
from dba_tablespaces a,
dba_data_files b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME

/
select a.TABLESPACE_NAME,sum(bytes)
from dba_tablespaces a,
dba_data_files b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME
group by a.TABLESPACE_NAME
order by 2 desc
/




Then we ran the following awk script. Seems to work well.
No guarantees of course. But a good starting point.

BytesPerDay=`head -1 $1 | awk '{print $2}'`
cp /dev/null $2
cat $1 | awk -v BytesPerDay=$BytesPerDay -v OutputFileName=$2 \
'
BEGIN {last=6;outfile=OutputFileName} {
if (NR == 1) getline;
for (i=0; i<7; i++){
found=0;
iindex=(i+last+1)%7;
if(a[iindex]+$2 < BytesPerDay) {
found=1;
last=iindex;
a[iindex]+=$2;
printf("insert into backup_schedle (ts, day) values(%c%s%c,%d)\n/\n",39,$1,39,iindex) >> out
file
break;
}
}
#
# if found = 0 at this point, we were not able
# to find a place for this so we will put it
# in the iindex with the least amount
#
if (found == 0) {
smallest=a[0];
smallesti=0;
for(i=1;i<7;i++) {
if (a[i] < smallest ) {
smallest=a[i];
smallesti=i;
}
}
a[smallesti]+=$2;
printf("insert into backup_schedule (ts day) values(%c%s%c,%d)\n/\n",39,$1,39,iindex) >> outfile
}
}
END {
for(i=0;i<7;i++)
printf("Size of backup %d = %12.0f\n",i,[i]);
}
'

Question
========

We have a few partitioned tables. Each partition has
its own tablespace. With this approach, different
parts (partitions) of a table get backed up on different
days. Perhaps not clean, but will work. Do you see any
problem with this?

Tom Kyte
January 22, 2003 - 8:18 am UTC

It'll work -- as long as you keep the last 3 or so hot backups of everything and all of the needed archive redo logs, no problem restoring with that.

Is Analytically functions included in 91 enterprise

A reader, March 05, 2003 - 3:31 am UTC

Hi tom,
If we need to use the analtical functions in 9'i do we have to get some additional licese , as we are currently having Oracle 9.0.0.1 enterprise Edition with partioning option


Tom Kyte
March 05, 2003 - 7:55 am UTC

they are part of 9i SE and EE.

you can discover what you have by looking at the new features guide:

</code> http://docs.oracle.com/docs/cd/B10501_01/nav/docindex.htm#index-NEW <code>

(read the getting to know Oracle8i guide for 8i options/features)

Is Analytically functions included in 91 enterprise

A reader, March 05, 2003 - 3:31 am UTC

Hi tom,
If we need to use the analtical functions in 9'i do we have to get some additional licese , as we are currently having Oracle 9.0.0.1 enterprise Edition with partioning option


A reader, March 05, 2003 - 3:32 am UTC

Hi tom,
If we need to use the analtical functions in 9'i do we have to get some additional licese , as we are currently having Oracle 9.0.0.1 enterprise Edition with partioning option


A reader, May 13, 2003 - 4:50 pm UTC

analytic functions was introduced in Introduced in Oracle 8.1.6, and in oracle 9i both standard and enterprise editions has license to use it. without additional license.
I think you should read here
</code> http://otn.oracle.com/products/oracle9i/content.html <code>
about available features.


is that possible?

sgs_ocp, May 22, 2003 - 7:56 am UTC

Hi Tom,

I would like to know
Can we use analytical functions to replace
start with and connect by

as its (latter) just hard to digest.


Tom Kyte
May 23, 2003 - 8:11 am UTC

no, they are apples and toaster ovens.

totally different beasts

Are all analytical functions convertable to standard SQL?

Fan, July 18, 2003 - 11:54 am UTC

Hi Tom,

I am wondering if I can convert any analytical function to standard SQL, no matter how complex.

If not, please give an example that has to be converted to use PL/SQL as well.

Thanks.

Tom Kyte
July 19, 2003 - 11:19 am UTC

most of them probably could but they would run slower the molasses in feburary in alaska. complicated self joins, correlated subqueries, etc.


I'm not going to search for one that absolutely cannot be -- cause it would be pretty near impossible to prove you couldn't.

besides -- analytics ARE standard sql.

Can I apply analystical here? Please help

A reader, January 16, 2006 - 2:28 pm UTC

select min(date)
from TABLE1
where ((equipment = 'XX' and audit_id > xxxxxx)
or (equipment_= 'XS' and audit_id > xxxxxx))
and date is not null


Tom Kyte
January 16, 2006 - 3:42 pm UTC

you could, but why do you want to?

to get the MIN(date) given your predicate, you have written the query correctly.

Now, if you wanted to have the min(date) that matched your predicate assigned to every row in your result set (instead of getting just one row as you are now), then we could investigate analytics.

Frankly you do not need the "date is not null" part (aggregates will not consider nulls) and you could just code:

where audit_id > xxxxxxx and equipment in ( 'XX', 'XS' )

but it would be pretty much the same (oracle would just rewrite the in back to being an or. An index on (equipment,audit_id,date) would be of benefit to a query like this.

Thank you!

A reader, January 17, 2006 - 4:42 pm UTC


Use of Analytical Functions.

Shailendra, February 17, 2006 - 4:07 am UTC

Hi Tom,
The text above is really useful. I however have a query. I have two tables
Table A
--------
COl 1 Col2
----- -----
AB CD
PQ XY


Table B
-------
COl1 Col2
---- -----
AB 10
AB 20
AB 30
PQ 40
PQ 50


I need to use analytical function to get a o/p as shown below



output
------

Col 1 Col 2 Col3
----- ----- ----
AB CD 10
20
30
PQ XY 40
50

How can this be achived using analytical function

Query re-written using analytical functions

Hitesh, April 21, 2006 - 4:38 am UTC

Hi Tom,

I am in the process of learning Analytical functions, however need your help in translating this query using analytics

We have trwo tables Rdata and Idata where Idata is the child of Rdata.

Rdata
Request_timestamp TIMESTAMP
Request_tag VARCHAR2(10)
Request_terms VARCHAR2(100)

Idata
Impression_timestamp TIMESTAMP
Request_tag VARCHAR2(10)

We are showing the TOP 100 Request_terms based on their count within a defined timeinterval for which the child records also exists.

Select rownum, terms, impressions, days from
( select terms, sum(imprs) impressions, count(dt) days from
( select trunc(r.request_timestamp) dt, r.request_terms terms, count(*) imprs from rdata r, idata i
where
r.request_timestamp < to_date('01-Apr-2005', 'dd-Mon-yyyy') and r.request_timestamp >= (to_date('01-Apr-2005', 'dd-Mon-yyyy') - interval '7 0:0:0.0' day to second)
and r.request_tag = i.request_tag group by r.request_terms, trunc(r.request_timestamp)
)
group by terms order by impressions desc )
where rownum <= 100

Thanks

Tom Kyte
April 21, 2006 - 7:32 am UTC

no creates
no inserts
no look

How to get this output using Analytic functions

Ashiq Shamsudeen A, April 21, 2006 - 8:06 am UTC

I've table rnd(which has 2 columns) with the following data's

REWARDLIST_REWARDID S
------------------- -
1 0
1 1
1 1
2 0
2 0
2 1
2 1
2 2
3 0
3 1
3 2
4 1
4 1
4 1


I'll be grouping the above data ,

SELECT REWARDLIST_REWARDID ,status
from rnd
group by REWARDLIST_REWARDID,status

and i'll get below results

REWARDLIST_REWARDID S
------------------- -
1 0
1 1
2 0
2 1
2 2
3 0
3 1
3 2
4 1


I need query to get the result with the following conditions , status should be same for any given id's.So from the above result I should get only the rewardlist_rewardid = 4.

REWARDLIST_REWARDID S
------------------- -
4 1


How can I get this using Analytic functions ?

=======
create table rnd(rewardlist_rewardid int, status char(1))
/
insert into rnd values(1,'0');
insert into rnd values(1,'1');
insert into rnd values(1,'1');
insert into rnd values(2,'0');
insert into rnd values(2,'0');
insert into rnd values(2,'1');
insert into rnd values(2,'1');
insert into rnd values(2,'2');
insert into rnd values(3,'0');
insert into rnd values(3,'1');
insert into rnd values(3,'2');
insert into rnd values(4,'1');
insert into rnd values(4,'1');
insert into rnd values(4,'1');

commit;
=======================

Tom Kyte
April 21, 2006 - 8:48 am UTC

analytics are cool - but they are not the answer to everything.  

You are looking for an old fashioned aggregate here:


ops$tkyte@ORA10GR2> select rewardlist_rewardid, min(status)
  2    from rnd
  3   group by rewardlist_rewardid
  4  having count(distinct status) = 1;
 
REWARDLIST_REWARDID M
------------------- -
                  4 1
 

Why an analytic function?

Michel Cadot, April 21, 2006 - 8:35 am UTC

There is no need of analytic function to get your result:

select REWARDLIST_REWARDID, min(status)
from rnd
group by REWARDLIST_REWARDID
having min(status) = max(status)
order by REWARDLIST_REWARDID
/

Regards
Michel


Tom Kyte
April 21, 2006 - 8:52 am UTC

interesting, our approaches would return different results ;)

depends on whether you count NULLs as being "the same" or not... Mine says "they are the same", yours would not.

Neither is wrong, until the requirement is stated though... Just something to keep in mind.

NULL

Michel Cadot, April 21, 2006 - 9:17 am UTC

I don't think our queries give a distinct result as count ignores null values.
But as i am curious i add 3 rows in test:

AGND01> select * from rnd where REWARDLIST_REWARDID in (5,6);
REWARDLIST_REWARDID S
------------------- -
5
5 1
6

3 rows selected.

And check both queries:

AGND01> select rewardlist_rewardid, min(status)
2 from rnd
3 group by rewardlist_rewardid
4 having count(distinct status) = 1;
REWARDLIST_REWARDID M
------------------- -
4 1
5 1

2 rows selected.


AGND01> select REWARDLIST_REWARDID, min(status)
2 from rnd
3 group by REWARDLIST_REWARDID
4 having min(status) = max(status)
5 order by REWARDLIST_REWARDID
6 /
REWARDLIST_REWARDID M
------------------- -
4 1
5 1

2 rows selected.

One step further:

AGND01> select REWARDLIST_REWARDID, min(status)I,
2 max(status) X, count(status), count(distinct status)
3 from rnd
4 group by rewardlist_rewardid
5 /
REWARDLIST_REWARDID I X COUNT(STATUS) COUNT(DISTINCTSTATUS)
------------------- - - ------------- ---------------------
1 0 1 3 2
2 0 2 5 3
3 0 2 3 3
4 1 1 3 1
5 1 1 1 1
6 0 0

6 rows selected.

AGND01> select * from v$version;
BANNER
------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

Regards
Michel

Tom Kyte
April 21, 2006 - 9:25 am UTC

doh, you are right.

but that does point out a potential flaw in both our implementations with regards to null :)

Analytical abilities

Hitesh, April 22, 2006 - 10:39 am UTC

Here is the reqd information

CREATE TABLE RDATA
("REQUEST_TAG" VARCHAR2(10) NOT NULL ENABLE,
"REQUEST_TIMESTAMP" TIMESTAMP (0) NOT NULL ENABLE,
"REQUEST_TERMS" VARCHAR2(100)
)

CREATE TABLE IDATA
("REQUEST_TAG" VARCHAR2(10) NOT NULL ENABLE,
"IMPRESSION_TIMESTAMP" TIMESTAMP (0) NOT NULL ENABLE
)

Insert into RDATA Values ('kxi59CzvKBZnMMKq', '31-MAR-06 10.33.13 PM', 'shoe carnival');
Insert into RDATA Values ('CoL1ibkg7fTjmyB6', '31-MAR-06 10.33.13 PM', 'stalog 17');
Insert into RDATA Values ('09/zbGTZLwkR0B93', '31-MAR-06 10.33.16 PM', 'blues brothers 2');
Insert into RDATA Values ('Fla2hrefhHMBxInw', '31-MAR-06 10.33.18 PM', 'Sturm Company Parka');
Insert into RDATA Values ('iyNlkRyFYVNekBWg', '31-MAR-06 10.33.18 PM', 'Sturm Company Parka');

Insert into Idata Values ('kxi59CzvKBZnMMKq' ,'31-MAR-06 10.33.18 PM');
Insert into Idata Values ('CoL1ibkg7fTjmyB6' ,'31-MAR-06 10.33.18 PM');
Insert into Idata Values ('09/zbGTZLwkR0B93' ,'31-MAR-06 10.33.20 PM');
Insert into Idata Values ('Fla2hrefhHMBxInw' ,'31-MAR-06 10.33.21 PM');
Insert into Idata Values ('iyNlkRyFYVNekBWg' ,'31-MAR-06 10.33.18 PM');


Tom Kyte
April 22, 2006 - 3:19 pm UTC

no keys, no join conditions.

How about

a) the explaination of the problem (keep it all together, don't make me page up, down all around)

b) the example

c) with everything your mom would need to understand (that is my litmus test - pretend you were trying to explain this problem to your mom, give the details)

Financial formulas

VA, October 17, 2006 - 9:54 am UTC

Can analytic functions be used to calculate common financial formulas used in Fixed Income/Bond trading? These formulas are readily available in that pesky Excel so users expect it to be a piece of cake.

Specifically, I need to calculate the Yield to Maturity for bonds with mathematical formulas defined as in

</code> http://www.thismatter.com/Money/Bonds/Bond-Yields.htm http://www.asset-analysis.com/bonds/bonytm.html http://www.streetauthority.com/terms/y/yield-to-maturity.asp <code>
etc

Thoughts?

Thanks

Tom Kyte
October 17, 2006 - 9:58 am UTC

my thoughts would be....

you type out the algorithm in psuedo code here that would work on a table and show us what is necessary to be done with some sample data.

“bin fitting” with SQL

Gabe, October 17, 2006 - 12:52 pm UTC

Back in Jan 2003 “steve” asked about a solution for distributing all tablespaces for backup over 7 days.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1941199930674#7436091439275 <code>

Here is a solution which _works_ well for uniformly distributed sizes in the set.

create table tbs
( nm varchar2(10) not null
,sz number(9) not null
);

insert into tbs
select 'a'||rownum, 100+rownum*decode(mod(rownum,2),1,1,-1) from user_objects where rownum <51;

select nm,sz,rsz,equiwidth_bucket day,equiwidth_bucket*cl day_limit
from (
with r as (select nm,sz,sum(sz) over (order by sz,nm) rsz from tbs)
,x as (select max(rsz) max_c, ceil(sum(sz)/7) cl from r)
SELECT nm,sz,rsz,cl, width_bucket(rsz,1,max_c+1,7) equiwidth_bucket
from r, x
) order by equiwidth_bucket
;

For "sum(sz) over (order by sz,nm)" ... it is important for the "sz,nm" to uniquely identify a row in the table ... which is OK for tablespace names.

And the _proof_ …

flip@FLOP> select sum(sz), ceil(sum(sz)/7) from tbs;

SUM(SZ) CEIL(SUM(SZ)/7)
---------- ---------------
4975 711

flip@FLOP> select equiwidth_bucket day,sum(sz)
2 from (
3 with r as (select nm,sz,sum(sz) over (order by sz,nm) rsz from tbs)
4 ,x as (select max(rsz) max_c from r)
5 SELECT nm,sz, width_bucket(rsz,1,max_c+1,7) equiwidth_bucket
6 from r, x
7 )
8 group by equiwidth_bucket
9 order by equiwidth_bucket;

DAY SUM(SZ)
---------- ----------
1 660
2 720
3 674
4 777
5 615
6 804
7 725

Cheers.


analytical query

Houman, November 07, 2006 - 2:55 pm UTC

I need a query that will return me the first (first fetch) number between 1 and 99999 that is:
NOT IN (SELECT SCHJOB_FILLER FROM SCHJOB WHERE PLANT_CODE = '12' AND SCHJOB_FILLER IS NOT NULL)

all in one select statement. is it possible

SCHJOB_FILLER is a number


Tom Kyte
November 07, 2006 - 4:45 pm UTC

eh?

A SQL Model Clause solution for the tricky "bin fitting" question !!

Frank Zhou, November 29, 2006 - 4:01 pm UTC

Hi Tom,
      Here is a 10G SQL Model Clause solution for the tricky "bin fitting" question. (Posted by Steve on January 21, 2003 about "Distributing tablespace equally")
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1941199930674#7436091439275
The Model Cluase solution can distributes the data as closely as possible. 
SQL Model Clause is really awsome!!  A single query solution becomes a reality for many tricky questions & requirements.

SQL Model clause  rock...... 
Analytics         roll....

Thanks,

Frank
------------------------------------------
create table bk_schedule (
   tbs_name varchar2(40)  not null,  
   tbs_size number(8) not null  
   )
   
insert into bk_schedule
select 'tbs'||n, 100+n*decode(mod(n,2),1,1,-1) from  
(select level n from dual connect by level <51)
commit

SQL> select tbs_name, tbs_size, Days from
  2   (select tbs_name, tbs_size, Days, rn from
  3     (select tbs_name, tbs_size, sum(tbs_size) over ( )sum_space,
  4             row_number() over (order by tbs_size desc) rn from bk_schedule)
  5   model
  6   dimension by ( rn)
  7   measures (tbs_name, tbs_size, 0 it, sum_space,
  8   CAST(null AS number) Days, CAST(null AS number) min_tmp,
  9   CAST(null AS number) Day_1, CAST(null AS number) Day_2,
 10   CAST(null AS number) Day_3, CAST(null AS number) Day_4,
 11   CAST(null AS number) Day_5, CAST(null AS number) Day_6,
 12   CAST(null AS number) Day_7,
 13   CAST(null AS number) pDay_1, CAST(null AS number) pDay_2,
 14   CAST(null AS number) pDay_3 ,CAST(null AS number) pDay_4,
 15   CAST(null AS number) pDay_5, CAST(null AS number) pDay_6,
 16   CAST(null AS number) pDay_7
 17   )
 18   RULES iterate(10000)       
      until(pDay_1[1]+pDay_2[1]+pDay_3[1]+pDay_4[1]+pDay_5[1]+pDay_6[1]+pDay_7[1]>= sum_space[1])
 20   (  ------Set up the priority in case there is a tie between 2 days
 21   pDay_1[2] = case when it[ITERATION_NUMBER] = 0 then 0 end ,
 22   pDay_2[2] = case when it[ITERATION_NUMBER] = 0 then 0.0001 end ,
 23   pDay_3[2] = case when it[ITERATION_NUMBER] = 0 then 0.0002 end ,
 24   pDay_4[2] = case when it[ITERATION_NUMBER] = 0 then 0.0003 end ,
 25   pDay_5[2] = case when it[ITERATION_NUMBER] = 0 then 0.0004 end ,
 26   pDay_6[2] = case when it[ITERATION_NUMBER] = 0 then 0.0005 end ,
 27   pDay_7[2] = case when it[ITERATION_NUMBER] = 0 then 0.0006 end ,
 28   --------Find out what is the minium summation value among the days
 29   min_tmp[1] = least(sum(pDay_1)[any], sum(pDay_2)[any], sum(pDay_3)[any],
 30                      sum(pDay_4)[any], sum(pDay_5)[any], sum(pDay_6)[any],
 31       sum(pDay_7)[any]) ,
 32   -------Find out which day has the minium summation value
 33   Day_1[ITERATION_NUMBER] = CASE WHEN sum(pDay_1)[any] = min_tmp[1]
 34                                  THEN tbs_size[ITERATION_NUMBER]  END,
 35   Day_2[ITERATION_NUMBER] = CASE WHEN sum(pDay_2)[any] =  min_tmp[1]
 36                                  THEN tbs_size[ITERATION_NUMBER]  END,
 37   Day_3[ITERATION_NUMBER] = CASE WHEN sum(pDay_3)[any] =  min_tmp[1]
 38                                  THEN tbs_size[ITERATION_NUMBER]  END,
 39   Day_4[ITERATION_NUMBER] = CASE WHEN sum(pDay_4)[any] =  min_tmp[1]
 40                                  THEN tbs_size[ITERATION_NUMBER]  END,
 41   Day_5[ITERATION_NUMBER] = CASE WHEN sum(pDay_5)[any] =  min_tmp[1]
 42                                  THEN tbs_size[ITERATION_NUMBER]  END,
 43   Day_6[ITERATION_NUMBER] = CASE WHEN sum(pDay_6)[any] =  min_tmp[1]
 44                                  THEN tbs_size[ITERATION_NUMBER]  END,
 45   Day_7[ITERATION_NUMBER] = CASE WHEN sum(pDay_7)[any] =  min_tmp[1]
 46                                  THEN tbs_size[ITERATION_NUMBER]  END,
 47   --------Determine what day to bakcup
 48  Days[ITERATION_NUMBER] =CASE WHEN sum(pDay_1)[any] = min_tmp[1] THEN 1
 49                               WHEN sum(pDay_2)[any] = min_tmp[1] THEN 2
 50          WHEN sum(pDay_3)[any] = min_tmp[1] THEN 3
 51          WHEN sum(pDay_4)[any] = min_tmp[1] THEN 4
 52          WHEN sum(pDay_5)[any] = min_tmp[1] THEN 5
 53          WHEN sum(pDay_6)[any] = min_tmp[1] THEN 6
 54          WHEN sum(pDay_7)[any] = min_tmp[1] THEN 7
 55        END,
 56  ----Record the current value
 57  pDay_1[1] = sum(Day_1)[any],
 58  pDay_2[1] = sum(Day_2)[any],
 59  pDay_3[1] = sum(Day_3)[any] ,
 60  pDay_4[1] = sum(Day_4)[any] ,
 61  pDay_5[1] = sum(Day_5)[any] ,
 62  pDay_6[1] = sum(Day_6)[any] ,
 63  pDay_7[1] = sum(Day_7)[any] ,
 64  it[ITERATION_NUMBER] =  ITERATION_NUMBER
 65   )
 66  )
 67  where tbs_name is not null
 68  order by rn  nulls last;

TBS_NAME                                   TBS_SIZE       DAYS                  
---------------------------------------- ---------- ----------                  
tbs49                                           149          1                  
tbs47                                           147          2                  
tbs45                                           145          3                  
tbs43                                           143          4                  
tbs41                                           141          5                  
tbs39                                           139          6                  
tbs37                                           137          7                  
tbs35                                           135          7                  
tbs33                                           133          6                  
tbs31                                           131          5                  
tbs29                                           129          4                               
tbs27                                           127          3                  
tbs25                                           125          2                  
tbs23                                           123          1                  
tbs21                                           121          1                  
tbs19                                           119          2                  
tbs17                                           117          3                  
tbs15                                           115          4                  
tbs13                                           113          5                  
tbs11                                           111          6                  
tbs9                                            109          7                  
tbs7                                            107          7                                 
tbs5                                            105          6                  
tbs3                                            103          5                  
tbs1                                            101          4                  
tbs2                                             98          3                  
tbs4                                             96          2                  
tbs6                                             94          1                  
tbs8                                             92          1                  
tbs10                                            90          2                  
tbs12                                            88          3                  
tbs14                                            86          4                  
tbs16                                            84          5                                 
tbs18                                            82          6                  
tbs20                                            80          7                  
tbs22                                            78          7                  
tbs24                                            76          6                  
tbs26                                            74          5                  
tbs28                                            72          4                  
tbs30                                            70          3                  
tbs32                                            68          2                  
tbs34                                            66          1                  
tbs36                                            64          1                  
tbs38                                            62          2                                   
tbs40                                            60          3                  
tbs42                                            58          4                  
tbs44                                            56          5                  
tbs46                                            54          6                  
tbs48                                            52          7                  
tbs50                                            50          7                  

50 rows selected.

********** Here is the proof that this solution works *****

SQL> select Days, sum(tbs_size)  from
  2   (select tbs_name, tbs_size, Days, rn from
  3     (select tbs_name, tbs_size, sum(tbs_size) over ( )sum_space,
  4             row_number() over (order by tbs_size desc) rn from bk_schedule)
  5   model
  6   dimension by ( rn)
  7   measures (tbs_name, tbs_size, 0 it, sum_space,
  8   CAST(null AS number) Days, CAST(null AS number) min_tmp,
  9   CAST(null AS number) Day_1, CAST(null AS number) Day_2,
 10   CAST(null AS number) Day_3, CAST(null AS number) Day_4,
 11   CAST(null AS number) Day_5, CAST(null AS number) Day_6,
 12   CAST(null AS number) Day_7,
 13   CAST(null AS number) pDay_1, CAST(null AS number) pDay_2,
 14   CAST(null AS number) pDay_3 ,CAST(null AS number) pDay_4,
 15   CAST(null AS number) pDay_5, CAST(null AS number) pDay_6,
 16   CAST(null AS number) pDay_7
 17   )
 18   RULES iterate(10000)
 19   until(pDay_1[1]+pDay_2[1]+pDay_3[1]+pDay_4[1]+pDay_5[1]+pDay_6[1]+pDay_7[1]>= sum_space[1])
 20   (  ------Set up the priority in case there is a tie between 2 days
 21   pDay_1[2] = case when it[ITERATION_NUMBER] = 0 then 0 end ,
 22   pDay_2[2] = case when it[ITERATION_NUMBER] = 0 then 0.0001 end ,
 23   pDay_3[2] = case when it[ITERATION_NUMBER] = 0 then 0.0002 end ,
 24   pDay_4[2] = case when it[ITERATION_NUMBER] = 0 then 0.0003 end ,
 25   pDay_5[2] = case when it[ITERATION_NUMBER] = 0 then 0.0004 end ,
 26   pDay_6[2] = case when it[ITERATION_NUMBER] = 0 then 0.0005 end ,
 27   pDay_7[2] = case when it[ITERATION_NUMBER] = 0 then 0.0006 end ,
 28   --------Find out what is the minium summation value among the days
 29   min_tmp[1] = least(sum(pDay_1)[any], sum(pDay_2)[any], sum(pDay_3)[any],
 30                      sum(pDay_4)[any], sum(pDay_5)[any], sum(pDay_6)[any],
 31       sum(pDay_7)[any]) ,
 32   -------Find out which day has the minium summation value
 33   Day_1[ITERATION_NUMBER] = CASE WHEN sum(pDay_1)[any] = min_tmp[1]
 34                                  THEN tbs_size[ITERATION_NUMBER]  END,
 35   Day_2[ITERATION_NUMBER] = CASE WHEN sum(pDay_2)[any] =  min_tmp[1]
 36                                  THEN tbs_size[ITERATION_NUMBER]  END,
 37   Day_3[ITERATION_NUMBER] = CASE WHEN sum(pDay_3)[any] =  min_tmp[1]
 38                                  THEN tbs_size[ITERATION_NUMBER]  END,
 39   Day_4[ITERATION_NUMBER] = CASE WHEN sum(pDay_4)[any] =  min_tmp[1]
 40                                  THEN tbs_size[ITERATION_NUMBER]  END,
 41   Day_5[ITERATION_NUMBER] = CASE WHEN sum(pDay_5)[any] =  min_tmp[1]
 42                                  THEN tbs_size[ITERATION_NUMBER]  END,
 43   Day_6[ITERATION_NUMBER] = CASE WHEN sum(pDay_6)[any] =  min_tmp[1]
 44                                  THEN tbs_size[ITERATION_NUMBER]  END,
 45   Day_7[ITERATION_NUMBER] = CASE WHEN sum(pDay_7)[any] =  min_tmp[1]
 46                                  THEN tbs_size[ITERATION_NUMBER]  END,
 47   --------Determine what day to bakcup
 48  Days[ITERATION_NUMBER] =CASE WHEN sum(pDay_1)[any] = min_tmp[1] THEN 1
 49                               WHEN sum(pDay_2)[any] = min_tmp[1] THEN 2
 50          WHEN sum(pDay_3)[any] = min_tmp[1] THEN 3
 51          WHEN sum(pDay_4)[any] = min_tmp[1] THEN 4
 52          WHEN sum(pDay_5)[any] = min_tmp[1] THEN 5
 53          WHEN sum(pDay_6)[any] = min_tmp[1] THEN 6
 54          WHEN sum(pDay_7)[any] = min_tmp[1] THEN 7
 55        END,
 56  ----Record the current value
 57  pDay_1[1] = sum(Day_1)[any],
 58  pDay_2[1] = sum(Day_2)[any],
 59  pDay_3[1] = sum(Day_3)[any] ,
 60  pDay_4[1] = sum(Day_4)[any] ,
 61  pDay_5[1] = sum(Day_5)[any] ,
 62  pDay_6[1] = sum(Day_6)[any] ,
 63  pDay_7[1] = sum(Day_7)[any] ,
 64  it[ITERATION_NUMBER] =  ITERATION_NUMBER
 65   )
 66  )
 67  where tbs_name is not null
 68  group by Days;

      DAYS SUM(TBS_SIZE)                                                        
---------- -------------                                                        
         1           709                                                        
         2           707                                                        
         3           705                                                        
         4           704                                                        
         5           702                                                        
         6           700                                                        
         7           748                                                        

7 rows selected.

SQL> spool off
 

Model clause performance?

Greg, July 31, 2007 - 8:38 am UTC

What issues/concerns are there with the performance of the Model clause?
I'm assuming we're giving up (some?) performance for functionality ...

Obviously benchmark any "solution" .. but just curious if we need to be especially wary of the performance of this thing (think a million+ rows, large data sets, etc ...)

Has anyone used it on that scale yet? or just on smaller data sets? (which it seems to perform well on ..)

Order of processing scalar query whn anytix functiin are there

Asim, August 15, 2022 - 7:39 pm UTC


Doc says analytic function are processed after group by having and before order by. But it doesnt say anything about order of processing scalar query in select list, if it is after analytic or before analytic. The below query giving error


ORA-00904: "A"."MAXSAL": invalid identifier

select
a.empno,
a.ename,
a.sal,
max(a.sal) over() maxsal,
(select listagg(b.ename) from scott.emp b
where b.sal = a.maxsal) listaggnames
from scott.emp a

How can I refer to a result of analytic function in the scalar query, aliasing doesnt work.

I know I can write a different query to get fhe results but I want to know the processing order.



Connor McDonald
August 17, 2022 - 4:19 am UTC

Inline view.

select empno, ename, sal, (select listagg(b.ename) from scott.emp b where b.sal = maxsal) 
from 
(
select
  empno,
  ename,
  sal,
  max(a.sal) over() maxsal
from scott.emp a
)

QUALIFY Clause

Asim, September 09, 2022 - 7:20 am UTC


As there is HAVING clause to filter rows based on results of aggregate functions in almost all SQL implementations, google's bigquery has a QUALIFY clause to fllter rows based on result of ANALYTIC functions.

I know we can do the same in Oracle, by enclosing the whole query as inline view and then use where clause on results of analytic functions.

But it will be better if Oracle also allow QUALIFY clause, even if it is a syntax sugar, at least query will be more concise and readable.

Has oracle any plans for fhis in future releases?

Oracle already have some syntax sugars clauses like OFFSET .. FETCH, WINDOW clause.





Connor McDonald
September 12, 2022 - 3:09 am UTC

I've seen QUALIFY but to my knowledge, there is no current plan.

(Of course that may change)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library