-
단일함수, 집계함수데이터베이스 2021. 5. 5. 20:54
자주 쓰는 데이터 유형
CHARACTER , VARCHAR, NUMERIC, DATETIME 이 있다.
*CHAR와 VARCHAR 의 차이
CHAR 는 고정된 문자, VARCHAR 는 가변적 문자를 받는다.
CHAR 자료형에 정해진 데이터크기보다 작은 데이터를 넣으면 나머지가 공백으로 채워진다.
CHAR 에서 'AA' 와 'AA ' 는 동일한 데이터가 될 수 있지만,
VARCHAR 에서 둘은 다른 데이터이다.
단일행 함수의 종류
종류 내용 함수의 예 문자형 함수 문자 입력,
문자나 숫자를 반환LOWER, UPPER, ASCII, CHR/CHAR, CONCAT,
SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM,RTRIM,TRIM숫자형 함수 숫자 입력,
숫자 반환ABS,SIGN, MOD, CEIL/CEILING, FLOOR, ROUND, TRUNC, SIN, COS,
TAN, EXP, POWER, SQRT, LOG, LN날짜형 함수 DATE 타입 값의 연산 SYSDATE/GETDATE, EXTRACT/DATEPART,
TO_NUMBER(TO_CHAR(d,'YYYY'|'MM'|'DD'))/YEAR,MONTH,DAY변환형 함수 문자,숫자,날짜형 값 데이터타입 변환 CAST, TO_NUMBER, TO_CHAR, TO_DATE,
CAST, CONVERTNULL 관련 NULL 처리 NVL/ISNULL, NULLIF , COALESCE CASE 표현
IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해 SQL 의 비교 연산 기능을 보안한다.
SELECT ENAME, CASE WHEN SAL>200 THEN SAL ELSE 2000 END AS REVISED_SALARY FROM EMP;ORACLE 은 DECODE(표현식, 기준값1, 기준값2..) 함수를 이용할 수 있다.
NULL 과 공집합
컬럼 내 값이 없는 상태인 NULL 과 조건에 부합하는 데이터가 0건인 공집합은 다르게 구분해 처리해야한다.
1. "JSC" 라는 이름의 직원의 매니저는 존재하지 않는다.
SELECT MGR FROM EMP WHERE ENAME = 'JSC'; ----------------------------- 선택된 레코드가 없습니다.2. NVL 함수를 이용해 9999를 출력하고자 하지만, 공집합은 NULL 이 아니기 때문에 여전히 레코드가 없다고 나온다.
SELECT NVL(MGR,9999) AS MGR FROM EMP WHERE ENAME = 'JSC'; ----------------------------- 선택된 레코드가 없습니다.3. 집계함수를 이용해 NVL 대신 적용해본다. 빈칸으로 보이지만 실제 NULL 이 나온다.
SELECT MAX(MGR) AS MGR FROM EMP WHERE ENAME = 'JSC'; ----------------------------- MGR ------------- 1행이 선택되었습니다.4. 집계함수로 나온 NULL 에 NVL 함수를 적용해 원하는 데이터를 출력한다.
SELECT NVL(MAX(MGR),9999) AS MGR FROM EMP WHERE ENAME = 'JSC'; ----------------------------- MGR -------- 9999 1행이 선택되었습니다.
집계함수
다중 행 함수 : 여러 행들의 그룹이 모여서 그룹당 하나의 결과를 돌려주는 함수
기본적으로 많이 사용하는 집계함수는 COUNT, SUM, AVG, MAX,MIX, STDDEV, VARIANCE/VAR 등이 있다.
이외 ROLLUP, GROUPING SET 같은 GROUP 함수, 분석기능을 가진 WINDOW 함수도 있다.
집계함수의 특징
1. 다중 행 함수이다.
2. GROUP BY 절은 행들을 소그룹화한다.
3. SELECT, HAVING, ORDER BY 절에 사용할 수 있다.
4. GROUP BY 가 있어야 사용가능하다.
5. WHERE 절에는 집계함수를 쓸 수 없다.
GROUP BY 절
WHERE 절을 통해 테이블에 1차적으로 존재하는 데이터를 조회하지만,
팀별로 몇 명의 선수가 있는지, 선수들의 평균 신장, 가장 키가 큰 사람 등 가공된 2차 정보도 필요하다.
GROUP BY 는 FROM과 WHERE 뒤에 오며, 데이터들을 작은 그룹으로 분류해 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다.
SELECT [컬럼명] FROM [테이블명] WHERE [조건식] GROUP BY [컬럼/표현] HAVING [그룹조건식]GROUP BY 절에서 그룹 단위를 표기해 주어야 SELECT 절에서 그룹 단위의 컬럼과 집계함수를 사용할 수 있다.
또 SELECT 절에 ALLIAS 는 GROUP BY 에서 사용할 수 없다.
SELECT POSITION AS 포지션 , AVG(HEIGHT) AS 키 FROM PLAYER; ----------------------------------------------------------- 단일 그룹의 그룹함수가 아닙니다. SELECT POSITION AS 포지션 , AVG(HEIGHT) AS 키 FROM PLAYER GROUP BY 포지션; ----------------------------------------------------------- "포지션" 부적합한 식별자아래와 같이 사용한다.
POSITION 이란 소그룹으로 그루핑(GROUPING) 돼, 통계정보가 만들어진다.
SELECT POSITION AS 포지션 ,AVG(HEIGHT) AS 평균키 ,MAX(HEIGHT) AS 최고키 FROM PLAYER GROUP BY POSITION;FROM절에 정의된 집합 개별행에 WHERE 조건이 적용되며, WHERE 절에는 집계함수를 쓸 수 없다.
WHERE 조건에 맞는 행이 GROUP BY 절의 대상이 되며, 이후 HAVING 절의 조건에 맞는 행들이 출력된다.
소그룹 데이터 중 일부만 필요할 경우 WHERE 나 HAVING 어느쪽에 조건을 달아도 동일한 결과를 보여주지만,
GROUP BY 계산 대상을 줄이기 위해 WHERE 절에 작성하는 것이 자원 활용에 긍정적이다.
WHERE 절 조건은 대상 데이터의 개수가 변경되므로 결과 데이터 값이 변경되고,
HAVING 절 조건은 데이터 변경없이 출력되는 레코드 수만 변경된다.!
CASE 표현을 활용한 월별집계
예) 부서별로 월별 입사자의 평균 급여 알아내기
1. 개별 입사정보에서 월별 데이터 추출
SELECT ENAME AS 사원명 ,DEPTNO AS 부서번호 ,SAL AS 급여 ,MONTH(HIREDATE) AS 입사월 FROM EMP;2. 월별 데이터 구분
SELECT ENAME AS 사원명 ,DEPTNO AS 부서번호 ,CASE MONTH WHEN 1 THEN SAL END AS M01 //DECODE(MONTH,1,SAL) ,CASE MONTH WHEN 2 THEN SAL END AS M02 ,CASE MONTH WHEN 3 THEN SAL END AS M03 ... 12월까지 FROM SELECT ENAME ,DEPTNO ,SAL ,MONTH(HIREDATE) AS MONTH FROM EMP;3. 부서별 데이터 집계
SELECT DEPTNO AS 부서번호 ,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 ... 12월까지 FROM SELECT ENAME ,DEPTNO ,SAL ,MONTH(HIREDATE) AS MONTH FROM EMP GROUP BY DEPTNO;집계함수와 NULL
빈칸을 NULL 이 아닌 ZERO 로 표현하기 위해 NVL 함수를 이용하는 경우가 많은데,
다중 행 함수를 사용하는 경우는 불필요한 부하가 생겨 굳이 NVL 을 다중 행 함수 안에서 사용할 필요가 없다.
다중 행 함수는 입력값 전체가 NULL 일 때만 NULL 이 나오고, 일부가 NULL 일 경우 NULL 인 행을 그냥 함수 대상에서 제외한다.
CASE 사용시 ELSE 를 생략하면 DEFAULT 가 NULL 이다.
NULL 은 연산의 대상이 아니기 때문에 , 불필요하게 값을 0 을 지정할 필요가 없다.
불필요한 사용 예 1) SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0 END)
불필요한 사용 예 2) SUM(NVL(SAL,0))
어차피 연산에서 빠지는데 불필요한 0 변환으로 데이터 건수만큼 연산이 일어나게 하는 것은 자원의 낭비이다.
전체 결과가 NULL 일 경우에만 NVL(SUM(SAL),0) 을 사용하면 된다.
SELECT 실행순서
1. FROM 대상 테이블을 참조한다.
2. WHERE 대상 데이터가 아닌 것을 제거한다.
3. GROUP BY 소그룹화한다.
4. HAVING 조건에 맞는 것만 출력한다.
5. SELECT 데이터 값을 출력,계산한다.
6. ORDER BY 데이터를 정렬한다.
위 순서로 실행되기 때문에
DISTINCT , GROUP BY , UNION 이 사용된 경우가 아니라면,
SELECT 에 조회 컬럼으로 지정하지 않은 컬럼도 ORDER BY 대상으로 사용할 수 있다.
SELECT EMPNO, ENAME FROM EMP ORDER BY MGR;GROUP BY 절에서 그루핑 기준을 정의하게 되면 DB는 일반적인 SELECT 문장에 FROM 절에 정의된 테이블 구조를 가져가지 않는다.
GROUP BY 그루핑 기준에 사용된 컬럼과 집계함수에 사용될 수 있는 숫자형 데이터 컬럼들의 집합을 새로 만들며, 개별 데이터는 필요 없으므로 저장하지 않는다.
그래서 GROUP BY 이후 수행절인 SELECT 나 ORDER BY 에서 개별데이터를 사용하는 경우 에러가 발생한다.
결과적으로 SELECT 절에서는 그루핑기준과 숫자 형식 칼람의 집계함수를 사용할 수 있지만,
그루핑 기준 외 문자 형식 컬럼은 사용할 수 없다!!!
예1) 직업별 임금 구하기
SELECT JOB //문자 형식 컬럼 ,SAL FROM EMP GROUP BY JOB HAVING COUNT(*) > 0 ORDER BY SAL; ------------------------------------- JOB, SAL * GROUP BY 표현식이 아닙니다.SELECT JOB FROM EMP GROUP BY JOB HAVING COUNT(*) > 0 ORDER BY SAL; ------------------------------------ ORDER BY SAL * GROUP BY 표현식이 아닙니다.아래와 같이 GROUP BY 절이 사용되면 ORDER BY 에 집계함수를 사용할 수 있다.
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB HAVING COUNT(*) > 0 ORDER BY SUM(SAL);예2) DOWNLOAD 테이블에서 게시판 번호 내 가장 최근 다운로드 받은 날짜를 출력하고자 할 때
다운로드날짜 컬럼은 DATETIME 형식
1. 실패 쿼리
//RGST_DTTM String 형식이라 실패 SELECT BOD_SEQ ,DATE_FORMAT(RGST_DTTM,'%Y-%m-%d %H:%m') AS RECENT_DOWNDATE FROM TB_DOWNLOAD group by BOD_SEQ LIMIT 1; //GROUP BY clause and contains nonaggregated column 'DOWNLOAD.RGST_DTTM'2. 성공 쿼리
//RGST_DTTM 에 집계함수 MAX 적용 SELECT BOD_SEQ ,DATE_FORMAT(MAX(RGST_DTTM),'%Y-%m-%d %H:%m') AS RECENT_DOWNDATE FROM TB_DOWNLOAD group by BOD_SEQ;
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형