ORACLE FIRST()

오라클 FIRST()

 

이런 함수가 있는지 처음 알았다.

 

FIRST AND LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. If only row ranks as FIRST or LAST, then the aggregate operates on the set with only one element.

 

If you omit the OVER caluse, then the FIRST and LAST functions are treated as aggregate functioins. You can use these functions as analytic functions by specifying the OVER caluse. The query_partition_clause is the only part of the OVER clause valid with these functions. If you include the OVER clause but omit the query_partition_clause, then the function is treated as an analytic function, but the window defined for analysis is the entire table.

 

These function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly  converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

 

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.

 

• The aggregate_function argument is any one of the MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV functions. It operators on values from the rows that rank either FIRST, LAST. If only one row ranks as FIRST or LAST, then the aggregate operates on a singleton(nonaggregate) set.
• The KEEP keyword is for semantic clarity. It qualifies aggregate_function, indicating that only the FIRST or LAST values of aggregate_function will be returned.
• DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle Database will aggregate over only those rows with the minimum(FIRST) or the maximum(LAST) dense rank (also called olympic rank).

 

오라클의 hr 계정에서 테스트가 가능하다.

 

Aggregate Example

The following example returns, whthin each department of the sample table hr.employees, the minimum salary among the employees who make the lowest commission and the maximim salary among the employees who make the highest commission:

SQL > 

SELECT department_id,
        MIN(salary),
        MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY COMMISSION_PCT) "WORST",
        MIN(salary) KEEP (DENSE_RANK LAST ORDER BY COMMISSION_PCT) "WORST2",
        MAX(salary),
        MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "BEST",
        MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "BEST2"
FROM employees
GROUP BY department_id
ORDER BY department_id;

 

 

Analytic Example

The next example makes the same calculation as the previous example but returns the result for each employees whthin the department:

 

SQL>

SELECT last_name, department_id, salary,
        MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY department_id) "WORST",
        MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Best"
   FROM employees
   ORDER BY department_id, salary, last_name ;

 

 

 

 

DENSE_RANK() 에 대한 것은 아래 글을 참조한다.

https://web-obj.tistory.com/389

ORACLE DENSE_RANK()