[Basic] Group함수, Having

1 그룹함수 개념과 종류

• 테이블 전체 행을 하나 이상의 칼럼을 기준으로 그룹화 => 그룹별 결과 출력
• 종류
  • COUNT: 행의 갯수 출력.

  SELECT COUNT(comm)
  FROM professor
  WHERE deptno = 101;

  COUNT([*]|[DISTINCT|ALL] )
  *은 Null 포함. DISTINCT는 중복값 제외. ALL은 default로 중복값 포함. 인수는 CHAR, VARCHAR2, NUMBER, DATE 타입 사용 가능.
  • MAX: Null 제외 최댓값 MIN: Null 제외 최솟값

  SELECT MAX(height), MIN(height)
  FROM student
  WHERE deptno = 102;

  인수는 NUM만 사용 가능
  • SUM: Null 제외 모든 행의 합 AVG: Null 제외 모든 행의 평균

  SELECT AVG(weight), SUM(weight)
  FROM student
  WHERE deptno = 101;

  인수는 NUMBER만 사용 가능.
  • STDDEV: Null 제외 모든 행의 표준편차 VARIANCE: Null 제외 모든 행의 분산

  SELECT STDDEV(sal), VARIANCE(sal)
  FROM professor;

  • GROUPING: 해당 칼럼이 그룹에 사용되었는지 여부 표시 1 or 0
  • GROUPING SETS: 한 번의 질의로 여러 개의 그룹화

2 데이터 그룹 생성

2-1 GROUP BY 절

• 특정 칼럼 값을 기준으로 테이블 전체 행을 그룹별로 나누기
  ex) 교수 테이블에서 학과별로 나눠서 평균 급여 구하기
• GROUP BY 절에 명시되지 않은 칼럼은, 그룹함수와 함께 사용할 수 없다
• 적용 규칙
  • 1 그룹핑 전에 where절에 사용되는 그룹 대상 집합 먼저 선택
  • 2 group by 절에는 반드시 칼럼 이름 포함. 칼럼 별명 사용 불가.
  • 3 그룹별 출력 순서 default는 오름차순
  • 4 select 절에서 나열된 칼럼 이름이나 표현식은, group by 절에 반드시 명시(위의 bold)
  • 5 group by 절에 명시한 칼럼 이름은 select 절에서 명시하지 않아도 된다

SELECT deptno, position, AVG(sal)
FROM professor
GROUP BY deptno;

• 이 경우는 에러가 뜬다. 이유는 position은 group by 절에 명시하지 않은 칼럼이기 때문(bold 위반). 그리고 deptno는 group by에 명시되었기 때문에 select에 명시하지 않아도 된다(5)(그럼에도 일반적으로 안헷갈리려고 deptno 쓴다). 수정은 아래와 같이

SELECT AVG(sal)
FROM professor
GROUP BY deptno;

2-2 다중 칼럼 그룹핑

• 하나 이상의 칼럼을 사용해 그룹을 나누고 => 그룹별로 다시 서브 그룹 나누기
  ex) 전체 교수를 학과별로 나누고, 직급별로 다시 나누기

SELECT deptno, grade, COUNT(*), ROUND(AVG(weight))
FROM student
GROUP BY deptno, grade;

• 이 경우 먼저 deptno로 나누고, 그 안에서 grade로 나누고, 거기서 각각 count와 avg를 실행

2-2-1 ROLLUP와 CUBE

• ROLLUP: 열의 순서대로 계층적인 부분 합계 생성.
  ABC라는 칼럼이 있으면 A, AB, ABC, 전체 의 4개의 그룹이 생긴다.
  ex) 아래는 A가 부서, B가 직무.
  아래와 같은 순서로 생긴다.
  1 AB그룹(Sales, Clerk)
  2 A그룹(Sales, null)
  3 전체 그룹(Null, Null)

select d.dname, e.job, sum(sal)
from emp e, dept d
where e.deptno = d.deptno
group by rollup(d.dname, e.job);

• CUBE: 모든 가능한 부분 합계를 생성.
  ABC라는 칼럼이 있으면 A, B, C, AB, AC, BC, ABC, 전체 의 8개의 그룹이 생긴다.
  ex) ex) 아래는 A가 부서, B가 직무.
  아래와 같은 순서로 생긴다
  1 AB그룹(Sales, Clerk)
  2 A그룹(Sales, Null)
  3 B그룹(Null, Clerk)
  4 전체 그룹(Null, Null)

select d.dname, e.job, sum(sal)
from emp e, dept d
where e.deptno = d.deptno
group by cube(d.dname, e.job);

• 그냥 group by 와의 비교. 그냥 AB 그룹만을 만든다.

select d.dname, e.job
from emp e, dept d
where e.deptno = d.deptno;

2-2-2 GROUPING 함수

• 지정한 칼럼이 rollup, cube에 사용되었으면 0, 아니면 1을 반환

SELECT deptno, grade, count(*), GROUPING(deptno), GROUPING(grade)
FROM student
GROUP BY ROLLUP(deptno, grade);

2-2-3 GROUPING SETS 함수

• GROUP BY 절에서 그룹 조건을 여러 개 지정. => 각 그룹 조건에 대해 별도로 GROUP BY한 걸 UNION ALL한 것과 같은 결과.

GROUP BY
GROUPING SETS(a, b, c)

GROUP BY a UNION ALL
GROUP BY b UNION ALL
GROUP BY c

GROUP BY
GROUPING SETS(a, b, (b, c))

GROUP BY a UNION ALL
GROUP BY b UNION ALL
GROUP BY b, c

GROUP BY
GROUPING SETS(a, ROLLUP(b, c))

GROUP BY a UNION ALL
GROUP BY ROLLUP b, c

GROUP BY
GROUPING SETS(a, CUBE(b, c))

GROUP BY a UNION ALL
GROUP BY CUBE b, c

• 실 사용 예

SELECT deptno, grade, TO_CHAR(birthdate, 'YYYY') birthdate, COUNT(*)
FROM student
GROUP BY GROUPING SETS((deptno, grade), 
						(deptno, TO_CHAR(birthdate, 'YYYY')));

이렇게 하면 (deptno, grade)로 구성된 grouping과 (deptno, birthdate)로 구성된 grouping을 union all해서 합친 결과.

SELECT deptno, grade, TO_CHAR(birthdate, 'YYYY') birthdate, COUNT(*)
FROM student
GROUP BY deptno, grade
UNION ALL
SELECT deptno, grade, TO_CHAR(birthdate, 'YYYY') birthdate, COUNT(*)
FROM student
GROUP BY deptno, TO_CHAR(birthdate, 'YYYY') 
                        

와 같다

3 HAVING 절

• GROUP BY 절에 조건을 적용
• WHERE과의 차이. 같이 쓰일 때 실행 과정.
  • 1 테이블에서 WHERE 절에 의해 조건을 만족하는 행 집합 선택(그룹화하기 전에 먼저 검색 조건 실행)
  • 2 GROUP BY 절에 의해 그룹핑
  • 3 HAVING절에 의해 조건 만족하는 그룹 선택(그룹화 된 결과 집합에 검색 조건 실행)
• 실무에서는 where을 먼저 하는 게 그룹화 전에 행 줄여서 더 효율적
• WHERE은 안헷갈리게 GROUP BY 보다 먼저 기재. HAVING은 뒤에 기재.

SELECT grade, COUNT(*), ROUND(AVG(height)) avg_height, 
		ROUND(AVG(weight)) avg_weight
FROM student
GROUP BY grade
HAVING COUNT(*) > 4
ORDER By avg_height DESC;

학년별로 그룹핑해서 명수가 4명 이상인 학년만 출력