검색결과 리스트
글
집계함수 | 사용목적 |
COUNT(*) | NULL 값을 포함한 행의 수를 출력 |
COUNT(표현식) | 표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력 |
SUM([DISTINCT | ALL] 표현식) | 표현식의 NULL 값을 제외한 합계를 출력한다. |
AVG([DISTINCT | ALL] 표현식) | 표현식의 NULL값을 제외한 평균을 출력한다. |
MIN([DISTINCT | ALL] 표현식) | 표현식의 최소값을 출력 (문자,날짜,데이터타입 사용가능) |
STDDEV([DISTINCT | ALL] 표현식) | 표현식의 표준편차 출력 |
VARIAN([DISTINCT | ALL] 표현식 ) | 표현시의 분산 출력 |
기타. | 벤더별로 다양한 통계식 제공함. |
- 일반적으로 집계함수는 GROUP BY 절과 같이 사용된다.
(테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로 사용 가능하다.)
SELECT COUNT(*) " 전체행수", COUNT(HEIGHT) "키 건수", MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER;
-> COUNT(HEIGHT)는 NULL을 제외한 결과만 출력. COUNT(*)은 NULL을 포함한 결과값 출력.
2. GROUP BY 절.
- WHERE 절을 통해 조건에 맞는 데이터를 조회했지만, 테이블에 1차적으로 존재하는 데이터 이외의 정보 등 2차 가공이 필요할 때.,
(각 팀별로 선수가 몇명인지, 선수들의 평균 신장과 몸무게가 얼마나 되는지, 또는 각 팀에서 가장 큰 키의 선수가 누구인지 등의 2차 가공 정보도 필요하다.)
- GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다.
* GROUP BY 절과 HAVING절의 특징
- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
- 집계함수의 통계 정보는 NULL값을 가진 행을 제외하고 수행한다.
- GROUP BY 절에는 SELET 절과는 달리 ALIAS 명을 상용할 수 없다.
- 집계 함수는 WHERE 절에는 올 수 없다.
(집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다.)
- HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
- GRUOP BY 절에 의한 소그룹 별로 만들어진 집계 데이터 중 HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
- HAVING 절은 일반적으로 GROUP BY절 뒤에 위치한다.
* GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹단위의 칼럼과 집계함수를 사용할 수 있다.
EX. 각 부서별 평균급여 집계
1) GROUP BY 사용없이 집계함수 사용.
SELECT DEPTNO, AVG(SAL)
FROM EMP;
결과 :
SELECT SAL, AVG(SAL)
*
ERROR at line 1:
ORA-00937: not a single-group group function
=> 에러가 발생한다.( GROUP BY 절에 그룹 단위를 표시해 주어야 그릅단위 컬럼과 집계함수를 사용할 수 있다.)
2) GROUP BY 사용.
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
=> 원하는 결과 출력됨.
SELECT DEPTNO DEPART_NUMBER, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DEPART_NUMBER;
ERROR at line 3:
ORA-00904: "DEPART_NUMBER": invalid identifier
=> 칼럼에 대한 ALIAS는 SELECT 절에 정의하고 ORDER BY 절에서는 재활용 할 수 없지만 GROUP BY 절에서는 사용할 수 없다.
- SELECT에 GROUP BY에 포함되지 않은 칼럼을 사용할 경우 에러가 발생한다.
SELECT ENAME, AVG(SAL) AVG_SAL
FROM EMP
GROUP BY DEPTNO;
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO; DEPTNO AVG_SAL ---------- ---------- 30 1566.66667 20 2175 10 2916.66667 |
3. HAVING 절
예제 1. K - 리그 선수들의 포지션별 평균 키를 구하는데 평균키가 180센티미터 이상인 정보만 표시하라.
( WHERE절과 GROUP BY 절을 사용해 작성하라.
A. 오답
SELECT POSITION, ROUND(AVG(HEIGHT),2)
FROM PLAYER
WHERE AVG(HEIGHT)>=180
GROUP BY(POSITION)
=> 에러발생한다. WHERE절에는 AVG()라는 집계함수를 사용할 수 없다.
- WHERE 절은 FROM절에 정의된 집합의 개별행에 WHERE절의 조건이 먼저 적용되고 WHERE절의 조건에 맞는 행이 GROUP BY절의 대상이된다. 그런다음 HAVING 조건절이 적용된다.
- HAVING절은 WHERE절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에 차이가 있다.
- GROUP BY 절과 HAVING 절의 순서를 바꿔도 ORACLE 에서는 문법에러가 없지만, SQL server 에서는 문법오류가 발생한다.
A. 정답 SELECT POSITION, ROUND(AVG(HEIGHT),2) AVG_HEIGHT FROM PLAYER GROUP BY POSITION HAVING AVG(HEIGHT) >=180 ; POSITION AVG_HEIGHT -------------- ------------- GK 186,26 DF 180.21
SELECT DEPTNO, ROUND(AVG(SAL),2) FROM EMP GROUP BY DEPTNO HAVING ROUND(AVG(SAL),2)>=2000;
DEPTNO ROUND(AVG(SAL),2) ---------- ----------------- 20 2175 10 2916.67 |
예제2.
K - 리그 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수는 얼마인가에 대한 쿼리를 써라.
A1.
SELECT TEAM_ID, COUNT(*)
FROM PLAYER
WHERE TEAM_ID IN ('K02','K09')
GROUP BY (TEAM_ID)
A2.
SELECT TEAM_ID, COUNT(*)
FROM PLAYER
GROUP BY TEAM_ID
HAVING TEAM_ID IN ('K02','K09)
결과는 둘다 다음과 같다.
TEAM_ID COUNT
------------- -------------
K02 49
K09 49
* GROUP BY 소그룹의 데이터 중 일부만 필요한 경우 GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산을 하는 방법과, HAVING 절에서 필요한 데이터만 필터하는 두 가지 방법이 있다.
가능하면 WHERE 절에서 조건을 적용하여 GROUP BY대상을 줄이는 것이 효율적인 자원 사용 측면에서 바람직하다.
실습 가능한 EMP 테이블로 조회 Q. 각 DEPTNO 10번과 20번의 사원들 수 를 추출하라. A1. SELECT DEPTNO, COUNT(*) FROM EMP WHERE DEPTNO IN(10,20) GROUP BY DEPTNO; A.2 SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING DEPTNO IN (10,20) DEPTNO COUNT(*) ---------- ---------- 20 5 10 3 |
예제3. 포지션별 평균키만 출력하는데, 최대키가 190 이상인 선수를 가지고 있는 포지션의 정보만 추출한다.
* SELECT 절에 사용하지않은 MAX라는 집계함수를 HAVING 절에 조건으로 사용한 예시
A.
SELECT POSITION, AVG(HEIGHT)
FROM PLAYER
GROUP BY POSITION
HAVING MAX(HEIGHT) > =190 ;
즉, HAVING 절은 SELECT 절에 사용되지 않은 컬럼이나 집계함수가 아니더라도 GROUPBY 절의 기준항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.
* 주의 : WHERE절의 조건 변경은 대상 데이터의 개수가 변경되므로 결과 데이터의 값이 변경될 수 있지만, HAVING 절의 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드의 개수만 변경될 수 있다.
EMP 테이블에 적용 Q. 부서별 평균급여를 출력하는데, 최대 급여가가 2000 이상인 부서의 정보만 추출 - 부서의 맥스 SAL 결과 FROM EMP GROUP BY DEPTNO DEPTNO MAX(SAL) ---------- ---------- 30 2850 20 3000 10 5000
============================================================================= A. SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) > = 3000; DEPTNO AVG(SAL) ---------- ---------- 20 2175
10 2916.66667 * 30번 부서는 MAX가 3000 이하이므로 결과에서 필터된것을 확인할 수 있다. |
4. CASE 표현을 활용한 월별 데이터 집계
- 집계함수 CASE () ~ GROUP BY 기능은 모델링의 제1 정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법이다.
예제 : 부서별로 월별 입사자의 평균 급여를 알고싶다.
STEP 1) 개별 입사정보에서 월별 데이터를 추출하는 작업 진행
A1. 오라클 SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) HIRE_MON, SAL FROM EMP; A2. SQL server SELECT ENAME, DEPTNO, DATEPART(MONTH, HIREDATE) HIRE_MON, SAL FROM EMP;
STEP 2. 월별 데이터 구분 A. ORACLE SELECT ENAME, DEPTNO, CASE MONTH WHEN 1 THEN SAL END M01, CASE MONTH WHEN 2 THEN SAL END M02, CASE MONTH WHEN 3 THEN SAL END M03, CASE MONTH WHEN 4 THEN SAL END M04, CASE MONTH WHEN 5 THEN SAL END M05, CASE MONTH WHEN 6 THEN SAL END M06, CASE MONTH WHEN 7 THEN SAL END M07, CASE MONTH WHEN 8 THEN SAL END M08, CASE MONTH WHEN 9 THEN SAL END M09, CASE MONTH WHEN 10 THEN SAL END M10, CASE MONTH WHEN 11 THEN SAL END M11, CASE MONTH WHEN 12 THEN SAL END M12 FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP) ; * FROM 절에 사용된 인라인뷰는 2장4절에서 배울예정. STEP 3. 부서별 데이터 집계 ( 부서별로 월별 입사자의 평균급여를 알기위해 AVG함수와 GROUP BY 절 사용.) SELECT DEPTNO, AVG(CASE MONTH WHEN 1 THEN SAL END) M01, AVG(CASE MONTH WHEN 2 THEN SAL END) M02, AVG(CASE MONTH WHEN 3 THEN SAL END) M03, AVG(CASE MONTH WHEN 4 THEN SAL END) M04, AVG(CASE MONTH WHEN 5 THEN SAL END) M05, AVG(CASE MONTH WHEN 6 THEN SAL END) M06, AVG(CASE MONTH WHEN 7 THEN SAL END) M07, AVG(CASE MONTH WHEN 8 THEN SAL END) M08, AVG(CASE MONTH WHEN 9 THEN SAL END) M09, AVG(CASE MONTH WHEN 10 THEN SAL END) M10, AVG(CASE MONTH WHEN 11 THEN SAL END) M11, AVG(CASE MONTH WHEN 12 THEN SAL END) M12 FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP) GROUP BY DEPTNO; |
=> 같은 예제 ORACLE의 DECODE 사용 쿼리.
SELECT DEPTNO,
AVG(DECODE(MONTH, 1,SAL)) M01,
AVG(DECODE(MONTH, 2,SAL)) M02,
AVG(DECODE(MONTH, 3,SAL)) M03,
AVG(DECODE(MONTH, 4,SAL)) M04,
AVG(DECODE(MONTH, 5,SAL)) M05,
AVG(DECODE(MONTH, 6,SAL)) M06,
AVG(DECODE(MONTH, 7,SAL)) M07,
AVG(DECODE(MONTH, 8,SAL)) M08,
AVG(DECODE(MONTH, 9,SAL)) M09,
AVG(DECODE(MONTH, 10,SAL)) M10,
AVG(DECODE(MONTH, 11,SAL)) M11,
AVG(DECODE(MONTH, 12,SAL)) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP)
GROUP BY DEPTNO;
5. 집계 함수와 NULL 처리.
- 리포트의 빈칸을 NULL이 아닌 숫자 0 으로 표현하기 위해 NVL / ISNULL 함수를 사용하는 경우가 많은데, 다중행 함수를 이용하는 경우 오히려 불필요한 부하가 발생하므로 굳이 NVL 함수를 다중행 함수안에 사용할 필요가 없다.
- 다중행 함수는 입력값의 전체가 NULL인 경우에만 NULL이 리턴되고 전체 건수 중 일부만 NULL인 경우 NULL을 함수의 대상에서 제외한다.
- CASE 사용시 ELSE 절을 생략하게 되면 Default 값이 null이다. null 이 연산의 대상이 아닌 반면 SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0 END) 처럼 ELSE절에서 0을 지정하게되면 SUM 연산에 사용되므로 자원의 사용이 많아진다. 같은 결과를 얻을 수 있다면 가능한 ELSE절의 상수값을 지정하지 않거나 ELSE 절을 작성하지 않는것이 좋다.
- 개별 데이터의 급여가 NULL인 경우 NULL의 특성으로 자동적으로 연산에서 제외되게 되는데 불필요하게 ISNULL 또는 NVL함수를 사용해 데이터 건수만큼 연산이 일어가네 하는 것은 자원낭비다.
ex) SUM(NVL(SAL,0)) / SUM(ISNULL(SAL,0))
-리포트 출력 때 NULL이 아는 0을 표시하고 싶은 경우에는 NVL(SUM(SAL),0) 혹은 ISNULL(SUM(SAL),0) 을 사용하면된다.
예제: 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장 작성
A. SIMPLE CASE EXPRESSION
SELECT TEAM_ID,
NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END),0) FW,
NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END),0) MF,
NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END),0) DF,
NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END),0) GK,
COUNT(*)
GROUP BY TEAM_ID
A2. ELSE 생략가능하므로 생략
SELECT TEAM_ID,
NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 END),0) FW,
NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 END),0) MF,
NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 END),0) DF,
NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 END),0) GK,
COUNT(*)
GROUP BY TEAM_ID
ISNULL(SUM(CASE WHEN POSITION='MF' THEN 1 END),0) MF,
ISNULL(SUM(CASE WHEN POSITION='DF' THEN 1 END),0) DF,
ISNULL(SUM(CASE WHEN POSITION='GK' THEN 1 END),0) GK,
COUNT(*)
FROM PLAYER
GROUP BY TEAM_ID
예제 2. GROUP BY절 없이 PLAYER의 포지션별 평균 키 및 전체 평균키 출력
SELECT
ROUND(AVG(CASE WHEN POSITION='FW' THEN HEIGHT END),2)
ROUND(AVG(CASE WHEN POSITION='MF' THEN HEIGHT END),2)
ROUND(AVG(CASE WHEN POSITION='DF' THEN HEIGHT END),2)
ROUND(AVG(CASE WHEN POSITION='GK' THEN HEIGHT END),2)
'자격증을 위한 > SQLD' 카테고리의 다른 글
| [SQLD] 2과목 SQL 기본및 활용 - 1장 9절 JOIN (0) | 2016.03.09 |
|---|---|
| [SQLD] 2과목 SQL 기본및 활용 - 1장 8절 ORDER BY 절 (0) | 2016.03.09 |
| [SQLD] 2과목 SQL 기본및 활용 - 1장 6절 함수 (0) | 2015.08.29 |
| [SQLD] 2과목 SQL 기본및 활용 - 1장 5절 WHERE (0) | 2015.08.29 |
| [SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL (0) | 2015.08.29 |
RECENT COMMENT