Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle analytic functions (Read 4439 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle analytic functions
03.09.08 at 08:17:33
 
Oracle analytic functions
 
Available since Oracle 8.1.6
 
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause.
 
The general syntax of analytic function is:
 
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )
 
See for examples:
http://www.psoug.org/reference/analytic_functions.html
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle analytic functions
Reply #1 - 03.09.08 at 08:30:16
 
Example: return rows from the last batch only
 
Consider a logging table that is used for batch runs.
Each run creates multiple records that are labeled with a batch_id:
 
logging table. Columns: batch_id, col_pk
 
To return only the rows with the highest batch_id I can use the following query in which I use the analytic function.
 
select * from  
(
  select l.*
  , max(batch_id) over (partition by col_pk) last_batch_id
  from logging l
)
where last_batch_id = batch_id
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle analytic functions
Reply #2 - 03.09.08 at 14:30:28
 
Example: return previous row
 
Equivalent to the previous example, consider a logging table that is used for batch runs. Each run creates multiple records that are labeled with a batch_id:
 
logging table. Columns: batch_id, col_pk
 
To return only the rows with the one but highest batch_id I can use the following query in which I used the analytic function.  
 
select * from  
(
  select l.*
  , row_number() over (partition by col_pk order by batch_id desc) rownr
  from logging l
)
where rownr = 2
 
Note that rownr = 1 would have selected the row with the highest batch_id and rownr=4 would have selected the row with the batch_id ranked 4, etc.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1