Excel – Charting the Results of Oracle Analytic Functions
This is a somewhat complicated example that builds a couple of sample tables, uses a SQL statement with the Oracle analytic function LEAD submitted through ADO in an Excel macro, and then presents the information on an Excel worksheet. When the user clicks one of three buttons on the Excel worksheet, an Excel macro executes that then build charts using disconnected row sources – a disconnected ADO recordset is used to sort the data categories before pushing that data into the charts that are built on the fly.More…
Cool SQL Analytic Function: RATIO_TO_REPORT
RATIO_TO_REPORT is a very handy and useful function. Before going through what it does, let’s first look at this example:
SELECT last_name, salary,
ROUND (salary / SUM (salary) OVER () * 100,
2
) percent_of_total
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY last_name;
Returns:
LAST_NAME SALARY PERCENT_OF_TOTAL
------------------------- ---------- --------------------------------------
Baida 2900 20.86
Colmenares 2500 17.99
Himuro 2600 18.71
Khoo 3100 22.3
Tobias 2800 20.14
5 row(s) retrieved
The percent_of_total column represents the salary for each employee as a percentage of the total salary paid to all clerks. The above query can also be re-written using the RATIO_TO_REPORT SQL function:
SELECT last_name, salary,
ROUND
(ratio_to_report (salary) OVER () * 100,
2
) AS percent_of_total
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY last_name
Both queries return the same output.
Oracle documentation definition: The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. Its syntax is:
RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )
In this, the following applies:
expr can be any valid expression involving column references or aggregates.
The PARTITION BY clause defines the groups on which the RATIO_TO_REPORT function is to be computed. If the PARTITION BY clause is absent, then the function is computed over the whole query result set.
Finally, Tom Kyte used the RATIO_TO_REPORT function to calculate the percentage of each browser hitting his blog and to calculate the percentage of his posts per month. Sergio Leunissen used the RATIO_TO_REPORT function as a basis for an APEX (HTML DB) report.
SQL Analytic functions are simple and powerful.
Filed in Oracle, Tips with 4 Comments | Tags: analytic, function, sqlCool SQL Analytic Function: NTILE
The NTILE function divides an ordered partition into buckets and assigns a bucket number to each row in the partition.
The buckets are calculated so that each bucket has exactly the same number of rows assigned to it.
If the number of rows in the partition does not divide evenly into the number of buckets, then the number of rows assigned for each bucket will differ by one at most. The extra rows will be distributed one for each bucket starting from the lowest bucket number.
For example, you want to organize the employees into four buckets: (more…)
Filed in Oracle with 2 Comments | Tags: analytic, sqlKeep first, Keep last
A famous person once said: Oracle’s analytic SQL functions rock, Oracle’s analytic SQL functions roll. I totally agree. However, I would like to add to this that they rock and roll on a much faster beat if you know more about them. To explain what I mean, let’s take an example:
Filed in Oracle, Tips with 2 Comments | Tags: analytic, function, sql
