Showing posts with label Analytic Functions. Show all posts
Showing posts with label Analytic Functions. Show all posts

Sunday, December 4, 2011

Oracle 11g New Analytic Functions

ListAgg

LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the <measure> column.

ListAgg syntax:

LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

Examples:

-- LISTAGG ALL EMPLOYEES IN A GROUP ORDERED BY SALARY DESC
SELECT LISTAGG(FIRST_NAME || '  ' || LAST_NAME , ';') WITHIN GROUP(ORDER BY SALARY DESC) FROM EMPLOYEES

image

-- LISTAGG ALL EMPLOYEES IN A GROUP ORDERED BY YEAR
SELECT  LISTAGG(FIRST_NAME || '  ' || LAST_NAME , ';') WITHIN GROUP (ORDER BY HIRE_DATE),
             TO_CHAR(HIRE_DATE, 'YYYY') HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID=60
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')

image

-- LISTAGG EMPLOYEES, GROUP BY DEPARTMENT_ID
SELECT  DEPARTMENT_ID,
            LISTAGG(FIRST_NAME || '  ' || LAST_NAME , ';') WITHIN GROUP (ORDER BY HIRE_DATE)
FROM EMPLOYEES            
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID

image

-- LISTAGG EMPLOYEES USED AS AN ANALYTIC FUNCTION
SELECT  DEPARTMENT_ID,
             FIRST_NAME,
             LAST_NAME,
            LISTAGG(FIRST_NAME || '  ' || LAST_NAME , ';') WITHIN GROUP (ORDER BY HIRE_DATE) OVER (PARTITION BY department_id) as EMPLOYEES_WITHIN_DEPARTMENT
FROM EMPLOYEES            
ORDER BY DEPARTMENT_ID

image

NTH_VALUE

NTH_VALUE returns the measure_expr value of the nth row in the window defined by the analytic_clause. The returned value has the data type of the measure_expr.

You can now obtain the nth row in a window. NTH_VALUE is an addition to the already existing analytic functions: FIRST_VALUE and LAST_VALUE

Example:

-- SELECT EMPLOYEES ORDER BY DEPARTMENT_ID, HIRE_DATE AND ASSIGN A ROW NUMBER
SELECT DEPARTMENT_ID, HIRE_DATE, FIRST_NAME, LAST_NAME,
ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID, HIRE_DATE) ROW_NUMBER FROM EMPLOYEES
ORDER BY DEPARTMENT_ID, HIRE_DATE

image

Now, if we wanted the 2nd value in the above window, we could use nth_value function as follows:

-- SELECT 2ND EMPLOYEE ORDER BY HIRE_DATE
SELECT DEPARTMENT_ID, HIRE_DATE, FIRST_NAME, LAST_NAME,
ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID, HIRE_DATE) ROW_NUMBER ,
NTH_VALUE(FIRST_NAME || ' ' || LAST_NAME, 2) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) NTH_VALUE
FROM EMPLOYEES

image