SQL 문자, 숫자, 날짜, COUNT, CASE WHEN, 데이터의 그룹화

SELECT 그룹화할 열 이름1, 집계 함수
FROM 테이블명
WHERE 조건절
GROUP BY 열 이름1
HAVING 집계 함수 조건
ORDER BY 열 이름;

--  '특정한 테이블에서 특정한 조건을 만족하는 데이터를 추출한 후 특정한 조건을 만족한
--   그룹화된 특정 열 및 집계 함수를 나타내라'는 뜻이다.

---

문자 함수

함수	설명	함수	설명
LOWER	모든 문자를 소문자로 변환	RTRIM	문자열 오른쪽 공백을 잘라낸다.
UPPER	모든 문자를 대문자로 변환	LTRIM	문자열 왼쪽 공백을 잘라낸다.
INITCAP	첫글자만 대문자,나머지 소문자	TRIM	문자열의 양쪽을 잘라낸다.
LENGTH	문자의 길이를 나타낸다.	REPLACE	특정 문자열을 다른 문자열로 대체한다.
SUBSTR	문자 값 중 원하는 길이만큼만 나타냄.	COALESCE	조건에 따라 여러가지 값으로 치환한다.
CONCAT	두 문자열을 연결한다( ||연사자와 같다 )	LPAD	왼쪽부터 특정 문자로 자리를 채운다.
INSTR	명명된 문자의 위치를 구한다.	RPAD	오른쪽부터 특정 문자로 자리를 채운다.

 

ex) STU_INFO테이블을 사용하여 이름을 소문자로 변환하고 NAME_SMALL이라는 별칭을 설정

SELECT ID,NAME, LOWER(NAME) AS NAME_SMALL FROM STU_INFO

--LOWER(열 이름) 말고도 LOWER('ABCD')형태로 직접 데이터 값을 넣어도 된다.

 

 

ex)STU_INFO테이블을 사용하여 이름을 대문자로 변환하고 NAME_CAP이라는 별칭을 설정

SELECT ID, NAME, UPPER(NAME) AS NAME_CAP FROM STU_INFO

 

 

ex)STU_INFO테이블을 사용하여 이름 왼쪽에 'F'가 나오면 삭제, 오른쪽부터 'T'문자를 만나면 삭제 후 NAME_T라는 별칭 설정

SELECT ID, NAME, LTRIM(name,'F') AS NAME_LT,
                 RTRIM(name,'T') AS NAME_RT
FROM stu_info;

만약 삭제할 문자를 지정해 주지 않으면 공백을 제거한다.

 

 

 

ex)STU_INFO테이블을 사용하여 이름의 자릿수를 세고 NAME_CNT라는 별칭을 설정

SELECT ID, NAME, LENGTH(NAME) AS NAME_CNT FROM STU_INFO

 

 

ex)STU_INFO테이블을 사용하여 이름의 두 번째에서 네 번째에 위치한 글자를 나타내고 NAME_STR이라는 별칭을 설정

--SUBSTR('문자열' or 열 이름, 시작 위치, 길이)
SELECT ID,NAME,SUBSTR(NAME,2,3) AS NAME_STR FROM STU_INFO;

 

 

ex)그 외

--INSTR
INSTR('ABCD','D')   --4

--LPAD
LPAD('ABCD', 6, '*')  --**ABCD   --가운데는 적용후 만들어질 자릿수다

--RPAD
RPAD('ABCD', 6, '*')  --ABCD**

--REPLACE
--REPLACE('문자열' or 열 이름, '바꾸려는 문자열', '바뀔 문자열')
SELECT job_id, REPLACE(job_id, 'ACCOUNT', 'ACCNT')
--하면 job_id 안에 'ACCOUNT'문자열이 'ACCNT'로 바뀐다.

---

숫자 함수

함수	설명	함수	설명
ROUND	소수점의 자릿수를 지정하여 반올림함	SQRT	제곱근을 나타낸다.
TRUNC	해당 소수점 자리에서 잘라낼 때 사용	POWER	거듭제곱을 출력한다.
MOD(M,N)	M을 N으로 나눈 나머지를 나타낸다.	SIN	지정한 각도의 SIN값을 나타낸다.
ABS	값을 절대값으로 변환할 때 사용한다.	TAN	지정한 각도의 TAN값을 나타낸다.
SIGN	양수면 1, 음수면 -1, 0이면 0 출력한다.	COS	지정한 각도의 COS값을 나타낸다.
CEIL	숫자를 정수로 올림한다.	PI	지정한 각도의 파이값을 나타낸다.
FLOOR	숫자를 정수로 내림한다.

ex) PROD_SALES테이블을 사용하여 소수점 둘째 자리에서 반올림한 제품 판매금액을 계산한 후 별칭을 SALES_REV라고 설정

SELECT PROD_ID, ROUND(TOTAL_SALES,1) AS SALES_REV FROM PROD_SALES;
--만약 소수점 첫째 자리에서 반올림하려면 ROUND(TOTAL_SALES,0)
--만약 정수 첫째 자리에서 반올림하려면   ROUND(TOTAL_SALES,-1)
--  1  2  3  .  4  5  6  7
-- -3 -2 -1     0  1  2  3  ...

 

 

ex) PROD_SALES테이블을 사용하여 제품 판매금액을 제품 판매수량으로 나눈 나머지를 구한 후 별칭은 SALES_BALANCE라고 해보자

SELECT PROD_ID, MOD(TOTAL_SALES,SALES_NUM) AS SALES_BALANCE FROM PROD_SALES;

 

 

ex)PRO_SALES테이블을 사용하여 경제적 이익의 절댓값을 나타내고 별칭은 PRFT라고 하여 나타내라

SELECT PROD_ID, ABS(ECON_INCOME) AS PREF FROM PROD_SALES;

 

 

ex) 그 외

--TRUNC
--만약 salary 값이 566.66667일 때
SELECT salary 일급
       TRUNC(salary,0) 적용결과 0
       TRUNC(salary,1) 적용결과 1
       TRUNC(salary,-1) 적용결과 minus1
FROM employees;

--결과
--      일급 | 적용결과0 | 적용결과1 | 적용결과minus1
-- 566.66667 |    566    |   566.6   |     560

---

날짜 함수

DBMS마다 날짜를 활용한 기능은 다양하고, 그 종류가 매우 많다. 이러한 기능들을 정확히 알려면 각 DBMS의 설명서를 참고하는 것이 좋다.

 

함수	설명	비고
ADD_MONTHS	지정한 날짜에 개월 수를 더한 값을 출력한다.
SYSDATE	현재 시스템의 날짜 데이터를 반환한다.	ACCESS는 NOW( )를 사용하며 ORACLE은 SYSDATE를, SQL SERVER와 SYBASE는 GATEDATE( )를 사용한다.
NEXT_DAY	명시된 날짜부터 돌아오는 요일에 대한 날짜를 출력한다.(SUNDAY:1, MONDAY:2, ...)	ORACLE에서 제공한다.
LAST_DAY	해당 월의 마지막 날짜를 반환한다.	ORACLE에서 제공한다.
MONTH_BETWEEN	지정된 월과 월간의 개월 수를 반환한다. MONTH_BETWEEN(date_1,date_2)은 date_1과 date_2 사이의 기간을 월로 나타낸다. (한달 이내거나 정확한 달로 맞아 떨어지지 않을 경우는 소수점으로 표시한다.)	ORACLE에서 제공한다.

ex) CLERK테이블을 사용하여 직원들 생일에 한 달을 더해서 나타내고 별칭을 BIRTH_P1이라고 설정하면

--  ADD_MONTHS(열 이름, 추가할 달),  ADD_MONTHS(date,integer)는 지정된 날짜의 월에
--  일정한 정수값을 더한 날짜를 반화한다.
SELECT ID, BIRTH_DT, ADD_MONTHS(BIRTH_DT,1) AS BIRTH_P1 FROM CLERK;

 

 

 

※ORACLE

ORACLE에서 날짜의 연산 규칙은 다음과 같다.

-날짜와 숫자를 더하거나 빼면 날짜 결과를 출력한다.

-날짜에서 날짜를 빼면 두 날짜 사이의 일수를 출력한다.

-날짜에 시간을 더하거나 빼려면 시간을 24로 나누어서 더하거나 뺀다.

EX)

SYSDATE는 ORACLE 데이터베이스 시스템이 설치되어 있는 시스템의 현재 날짜와 시간을 반환하는 함수이다.

한글판 오라클 익스프레스는 기본으로 년(YY)/월(MM)/일(DD) 형ㅎ태로 출력한다.

SELECT TO_CHAR(SYSDATE,'YY/MM/DD/HH24:MI') 오늘날짜,
       SYSDATE + 1 더하기1,
       SYSDATE - 1 빼기1,
       TO_DATE('20171202') - TO_DATE('20171201') 날짜빼기,
       SYSDATE + 13/24 시간더하기
FROM   DUAL;

--결과
--     오늘날짜    |   더하기1   |     빼기1   |  날짜빼기 |  시간더하기
--  19/11/23/11:00 |    19/11/24 |    19/11/22 |          1|    19/11/24

위에서 사용한 TO_CHAR는 문자 변환 함수이다. SYSDATE의 시간까지 출력하기 위해 사용되었다.

 

SQL문을 조작할 때 오라클 데이터베이스 시스템은 특정한 경우에 데이터 타입을 자동으로 변환한다.

FROM	TO
VARCHAR2 혹은 CHAR	NUMBER(숫자)
VARCHAR2 혹은 CHAR	DATE(날짜)
NUMBER	VARCHAR2(문자)
DATE	VARCHAR2(문자)

ex)

SELECT 1 + '2' FROM DUAL;

실행결과: 3

오라클 데이터베이스 시스템이 계산을 위해 문자'2'를 자동으로 숫자 2로 변환하여 계산하였다. 하지만 자동 데이터 타입 변환을 이용할 수 있더라도 SQL문의 성능과 안정성을 위해 수동 데이터 타입 변환으로 수행한 것이 좋다.

 

수동 데이터 타입 변환

함수	설명
TO_CHAR	숫자, 문자, 날짜 값을 지정 형식의 VARCHAR2타입으로 변환한다.
TO_NUMBER	문자를 숫자 타입으로 변환한다.
TO_DATE	날짜를 나타내는 문자열을 지정 형식의 날짜 타입으로 변환한다.

자세한건 여기에 안적으니 알아서 찾아보기

---

COUNT함수

함수	설명	비고
COUNT	행의 수를 나타낸다.	NULL값을 포함한 전체 행의 수 : COUNT(*) NULL값을 제외한 전체 행의 수 : COUNT(열 이름) 중복을 제외한 행의 수 : COUNT(DISTINCT 열 이름)
SUM	행의 합계를 나타낸다.	전체 합계 : SUM(열 이름)
AVG	행의 평균을 나타낸다.	평균 : AVG(열 이름)
MAX	행의 최댓값을 나타낸다.	최댓값 : MAX(열 이름)
MIN	행의 최솟값을 나타낸다.	최솟값 : MIN(열 이름)
STDENV	행의 표준편차를 나타낸다.	표준편차 : STDENV(열 이름)
VARIANCE	행의 분산을 나타낸다.	분산 : VARIANCE(열 이름)

.--COUNT 함수는 데이터의 검증용으로도 많이 사용된다.
SELECT COUNT(*) AS ENG_CNT FROM STU_SCORE;
SELECT COUNT(DISTINCT ENG_SCORE) AS ENG_CNT FROM STU_SCORE;


--SUM
SELECT SUM(MATH_SCORE) AS MATH_TOTAL FROM STU_SCORE;


--AVG
SELECT AVG(MUSIC_SCORE) AS MUSIC_AVG FROM STR_SCORE;     --MUSIC_AVG : 89.83
--하지만 NULL값을 가진 열은 계산에서 생략되어 평균값이 잘못될 수 있으므로 COALESCE함수를 사용한다.
SELECT AVG(COALESCE(MUSIC_SCORE,0)) AS MUSIC_AVG FROM STU_SCORE;


--MAX , MIN
SELECT MAX(MATH_SCORE) AS MAX_SCORE, MIN(MATH_SCORE) AS MIN_SCORE FROM STU_SCORE;

-집게 함수를 사용하면 NULL값은 계산에서 무시된다

 (ex) 위에서 AVG함수 예시를 보면 알 수 있다. 이 때 COALESCE함수를 이용하여 치환하면된다.)

-별칭을 지정할 때 테이블에 존재하는 열 이름이 아닌 새로운 이름으로 지정하는 것이 좋다.

-숫자형 데이터를 분석할 때, SUM, AVG, MAX, MIN 값을 사용하여 데이터를 검증하는 것은 중요하다.

---

데이터 값에 순위 매기기 (RANK, DENSE_RANK, ROW_NUMBER)

함수	설명	순위 예
RANK OVER( )	공통 순위를 출력하되 공통 순위만큼 건너뛰어 다음 순위를 출력한다.	1,2,2,4, ...
DENSE_RANK OVER( )	공통 순위를 출력하되 건너뛰지 않고 바로 다음 순위를 출력한다.	1,2,2,3, ...
ROW_NUMBER OVER( )	공통 순위를 업이 출력한다.	1,2,3,4
NTILE(숫자) OVER( )	지정된 숫자만큼 집단으로 나누어서 출력한다.	ex) 조건대로 정렬 후 숫자만큼 분할하여 표시

뒤에 OVER( )을 하여 안에 넣어야한다.

RANK() OVER([PARTITION BY 열 이름] ORDER BY 열 이름)

--EX) employees 테이블의 salary값이 높은 순서대로 순위를 매겨 출력해보기
SELECT employee_id,
       salary,
       RANK() OVER(ORDER BY salary DESC) RANK_급여
FROM emplyees;

--EX) employees테이블 직원이 속한 department_id안에서 salary값이 높은 순서대로 순위를 매겨 출력
SELECT department_id,salary,
       DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) DENSE_RANK 급여
FROM emplyees
ORDER BY department_id;

--EX) 키 큰 순으로 정렬한 후 위에서 부터 두 개의 그룹으로 분할하기
SELECT NTILE(2) OVER(ORDER BY height DESC) "반번호", userName,addr,hiehgt
FROM userTBL;

PARTITION BY 열 이름 : 그룹으로 묶어서 순위를 매겨야 할 때 사용

ORDER BY 열 이름 : 순위를 매길 열

 

밑에 예시를 보면 PARTITION BY절을 사용해여 전체가 아닌 department_id, 즉 부서별로 그룹화한 후 salary값이 높은 순(내림차순)으로 순위를 매긴 것을 볼 수 있다.

---

CASE WHEN 문장

CASE WHEN 문장

SELECT 열이름,
       CASE WHEN [조건1] THEN [결과값1]
            WHEN [조건2] THEN [결과값2]
            ELSE [결과값3] END AS 새로운 열 이름
FROM 테이블명;

ex) 직업이 OFFICER 이면서 영어 점수가 80점 이상일 때 5%인상 80점 미만일 때 4% 인상

     직업이 MANAGER 이면서 영어 점수가 80점 이상일 때 3%인상 80점 미만일 때 2%인상으로 연봉이 조정될 경우 필요한 추가 예산은 얼마인가?

SELECT (SUM(CASE WHEN JOB = 'OFFICER' AND ENG_SCORE >= 80
                 THEN CURRENT_SAL*1.05
                 WHEN JOB = 'OFFICER' AND ENG_SCORE < 80
                 THEN CURRENT_SAL*1.04
                 WHEN JOB = 'MANAGER' AND ENG_SCORE >= 80
                 THEN CURRENT_SAL*1.03
                 WHEN JOB = 'MANAGER' AND ENG_SCORE < 80
                 THEN CURRENT_SAL*1.02
                 ELSE CURRENT_SAL 
           END)-SUM(CURRENT_SAL)) AS ADD_BUDGET
FROM STAFF_SAL;

 

DECODE함수

물론 DBMS특성에 따라 지원이 안 되는 경우도 있다.

DECODE (열 이름, 조건 값, 치환 값, 기본값)

--치환 값: 조건 값에 해당할 경우 출력 값
--기본값: 조건 값에 해당하지 않을 경우 출력 값

--EX) DECODE( gender, 'M' , '남성', '여성' )

---

데이터의 그룹화( GROUP BY )

그룹화하면 데이터를 논리적 집합으로 나누어서 데이터의 특성을 요약할 수 있다. 

 

 

1.열 이름으로 그룹화

SELECT 그룹화할 열 이름1, 그룹화할 열 이름2, 집계 함수
FROM 테이블명
WHERE 조건절
GROUP BY 열 이름1, 열 이름2;

                                                    집계 함수를 제외한 SELECT문의 모든 열은 GROUP BY절에 있어야한다.

2.열 위치로 그룹화

SELECT 그룹화할 열 이름1, 그룹화할 열 이름2, 집계 함수
FROM 테이블명
WHERE 조건절
GROUP BY 1,2;

 

 

ex)

	ENG	MATH	GENDER
김필호	28	49	남자
이치훈	68	98	남자
김영숙	87	87	여자
성은영	65	78	여자

 

1.집계함수만 사용한 경우

SELECT AVG(ENG) AS ENG_SCORE,
       AVG(MATH) AS MATH_SCORE
FROM CLASS_SCORE;


-- 결과

-- ENG_SCORE | MATH_SCORE
--     62    |     78

 

2.GROUP BY 사용한 경우

SELECT GENDER,
       AVG(ENG) AS ENG_SCORE
       AVG(MATH) AS MATH_SCORE
FROM CLASS_SCORE
GROUP BY GENDER;

--결과

-- GENDER | ENG_SCORE | MATH_SCORE
--  남자  |     48    |    73.5
--  여자  |     76    |    82.5

 

추가

1.원하는 열을 GROUP BY절에 포함할 수 있다.

GROUP BY절에 포함되는 열이 한 개씩 증가할수록 차원이 한 개씩 증가하고 좀 더 세부적으로 데이터를 분석할 수 있다.

 

2.GROUP BY절은 WHERE 조건절 뒤에 위치하고, ORDER BY절 앞에 위치한다.

즉, ORDER BY절은 항상 문장의 마지막에 위치한다.

--예시
SELECT SEG, COUNT(*) AS CNT, SUM(CARD_FLG) AS CARD_FLG
FROM PPC_201312
GROUP BY SEG
ORDER BY SEG ;

 

3.그룹화될 열에 NULL갑을 포함할 경우 NULL값도 그룹화된다.

NULL값을 여러개 포함하고 있을 경우 하나의 NULL값으로 그룹화된다.

---

그룹화된 데이터의 필터링

SELECT 그룹화할 열 이름1, 집계 함수
FROM 테이블명
WHERE 조건절
GROUP BY 열 이름1
HAVING 집계 함수 조건
ORDER BY 열 이름;

--  '특정한 테이블에서 특정한 조건을 만족하는 데이터를 추출한 후 특정한 조건을 만족한
--   그룹화된 특정 열 및 집계 함수를 나타내라'는 뜻이다.

HAVING은 WHERE조건절과 마찬가지로 조건을 주는 역할을 한다. 차이는 HAVING은 그룹화된 변수에 대해 조건을 준다는 점이다.

 

 

EX) 고객구매 테이블 : PROD_SALES

CUST_NM	PRD_ID	SALES_AMT	CUST_NM	PRD_ID	SALES_AMT
LEE	546	3000	KIM	324	4780
KANG	564	87900	KWON	556	45478
KIM	254	3000	YOO	567	78900
PARK	877	98787	LEE	890	10000
KIM	787	2341	PARK	566	50000

 

1.구매횟수가 두 번 이상인 고객에서  마케팅을 하려한다. 마케팅 대상자는 누구인가?

SELECT CUST_NM, COUNT(*) AS CNT
FROM PRO_SALES
GROUP BY CUST_NM
HAVING COUNT(*)>1;

--CUST_NM | CNT
--  KIM   |  3
--  LEE   |  2
-- PARK   |  2

 

2. 구매금액의 합이 7만원 이상인 고객에서 마케팅을 하려고한다. 마케팅 대상자는 누구인가?

SELECT CUST_NM, SUM(SALES_AMT) AS SALES_AMT
FROM PROD_SALES
GROUP BY CUST_NM
HAVING SUM(SALES_AMT)>=70000

-- CUST_NM | SALES_AMT
--  KANG   |  87900
--  PARK   |  139787
--  YOO    |  78900

 

추가

1.그룹화 필터링 후 데이터를 정렬할 때 ORDER BY절을 사용할 수 있다.

정렬하는 ORDER BY절은 항상 문자의 마지막에 사용되므로 GROUP BY 뒤에 HAVING이 오고 그 뒤 마지막에 ORDER BY절이 옴을 알 수 있다.

 

2.WHERE 조건절의 조건은 데이터가 그룹화되기 전에 필터링하고, HAVING절의 조건은 데이터가 그룹화된 후에 필터링한다.

WHERE조건절에 의해 제외된 행은 그룹화할 때도 제외되기 때문에 HAVING절의 고려 대상이 아니다. 즉, WHERE조건절에 의해 1차 피터링된 대상을 그룹화하여 HAVING절이 2차 필터링하는 것이다. 다음 문장을 실행하면 데이터 필터링 순서는 다음과 같다.

SELECT CUST_NM, SUM(SALES_AMT) AS SALES_TOTAL
FROM PROD_SALES
WHERE SALES_AMT >= 50000
GROUP BY CUST_NM
HAVING SUM(SALES_AMT) >= 100000;

--1단계 WHERE 조건절 데이터 필터링
--CUST_NM | PRD_ID | SALES_AMT
-- KANG   |  564   |  87900
--  YOO   |  567   |  78900
-- PARK   |  877   |  89787
-- PARK   |  566   |  50000

--2단계 HAVING절 조건 필터링
--CUST_NM | SALES_TOTAL
--  PARK  |  139787