postgresql - 그룹 함수부터 Cube 절까지

Q. emp 테이블에서 같은 부서 내 본인보다 높은 연봉을 받는 동료직원의 이름, 부서번호, 연봉 출력하기

select e1.name as 직원이름, e1.sal as 직원연봉, e2.name as 동료이름, e2.sal as 동료연봉 
from emp2 e1 join emp2 e2
on e1.deptno=e2.deptno and e1.sal < e2.sal;

그룹 함수

• 함수의 분류

  • 단일행 함수
    • 하나의 행이 삽입되면 하나의 결과가 리턴
  • 복수행 함수(그룹함수)
    • 여러 개의 행이 삽입되면 하나의 결과가 리턴

• count 함수는 NULL은 세지 않음. 숫자, 문자, 날짜 컬럼 모두 센다

• sum, avg 함수는 NULL은 무시하며 숫자 컬럼만 연산가능

• avg 함수 주의사항

  • NULL을 포함한 평균을 구하는 경우 NULL 대상은 아예 연산 대상에서 제외가 된다.
  • ex) 총 10명이고 NULL이 5명이라면 avg함수는 5명의 평균값을 출력하기 때문에 sum(컬럼명)/count(*)의 값(전체 평균)과 값이 달라진다.

• round() 함수는 반올림

GROUP BY

• 6개의 구문 중 4번째 위치

  • SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY

• GROUP BY에서 만약 같은 그룹으로 묶일 때 더 세분화하여 매핑하고 싶다면 GROUP BY d, p;처럼 하나의 조건을 더 준다.

• GROUP BY 절에 나열된 컬럼 이외의 컬럼은 select 절에 단독으로 사용할 수 없다.

  • 그룹의 개수와 일치하지 않기 때문에 group by로 인해 축약되었기 때문에 개수가 일치하지 않는다.

HAVING 절

• GROUP BY 절 뒤에 사용, GROUP BY 연산 결과의 추가 조건 전달 시 사용
• WHERE 절은 그룹 함수 조건 사용 불가
• 일반, 그룹 조건을 전달하는 역할 수행
• 일반 조건의 경우 where/having 모두 사용 가능하나, where절 사용이 유리

SELECT customer_id, SUM(price * quantity) AS total_price
FROM orders
GROUP BY customer_id
Having SUM(price * quantity) >= 500;

• orders 테이블에서 각 고객별로 주문한 총 가격이 500 이상인 경우에만 출력하는 쿼리

GROUPING SET 절

• GROUP BY 절과 함께 사용
• 여러 형태의 그룹 정보를 동시 전달 가능
• GROUP BY 결과의 결합, 각 연산 결과를 하나로 나타내고 싶을 때 사용
• 각각의 그룹핑 데이터를 union으로 결합한 것으로 표현
• 그룹 level을 직접 전달
• 컬럼 순서가 달라져도 동일한 데이터 셋이 출력된다
• 사용자가 나열한 수 만큼의 그룹 연산 결과를 동시에 출력

select deptno, position, max(sal) from emp2
group by grouping sets(deptno, position,());
// 여기서 ()는 전체를 의미

Q. student 테이블에서 각 학년별 키의 평균, 학과별 키의 평균, 학년과 학과별 키의 평균, 전체 키의 평균을 출력

select avg(height) from student
group by grouping sets(grade, deptno, (grade, deptno), ());

Rollup 절

• grouping sets와 비슷한 표현식
• group by 절과 함께 사용, group by 결과의 결합
• 여러 형태의 그룹 정보를 동시에 전달하는 것이 가능
• grouping sets와는 달리 컬럼끼리의 그룹을 직전 전달할 필요가 없다
• 전달된 컬럼을 가지고 발생 가능한 여러 조합을 알아서 출력
• rollup에 나열된 컬럼 순서에 따라 다른 결과가 나온다
• rollup 컬럼 수 + 1개의 레벨(집단) 생성, 나열된 컬럼의 수보다 1개 많은 집단이 출력된다.
• 예시

select deptno, position, sum(sal) from emp2
group by rollup(deptno, position);
// 전체 소계를 () 없이 출력한 결과를 얻는다

Cube 절

• group by 절과 함께 사용하며 여러 형태의 그룹 정보를 동시에 전달 가능
• group by 결과의 결합 -> grouping set, rollup 과 같은 기능을 수행
• grouping sets와는 달리 묶을 그룹을 직접 전달할 필요가 없다
• 컬럼 순서가 변경되어도 동일한 결과
• 컬럼 수가 n일 때 2^n레벨의 소계 출력
  -> CUBE(A, B, C)라면 group by A, group by B, group by C, group by A, B group by B, C group by A, C group by A, B, C
  즉 총 2^3, 8개 출력
• 사용자가 원하는 그룹 형태가 정해진 경우 grouping set이 더 직관적이다

출처: https://velog.io/@bacccine/PostgreSQL-2130%EA%B0%95%EA%B9%8C%EC%A7%80%EC%9D%98-%EB%82%B4%EC%9A%A9-%EC%A0%95%EB%A6%AC