오라클 함수-집합 함수, 분석함수, 정규표현식(10g) (2008.09.10 )

1. 집합 함수 (Aggregate Functions)

 

1) avg 함수

select avg(salary) from employees;

 

select employee_id, department_id, salary, 

RAOUND(AVG(salary) OVER (PARTITION BY department_id),0) "AvgByDeptid"

from employees

where department_id IN (10,20,30)

 

2) rank 함수

select rank(3000) whthin group(order by salary desc) "rank" from employees;

 

select emplyee_id, salary, rank() over (order by salary) "rank" from employees;

 

3) sum, min, max, count 함수

 

 2. 분석함수

 

1) first_value 함수

select employee_id, salary,

FIRST_VALUE(salary)

OVER(PARTITION BY department_id ORDER BY salary DESC)

"Highsal_Departmentid"

from employees;

 

2)count 함수

 

3) sum 함수