Oracle Analytic SQL.ppt

Oracle Analytic SQL NCOUG 2008 By: Ron Warshawsky CTO DBA InfoPower, Inc. Oracle Analytic SQL Note: “standard” name is “Window” functions When? – Starting 8i Why? – Simple Solution of Complex Problems Why Exactly? – advanced ranking, aggregation, row comparison, statistics, “what if” scenarios Order of Evaluation in SQL: Prior to “ORDER BY” clause Oracle Analytic SQL Syntax: Analytic-Function(Argument,Argument,...)OVER (? Query-Partition-Clause? Order-By-Clause? Windowing-Clause) PARTITION BY – aggregates result set into groups ORDER BY – orders data within a partition WINDOWING – rows or ranges (logical offset) Oracle Analytic SQL More Windowing: ROWS|RANGE BETWEEN ... AND UNBOUNDED PRECEDING – start of partition UNBOUNDED FOLLOWING – end of partition CURRENT ROW value_expr PRECEDING| FOLLOWING Examples: ROWS BETWEEN 1 PRECEDING and 1 FOLLOWING ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW RANGE BETWEEN INTERVAL 7 day PRECEDING AND CURRENT ROW Oracle Analytic SQL Major Functions: STANDARD AGGREGATE FUNCTIONS ROW_NUMBER() LAG() LEAD() RANK() DENSE_RANK() PERCENT_RANK() NTILE() FIRST_VALUE() LAST_VALUE() FIRST() LAST() STATISTICAL FUNCTIONS Oracle Analytic SQL Practical Examples Oracle Analytic SQL Selecting the Top n Records select sql_id, BUFFER_GETS_DELTA from ( select sql_id, BUFFER_GETS_DELTA, dense_rank() over (order by BUFFER_GETS_DELTA desc) dr from DBA_HIST_SQLSTAT ) x where dr = 5 ;  Oracle Analytic SQL Calculate 3 period Moving Average select snap_id ,sql_id ,BUFFER_GETS_DELTA ,avg(BUFFER_GETS_DELTA) over (order by snap_id rows b