복수행 함수 (그룹 함수)

1. COUNT 함수

입력되는 데이터의 총 건수를 반환

SELECT COUNT (*) , COUNT(hpage)

FROM professor ;

COUNT(*) = NULL값 포함 결과

COUNT(hpage) = NULL값 제외한 결과 

2. SUM 함수

입력된 데이터들의 합계값을 구하는 함수

3. AVG 함수

입력된 데이터들의 평균값을 구하는 함수

※ 전체 평균을 구할 때 NULL 값이 있을 수도 있기에 NVL함수를 응용해서 구해줌

예) 전체 인원 16명, 보너스 받는인원 10명, 그대로 평균을 구해주면 보너스 총합에서 보너스 받는 인원만 나눠주기에

정확한 결과가 나오지 않는다. 그래서 NVL을 이용해서 보너스 받지않는 인원은 0 으로 해서 평균을 구해준다.

4. MAX / MIN 함수

MAX 함수 = 주어진 데이터 중에서 가장 큰 값을 돌려줌

MIN 함수 = 주어진 데이터 중에서 가장 작은 값을 돌려줌

SELECT MAX(SAL), MIN(SAL)                            SELECT MAX(hiredater), MIN(hiredate)

FROM emp;                                                     FROM emp;

MAX(SAL)   MIN(SAL)                                        MAX(HIREDATE)    MIN(HIREDATE)

--------  --------                                        -------------    -------------

       5000          800                                            23-MAY-87         17-DEC-80

날짜의 경우 최근 날짜가 크고 이전 날짜가 작다.

5. STDDEV 함수 / VARIANCE 함수

STDDEV 함수 = 표준편차 구하는 함수

VARIANCE 함수 = 분산 구하는 함수

특정 조건으로 세부적인 그룹화 하기 (GROUP BY 절 사용)

GROUP BY 절 사용시 주의사항

1. SELECT 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 한다.

2. GROUP BY 절에 사용된 컬럼은 SELECT 절에 사용되지 않아도 된다.

3. GROUP BY 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias 는 사용하면 안된다.

조건을 주고 검색하기 (HAVING 절 사용)

그룹 함수를 비교 조건으로 사용하고 싶은 경우에 WHERE 절 대신 HAVING 절을 사용

자동으로 소계 / 합계를 구해주는 함수

1. ROLLUP 함수

ROLLUP 함수는 주어진 데이터들의 소계를 구해줌

       deptno ,position 컬럼으로 ROLLUP 한 경우

       deptno 컬럼으로 ROLLUP 한 경우                          position 컬럼으로 ROLLUP 한 경우

2. CUBE 함수

ROLLUP 함수와 같이 각 소계도 출력하고 전체 총계까지 출력

     deptno,position 컬럼으로 ROLLUP 한 경우

실전 그룹핑 관련 함수

1. GROUPING SETS 

그룹핑 조건이 여러 개 일 경우 유용하게 사용됨

  기존 방법은 따로 구한뒤 UNION 으로 묶었지만 GROUPING SETS 함수를 사용한 경우 간결하게 쿼리를 작성가능

2. LISTAGG 함수

출력 결과를 가로로 나열

LISTAGG(나열하고싶은 컬럼명,'데이터 구분할 문자') WITHIN GROUP(ORDER BY 가로로나열하고싶은 규칙)

LISTAGG(name,'**') WITHIN GROUP(ORDER BY hiredate) 

3.LAG 함수

이전 행 값을 가져 올 때 사용하는 함수

-문법:

LAG(출력할 컬럼명 , OFFSET , 기본 출력값)

 OVER(Query_partition구문 , ORDER BY 정렬할 컬럼)

4. LEAD함수

LAG 함수와 반대로 이후의 값을 가져오는 함수. LAG 함수와 방법은 동일하나 OFFSET 값이 마지막에 보인다.

5. RANK 함수 - 순위 출력 함수

집계용 - 단일조건 순위 출력

분석용 - 전체조건에서 순위 출력

집계용 문법 

-RANK(조건값) WITHIN GROUP(ORDER BY 조건값 컬럼명 [ASC|DESC])

예 ) 이름이 '송도권' 인 교수의 순위를 조회

분석용 문법 : RANK() 뒤가 WITHIN GROUP 에서 OVER 로 바뀜

-RANK() OVER(ORDER BY 조건컬럼명 [ASC|DESC]

6. SUM( ) OVER 를 활용한 누계 구하기

7. 판매 비율 구하기

RATIO_TO_REPORT 라는 함수를 사용하여 비율을 구할 수 있다.

예 ) panmae 테이블에서 100번 제품의 판매 내역과 각 판매점 별로 판매 비중을 구해보자

8. LAG 함수를 활용한 차이 구하기

출처: http://goalker.tistory.com/72 [오라클 스터디]