Oracle 8 i Analytical SQL Features Willie Albino

Oracle 8 i Analytical SQL Features Willie Albino

Analytical SQL Features Overview l Available in Oracle 8. 1. 6 and above l Analytical Features/Enhancements – GROUP BY Extensions l – Analytical SQL Functions l – Analytical functions enabling rankings, moving window calculations, lead/lag analysis CASE Expressions l 2 SQL GROUP BY clause has been augmented to make querying and reporting easier Increased/Efficient ‘if-then-else’ capabilities provided Willie Albino 9/23/2021

Oracle 8 i Analytical SQL Features GROUP BY Extensions Willie Albino

GROUP BY Extensions l ROLLUP – – l CUBE – – 4 Calculates subtotals at increasing levels of aggregation from the most detail to a grand total used to generate simple cross tabular reports Calculates all possible combinations of subtotals used to generate full cross tabular reports Willie Albino 9/23/2021

GROUP BY Extensions Syntax ROLLUP: SELECT <column list> FROM <table…> GROUP BY ROLLUP(column_list); CUBE: SELECT <column list> FROM <table…> GROUP BY CUBE(column_list); 5 Willie Albino 9/23/2021

Standard GROUP BY Example select region, product, SUM(amount) from region, product_sales where region_id = reg_id and region = 'East' and product_id = prod_id group by region, product 6 REGION ------East East East Willie Albino PRODUCT SUM(AMOUNT) ------Hats 75 Jackets 100 Pants 100 Shirts 75 Shoes 130 Suits 90 Sweaters 75 T-Shirts 20 Ties 60 9/23/2021

GROUP BY with ROLLUP select region, product, SUM(amount) from region, product_sales where region_id = reg_id and region = 'East' and product_id = prod_id group by ROLLUP(region, product) 7 REGION ------East East East - Willie Albino PRODUCT SUM(AMOUNT) ------Hats 75 Jackets 100 Pants 100 Shirts 75 Shoes 130 Suits 90 Sweaters 75 T-Shirts 20 Ties 60 725 9/23/2021

GROUP BY with CUBE select region, product, SUM(amount) from region, product_sales where region_id = reg_id and region = 'East' and product_id = prod_id group by CUBE(region, product) 8 REGION ------: East - Willie Albino PRODUCT SUM(AMOUNT) ------: : Ties 60 725 Hats 75 Jackets 100 Pants 100 Shirts 75 Shoes 130 Suits 90 Sweaters 75 T-Shirts 20 Ties 60 725 9/23/2021

Comments about ROLLUP/CUBE 9 l ROLLUP: creates subtotals at n+1 levels where n equals the number of grouping columns l CUBE: creates 2 n combinations of subtotals where n equals the number of grouping columns l Sub-total generation more efficient than equivalent SQL code (a 4 column CUBE grouping, has a 93. 75% reduction in table access, ROLLUP has 80%) Willie Albino 9/23/2021

Comments about ROLLUP/CUBE l Partial rollups/cubes can be specified – 10 GROUP BY exp 1, CUBE(exp 2, exp 3, . . ) l ROLLUP/CUBE can be used with all aggregating functions (MAX, MIN, AVG, etc. ) l HAVING clause applies to all the data returned l NULLs are generated for dimensions at subtotal levels Willie Albino 9/23/2021

GROUPING() Function l Used to distinguish between NULLs in data and NULLs generated by ROLLUP/CUBE extensions l GROUPING() return values: – – l SYNTAX: – – 11 1 for extension-generated NULLs, 0 for NULL data values Can be passed to DECODE for custom interpretation SELECT. . GROUPING(column name). . GROUP BY. . SELECT. . DECODE(GROUPING(col), 1, ‘Sub’, col)) … Willie Albino 9/23/2021

GROUPING() Function Example select DECODE(GROUPING(region), 1, 'All Regions', 0, region) region, DECODE(GROUPING(product), 1, 'All Products', 0, product) product, SUM(amount) from region, product_sales where region_id = reg_id and region = 'East' and product_id = prod_id group by CUBE(region, product) 12 REGION ------: East All Regions All Regions All Regions Willie Albino PRODUCT SUM(AMOUNT) ------: : Ties 60 All Products 725 Hats 75 Jackets 100 Pants 100 Shirts 75 Shoes 130 Suits 90 Sweaters 75 T-Shirts 20 Ties 60 All Products 725 9/23/2021

Oracle 8 i Analytical SQL Features Analytical SQL Functions Willie Albino

Analytical SQL Functions l Analytical Function Categories: – – – l 14 Ranking Functions Windowing Functions Reporting Functions Lag/Lead Functions Statistics Functions are applied after all joins, WHERE, GROUP BY and HAVING clauses are performed, but before the ORDER BY clause is applied Willie Albino 9/23/2021

Basic Analytical Function Syntax: <function_name>() OVER ( [PARTITION BY <exp 1> [, …]] ORDER BY <exp 2> [ASC|DESC] [NULLS FIRST|NULLS LAST] ) Example: SELECT RANK(amount) OVER (PARTITION BY region ORDER BY amount) FROM REG_SALES; 15 Willie Albino 9/23/2021

Function Syntax Comments l PARTITION BY <exp> [, …] - this clause divides the query result into groups within which the analytical function operates – – 16 If the PARTITION BY clause is missing, the function operates over the entire dataset <exp> can be any valid expression involving column references Willie Albino 9/23/2021

Function Syntax Comments l ORDER BY <exp>[ASC|DESC] [NULLS FIRST|NULLS LAST] – – – specifies how the data is ordered within a group (partition) ASC|DESC specifies the sorting order for the grouping. The default sorting order is ASC. The presence of ORDER BY affects the outcome of analytical functions l l l 17 With ORDER BY, the set of rows used is the current row and all preceding rows in the partition (a growing window) Without ORDER BY, all the rows in the partition will be used The ORDER BY clause can be used to resolve ties between repeated values in a set. Willie Albino 9/23/2021

Function Syntax Comments l ORDER BY <exp>[ASC|DESC] [NULLS FIRST|NULLS LAST] – The NULLS FIRST|NULLS LAST clause determines the position of NULLs in the ordered sequence. If omitted, the position depends on the ASC, DESC arguments. l – 18 NULLs are considered to be larger than any other values. It is not guaranteed that the data will be sorted on the measures. Use the ORDER BY clause to specify the ordering sequence. Willie Albino 9/23/2021

Ranking Functions l Computes the rank of a record with respect to other records in the dataset based on the values of a set of measures l Ranking Functions: – – 19 RANK() and DENSE_RANK() CUME_DIST() and PERCENT_RANK() NTILE() ROW_NUMBER() Willie Albino 9/23/2021

RANK() and DENSE_RANK() Functions 20 l The RANK() and DENSE_RANK() functions allow you to rank items in a dataset or sub-group. l The RANK() function leaves gaps in the ranking sequence when there are ties in the rankings. l The DENSE_RANK() function does not leave gaps in the ranking sequence when there are ties in the rankings. Willie Albino 9/23/2021

Example of RANK()/DENSE_RANK() select amount, RANK() OVER (ORDER BY amount) AS rank_asc, DENSE_RANK() OVER (ORDER BY amount) AS dense_rank from product_sales, product, region where prod_id=product_id and region_id=reg_id and region='East' order by amount; AMOUNT RANK_ASC ------20 1 60 2 75 3 90 6 100 7 130 9 DENSE_RANK -----1 2 3 3 3 4 5 5 6 NOTES: Ranking value will repeat (leaving gaps) when the same data values are found in the dataset The order or the rows with repeated values is non-deterministic DENSE_RANK() does not leave gaps in the rank values for repeated data values (RANK() does) The largest rank value produced by DENSE_RANK equals the number of distinct values in the dataset 21 Willie Albino 9/23/2021

Using RANK() For Top-N Values List select * from ( select region, product, SUM(amount) amt, SUM(profit) profit, RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rank from product_sales, product, region where prod_id=product_id and region_id=reg_id GROUP BY region, product ) where rank_sum_amt < 4 Region -----Central East West Product ------Sweaters Shirts Pants Shoes Jackets T-Shirts Amt ---242 213 123 130 100 100 99 89 Profit -----92 87 57 30 28 24 13 17 23 Rank ---1 2 3 1 2 2 1 2 3 NOTES: Using RANK() or DENSE_RANK(), you can get the top N ranks within a dataset DENSE_RANK() could yield different results than RANK() depending on repeated values within the dataset. (The function ROW_NUMBER() would provide more accurate results. ) The bottom N rankings can be generated by changing the ordering sequence within the rank expression (e. g. , ORDER BY SUM(amount) ASC)) 22 Willie Albino 9/23/2021

CUME_DIST() and PERCENT_RANK() l CUME_DIST() computes the position of a specified value relative to the set of values (also known as inverse of percentile in statistics books) – – l PERCENT_RANK() returns the percent rank of a value relative to a group of values – 23 CD = (# values different from or equal to x)/(total # of values) Return values are between 0 and 1 PR = (rank of row in partition - 1)/(# of rows in the partition - 1) Willie Albino 9/23/2021

Example of CUME_DIST() & PERCENT_RANK() select region, product, SUM(amount), CUME_DIST() OVER (PARTITION BY region ORDER BY SUM(amount) ASC) AS cume_dist, PERCENT_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) ASC) AS pct_rnk from product_sales, product, region where prod_id=product_id and region_id=reg_id GROUP BY region, product 24 Willie Albino Region Product Amt Cume_Dist Pct_Rnk Central Central East East East : : Belts Suits Ties Hats Pants Shirts Sweaters T-Shirts Ties Hats Shirts Sweaters Suits Jackets Pants Shoes : : 85 98 104 111 123 213 242 20 60 75 75 75 90 100 130 : : 0. 1429 0. 2857 0. 4286 0. 5714 0. 7143 0. 8571 1 0. 1111 0. 2222 0. 5556 0. 6667 0. 8889 1 : : 0 0. 1667 0. 3333 0. 5 0. 6667 0. 8333 1 0 0. 125 0. 625 0. 75 1 : : 9/23/2021

NTILE(n) and ROW_NUMBER() l l l 25 NTILE(n) – Divides dataset into a specified number of buckets – Takes the number of buckets as an argument ROW_NUMBER() – assigns a unique number to each row within a partition – row numbers start with 1 and increase sequentially within each partition – better than RANK() or DENSE_RANK() for top-N queries Rows with rankings that are ties will not necessarily be assigned to the same bucket (if they span buckets) or the same row number in subsequent runs of the query using the same dataset Willie Albino 9/23/2021

Example of NTILE() & ROW_NUMBER() select region, product, SUM(amount), NTILE(3) OVER (PARTITION BY region ORDER BY SUM(amount) ASC) AS bucket, ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) ASC) AS row from product_sales, product, region where prod_id=product_id and region_id=reg_id GROUP BY region, product 26 Willie Albino Region -----Central Central East East East Product Amt -------Belts 85 Suits 98 Ties 104 Hats 111 Pants 123 Shirts 213 Sweaters 242 T-Shirts 20 Ties 60 Hats 75 Shirts 75 Sweaters 75 Suits 90 Jackets 100 Pants 100 Shoes 130 Bucket ------1 1 1 2 2 3 3 1 1 1 2 2 2 3 3 3 Row ----1 2 3 4 5 6 7 8 9 9/23/2021

Oracle 8 i Analytical SQL Features Windowing Functions Willie Albino

Windowing Functions 28 l Used to compute cumulative, moving or centered aggregates l Returns a value for each row in a dataset which depends on other rows in the corresponding window l Windowing functions include moving sum, moving average, moving min/max, cumulative sum and statistical functions, first and last value in window Willie Albino 9/23/2021

Windowing Function Syntax {SUM|AVG|MAX|MIN|COUNT|FIRST_VALUE|LAST_VALUE} OVER ( PARTITION BY <exp 1> [, …] ORDER BY <exp 2> [ASC|DESC] [NULLS FIRST|NULLS LAST] ROWS | RANGE { UNBOUNDED PRECEDING | <exp 3> PRECEDING} | BETWEEN {UNBOUNDED PRECEDING | <exp 4> PRECEDING} AND {CURRENT ROW | <exp 5> FOLLOWING} } ) 29 Willie Albino 9/23/2021

Windowing Function Syntax Comments l <exp> – Must be a constant or an expression which evaluates to a positive value – If ROWS was specified, it’s a physical offset which represents number of rows in the window – If RANGE was specified, it’s a logical offset (value or interval literal) l 30 An interval literal is specified as follows: RANGE INTERVAL n DAYS|MONTHS|YEARS RANGE x PRECEDING|FOLLOWING Willie Albino 9/23/2021

Windowing Function Syntax Comments l ROWS | RANGE – – l BETWEEN … AND … – – 31 ROWS specifies the window in physical units RANGE specifies the window as a logical offset Specifies the start and end point of the window If BETWEEN is omitted an end point is specified, that point will be considered the start point and the current row will be used as the end point Willie Albino 9/23/2021

Windowing Function Syntax Comments l UNBOUNDED PRECEDING – l UNBOUNDED FOLLOWING – 32 Specifies that the window starts at the first row of the partition, (or the start of the dataset, if the PARTITION BY clause is omitted) Specifies that the window ends at the last row of the partition, (or the last row of the dataset, if the PARTITION BY clause is omitted) Willie Albino 9/23/2021

Windowing Function Syntax Comments l CURRENT_ROW – As a start point: l l – As an end point: l l 33 If ROWS was specified, makes the current row the start of the window. If RANGE was specified, then the current value is the start of the window If ROWS was specified, makes the current row the end of the window. If RANGE was specified, then the current value is the end of the window Willie Albino 9/23/2021

Windowing Function Syntax Comments l <exp> FOLLOWING – l <exp> PRECEDING – l 34 If this is the start point, then the end point must be <exp> FOLLOWING or UNBOUNDED FOLLOWING If this is the end point, then the start point must be <exp> PRECEDING or UNBOUNDED PRECEDING This applies whether ROWS or RANGE was specified Willie Albino 9/23/2021

Example of a Partition (Sub-Grouping) Based Moving Window QUERY: Calculate a running total of the amount of sales by region select region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY amount ROWS UNBOUNDED PRECEDING) as mov_amt_sum from product_sales, product, region where prod_id=product_id and region_id=reg_id 35 REGION AMOUNT ------Central 85 Central 98 Central 104 Central 111 Central 123 Central 213 Central 242 East 20 East 60 East 75 East 90 East 100 East 130 Willie Albino MOV_AMT_SUM -----85 183 287 398 521 734 976 20 80 155 230 305 395 495 595 725 9/23/2021

Example of Date Based Moving Window Summaries select cust_id, trans_dt, amt, sum(amt) over (partition by cust_id order by trans_dt range interval '1' month preceding) sum_1_mnth, sum(amt) over (partition by cust_id order by trans_dt range between interval '1' month preceding and interval '1' month following) sum_2_mnth, sum(amt) over (partition by cust_id order by trans_dt range between interval '7' DAY preceding and interval '7' DAY following) sum_wk from cust_daily_summary order by cust_id, trans_dt 36 cust_id ------1643060 1643060 1643060 1643060 1643060 Trans_dt Amt Sum_1_mth Sum_2_mth Sum_wk ------- -----3/15/00 -3. 08 118. 78 -3. 08 4/15/00 121. 86 118. 78 103. 27 121. 86 5/11/00 -6. 80 115. 06 101. 85 -15. 51 5/12/00 0. 25 115. 31 100. 88 -15. 51 5/13/00 -0. 85 114. 46 96. 63 -15. 51 5/14/00 -1. 68 112. 78 92. 06 -15. 51 5/15/00 -6. 43 106. 35 88. 95 -15. 51 6/11/00 -4. 50 -20. 01 -36. 91 -17. 40 6/12/00 -0. 97 -14. 18 -32. 51 -17. 40 6/13/00 -4. 25 -18. 68 -35. 76 -17. 40 6/14/00 -4. 57 -22. 40 -35. 42 -17. 40 6/15/00 -3. 11 -23. 83 -34. 63 -17. 40 7/11/00 -4. 00 -21. 40 -28. 95 -10. 80 7/12/00 -2. 40 -19. 30 -27. 57 -10. 80 7/13/00 -3. 00 -21. 33 -27. 73 -10. 80 7/14/00 -0. 51 -17. 59 -24. 83 -10. 80 7/15/00 -0. 89 -13. 91 -23. 82 -10. 80 Willie Albino 9/23/2021

Oracle 8 i Analytical SQL Features Reporting Functions Willie Albino

Reporting Functions l l l Allow for the calculation of aggregate values within a data partition Return the same aggregate value for every row in a partition Syntax: – 38 {SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE} ([ALL | DISTINCT] {<value expression 1> | *}) OVER ([PARTITION BY <value expression 2>[, . . . ]]) Willie Albino 9/23/2021

Example of Using Reporting Functions Query: Find the region where each product was best seller SELECT product, region, sum_amt FROM (SELECT product, region, SUM(amount) AS sum_amt, MAX(SUM(amount)) OVER (PARTITION BY product) AS max_sum_amt FROM product_sales, region, product WHERE region_id=reg_id AND product_id=prod_id GROUP BY product, region) WHERE sum_amt = max_sum_amt 39 Willie Albino REGION ------BELTS Central HATS Central JACKETS East JEANS West PANTS Central SHIRTS Central SHOES East SOCKS West SUITS Central SWEATERS Central T-SHIRTS West TIES Central SUM_AMT ----85 111 100 50 123 213 130 78 98 242 89 104 9/23/2021

New Reporting Functions l RATIO_TO_REPORT(exp) – l LEAD() and LAG() – – – 40 Computes the ratio of a value to the sum of a set of values Useful for comparing values in different time period Allows access to more than one row in a table without a self-join LAG() provides access to a prior row (at a given offset) LEAD() provides access to a row after the current position These functions are position, not value based Willie Albino 9/23/2021

New Reporting Function Syntax l Syntax – – RATIO_TO_REPORT(<exp 1>) OVER ([PARTITION BY <exp 2> [, …]]) LEAD | LAG (<exp 1> [, <offset> [, <default>]]) OVER ([PARTITION BY <exp 2> [, …]]) ORDER BY <exp 3> [ASC|DESC] [NULLS FIRST | NULLS LAST] [, …]) l l 41 <offset> is optional and defaults to 1 <default> is optional and is the value returned if the <offset> falls outside the bounds of the dataset Willie Albino 9/23/2021

Example of RATIO_TO_REPORT() Query: Find ratio of total sales per product to total sales SELECT product, SUM(amount) AS sum_amt, SUM(amount)) OVER() AS total_amt, RATIO_TO_REPORT(SUM(amount)) OVER () AS ratio FROM product_sales, product, region WHERE prod_id = product_id AND region='East' GROUP BY product 42 PRODUCT -------Belts Hats Jackets Jeans Pants Shirts Shoes Socks Suits Sweaters T-Shirts Ties Willie Albino SUM_AMT TOTAL_AMT RATIO ----------150 2443. 061 186 2443. 076 199 2443. 081 50 2443. 020 268 2443. 110 363 2443. 149 230 2443. 094 78 2443. 032 188 2443. 077 392 2443. 160 109 2443. 045 230 2443. 094 9/23/2021

Example of LAG Function Query: Compare cust’s present amount to the amount 2 days ago SELECT cust_id, acct_date, sum(amt_usd) amount, LAG(SUM(amt_usd), 2, -999) OVER (PARTITION BY cust_id ORDER BY acct_trans_date ) AS old_amt FROM acct WHERE acct_date > '01 -NOV-00' GROUP BY cust_id, acct_date 43 Willie Albino cust_ID -------1643060 1643060 1643060 1659880 ACCT_DATE -----11/11/2000 11/12/2000 11/13/2000 11/14/2000 11/15/2000 12/11/2000 12/12/2000 12/13/2000 12/14/2000 12/15/2000 11/11/2000 11/12/2000 11/13/2000 AMOUNT OLD_AMT -------147. 47 -999 -5. 5 -999 -2. 27 147. 47 -11. 72 -5. 5 -4. 25 -2. 27 -7. 15 -11. 72 -2. 25 -4. 25 -2. 05 -7. 15 -15. 13 -2. 25 -0. 71 -2. 05 169. 17 -999 -14. 25 -999 -12. 5 169. 17 9/23/2021

Oracle 8 i Analytical SQL Features CASE Expressions Willie Albino

CASE Expressions l Used for bucketing data – l Very similar to DECODE statement – – l Provides more flexibility and logical power Offers better performance and is easier to read Syntax: – 45 allows for differently sized buckets CASE WHEN <cond 1> THEN <v 1> WHEN <cond 2> THEN <v 2> … [ ELSE <vn> ] END Willie Albino 9/23/2021

Example #1 of Using CASE Expressions SELECT SUM(CASE WHEN SUM(amount) BETWEEN 0 AND 50 THEN 1 ELSE 0 END) AS "0 -50", SUM(CASE WHEN SUM(amount) BETWEEN 51 AND 150 THEN 1 ELSE 0 END) AS "51 -150", SUM(CASE WHEN SUM(amount) BETWEEN 151 AND 250 THEN 1 ELSE 0 END) AS "151 -250", SUM(CASE WHEN SUM(amount) > 251 THEN 1 ELSE 0 END) "251+" FROM product_sales, product, region WHERE prod_id = product_id AND region='East' GROUP BY product 46 Willie Albino 0 -50 ---1 51 -150 -----3 151 -250 ------5 251+ ---5 9/23/2021

Example #2 of Using CASE Expressions SELECT CASE WHEN amount BETWEEN 0 AND 50 THEN ' 0 -50' WHEN amount BETWEEN 51 AND 150 THEN ' 51 -150' WHEN amount BETWEEN 151 AND 250 THEN '151 -250' WHEN amount > 250 THEN '251+' END bucket, COUNT(*) cnt, SUM(amount) amt FROM product_sales, product, region WHERE prod_id = product_id AND region='East' GROUP BY CASE WHEN amount BETWEEN 0 AND 50 THEN ’ 0 -50' WHEN amount BETWEEN 51 AND 150 THEN ’ 51 -150' WHEN amount BETWEEN 151 AND 250 THEN '151 -250' WHEN amount > 250 THEN '251+' END ORDER BY bucket 47 Willie Albino BUCKET -----0 -50 51 -150 151 -250 251+ CNT --3 21 2 3 AMT ---115 1873 455 176 9/23/2021

Summary l New analytical functionality in Oracle 8. 1. 6 (+) – – l Enhancements include – – – 48 Makes it easier to code certain types of SQL Allows for more efficient SQL code when compared to the equivalent pure SQL implementation SQL GROUP BY clause has been augmented to make querying and reporting easier Analytical functions enabling rankings, moving window calculations, lead/lag analysis Better ‘if-then-else’ capabilities provided through CASE Willie Albino 9/23/2021