[SQLD] 2과목 "SQL 기본" - 단일행함수, 집계함수 파트 핵심 요약 정리

함수(Function) :: 내장함수, 사용자정의 함수

- 내장함수 : 단일행함수, 다중행함수
- 단일행함수 : 문자형, 숫자형, 날짜형, 형변환, NULL 관련 함수
- 다중행함수 : 집계함수, 그룹함수, 윈도우함수

문자형 함수

문자를 연산하는 함수.
`LOWER`,`UPPER`, `INITCAP`, `ASCII`, `CHAR`, `CONCAT`, `SUBSTR`, `LENGTH`, `LTRIM`, `RTRIM`, `TRIM`

-- 문자형 함수

-- UPPER : 대문자 변환
-- LOWER : 소문자 변환
-- INITCAP : 첫글자 대문자, 나머지 소문자
SELECT 'aBC' FROM DUAL;
SELECT UPPER('aBC') FROM DUAL; // 대문자 변환
SELECT LOWER('aBC') FROM DUAL; // 소문자 변환
SELECT INITCAP('aBC') FROM DUAL; // 첫 글자 대문자, 나머지 소문자
SELECT INITCAP(ENAME) FROM EMP;

-- ASCII : 문자를 아스키코드로 변환
-- CHR : 아스키코드를 문자로 변환
SELECT ASCII('A') FROM DUAL; // CHAR 문자 → 숫자형코드
SELECT ASCII(NULL) FROM DUAL; // 아스키코드 없음 (NULL은 NULL)
SELECT CHR(65) FROM DUAL; // 숫자형코드 → CHAR 문자

-- CONCAT : 문자열 합치기
SELECT CONCAT(CONCAT('AB', 'CD'), 'EF') FROM DUAL; // 문자열 합치기
SELECT CONCAT(EMPNO, ENAME) FROM EMP;

-- LENGTH : 문자열 길이
SELECT LENGTH('ABCD') FROM DUAL;
SELECT LENGTH(ENAME) FROM EMP;

-- SUBSTR : 서브스트링 추출
SELECT SUBSTR('ABCD', 2, 2) FROM DUAL; // 2번째 값부터 2개
SELECT SUBSTR('ABCD', 2) FROM DUAL; // 2번째부터 끝까지

-- EMP에서 ENAME의 두번째부터 끝까지, 소문자로
SELECT LOWER(SUBSTR(ENAME, 2)) FROM EMP;

-- LTRIM : 왼쪽부터 원하는 문자 삭제
-- RTRIM : 오른쪽부터 원하는 문자 삭제
-- TRIM : 양쪽에서 원하는 문자 삭제
SELECT LTRIM('AABACD', 'A') FROM DUAL; // 왼쪽의 A 전부 삭제 (다른 문자/공백이 나올 때까지) --> BACD
SELECT RTRIM('AABACDAA', 'A') FROM DUAL; // 오른쪽의 A 전부 삭제 (다른 문자가 나올 때까지) --> AABACD
SELECT LTRIM('AABACDAA', 'AB') FROM DUAL; // 왼쪽부터 A or B이면 삭제 --> CDAA
SELECT LTRIM(' ABACDA', ' ') FROM DUAL; // 공백 삭제 (DEFAULT)

SELECT TRIM('A' FROM ' ABACDA') FROM DUAL; // 양쪽 삭제, 삭제할 문자 먼저 작성!
SELECT TRIM(' ABACDA ') FROM DUAL; // 양쪽 삭제, 삭제할 문자 먼저 작성!

숫자형 함수

숫자를 연산하는 함수
`ABS`, `SIGN`, `MOD`, `CEIL`, `FLOOR`, `ROUND`, `TRUNC`

-- 숫자형 함수

-- ABS 절댓값 함수
SELECT 10, -10, ABS(-10), ABS(-9.5) FROM DUAL; -- 10 -10 10 9.5

-- SIGN 부호 함수: 양수이면 1, 음수이면 -1, 0은 0
SELECT SIGN(10), SIGN(-10), SIGN(-550.2), SIGN(50.8), SIGN(0) FROM DUAL;

-- 실습: 급여에서 2000을 뺀 절댓값
SELECT ENAME, SAL, ABS(SAL - 2000), SIGN(SAL - 2000) FROM EMP;

-- MOD 나머지 함수
SELECT MOD(10, 3) FROM DUAL; -- 10/3의 나머지 값을 반환
 
 
-- 버림, 올림, 반올림
-- FLOOR, CEIL, ROUND, TRUNC

-- FLOOR
-- 소수 첫째자리에서 버림
-- 해당 숫자보다 작은 정수 중에 가장 큰 정수
SELECT FLOOR(10.3), FLOOR(11.35), FLOOR(10.8), FLOOR(12.8) FROM DUAL;

-- CEIL
-- 소수 첫째자리에서 올림
-- 해당 숫자보다 큰 정수 중에 가장 작은 정수
SELECT CEIL(10.3), CEIL(11.35), CEIL(10.8), CEIL(12.8) FROM DUAL;

-- ROUND
-- 원하는 자리에서 반올림
SELECT ROUND(12.876), ROUND(12.345) FROM DUAL;
-- 소수 첫째자리까지 표시 --> 둘째자리에서 반올림
SELECT ROUND(12.876, 1), ROUND(12.345, 1) FROM DUAL; -- 12.9 12.3
-- 마이너스 --> 정수부분 표시
SELECT ROUND(12.876, -1), ROUND(12.345, -1) FROM DUAL; -- 10 10

-- TRUNC
-- 선택적 버림 (특정 숫자 선택)
SELECT TRUNC(12.876), TRUNC(12.876, 1), TRUNC(12.876, 2) FROM DUAL; -- 12 12.8 12.87


-- 실습
-- 1. SAL 1500 이상의 사원의 SAL을 15% 인상. 단 소수점 이하는 버린다.
SELECT FLOOR(SAL * 1.15) FROM EMP WHERE SAL >= 1500;

-- 2. SAL이 2000 이하인 사원들의 SAL을 20% 인상. 단 10의 자리에서 반올림
SELECT ENAME, SAL, ROUND(SAL * 1.2, -2) FROM EMP WHERE SAL >= 2000;

날짜형 함수

DATE 타입의 값을 연산하는 함수
`SYSDATE`, `EXTRACT`, `YEAR|MONTH|DAY`, 

-- 날짜형 함수

SELECT SYSDATE FROM DUAL; -- 오늘 날짜
SELECT SYSDATE-1, SYSDATE, SYSDATE+1 FROM DUAL; -- 어제 오늘 내일
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE) FROM DUAL; -- 연도만 뽑아내기
SELECT SYSDATE, EXTRACT(MONTH FROM SYSDATE) FROM DUAL; -- 월만 뽑아내기
SELECT SYSDATE, EXTRACT(DAY FROM SYSDATE) FROM DUAL; -- 일만 뽑아내기

-- 실습: 입사년도가 1981년인 사원 중 SAL이 1500 이상인 사원의 EMPNO, ENAME, SAL, HIREDATE
SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP
WHERE SAL >= 1500 AND
HIREDATE BETWEEN '81.01.01' AND '81.12.31';

SELECT EMPNO, ENAME, SAL, HIREDATE FROM EMP
WHERE SAL >= 1500 AND
EXTRACT(YEAR FROM HIREDATE)='1981';

-- 실습: 각 사원들의 입사년도만 보기
SELECT ENAME, EXTRACT(YEAR FROM HIREDATE) FROM EMP;

형변환 함수

데이터 타입을 변형시키는 함수
`TO_NUMBER`, `TO_CHAR`, `TO_DATE`

-- 형변환 함수

-- 자동 형변환 (암시적 형변환)
SELECT 10 + 10 FROM DUAL;
SELECT 10 + '10' FROM DUAL;
SELECT '10' + '10' FROM DUAL;

-- 강제형 변환 (명시적 형변환)
SELECT 10 + A FROM DUAL; -- error
SELECT 10 + ASCII('A') FROM DUAL;
SELECT 10 + TO_NUMBER('10') FROM DUAL;

SELECT TO_CHAR(10) + 10 FROM DUAL; -- 강제 형변환 --> 자동 형변환

-- 1234에서 23출력
SELECT SUBSTR('1234', 2, 2) FROM DUAL; -- 2번째 문자부터 2개
SELECT SUBSTR(1234, 2, 2) FROM DUAL; -- 자동 형변환으로 에러 없이 동작함

-- YYYYMMDD 형식에 맞게 문자로 형변환
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL; -- 년월일
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYYMMD') FROM DUAL; -- D 하나 -> 요일

-- ~~년 ~~월 ~~일 형태로 출력
SELECT SYSDATE,
EXTRACT(YEAR FROM SYSDATE) || '년 ' ||
EXTRACT(MONTH FROM SYSDATE) || '월 ' ||
EXTRACT(DAY FROM SYSDATE) || '일'
FROM DUAL;

NULL 관련 함수

NULL을 처리하는 함수
`NVL`, `NVL2`, `NULLIF`, `COALESCE`

-- NULL 관련 함수
-- NVL, NVL2, VULLIF, COALESCE

-- NVL(A, B) : A가 NULL이 아니면 A, NULL이면 B
-- NVL2(A, B, C) : A가 NULL이 아니면 B, NULL이면 C
SELECT SAL, COMM, SAL+COMM FROM EMP; --> NULL값이 포함된 경우 때문에 계산 결과가 NULL이 됨
SELECT SAL, COMM, SAL+NVL(COMM, 0) FROM EMP; --> NULL인 경우 NULL 대신 0으로 계산

-- NULLIF(A, B) --> A == B이면 NULL, A != B이면 A (같으면 NULL, 다르면 앞의 값)
SELECT NULLIF(3, 4), NULLIF(4, 4) FROM DUAL; -- 3 NULL

-- COALESCE(A, B, C, ...) : NULL이 아닌 값이 나오면 반환
SELECT COALESCE(3, 4, 2, 1, NULL) FROM DUAL; --> 3 (NULL이 아닌 제일 앞에 있는 값)
SELECT COALESCE(NULL, NULL, NULL) FROM DUAL; --> NULL이 아닌 값이 없으면 NULL
SELECT COALESCE(1) FROM DUAL; --> 에러! (인수는 최소 2개 이상)

 

CASE 함수, DECODE 함수

SQL 내부에서 IF문처럼 사용 가능한 표현식.
IF~THEN~ELSE 논리로 표현식을 작성하여 SQL 비교 연산 기능을 보완하는 역할을 한다.

-- CASE, DECODE 함수

-- CASE 함수 : 컬럼 값을 케이스 별로 나눠서 처리
-- SELECT 컬럼명1, ... ,    -- CASE문 앞에 콤마를 써줌에 유의 (CASE 결과로 새로운 '열'을 만든다는 뜻)
-- CASE 컬럼명
--   WHEN 기준값1 THEN 값1  -- 컬럼명에 해당하는 값이 기준값1과 같으면 값1을 써줌
--   WHEN 기준값2 THEN 값2  -- 컬럼명에 해당하는 값이 기준값2와 같으면 값2를 써줌
--   .....
--   <ELSE 값>  	-- 옵션
-- END          	-- CASE문 끝
-- FROM 테이블명;

-- 10: 영업1부, 20: 영업2부, 30: 영업3부
SELECT ENAME, DEPTNO,
CASE DEPTNO
    WHEN 10 THEN '영업1부'
    WHEN 20 THEN '영업2부'
    WHEN 30 THEN '영업3부'
END AS 부서  -- 결과 컬럼 이름 설정 (AS 생략 가능, 작은따옴표 X 큰 따옴표 O 따옴표 없이 O 숫자 쓰려면 큰 따옴표 같이)
FROM EMP;

-- JOB별 SAL 인상. 소수 첫째자리에서 반올림
-- CLERK. MANAGER : 10%
-- SALESMAN : 20%
-- PRESIDENT, ANALYST : 30%
SELECT * FROM EMP;
SELECT ENAME, JOB, SAL,
CASE JOB
    WHEN 'CLERK' THEN ROUND(SAL * 1.1)
    WHEN 'MANAGER' THEN ROUND(SAL * 1.1)
    WHEN 'SALESMAN' THEN ROUND(SAL * 1.2)
    WHEN 'PRESIDENT' THEN ROUND(SAL * 1.3)
    WHEN 'ANALYST' THEN ROUND(SAL * 1.3)
END INCREASED_SAL
FROM EMP;

-- 케이스 줄이기
SELECT ENAME, JOB, SAL,
CASE
    WHEN JOB = 'CLERK' OR JOB = 'MANAGER' THEN ROUND(SAL * 1.1)
    WHEN JOB = 'SALESMAN' THEN ROUND(SAL * 1.2)
    WHEN JOB = 'PRESIDENT' OR JOB = 'ANALYST' THEN ROUND(SAL * 1.3)
END INCREASED_SAL
FROM EMP;

-- DECODE : CASE문을 간결하게 표현
-- DECODE(표현식(컬럼명), 기준값1, 값1, 기준값2, 값2, ... <DEFAULT값>)
SELECT ENAME, DEPTNO,
DECODE(DEPTNO, 10, '영업1부', 20, '영업2부', '영업3부') DEPTNAME
FROM EMP;

-- 사원들의 급여별 등급 분류
-- 1500 미만: C
-- 3000 미만: B
-- 3000 이상: A
SELECT ENAME, SAL,
CASE
    WHEN SAL >= 3000 THEN 'A'
    WHEN SAL < 1500 THEN 'C'
    ELSE 'B'
END GRADE
FROM EMP;

-- ENAME이 S로 시작하면 S, A로 시작하면 A, 나머지는 X
SELECT ENAME,
CASE
    WHEN ENAME LIKE 'S%' THEN 'S'
    WHEN ENAME LIKE 'A%' THEN 'A'
    ELSE 'X'
END FNAME
FROM EMP;

SELECT ENAME,
DECODE(SUBSTR(ENAME, 1, 1), 'S', 'S', 'A', 'A', 'X') AS FNAME --> 첫 글자가 S면 S, A면 A, 아니면 X
FROM EMP;

 

GROUP BY, HAVING 절

집계함수(Aggregate Function)

다중행 그룹이 모여서 하나의 결과를 돌려주는 함수. `COUNT`, `SUM`, `AVG`, `MAX`, `MIN`
`GROUP BY` 절로 행들을 소그룹화 하여 각 그룹 별 결과를 낼 수 있으며, 집계함수는 SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다. SQL 실행 순서 기준으로 GROUP BY 이후에 집계함수가 실행되기 때문에 GROUP BY 절에는 집계 함수를 사용할 수 없다.

복습!
SQL 작성 순서⭐ : SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY
SQL 실행 순서⭐ : FROM / WHERE / GROUP BY / HAVING / SELECT / ORDER BY

* WHERE은 FROM 절에 대한 조건절, HAVING은 GROUP BY로 묶은 소그룹에 대한 조건절

집계함수는 `집계함수명([DISTINCT | ALL] 컬럼 | 표현식)`으로 작성되며, 출력 옵션 선택이 가능하다.
: `DISTINCT` →  중복 제거해서 결과 출력, `ALL` → default 값으로 집계 결과 전체를 그대로 출력

또한 집계함수에서는 NULL행이 무시되기 때문에 NULL이 섞여 있는 경우와 아닌 경우의 결과 값이 달라짐에 주의!
또한 SELECT절에 GROUP BY에 사용된 컬럼과 집계 함수만 사용할 수 있음에 주의!

-- 집계함수
-- COUNT, SUM, AVG, MAX, MIN

SELECT * FROM EMP;
SELECT COUNT(*) FROM EMP; --> 전체 행 개수
SELECT COUNT(ENAME), COUNT(SAL), COUNT(COMM), COUNT(DEPTNO) FROM EMP; --> 해당 컬럼에서 NULL 제외한 값의 개수

SELECT COUNT(*) FROM TEST4;
SELECT COUNT(A), COUNT(B) FROM TEST4;

-- COUNT로 행을 가져와서, 해당 행의 컬럼에 대해 집계함수 실행
SELECT COUNT(*), SUM(SAL), ROUND(AVG(SAL)), MAX(SAL), MIN(SAL)
FROM EMP
WHERE SAL >= 1500;

-- 10번 부서에서 근무하는 사원들의 사원수, 급여합계, 급여평균
-- SELECT에는 집계함수와 GROUP BY 표현식에 사용한 컬럼만 출력 가능
SELECT ENAME, EMPNO, COUNT(*), SUM(SAL), ROUND(AVG(SAL))
FROM EMP
WHERE DEPTNO = 10
group by ENAME, EMPNO;

SELECT DEPTNO, COUNT(*), SUM(SAL), ROUND(AVG(SAL))
FROM EMP
group by DEPTNO
ORDER BY deptno ASC; -- ASC: 오름차순(default), DESC: 내림차순

SELECT DEPTNO, COUNT(*), SUM(SAL), ROUND(AVG(SAL))
FROM EMP
WHERE SAL >= 1000 -- WHERE => FROM의 조건절 (테이블에서 가져올 때)
group by DEPTNO
HAVING SUM(SAL) <= 10000 -- HAVING => GROUP으로 묶고난 후의 조건절
ORDER BY deptno ASC;

-- JOB이 CLERK인 사원들에 대해서 각 부서별로 그룹화한 후,
-- 부서원 최소 SAL이 1000이하인 부서에서 사원들의 SAL 총합
-- DEPTNO 오름차순 정렬
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE JOB = 'CLERK'
GROUP BY DEPTNO
HAVING MIN(SAL) <= 1000
ORDER BY DEPTNO;

-- SAL가 1500 이상인 사원들에 대해 부서별 그룹화한 후,
-- 부서원의 최소 SAL가 2000 이상, 최대 SAL가 3000 이하인 경우
-- 부서에서 직원들의 평균 SAL 소수 첫째자리까지 표시
-- 평균 SAL 내림차순 정렬
SELECT DEPTNO, ROUND(AVG(SAL), 1)
FROM EMP
WHERE SAL >= 1500
GROUP BY DEPTNO
HAVING MIN(SAL) >= 2000 AND MAX(SAL) <= 3000
ORDER BY AVG(SAL) DESC;


CREATE TABLE TEST5
(
    A NUMBER(10),
    B NUMBER(10),
    C NUMBER(10),
    D NUMBER(10)
);
INSERT INTO TEST5 VALUES(10, 20, 30, 40);
INSERT INTO TEST5 VALUES(40, NULL, 50, 40);
INSERT INTO TEST5 VALUES(50, 10, NULL, NULL);
INSERT INTO TEST5 VALUES(NULL, 30, 30, 30);
SELECT * FROM TEST5;

-- 집계함수에서는 NULL행 자체가 무시됨
SELECT SUM(A) FROM TEST5;
SELECT ROUND(AVG(A), 1) FROM TEST5;

-- 아래 결과 다름(NULL이 섞여 있으면)
SELECT SUM(A) + SUM(B) FROM TEST5;
SELECT SUM(A+B) FROM TEST5;

 

ORDER BY 절

조회된 데이터를 특정 컬럼 기준으로 정렬하여 출력하는 절. SQL문 작성 순서도 마지막, 실행 순서도 마지막!
default로 `ASC` 오름차순 정렬, `DESC` 내림차순 정렬 옵션 선택 가능

-- ORDER BY 우선순위1, 우선순위2, ... [ASC|DESC];

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
ORDER BY DEPTNO, SAL;

-- 일단 FROM으로 다 가져왔으니 SELECT하지 않은 컬럼으로도 정렬 가능
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
ORDER BY HIDERATE;

-- GROUP BY로 묶은 상태이면 SELECT한 컬럼 값 OR 집계함수로만 정렬 가능
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY MAX(SAL) DESC;

-- SELECT에서 설정한 별칭으로 정렬 가능
SELECT DEPTNO D, SUM(SAL) S
FROM EMP
GROUP BY DEPTNO
ORDER BY D;

-- ORDER BY 1 -> SELECT의 첫번째 컬럼으로 정렬 / ORDER BY 2 -> 두번째 컬럼으로 정렬
SELECT DEPTNO D, SUM(SAL) S
FROM EMP
GROUP BY DEPTNO
ORDER BY 1; -- SELECT 목록 식의 수에 맞게 작성(0은 안 됨, 1부터)

-- 여러가지 방법을 섞어서도 정렬 기준 컬럼 선택 가능
SELECT EMPNO EM, ENAME EN, MGR M, SAL S, DEPTNO D
FROM EMP
ORDER BY D, 4 DESC, EMPNO; // DEPTNO -> SAL, EMPNO