Advanced SQL Reporting Features David Kent Sr Oracle

Advanced SQL Reporting Features David Kent, Sr. Oracle DBA Michigan Tech University Global Business Solutions, Inc.

Advanced SQL Reporting Features • TOPICS: • Use ROLAP operators for specialized querying • Use the GROUPING function to ascertain summary results • Use Analytic functions for moving averages, rankings, and lead/lag comparisons • Use the sample clause to instruct Oracle to select from a random sample of rows • Case Expressions Evaluation Code 539 2

Advanced SQL Reporting Overview Oracle 8 i Enhancements ROLAP Operators Grouping Analytic Functions SAMPLE Clause Evaluation Code 539 3

ROLAP Operators Extensions to GROUP BY clause ROLLUP Returns set of grouped rows and subtotal values Creates subtotals at increasing levels of aggregation CUBE Returns set of rows from ROLLUP and cross-tabulation Calculates all possible combinations of subtotals Evaluation Code 539 4

ROLLUP Syntax SELECT column, group_function FROM table [WHERE condition] [GROUP BY [ROLLUP] group_by_expression]; ROLLUP - Simple extension to the GROUP BY clause Calculates - multiple levels of subtotals - a grand total Evaluation Code 539 5

ROLLUP Example 1 { 2 3 Evaluation Code 539 6

CUBE Syntax SELECT column, group_function FROM table [WHERE condition] [GROUP BY [CUBE] group_by_expression]; CUBE - Simple extension to the GROUP BY clause. Calculates - subtotals for all possible combinations - a grand total Evaluation Code 539 7

CUBE Example 1 { 2 3 4 Evaluation Code 539 8

GROUPING Syntax SELECT column, group_function, GROUPING(expression) FROM table [WHERE condition] [GROUP BY [CUBE][ROLLUP] group_by_expression]; GROUPING - Selection list object of the column clause Returns 0 - for any other value, including stored NULL 1 - for a NULL where row is subtotal Evaluation Code 539 9

GROUPING Example 1 2 3 Evaluation Code 539 10

Analytic Functions SQL analysis functions used to perform analytic tasks Lag/Lead Functions Ranking Functions Reporting Functions Statistics Functions Windowing Functions Evaluation Code 539 11

Lag/Lead Functions Syntax LAG (expression, offset) OVER (ORDER BY expression) AS alias_name LEAD (expression, offset) OVER (ORDER BY expression) AS alias_name LAG/LEAD - Compares values in different time periods access to multiple rows without a self-join Provides LAG - access to row at offset prior to current position LEAD - access to row at offset after current position Evaluation Code 539 12

Lag/Lead Functions Evaluation Code 539 13

Ranking Functions Syntax Rank Function OVER (ORDER BY group_function ASC/DESC) AS alias RANKing Functions - used to compute the rank of a record - CUME_DIST/PERCENT_RANK - NTILE - RANK/DENSE_RANK - ROWNUM - TOP_N/BOTTOM_N Computes Rank with respect to other records in the dataset based on the values of a set of measures Evaluation Code 539 14

Ranking Functions Evaluation Code 539 15

Reporting Functions Syntax SELECT column_name, (SELECT value expression 1 OVER(PARTITION BY value expression 2) AS alias FROM table_name. . . REPORTing Functions - accesses query aggregate values Usage return same aggregate value for every row in partition perform multiple passes of data in a single query block Evaluation Code 539 16

Reporting Functions Evaluation Code 539 17

Statistic Functions STATISTIC Functions - compute covariance, correlation, linear regression - CORR - COVAR_POP - COVAR_SAMP - LINEAR REGRESSION FUNCTIONS - REGR_AVGY, REGR_AVGX - REGR_COUNT - REGR_R 2 - REGR_SLOPE, REGR_INTERCEPT - REGR_SXX, REGR_SYY, REGR_SXY - STDDEV_POP/STDDEV_SAMP - VAR_POP - VAR_SAMP Usage Evaluation Code 539 engaged as windowing and reporting functions most of the functions contain two arguments 18

Statistic Functions Evaluation Code 539 19

Windowing Functions WINDOWing Functions – compute cumulative, moving, centered aggregates - CURRENT ROW - UNBOUNDED FOLLOWING - UNBOUNDED PRECEDING Usage access to more than one row without using a self-join allowed only in SELECT and ORDER BY clauses Evaluation Code 539 20

Windowing Functions 3. 4 + 102. 5 / 2 = 52. 95 56 + 224 / 2 = 140 Evaluation Code 539 21

SAMPLE Clause The SAMPLE Clause Specifies results based on random table sample SAMPLE Specification Simple select queries (no joins) Simple create table as select queries Usage Data mining Data warehousing Avoid full table scan Evaluation Code 539 22

SAMPLE Clause SELECT COUNT(*) * 50 FROM employee SAMPLE BLOCK (1); 1 CREATE TABLE sample_emp AS SELECT employee_id, department_id FROM employee SAMPLE(10); SELECT e. employee_id FROM sample_emp e, department d WHERE e. department_id = d. department_id AND d. name = ‘SALES’; 2 Evaluation Code 539 23

CASE Expressions A flexible extension to the DECODE Function. Used for IF-THEN-ELSE evaluations. Simple CASE expressions Introduced in 8 i Returns an Alternate value when an EXACT match is found Searched CASE expressions Introduced in 9 i Returns an Alternate value when a PATTERN match is found Evaluation Code 539 24

Simple CASE Expression Example Evaluation Code 539 25

Searched CASE Example Evaluation Code 539 26

NULLIF and COALESCE Functions (Case Abbreviations) NULLIF(Expr 1, Expr 2) Returns NULL if the first argument is equal to the second, otherwise returns first argument COALESCE(expr 1, expr 2, expr 3, . . . ) Returns the first non-null argument, if all arguments are null, returns NULL Evaluation Code 539 27

Thank You! David J. Kent david@mtu. edu Please fill out the Evaluation Form Evaluation Code 539 28