How To Use Analytic Functions In Oracle

How To Use Analytic Functions In Oracle

July 06, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. Oracle Analytic functions will help you to use grouping functions on every single row and the query result will be done without grouping the values. Oracle DBA recommends to use Oracle Analytic functions on your web pages or doing reports and not to download all lines to website to do sum. You can add an extra column to your SQL query and apply Oracle Analytic functions. The final output calculated in the extra column can be used with just fetching the first row.

The first examples is a source query that we will use for the following examples. The query contains Oracle dual table and the pseudo column Rownum data is amended using Oracle MOD function.

 SELECT mod (rownum,3) AS amount
   FROM dual
CONNECT BY rownum < 21;

dba oracle dba database management system oracle performance tuning oracle analytic functions oracle analytic functions in oracle online colleges oracle mod oracle sum oracle rownum oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The following Oracle DBA query shows how to use Oracle COUNT function with Analytic function mode. As you see all lines are counted and the result “total_count” is done without grouping the data.

SELECT amount, COUNT(*) over () AS total_count
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
       CONNECT BY rownum < 21 ) ;

dba oracle dba database management system oracle performance tuning oracle analytic functions oracle analytic functions in oracle online colleges oracle mod oracle sum oracle rownum oracle count oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Oracle analytic functions can make the query simpler since we don’t need to worry about Oracle Group By clause. This example below shows how to receive minimum, maximum and sum using Oracle analytic functions.

SELECT amount,
       MIN(amount) over () AS min_amount,
       MAX(amount) over () AS max_amount,
       SUM(amount) over () AS sum_amount
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
       CONNECT BY rownum < 21 ) ;

dba oracle dba database management system oracle performance tuning oracle analytic functions oracle analytic functions in oracle online colleges oracle mod oracle sum oracle rownum oracle count oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

So far all Oracle DBA examples have applied Oracle Analytic functions over all lines, but there are more options to work with groups without grouping the data. The following query we do Sum for every different value type group e.g. Zeros, ones and twos are making three different groups and we are applying the Oracle SUM function on every group separately.

SELECT amount,
       SUM(amount) over (partition by amount) AS sum_grouped_amounts
  FROM (SELECT mod (rownum,3) AS amount
          FROM dual
CONNECT BY rownum < 21 ) ;

dba oracle dba database management system oracle performance tuning oracle analytic functions oracle analytic functions in oracle online colleges oracle mod oracle sum oracle rownum oracle count oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

There are option and functions to work with Oracle Analytic Functions take a look at Oracle web page for more examples.

See Also:
Oracle Select Oracle Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*