그룹함수 : SUM, COUNT, AVG, MAX, MIN, STDDEV(표준편차), VARIANCE(분산)

  • ROLLUP(컬럼명, 컬럼명, …) : 그룹별 소계/ n개의 열 -> n+1개 조합
  • CUBE(컬럼명, 컬럼명, …) : 모든 조합의 그룹별 소계/ n개의 열 -> 2^n개 조합
SELECT column1, column2 
FROM table
GROUP BY column1, ROLLUP(column2);

GROUP BY : 특정 속성을 기준으로 그룹화함, 그룹함수와 함께 쓰임

  • GROUP BY를 사용한 SELECT 절에는 그룹함수나, GROUP BY에 사용된 칼럼 or 표현식만 와야함
  • 어느 칼럼 or 표현식을 SELECT 절에 사용하고 싶으면 GROUP BY절에 꼭 사용되어야 함.
  • 반대경우 - GROUP BY에 사용하고 SELECT절에는 사용안해도 됨
  • 별명 사용 불가능 -> 본 칼럼명만 사용해야함
# 불가능
SELECT deptno, position, AVG(NVL(bonus, 0)) AS 평균보너스 
FROM professor
GROUP BY deptno;

# 가능 - 반대경우
SELECT deptno, AVG(NVL(bonus, 0)) AS 평균보너스 
FROM professor
GROUP BY deptno, position;

HAVING(그룹 조건절) : 그룹에 대한 조건 설정, 그룹함수는 조건을 걸때 HAVING을 사용해야함

SELECT column1, AVG(column2), column3 FROM table
WHERE column1 = 'A'
GROUP BY column3
HAVING AVG(column2) >= 100
ORDER BY 1;

○ ROWNUM : where절에 의해 정해진 순차적인 번호, order by에 의해 변경x

  • rownum을 정렬 후 변경된 순서로 지정하고 싶으면 인라인뷰로 서브쿼리를 사용하여 먼저 정렬한 후 지정해야 함
  • 정렬 후 변경된 순서에서 rownum의 조건을 지정해 주고 싶으면 다시 또 서브쿼리로 묶고 WHERE절로 조건지정
  • 만들어놓은 rownum에 별칭을 지정해줘야 호출 가능. 별칭을 지정하지 않고 rownum으로 호출 시 새로운 rownum을 부름
-- 테이블 행 순서 그대로 번호 부여 --
SELECT ROWNUM AS 순위, p.* 
FROM (SELECT * FROM professor) p;

-- ROWNUM 정렬 후 순서 지정 방법 --
SELECT ROWNUM AS 순위, p.* 
FROM (SELECT * FROM professor ORDER BY pay) p;

-- ROWNUM 정렬 후 순서 지정 후 조건 지정 방법 --
SELECT * FROM
  (SELECT ROWNUM AS 순위, p.* 
   FROM (SELECT * FROM professor ORDER BY pay) p)
WHERE 순위 BETWEEN 1 AND 3;

○ ROWID : 모든 row에 유일한 번호

○ LISTAGG(컬럼명, 구분자[선택,d:NULL]) WITHIN GROUP (ORDER BY 정렬기준) :  컬럼 데이터들을 이어서 출력

SELECT deptno, LISTAGG(NAME, ', ') WITHIN GROUP(ORDER BY hiredate DESC) AS "LISTAGG"
FROM professor
GROUP BY deptno;

○ LAG(컬럼명, offset, 기본값) OVER (ORDER BY 정렬기준) : 이전 행 값을 가져옴

  • offset : 이전행 위치차    ex) 바로 전 행의 값을 가져오고 싶으면 1, 두 행 전 값 : 2
  • 기본값 : 이전행의 값이 존재하지 않을 때 출력값
SELECT name, hiredate, pay, 
    LAG(pay, 1, 0) OVER(ORDER BY hiredate) AS lag
FROM professor;

○ LEAD(컬럼명, offset, 기본값) OVER (ORDER BY 정렬기준) : 다음 행 값을 가져옴

SELECT name, hiredate, pay, 
    LEAD(pay, 1, 0) OVER(ORDER BY hiredate) AS lead
FROM professor;

○ RANK(조건값) WITHIN GROUP (ORDER BY 조건컬럼) : 조건칼럼에서 조건 값의 순위를 출력

# 이름이 '송도권'인 교수의 순위 출력
SELECT rank('송도권') WITHIN GROUP(ORDER BY name) AS rank
FROM professor;

WINDOW 함수 

  • GROUP BY를 사용하지 않는 그룹함수 
  • OVER절 사용

○ RANK() OVER (PARTITION BY 파티션칼럼 ORDER BY 조건컬럼 ) : 조건칼럼의 정렬기준에 맞게 순위 출력

# 학과별 급여 순위를 오름차순, 내림차순으로 출력
SELECT name, pay, 
  RANK() OVER(PARTITION BY deptno ORDER BY pay) AS "rank asc",
  RANK() OVER(PARTITION BY deptno ORDER BY pay DESC) AS "rank desc"
FROM professor;

○ DENSE_RANK() : RANK()에 공동순위를 반영함

○ ROW_NUMBER() : 순차적인 번호 출력 (like ROWNUM)

# ex) 부서번호별로 급여 오름차순으로 번호부여 
SELECT ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY pay) AS 번호, p.*
FROM professor p;

-- row_number 조건 지정 방법 --
SELECT * FROM
(SELECT row_number() OVER (ORDER BY pay) AS 순위, p.*
FROM professor p)
where 순위 BETWEEN 1 AND 3;

→ row_number에 조건을 지정하고 싶으면 전체를 인라인뷰(서브쿼리)로 묶은 다음 WHERE절로 지정해 줘야함