251231 [ Day 3 ] - SQL (2)

TaeHyun·2025년 12월 31일

sql study

TIL

목록 보기

125/182

시작하며

오늘은 SQL을 마무리하고 여러 실습 문제를 풀어보았다.

집계 함수

데이터를 요약 → 데이터의 특징을 알 수 있음 (EDA)

COUNT(필드명) : 결측이 아닌 개수 반환
SUM(필드명) : 결측이 아닌 값의 합계 반환
AVG(필드명) : 결측이 아닌 값의 평균 반환
MAX(필드명) : 최댓값 반환
MIN(필드명) : 최솟값 반환
VARIANCE(필드명) : 분산 반환
- SQLite에서는 지원 X
STDEV(필드명, 구분자) : 표준편차 반환
- SQLite에서는 지원 X

SELECT sepal_length
	, COUNT(*) AS cnt
    , SUM(sepal_length) AS sum
    , AVG(sepal_length) AS avg
    , AVG(POWER(sepal_length, 2)) - POWER(AVG(sepal_length), 2) AS var
    , SQRT(AVG(POWER(sepal_length, 2)) - POWER(AVG(sepal_length), 2)) AS std
FROM IRIS
GROUP BY species;

날짜 시간 함수

날짜 시간 : 실수형(연산 가능)

DATE(필드명, 조정자) : 날짜를 YYYY-MM-DD 형태의 문자열로 반환
- 인수를 지정하지 않을 시 협정 표준시 날짜를 반환
- 조정자 크기를 날짜에 더함
TIME(필드명, 조정자) : 시간을 HH:MM:SS 형태의 문자열로 반환
- 인수를 지정하지 않을 시 협정 표준시 날짜를 반환
- 조정자 크기를 시간에 더함
DATETIME(필드명, 조정자) : 날짜시간을 YYYY-MM-DD HH:MM:SS 형태의 문자열로 반환
- 인수를 지정하지 않을 시 현재 날짜 시간을 협정표준시로 반환
STRFTIME(포맷, 필드명) : 날짜시간 필드를 포맷에 해당하는 문자열로 반환
JULIANDAY(필드명) : 율리우스일 반환

조정자	내용
`(-)N year`	날짜를 N년 미래 또는 -N년 과거로 조정
`(-)N month`	날짜를 N월 미래 또는 -N월 과거로 조정
`(-)N day`	날짜를 N일 미래 또는 -N일 과거로 조정
`(-)N hour`	날짜를 N시 미래 또는 -N시 과거로 조정
`(-)N minute`	날짜를 N분 미래 또는 -N분 과거로 조정
`(-)N second`	날짜를 N초 미래 또는 -N초 과거로 조정
`start of month`	날짜를 월초로 조정 `start of year` , `start of day` 도 있음
`weekday N`	날짜를 다음주 N번째 요일로 조정(0 : 일요일)
`localtime`	현지 시간의 날짜와 시간 반환

포맷	내용	예시	포맷	내용	예시
%Y	연도(4자리)	2025	%y	연도(2자리)	25
%m	월(정수, 01~12)	12	%M	분(정수, 00~59)	02
%d	일(정수, 01~31)	31	%S	초(정수, 00~59)	03
%j	연중 일의 순번(001~366)	365	%f	천 분의 1초	03.456
%H	시(24시간, 00~23)	15	%s	유닉스 시간	1703984523
%I	시(12시간, 01~12)	01	%W	연중 주의 순번(00~52)	52
%p	AM/PM 표기	AM	%w	주중 요일의 순번(0~6)	0(일요일)

SELECT DATE('now', 'localtime');
SELECT TIME('now', 'loctime');
SELECT DATETIME('now', 'localtime');

SELECT * FROM DT;

SELECT date, DATE(date) FROM DT;
SELECT date, TIME(date) FROM DT;
SELECT date, DATE(date), TIME(date) FROM DT;

SELECT date, DATE(date, '1 year') FROM DT;
SELECT date, DATE(date, '1 month', '1 day') FROM DT;
SELECT date, DATE(date, 'start of month') FROM DT;

SELECT date, TIME(date, '1 hour') FROM DT;
SELECT date, TIME(date, '1 minute', '1 second') FROM DT;
SELECT date, TIME(date, 'start of day', '-1 second') FROM DT;

SELECT date, STRFTIME('%Y년 %m월 %d일', date) FROM DT;
SELECT date, STRFTIME('%H시 %M분 %S초', date) FROM DT;
SELECT date, STRFTIME('%I:%M:%S %p', date) FROM DT;

SELECT STRFTIME('%s', 'now');
SELECT STRFTIME('%s', date) FROM DT;
SELECT STRFTIME('%s', 'now') - STRFTIME('%s', date) FROM DT;

SELECT (STRFTIME('%s', 'now') - STRFTIME('%s', '2000-01-01')) / 86400;

SELECT JULIANDAY('now') - JULIANDAY(date) FROM DT;
SELECT FLOOR(JULIANDAY('now') - JULIANDAY('2000-01-01'));

SELECT STRFTIME('%w', 'now');
SELECT STRFTIME('%w', '2000-01-01');

병합

병합할 때 기준 열을 외래키(Foreign Key)라고 함
병합 하기전 확인 사항
- 두 외래키에 서로 일치하는 원소가 있는지
- 오른쪽 외래키에 중복된 원소가 있는지
M:N 관계라면 둘 중 하나에서 중복을 제거하거나 그룹으로 요약하는 등의 전처리 필요

병합의 종류

내부 병합(Inner Join) : 감소 가능
- 결측값 처리 필요없음
- 행이 크게 줄어들면 문제가 생길 가능성 높음
전체 외부 병합(Full Outer Join) : 증가 가능
왼쪽 외부 병합(Left Outer Join) : 행 고정
오른쪽 외부 병합
교차 병합

중복 제거

SELECT species FROM IRIS;
SELECT DISTINCT species FROM IRIS;
SELECT species FROM IRIS GROUP BY species;

내부 병합

FROM 절에 왼쪽 테이블 지정
INNER JOIN 절에 오른쪽 테이블을 지정
ON 절에 두 테이블의 외래키를 등호로 연결

SELECT *
FROM HR_MEMBER
INNER JOIN HR_OFFICE
ON HR_MEMBER.직원명 = HR_OFFICE.직원명;

일반적인 패턴(별칭 지정)

SELECT A.*, B.내선, B.부서
FROM HR_MEMBER AS A
INNER JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명;

세 개의 테이블 병합

SELECT A.*, B.내선, B.부서, C.나이, C.혈액형, C.동아리
FROM HR_MEMBER AS A
INNER JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명
INNER JOIN HR_PERSON AS C
ON B.직원명 = C.이름;

전체 외부 병합

SELECT A.*, B.내선, B.부서
FROM HR_MEMBER AS A
FULL OUTER JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명;

전체 외부 병합에서 교집합 제외

SELECT A.*, B.내선, B.부서
FROM HR_MEMBER AS A
FULL OUTER JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명
WHERE A.직원명 ISNULL or B.직원명 ISNULL;

왼쪽 외부 병합

SELECT A.*, B.내선, B.부서
FROM HR_MEMBER AS A
LEFT OUTER JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명;

-- 오른쪽 외부 병합
SELECT A.*, B.내선, B.부서
FROM HR_MEMBER AS A
RIGHT OUTER JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명;

왼쪽 외부 병합에서 교집합 제외

SELECT A.*, B.내선, B.부서
FROM HR_MEMBER AS A
LEFT OUTER JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명
WHERE B.직원명 ISNULL;

외래키 2개 이상 지정(현업에서 많이 사용되는 패턴)

SELECT A.*, C.혈액형, C.동아리
FROM HR_MEMBER AS A
LEFT OUTER JOIN HR_PERSON AS C
ON A.직원명 = C.이름 AND A.나이 = C.나이;

서브 쿼리

메인쿼리 안에 추가한 쿼리
- 메인쿼리의 SELECT, FROM, WHERE 절 등에 서브쿼리를 추가할 수 있음
- 파생변수를 미리 생성한 테이블을 병합할 때 유용
여러 번 실행해야 하는 쿼리를 한 번의 쿼리로 대신하므로 복잡할 수 있지만 원하는 결과를 얻을 수 있다는 장점이 있음

SELECT *
FROM HR_PERSON
WHERE 혈액형 = 'O';

SELECT *
FROM (SELECT * FROM HR_PERSON WHERE 혈액형 = 'O');

SELECT *
FROM HR_MEMBER
WHERE 직원명 IN (SELECT 이름 FROM HR_PERSON WHERE 혈액형 = 'O');

SELECT *
FROM HR_MEMBER
WHERE 직원명 IN (SELECT 이름 FROM HR_PERSON WHERE 혈액형 = 'A');

SELECT A.*, B.내선, B.부서, CONCAT(B.내선, '-', B.부서) AS 조직
FROM HR_MEMBER AS A
LEFT JOIN HR_OFFICE AS B
ON A.직원명 = B.직원명;

SELECT A.*, B.조직
FROM HR_MEMBER AS A
LEFT JOIN (SELECT *, CONCAT(부서, '-', 내선) AS 조직 FROM HR_OFFICE) AS B
ON A.직원명 = B.직원명;

뷰

가상 테이블
데이터베이스 개체이지만 테이블처럼 데이터를 가지는 것은 아님
SELECT 문으로 생성하므로 뷰에 접근하면 테이블을 조회
테이블의 바로가기 아이콘과 비슷한 역할
서브쿼리 부분을 뷰로 생성하면 이후 코드를 간단하게 작성할 수 있음

-- 뷰 생성
CREATE VIEW VW_HR_OFFICE AS
SELECT *, CONCAT(부서, '-', 내선) AS 조직 FROM HR_OFFICE;

-- 뷰 활용
SELECT A.*, B.조직
FROM HR_MEMBER AS A
LEFT JOIN VW_HR_OFFICE AS B
ON A.직원명 = B.직원명;

인덱스

인덱스는 데이터를 빠르게 찾기 위해 데이터베이스가 미리 만들어 두는 탐색용 구조

CREATE INDEX idx_apt_price_addr ON APT_PRICE(주소);
CREATE INDEX idx_apt_detail_addr ON APT_DETAIL(지번주소);

실습

정렬 및 제한

SELECT COUNT(*) AS cnt
FROM APT_PRICE
WHERE 단지명 LIKE '%래미안%';

SELECT COUNT(*) AS cnt
FROM APT_PRICE
WHERE 단지명 LIKE '%래미안%' AND 시군구 = '강남구';

SELECT *
FROM APT_PRICE
WHERE 단지명 LIKE '%래미안%' AND 시군구 = '강남구' AND 거래금액 >= 500000;

SELECT *
FROM APT_PRICE
WHERE 단지명 LIKE '%한남더힐%'
ORDER BY 거래금액 DESC
LIMIT 10;

연산 및 함수 활용

SELECT *
	  , ROUND((거래금액 / 전용면적 * 3.3), 0) AS 평당금액
FROM APT_PRICE
ORDER BY 평당금액 DESC;

SELECT 등기일자
	  , 계약일자
    , JULIANDAY(등기일자) - JULIANDAY(계약일자) AS 처리일수
FROM APT_PRICE;

결측값 확인

SELECT COUNT(*) AS cnt
FROM APT_PRICE
WHERE 등기일자 IS NULL;

SELECT COUNT(*) - COUNT(등기일자) AS 결측건수
FROM APT_PRICE;

SELECT 시군구, ROUND(1.0 * (COUNT(*) - COUNT(등기일자)) / COUNT(*) * 100, 1) AS 결측비율
FROM APT_PRICE
GROUP BY 시군구
ORDER BY 결측비율 DESC;

SELECT 법정동
	  , ROUND(AVG(거래금액), 0) AS 평균가격
	  , ROUND(1.0 * (COUNT(*) - COUNT(등기일자)) / COUNT(*) * 100, 1) AS 결측비율
FROM APT_PRICE
WHERE 시군구 = '도봉구'
GROUP BY 법정동
ORDER BY 결측비율 DESC;

SELECT 단지명
	  , ROUND(AVG(거래금액), 0) AS 평균가격
	  , ROUND(1.0 * (COUNT(*) - COUNT(등기일자)) / COUNT(*) * 100, 1) AS 결측비율
FROM APT_PRICE
WHERE 시군구 = '도봉구' AND 법정동 = '창동'
GROUP BY 단지명
ORDER BY 평균가격 DESC, 결측비율 DESC;

결측값 처리

SELECT AVG(JULIANDAY(등기일자) - JULIANDAY(계약일자)) AS 평균처리일수
FROM APT_PRICE; --78 day

-- COALESCE 사용
SELECT 등기일자
	  , 계약일자
    , COALESCE(등기일자, DATE(계약일자, '+78 day')) AS 보정등기일자
FROM APT_PRICE;

-- IFNULL 사용
SELECT 등기일자
  	, 계약일자
    , IFNULL(등기일자, DATE(계약일자, '+78 day')) AS 보정등기일자
FROM APT_PRICE;

그룹화 및 집계

SELECT 단지명
	  , COUNT(*) AS 거래건수
	  , ROUND(AVG(거래금액), 0) AS 평균가격
FROM APT_PRICE
WHERE 시군구 = '도봉구'
GROUP BY 단지명
ORDER BY 거래건수 DESC
LIMIT 10;

SELECT 단지명
	  , COUNT(*) AS 거래건수
	  , ROUND(AVG(거래금액), 0) AS 평균가격
FROM APT_PRICE
WHERE 시군구 = '도봉구'
GROUP BY 단지명
HAVING 거래건수 >= 30
ORDER BY 평균가격 DESC
LIMIT 10;

시계열 분석

SELECT STRFTIME('%m', 계약일자) AS 계약월
	  , COUNT(*) AS 거래건수
	  , ROUND(AVG(거래금액), 0) AS 평균가격
FROM APT_PRICE
GROUP BY STRFTIME('%m', 계약일자);

중복 확인

SELECT COUNT(*) AS 전체건수 FROM APT_PRICE;
SELECT COUNT(DISTINCT(단지명)) AS 고유값_개수 FROM APT_PRICE;
SELECT COUNT(DISTINCT(주소)) AS 고유값_개수 FROM APT_PRICE;

CONCAT 사용(|| 로 대체 가능)

SELECT COUNT(DISTINCT(CONCAT(주소, ' ' , 단지명))) AS 고유값_개수 FROM APT_PRICE;
SELECT COUNT(DISTINCT(주소 || ' ' || 단지명)) AS 고유값_개수 FROM APT_PRICE;

뷰 생성

CREATE VIEW VW_APT_RECENT AS
SELECT 단지명, 주소, MAX(계약일자) AS 최신계약일자
FROM APT_PRICE
GROUP BY 단지명, 주소;

뷰 병합

SELECT A.*
FROM APT_PRICE AS A
LEFT JOIN VW_APT_RECENT AS B
ON A.단지명 = B.단지명
AND A.주소 = B.주소
AND A.계약일자 = B.최신계약일자;

외래키 탐색

단지명 고유값 개수 확인

SELECT COUNT(DISTINCT 단지명) AS 고유값_개수 FROM APT_PRICE; -- 4432

아파트명으로 일치 건수 확인

SELECT COUNT(*) AS 중복건수
FROM (SELECT 단지명 FROM APT_PRICE GROUP BY 단지명) AS A
INNER JOIN (SELECT 아파트명 FROM APT_DETAIL GROUP BY 아파트명) AS B
ON A.단지명 = B.아파트명; -- 1803

주소 고유값 개수 확인

SELECT COUNT(DISTINCT 주소) AS 고유값_개수 FROM APT_PRICE; -- 5063

주소로 일치 건수 확인

SELECT COUNT(*) AS 중복건수
FROM (SELECT DISTINCT 주소 FROM APT_PRICE) AS A
INNER JOIN (SELECT DISTINCT 지번주소 FROM APT_DETAIL) AS B
ON A.주소 = B.지번주소; -- 0

주소 표기 확인

SELECT 주소 FROM APT_PRICE LIMIT 10;
SELECT 지번주소 FROM APT_DETAIL LIMIT 10;

주소 표기 통일 후 일치 건수 확인

SELECT COUNT(*) AS 중복건수
FROM (SELECT DISTINCT 주소 FROM APT_PRICE) AS A
INNER JOIN (SELECT DISTINCT REPLACE(지번주소, '서울시', '서울특별시') AS 주소 FROM APT_DETAIL) AS B
ON A.주소 = B.주소; -- 4773

중복 확인

SELECT 지번주소, COUNT(*) AS 중복건수
FROM APT_DETAIL
GROUP BY 지번주소
HAVING 중복건수 > 1;

전체 뷰 생성

CREATE VIEW VW_APT_DETAIL AS
SELECT *, REPLACE(지번주소, '서울시', '서울특별시') AS 주소
FROM APT_DETAIL;

전체 개수 확인

SELECT COUNT(*) AS cnt FROM VW_APT_DETAIL; -- 10725

기존 뷰 삭제

DROP VIEW VW_APT_DETAIL;

rowid를 활용해서 중복 제거 뷰 생성

CREATE VIEW VW_APT_DETAIL AS
SELECT *, REPLACE(지번주소, '서울시', '서울특별시') AS 주소
FROM APT_DETAIL
WHERE rowid IN (SELECT MIN(rowid) FROM APT_DETAIL GROUP BY 지번주소);

중복제거 이후 확인

SELECT COUNT(*) AS cnt FROM VW_APT_DETAIL; -- 10690

병합

CREATE VIEW VW_APT_MERGED AS
SELECT A.*, B.세대수, B.주차대수, B.건설사
FROM APT_PRICE AS A
INNER JOIN VW_APT_DETAIL AS B
ON A.주소 = B.주소;

병합 확인

SELECT * FROM VW_APT_MERGED LIMIT 10;

인덱스 추가

CREATE INDEX idx_apt_price_addr ON APT_PRICE(주소);
CREATE INDEX idx_apt_detail_addr ON APT_DETAIL(지번주소);

기존 병합 결과 뷰 삭제
- IF EXISTS : 있을시에만 적용

DROP VIEW IF EXISTS VW_APT_MERGED;

재병합

CREATE VIEW VW_APT_MERGED AS
SELECT A.*, B.세대수, B.주차대수, B.건설사
FROM APT_PRICE AS A
INNER JOIN VW_APT_DETAIL AS B
ON A.주소 = B.주소;

병합 확인

SELECT * FROM VW_APT_MERGED;

마치며

현업에서 사용하는 SQL 방식이나 복잡한 내용들도 잘 이해할 수 있게 알려주셔서 너무 좋았던 SQL 수업이었다. 앞으로 SQL 문제를 계속 풀어보면서 SQL 자격증도 준비해보면 좋을 것 같다.

TaeHyun

Hello I'm TaeHyunAn, Currently Studying Data Analysis

이전 포스트

251230 [ Day 2 ] - SQL (1)

다음 포스트