analytical sql functions 1
TRANSCRIPT
-
8/14/2019 Analytical SQL Functions 1
1/14
Analytical SQL Functions theory and examples Part 2
on the Order By andWindowing lauses
To understand how Analytical Functions are used in SQL statements, you really have to look
at the four different parts of an Analytical clause:
the analytical !unction, for eample A!", L#A$, %#&'#(T)L#*&A(+
the partitioning clause, for eample %A&T)T)( -. /o0 or %A&T)T)( -. dept, /o0
the order "y clause, for eample order 0y /o0 nulls last
the windowing clause, for eample &A("# 1(-1($#$ %'#$)(" or &2S1(-1($#$ FLL2)("
(ote that typically only the Analytical Function is re3uired and the other clauses are
optional4 5owever, for certain functions, an rder -y clause may 0e re3uired4
This post will focus on the rder -y and 2indowin6 'lauses4 %art 7 of this seriesAnalytical
SQL Functions 8 theory and eamples 8 %art 7 on the %artition 'lausediscussed the %artition
'lause and the over9all structure of Analytical #pressions4
rder -y 'lause.ou may wonder what role an &$#& -. clause can play in an Analytical Function4 2e will
investi6ate4
There are some Analytical Functions that return information a0out the relative position of a
record &2*(1;-#&, &A(+, $#(S#*&A(+
-
8/14/2019 Analytical SQL Functions 1
2/14
from emp
order
by deptno
, sal desc
, comm desc
, hiredate desc
&esults:
ENAME DE!N" #A$ %&'EDA!E #A$_'AN_&N_DE! #A$_'AN_"E'A$$
***********************************************************************
+$A' - ./0--1*23N*4 5
M&$$E' - 6--.6*2AN*4. . 1
&N7 .- 0---4*N"*4
#+"!! .- 6---1*A'*48 . .
9"'D .- 6----6*DE+*4 6 6
2"NE# .- .180-.*A'*4 / /
ADAM# .- --.6*MA:*48 0 .
#M&!% .- 4--8*DE+*4- 5 /
;$AE 6- .40--*MA:*4 0
A$$EN 6- 5--.-*9E;*4 . 8
!3'NE' 6- 0---4*#E*4 6 4
MA'!&N 6- .0-.4*#E*4 /
-
8/14/2019 Analytical SQL Functions 1
3/14
To make row*num0er< meanin6ful, you must specify within what orderin6 you want to have
the row*num0er
-
8/14/2019 Analytical SQL Functions 1
4/14
The order 0y clause as used within Analytical #pressions is no different from the rder -y
used at the end of Select statements4 rder -y can make use of multiple columns, make use
of functions 8 /ust not analytical functions= 8 , 0e in AS'#($)(" or $#S'#($)(" order and
either put (1LL values first (1LLS F)&ST< or last (1LLS LAST(sal)over (partition by=ob))
)sal_rank_in_dept
from emp
order
by deptno
, sal desc
5owever, the net 3uery solves the issue and 6ives us the answer we were lookin6 for4
A6ain, a useful trick: if we need analytical results within an Analytical Function, we use an in9
line view to produce those results and enrich the records we process in the outer Analytical
Function with those results4
selectename
, deptno
, =ob
, sal
, av>_sal_in_=ob av>_=ob_sal
, sal *av>_sal_in_=ob sal_min_=ob_av>
, row_number()over (partition bydeptno
order by(sal *av>_sal_in_=ob)
)sal_min_=ob_av>_rank_in_dept
from (selectename
, deptno
, =ob
, sal
, av>(sal)over (partition by=ob)av>_sal_in_=ob
from emp
)
-
8/14/2019 Analytical SQL Functions 1
5/14
order
by deptno
, sal desc
&esults:
ENAME DE!N" 2"; #A$ A7_2";_#A$ #A$_M&N_2";_A7
#A$_M&N_2";_A7_'AN_&N_DE!
*****************************************************************
****************************
+$A' -MA'E!EE' ./0- ./0- -
M&$$E' -+$E' 6-- -68?0 .5.?0
.
&N7 .-'E#&DEN! 0--- 0--- -
/
#+"!! .-ANA$:#! 6--- 6--- -.
9"'D .-ANA$:#! 6--- 6--- -
6
2"NE# .-MANA7E' .180 .1.?0 5.?0
5
ADAM# .-+$E' -- -68?0 5.?0
0
#M&!% .-+$E' 4-- -68?0 *.68?0
;$AE 6-MANA7E' .40- .1.?0 *5.?0
/
A$$EN 6-#A$E#MAN 5-- /-- .--
5
!3'NE' 6-#A$E#MAN 0-- /-- --
0
MA'!&N 6-#A$E#MAN .0- /-- *0-
o04 )n
that respect is >A;#S, who works as 'L#&+ in the same department as ;A&T)(, is somewhat
0etter off: he comes third in terms of 6ap with the avera6e salary in his /o0 cate6ory4 ) admit
thou6h that this 3uery is 3uite farfetched 8 tryin6 to illustrate all concepts in the contet of
-
8/14/2019 Analytical SQL Functions 1
6/14
#;% and $#%T is perhaps overam0itious4
#an$ %s& #ow'(um"er
) always have trou0le fi6urin6 out why ) should &A(+ or $#(S#*&A(+< instead of
&2*(1;-#&4 Also see SQL for $ata 2arehousin6 in the racle 7?6 $ata 2arehousin6
"uide4 &2*(1;-#& seems to 6ive me the result ) am lookin6 for, however &A(+ is
specifically created to answer this type of 3uestion4
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10736/analysis.htm#i1007779%2311237http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10736/analysis.htm#i1007779%2311237http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10736/analysis.htm#i1007779%2311237http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10736/analysis.htm#i1007779%2311237 -
8/14/2019 Analytical SQL Functions 1
7/14
selectename
, hiredate
, sal
, rank()over (partition bydeptno
order bysal desc,comm desc ,hiredate desc
)sal_rank_in_dept
, rank()over (order bysal desc)sal_rank_overall
from emp
order
by sal desc
, comm desc
, hiredate desc
ENAME %&'EDA!E #A$ #A$_'AN_&N_DE! #A$_'AN_"E'A$$
*************************************************************
&N7 4*N"*4 0---
#+"!! 1*A'*48 6--- . .
9"'D -6*DE+*4 6--- 6 .
2"NE# -.*A'*4 .180 / /
;$AE -*MA:*4 .40- 0
+$A' -1*23N*4 ./0- 5
A$$EN .-*9E;*4 5-- . 8
!3'NE' -4*#E*4 0-- 6 4
M&$$E' .6*2AN*4. 6-- . 1
MA'!&N .4*#E*4 .0- / -
-
8/14/2019 Analytical SQL Functions 1
8/14
Take for eample this 3uery:
selectename
, =ob
, sal
, av>(sal)over (partition by=ob)=ob_av>
, av>(sal)over ()total_av>
from emp
where sal @.0--
order
by =ob
ENAME 2"; #A$ 2";_A7 !"!A$_A7
*************************************************
#M&!% +$E' 4-- -68?0600?00005
ADAM# +$E' -- -68?0600?00005
2AME# +$E' 10- -68?0600?00005
M&$$E' +$E' 6-- -68?0600?00005
+$A' MA'E!EE' ./0- ./0-600?00005
A$$EN #A$E#MAN 5-- /--600?00005
!3'NE' #A$E#MAN 0-- /--600?00005
o0 as the current row4 For S;)T5 the >-*A!" value was calculated as the Avera6e Salary
for all #mployees returned 0y the 3uery i4e4 with a salary @ B??< in the same >o0 as S;)T5
i4e4 'lerks(sal)over (partition by=ob
order by hiredate
)=ob_av>
from emp
where sal @.0--
order
by =ob
-
8/14/2019 Analytical SQL Functions 1
9/14
, hiredate
we 6et a result that may surprise you:
ENAME 2"; #A$ %&'EDA!E 2";_A7
************************************************
#M&!% +$E' 4--8*DE+*4- 4--
2AME# +$E' 10--6*DE+*4 480
M&$$E' +$E' 6--.6*2AN*4.-5?55558
ADAM# +$E' --.6*MA:*48 -68?0
+$A' MA'E!EE' ./0--1*23N*4 ./0-
A$$EN #A$E#MAN 5--.-*9E;*4 5--
-*A!" is no lon6er constant within a partition4 5ere is the
eplanation: 0y specifyin6 the order 0y clause, we in fact told the SQL en6ine two thin6s
74 order the rows within the >-9partitions on hiredate so the first hired employee in a
/o0 is listed first in the partition-*A!" value found for S;)T5 9 the first hired 'L#&+ 9 is arrived
at in the followin6 way: take all #mployees returned 0y the 3uery and from that set the
partition of all 'L#&+S4 rder them 0y hiredate4 (ow startin6 from the current record 9
S;)T5 9 take his Salary and the Salary9values of all precedin6 rows4 Since there are no
precedin6 rows in the partition, S;)T5Cs salary is the only one used for calculatin6 the
avera6e and is therefore e3ual to the avera6e4
-
8/14/2019 Analytical SQL Functions 1
11/14
>-*A!" for Turner 9 the third hired employee in the 6roup of SAL#S;#( 9 is calculated
usin6 the Salaries of T1&(#& himself and his two previously hired collea6ues 9 ALL#( and
2A&$4 The last SAL#S;A(, ;A&T)(, is outside the window4
)t follows from the a0ove that only for the last hired employees in each partition 9 the ones
where the window overlaps with the entire partition 9 the >-*A!" is e3ual to salary of all
employees with the same /o0 as found in the previous 3uery
-
8/14/2019 Analytical SQL Functions 1
12/14
The net ima6e compares the runnin6 avera6e with an order 0y 9 and therefore implicit
windowin6 clause< with the avera6e calculated with the same partition clause 0ut without
-
8/14/2019 Analytical SQL Functions 1
13/14
order 0y:
The reverse of the a0ove 3uery is the followin6, where the >-*A!" is calculated over the
current employee and all later hired collea6ues in the same >o0:
selectename
, =ob
-
8/14/2019 Analytical SQL Functions 1
14/14
, sal
, hiredate
, av>(sal)over (partition by=ob
order by hiredate
ran>e between current row andunbounded followin>
)=ob_av>
from emp
where sal @.0--
order
by =ob
ENAME 2"; #A$ %&'EDA!E 2";_A7
************************************************
#M&!% +$E' 4--8*DE+*4- -68?0
2AME# +$E' 10--6*DE+*45?55558
M&$$E' +$E' 6--.6*2AN*4. .--
ADAM# +$E' --.6*MA:*48 --
+$A' MA'E!EE' ./0--1*23N*4 ./0-
A$$EN #A$E#MAN 5--.-*9E;*4 /--