Oracle Analytic Functions

Oracle Analytic Functions is a SQL function in the Oracle Specific category. Oracle: analytic functions beyond the standard. RATIO_TO_REPORT gives the ratio of a value to the partition total. The syntax is RATIO_TO_REPORT(value) OVER (PARTITION BY col). It accepts value. It returns analytic result. A typical example: SELECT name, department, salary, RATIO_TO_REPORT(salary) OVER (PARTITION BY department) AS salary_ratio, SUM(salary) OVER (PARTITION BY department) AS dept_total FROM employees; -- salary_ratio = salary / dept_total per department -- All ratios in a department sum to 1 -- Other Oracle-specific analytics: -- LISTAGG (string aggregation) -- NTH_VALUE (nth value in ordered set) -- WIDTH_BUCKET (histogram buckets) -- LAG/LEAD with IGNORE NULLS (skip nulls in offset) A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).