SQL 활용 - 다중행함수와 GROUP BY절 활용(집계함수, HAVING 절, CASE GROUP BY 절)

SQL 활용 - 다중행함수와 GROUP BY절 활용(집계함수, HAVING 절, CASE GROUP BY 절)

 

다중행 함수 

 

집계함수

여러 행을 그룹화해서 그룹당 단 하나의 결과를 돌려주는 함수를 다중행 함수라고 합니다. 다중행 함수 중 하나인 집계함수의 특성은 다음과 같습니다.

 

- GROUP BY 절을 통해 행을 소그룹화 합니다.

- SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있습니다.

 

-- ALL : Defatul 옵션으로 생략이 가능합니다.
-- DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션입니다.

집계함수명 ([ DISTINCT | ALL ] 칼럼이나 표현식)

 

자주 사용되는 주요 집계함수입니다. 집계함수는 그룹에 대한 정보를 제공하므로 주로 숫자 유형에 사용되지만, MAX, MIN, COUNT 함수는 문자, 날짜 유형에도 적용이 가능합니다. 일반적으로 집계함수는 GROUP BY 절과 같이 사용되지만 테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로도 사용이 가능합니다

 

- COUNT(*) : NULL 값을 포함한 행의 수를 출력한다.
- COUNT(표현식) : 표현식의 값이 NULL 인 것을 제외한 행수를 출력한다.
- SUM([DISTINCT | ALL] 표현식) : 표현식의 NULL 값을 제외한 합계를 출력한다.
- AVG([DISTINCT | ALL] 표현식) : 표현식의 NULL 값을 제외한 평균를 출력한다.
- MAX([DISTINCT | ALL] 표현식) : 표현식의 최댓값을 출력한다.
- MIN([DISTINCT | ALL] 표현식) : 표현식의 최소값을 출력한다.
- STDDEV([DISTINCT | ALL] 표현식) : 표현식의 표준 편차를 출력한다.
- VARIANCE([DISTINCT | ALL] 표현식) : 표현식의 분산을 출력한다.

 

SELECT COUNT(*) AS 전체행수, COUNT(HEIGHT) AS 키건수,
MAX(HEIGHT) AS 최대키, MIN(HEIGHT) AS 최소키,ROUND(AVG(HEIGHT), 2) AS 평균키
FROM PLAYER;

 

COUNT 함수에 사용된 와일드 카드(*)는 전체 칼럼을 의미합니다. 전체 칼럼이 NULL인 행은 존재할 수 없으므로 결국 COUNT는 전체 행의 개수를 출력한 것이고, COUNT(HEIGHT)는 HEIGHT 칼럼 값이 NULL 인 33건은 제외된 건수의 합입니다.

 

GROUP BY 절

WHERE 절을 통해 조건에 맞는 데이터를 조회하지만 테이블에 1차적인 레코드 별 데이터 이외에 특정 칼럼값을 기준으로 하는 2차 가공 정보가 필요할 때가 있습니다. GROUP BY 절은 SQL 문에서 FROM과 WHERE 절 뒤에 위치하여 레코드의 특정 칼럼 값으로 분류해 소그룹에 대한 항목별 통계 정보를 얻을 때 사용합니다.

 

SELECT [DISTINCT] 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 컬럼 또는 표현식]
[HAVING 그룹조건식];

 

GROUP BY 절과 HAVING 절의 상세한 활용법은 다음과 같습니다.

 

- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계함수를 사용합니다.

- 집계함수의 처리 대상에 집계 컬럼의 값이 null인 레코드는 포함되지 않습니다.

- GROUP BY 절에서는 SELECT 절과 달리 ALIAS를 사용할 수 없습니다.

- 집계함수는 WHERE 절에 올 수 없습니다. 집계함수의 대상을 정하는 GROUP BY 절보다 WHERE 절이 먼저 수행되기 때문입니다.

- WHERE 절을 통해 전체 데이터를 GROUP으로 나누기 전에 불필요한 행들을 미리 제거합니다.

- HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹 집계함수를 이용한 조건을 표시하기 위해 사용합니다.

- GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력합니다.

- HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치합니다.

- 관계형 데이터베이스 환경에서는 뒤에 언급할 ORDER BY 절을 명시해야 데이터 정렬이 수행됩니다.

 

-- GROUP BY 절을 사용하지 않고 집계함수를 사용했을 때 어떤 결과를 보이는지 포지션별 평균키를 구해본다.
-- ORA-00937 : 단일 그룹의 그룹 함수가 아닙니다.

SELECT POSITION AS 포지션, AVG(HEIGHT) AS 평균키
FROM PLAYER;

-- GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹 단위의 칼럼과 집계함수를 사용할 수 있다.
-- GROUP BY절에서 POSITION 의 ALIAS는 사용할 수 없다.

SELECT POSITION AS 포지션, AVG(HEIGHT) AS 평균키
FROM PLAYER
GROUP BY POSITION;

-- 포지션별 최대키, 최소키, 평균키를 출력한다.
-- ORDER BY 절이 없기때문에 포지션 별로 정렬은 되지않는다.
-- 추가로 포지션과 키 정보가 없는 선수가 3명이라는 정보를 얻을 수 있으며, 측정대상 컬럼을 통해 포지션이 DF인 172명 중 30명은 키에 대한 정보가 없는 것도 알 수 있다.
-- 키 값이 NULL 인 경우는 계산 대상에서 제외된다.

SELECT POSITION AS 포지션, COUNT(*) AS 포지션별 인원수, COUNT(HEIGT) AS 측정대상
, MAX(HEIGHT) AS 최대키, MIN(HEIGHT) AS 최소키
, ROUND(AVG(HEIGHT),2) AS 평균키
FROM PLAYER
GROUP BY POSITION;

 

HAVING 절

 

 -- 케이리그 선수들의 포지션별 평균키를 구하는데 평균키가 180 cm 이상인 포지션의 정보만
 -- 표시하라는 요구 사항을 WHERE 절과 GROUP BY 절을 사용해 나타낸다.

 SELECT POSITION AS 포지션, AVG(HEIGHT) AS 평균키
 FROM PLAYER
 GROUP BY POSITION
 HAVING AVG(HEIGHT) >= 180;

 

GROUP BY 절과 HAVING 절의 순서를 바꾸어서 수행하더라도 문법 에러가 발생하지 않고 결과집합 또한 동일합니다.

 

그렇지만 SQL 처리순서를 보면, 포지션이란 소그룹으로 그룹핑돼 통계 정보가 만들어진 이후 적용된 결과 값에 대한 HAVING 절의 제한 조건에 맞는 데이터만 출력하는 것이므로 논리적으로 GROUPBY 절과 HAVING 절의 순서를 지키는 것이 좋습니다. 같은 실행 결과를 얻는 두 가지 방법 중 HAVING 절에 TEAM_ID 같은 GROUP BY 기준 칼럼에 대한 조건을 추가할 수도 있으나, 가능하면 WHERE 절에서 조건절을 적용해 GROUP BY의 계산 대상 자체를 줄이는 것이 효율적입니다

 

-- 선수들 중 K02 와 K09의 인원수는 얼마인가? 
-- WHERE 절과 GROUP BY 절을 사용한 SQL과 GROUP BY 절을 사용한 SQL과 GROUP BY 절과 HAVING 절을 모두 사용한 SQL을 작성합니다

SELECT TEAM_ID, COUNT(*)
FROM PLAYER
WHERE TEAM_ID IN ('K02', 'K09')
GROUP BY TEAM_ID;

SELECT TEAM_ID, COUNT(*)
FROM PLAYER
GROUP BY TEAM_ID
HAVING TEAM_ID IN ('K02', 'K09');

-- 포지션별 평균키만을 출력하는데, 최대키가 190cm 이상인 선수를 갖고 있는 표지션의 정보만 출력합니다.
-- MAX 집계함수를 HAVING 절에서 조건절로 사용합니다. 즉, HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있습니다.

-- 주의할 점은 WHERE 절의 조건 변경을 통해 결과 데이터의 개수가 변경되므로 결과 데이터 값이 변경될 수 있지만 
-- HAVING 절의 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드 개수만 변경됩니다.

SELECT TEAM_ID, AVG(HEIGHT)
FROM PLAYER
GROUP BY TEAM_ID
HAVING MAX(HEIGHT) >= 190

 

CASE 표현을 활용한 월별 데이터 집계

 

'집계함수(CASE()) ~ GROUP BY' 기능은 데이터 모델링 제1정규화로 인해 반복되는 칼럼에 대해 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을 정해진 칼럼 수 만큼 확장해 집계 보고서를 만드는 유용한 기법입니다.

 

부서별로 월별 입사자 평균 급여를 알고 싶다는 고객의 요구사항이 있습니다. 입사 후 1년마다 급여인상이나 보너스 지급과 같은 일정이 정기적으로 잡힌다면 업무적으로 중요한 정보가 될 수 있다.

 

-- STEP 1. 개별 데이터 확인, 먼저 개별 입사정보에서 월별 데이터를 추출하는 작업을 진행한다.

SELECT FIRST_NAME, DEPARTMENT_ID, EXTRACT (MONTH FROM HIRE_DATE) AS "입사월", SALARY
FROM HR.EMPLOYEES;

-- STEP2. 월별 구분
-- 추출된 MONTH 데이터를 Simple Case Expression 을 이용해 12개의 월별 컬럼으로 구분한다.

SELECT FIRST_NAME, DEPARTMENT_ID
, CASE MONTH WHEN 1 THEN SAL END AS M01
, CASE MONTH WHEN 2 THEN SAL END AS M02
, CASE MONTH WHEN 3 THEN SAL END AS M03
, CASE MONTH WHEN 4 THEN SAL END AS M04
, CASE MONTH WHEN 5 THEN SAL END AS M05
, CASE MONTH WHEN 6 THEN SAL END AS M06
, CASE MONTH WHEN 7 THEN SAL END AS M07
, CASE MONTH WHEN 8 THEN SAL END AS M08
, CASE MONTH WHEN 9 THEN SAL END AS M09
, CASE MONTH WHEN 10 THEN SAL END AS M10
, CASE MONTH WHEN 11 THEN SAL END AS M11
, CASE MONTH WHEN 12 THEN SAL END AS M12
FROM
(
    SELECT FIRST_NAME, DEPARTMENT_ID, EXTRACT(MONTH FROM HIRE_DATE) AS MONTH, SALARY AS SAL
    FROM HR.EMPLOYEES
);

-- STEP3. 부서별 데이터 집계
-- 최종적으로 보여주는 리포트는 부서별로 월별 입사자의 평균 급여을 알고싶다는 요구 사항이므로 부서별 평균값을 구하기 위해 GROUP BY 절과 AVG 집게 함수를 사용한다.
-- 직원 개인에 대한 정보는 더이상 필요 없으므로 제외한다.
-- ORDER BY 절을 사용하지 않기 때문에 부서번호별로 정렬되지않는다.

SELECT DEPARTMENT_ID
, AVG(CASE MONTH WHEN 1 THEN SAL END) AS M01
, AVG(CASE MONTH WHEN 2 THEN SAL END) AS M02
, AVG(CASE MONTH WHEN 3 THEN SAL END) AS M03
, AVG(CASE MONTH WHEN 4 THEN SAL END) AS M04
, AVG(CASE MONTH WHEN 5 THEN SAL END) AS M05
, AVG(CASE MONTH WHEN 6 THEN SAL END) AS M06
, AVG(CASE MONTH WHEN 7 THEN SAL END) AS M07
, AVG(CASE MONTH WHEN 8 THEN SAL END) AS M08
, AVG(CASE MONTH WHEN 9 THEN SAL END) AS M09
, AVG(CASE MONTH WHEN 10 THEN SAL END) AS M10
, AVG(CASE MONTH WHEN 11 THEN SAL END) AS M11
, AVG(CASE MONTH WHEN 12 THEN SAL END) AS M12
FROM
(
    SELECT FIRST_NAME, DEPARTMENT_ID, EXTRACT (MONTH FROM HIRE_DATE) AS MONTH, SALARY AS SAL
    FROM HR.EMPLOYEES
)
GROUP BY DEPARTMENT_ID

 

하나의 데이터에 여러 번 CASE 표현을 사용하고 집계함수가 적용되므로 SQL 처리 성능 측면에서 나쁘다고 생각할 수 있습니다. 같은 기능을 하는 리포트를 작성하기 위해 장문의 프로그램을 코딩하는 것에 비해 하나의 SQL 문으로 처리가능하므로 훨씬 효율적입니다. 데이터 건수가 많아질수록 처리 속도 차이는 더 커집니다. 개발자들은 가능한 하나의 SQL 문장으로 비즈니스적인 요구 사항을 처리할 수 있도록 노력해야 합니다.

 

-- Simple Case Expression 으로 표현된 위의 SQL 과 같은 내용으로 Oracle DECODE 함수를 사용한 SQL 문장을 작성한다.

SELECT DEPARTMENT_ID
, AVG(DECODE(MONTH, 1, SAL)) AS M01
, AVG(DECODE(MONTH, 2, SAL)) AS M02
, AVG(DECODE(MONTH, 3, SAL)) AS M03
, AVG(DECODE(MONTH, 4, SAL)) AS M04
, AVG(DECODE(MONTH, 5, SAL)) AS M05
, AVG(DECODE(MONTH, 6, SAL)) AS M06
, AVG(DECODE(MONTH, 7, SAL)) AS M07
, AVG(DECODE(MONTH, 8, SAL)) AS M08
, AVG(DECODE(MONTH, 9, SAL)) AS M09
, AVG(DECODE(MONTH, 10, SAL)) AS M010
, AVG(DECODE(MONTH, 11, SAL)) AS M011
, AVG(DECODE(MONTH, 12, SAL)) AS M012
FROM
(
    SELECT FIRST_NAME, DEPARTMENT_ID, EXTRACT (MONTH FROM HIRE_DATE) AS MONTH, SALARY AS SAL
    FROM HR.EMPLOYEES
)
GROUP BY DEPARTMENT_ID

 

 

집계함수와 NULL 처리

 

리포트 빈칸을 NULL이 아닌 ZERO로 표현하기 위해 NVL 함수를 사용하는 경우가 많습니다. 다중 행 함수를 사용하는 경우, NLV 함수를 사용하는 행이 포함되면 불필요한 부하가 발생하므로 굳이 사용할 필요가 없습니다. 다중 행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 NULL 인 행을 다중 행 함수의 대상에서 제외합니다.

 

가장 많이 실수하는 것 중 하나가 SUM(NVL(SAL,0)) 연산입니다. 개별 데이터의 급여가 NULL 인 경우 NULL의 특성으로 자동으로 SUM 연산에서 빠지는데, 불필요하게 0으로 변한 해 데이터 건수만큼 연산이 일어나게 할 필요가 없습니다. 리포트 출력 때 NULL이 아닌 0을 표시하고 싶은 경우 전체 SUM의 결과가 NULL 인 경우에만 한 번 NVL 함수를 사용하면 됩니다.

 

-- 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL을 작성한다. 데이터가없는 경우 0으로 표시한다.
SELECT TEAM_ID
, NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END),0) AS FW
, NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END),0) AS MF
, NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END),0) AS DF
, NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END),0) AS GK
, COUNT(*) AS SUM
FROM PLAYER
GROUP BY TEAM_ID;

-- ELSE 0, ELSE NULL 문구는 생략 가능하므로 다음과 같이 좀더 짧게 구성할 수 있다.

-- SIMPLE_CASE_EXPRESIION
SELECT TEAM_ID
, NVL(SUM(CASE POSITION WHEN 'FW' THEN 1  END),0) AS FW
, NVL(SUM(CASE POSITION WHEN 'MF' THEN 1  END),0) AS MF
, NVL(SUM(CASE POSITION WHEN 'DF' THEN 1  END),0) AS DF
, NVL(SUM(CASE POSITION WHEN 'GK' THEN 1  END),0) AS GK
, COUNT(*) AS SUM
FROM PLAYER
GROUP BY TEAM_ID;

-- SEARCHED_CASE_EXPRESIION
SELECT TEAM_ID
, NVL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END),0) AS FW
, NVL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END),0) AS MF
, NVL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END),0) AS DF
, NVL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END),0) AS GK
, COUNT(*) AS SUM
FROM PLAYER
GROUP BY TEAM_ID;

-- GROUP BY 절 없이 전체 선수들의 포지션별 평균키 및 전체 평균키를 출력할 수 있다.
SELECT
ROUND(AVG(CASE WHEN POSITION = 'FW' THEN HEIGHT END),2) AS FW
, ROUND(AVG(CASE WHEN POSITION = 'MF' THEN HEIGHT END),2) AS MF
, ROUND(AVG(CASE WHEN POSITION = 'DF' THEN HEIGHT END),2) AS DF
, ROUND(AVG(CASE WHEN POSITION = 'GK' THEN HEIGHT END),2) AS GK
, ROUND(AVG(HEIGT), 2) AS 전체평균키
FROM PLAYER