Analytics Functions – Oracle

RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.

SELECT ename, sal, ratio_to_report(sal) over (partition by deptno)
FROM emp
It returns the employee name, salary, and the ration of the salary to the sum salary of all employees partitioned by department no.

WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size

SELECT customer_id, cust_last_name, credit_limit,
WIDTH_BUCKET(credit_limit, 100, 5000, 10) “Credit Group”
FROM customers WHERE nls_territory = ‘Canada’
ORDER BY “Credit Group”;