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?
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
/
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
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
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?
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
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.
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.
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
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
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;
=======================
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
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
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');
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
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
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.
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.
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)