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

[레인]

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 함수