[1기] K-Digital Training: 데이터 분석 데브코스 - TIL_Day12

정연우·2023년 12월 5일
0

[1기] K-Digital Training: 데이터 분석 데브코스

목록 보기
12/22

학습 주제

SQL의 다양한 JOIN함수를 사용하는 이유와 어떻게 사용해야하는지 배웠습니다. 또한 UNION, WITH, SUBQUREY, TIMSTAMP, 타입 변환, 조건절 등 SQL에서 사용하는 유용한 함수들을 배웠습니다.

주요 메모 사항 소개

다양한 JOINS

테이블을 쪼개는 이유

  • 각 테이블을 관리하기 쉽고 효율적으로 데이터를 저장할 수 있기 때문, 또한 쪼개놓은 테이블과 조인 연산을 통해 다양한 사례를 해결할 수 있다.

  • 테이블에 변경사항이 생길 때 한 테이블로 저장했다면 모두 바꿔야하는데 테이블을 쪼개놓으면 변경이 있는 테이블만 수정하면되기 때문에 조인을 사용하면 테이블 관리가 쉽다. 또한 중복을 줄일 수 있다.

다양한 JOINS: INNER JOIN

  • 2개의 테이블에서 일치하는 행만 가져와 결합하는 조인. 즉 연결된 열에서 값이 일치한 행만 결과에 포함된다. (두 테이블 간의 교집합을 반환한다.)

on: 어떤 컬럼을 기준으로 결합할지 작성할 때 필요하다.

실습에서 3개 값을 기준으로 join을 한 이유 (user_name, product_id, date)

두 테이블을 결합할 때 각 행이 완전히 다른 행과 구분되기 위해선 세 개의 결합조건이 모두 필요하다.

다양한 JOINS: LEFT JOIN

  • 왼쪽 테이블에 모든 행을 가져오고 오른쪽 테이블에서 일치하는 행을 가져와 결합한다. 오른쪽 테이블에 일치하지 않는 행은 null값으로 표시된다.

특징

왼쪽 테이블에 모든 행을 포함하므로 조인으로 인해 누락되는 데이터가 없다. 오른쪽 테이블의 모든 행을 포함하려면 RIGHT JOIN을 써야한다.

두 테이블의 키값이 일치하지 않으면 null로 표시되므로 어떤 행이 일치하고 어떤 행이 일치하지 않는지 쉽게 알 수 있다.

좌측 테이블의 하나의 행과 일치하는 행이 우측 테이블에 여러 개가 있을 수 있는데 이 경우 결과 테이블이 기존 좌측 테이블보다 더 커질 수 있다.

다양한 JOINS: RIGHT JOIN

LEFT JOIN의 반대, 오른쪽 테이블의 모든 행을 가져오고 왼쪽 테이블에서 일치하는 행을 가져와 결합한다.

왼쪽 테이블에서 가져온 컬럼중 오른쪽 테이블과 키값이 일치하지 않는 경우 null값으로 표시된다.

다양한 JOINS: FULL OUTER JOIN

  • 두 테이블 간의 모든 행을 가져오며 일치하는 행과 일치하지 않는 행 모두를 포함하도록 결과를 반환하는 유형의 조인

결과 집합에는 두 테이블의 데이터가 모두 포함되고 일치하지 않는 행의 컬럼값은 null이 들어간다.

필터링되는 행이 없다. 따라서 결과 집합이 크고 복잡하면 DB에 과부하를 줄 수 있다.

MySQL은 FULL OUTER JOIN을 지원하지 않기 때문에 동일조건을 INNER, LEFT, RIGHT JOIN한 후 UNION으로 결합시킨다. (중복되는 행은 생략한다.)

JOIN조건을 모두 만족하지 않아도 행들을 가져와 null값으로 들어온다. (인덱스값이 하나라도 있으면 테이블에 포함된다.)

다양한 JOINS: CROSS JOIN(=Cartesian product)

  • 두 테이블 간의 가능한 모든 조합을 생성할 때 사용. 상품간 유사도를 구할 때 주로 사용한다.

  • ex) 3행 테이블 * 3행 테이블 → 결과: 9행 테이블

단점
무분별하게 사용하면 과부하가 발생할 수 있다.

유사도를 구하는 방법

실수값으로만 구성된 경우 코사인 유사도, 유클리디안 거리 등을 사용한다.

다양한 JOINS: Alias (=별칭)

FROM clicks c LEFT JOIN orders o on …

INNER JOIN products p…

→ clicks는 c, orders는 o, products는 p라고 지칭한다.

Alias를 사용하면 테이블 이름을 다 쓰지 않아도 되어 편리하고 같은 테이블끼리 결합할 때 서로 구분할 수 있다.

다양한 JOINS: SELF JOIN

자기 자신과 결합한다. (SELF JOIN이라는 구문이 있는 것은 아니다. INNER JOIN을 사용한다.) - 직원 테이블에서 직속 상사를 연결할 때 사용한다.

SELF JOIN을 할 때 Alias는 필수이다.

다양한 JOINS: 필터링

조인의 결과물은 테이블이다. 따라서 WHERE 구문으로 필터링을 할 수 있다.

SQL쿼리가 수행될 때 조인을 하고나서 그 결과물에 대해 WHERE문과 SELECT문을 실행한다.

쿼리를 효율적으로 짜려면 결과물을 다 만들고나서 필터를 거는 것보다는 필터를 먼저 걸고나서 결합하는 것이 더 좋다.

정리

INNER JOIN r on l.key = r.key: 교집합

LEFT JOIN r on l.key = r.key: 교집합을 포함한 왼쪽 집합

LEFT JOIN r on r.key is NULL: 교집합을 포함하지 않은 왼쪽 집합 (오른쪽이 널인 경우가 참이기 때문)

RIGHT JOIN r on l.key = r.key: 교집합을 포함한 오른쪽 집합

RIGHT JOIN r on l.key is NULL: 교집합을 포함하지 않은 오른쪽 집합 (오른쪽이 널인 경우가 참이기 때문)

LEFT … l.key = r.key UNION … RIGHT … l.key = r.key: 합집합 (FULL OUTER JOIN)

LEFT … l.key = r.key UNION … RIGHT … l.key = r.key WHERE l.key is NULL or r.key is NULL: 합집합에서 교집합을 뺀 집합

UNION

  • 두 개 이상의 SELECT문의 결과를 결합하여 하나의 결과 집합으로 만든다. (SELECT문을 위아래로 붙인다.)

UNION의 특징

중복되는 행을 제거 (컬럼 전체가 같아야 중복 제거가 된다.)

각 SELECT문에 결과 집합에 포함되는 열의 수와 데이터 타입이 일치해야한다.

UNION ALL

UNION과 유사하지만 중복제거를 하지 않는다.

WITH

복잡한 쿼리도 블럭 단위로 쪼개서 작성할 수 있어 코드를 이해하기 쉽다. 각 블럭은 다른 블럭에서도 테이블처럼 지칭해 사용할 수 있기 때문에 같은 것을 불러오는 쿼리를 여러번 실행하지 않아도 된다.

서브쿼리(subquery)

  • 다른 쿼리의 내부에 포함된 쿼리. 더 큰 쿼리의 일부로 사용된다.

복잡한 조건을 사용하여 데이터를 추출하거나 다양한 테이블간의 관계를 분석하는 등 다양한 쿼리 작업에 사용된다.

잘못 사용하면 리소스를 지나치게 많이 사용할 수 있고 DB성능에 영향을 줄 수 있기 때문에 쿼리 최적화를 신경써야한다.

select, from, where문에 사용가능하다.

  • select문에서는 각 그룹별 평균과 전체 평균을 비교할 때 사용한다. (결과물로 나온다)

  • from문에서는 조인하기 전에 필터링을 먼저하므로 성능상 이점을 가질 수 있다. (테이블을 만든다)

  • where문에서는 조건을 작성할 수 있다.

동일한 결과물이더라도 쿼리는 다양하다. (subquery, with, join) 따라서 상황에 맞게 가장 효율적인 쿼리방식을 선택해야한다.

with구문도 마찬가지로 필터링을 한 뒤에 조인을 할 수 있다.

EXISTS연산자는 IN연산자와 역할이 비슷하지만 EXISTS는 서브쿼리와 함께 사용한다. IN연산자는 비교 대상이 되는 값을 직접 입력한다.

EXISTS는 true, false를 반환한다. ture면 쿼리 전체가 실행되고 false면 전체 쿼리 결과가 출력되지 않는다.

타임스탬프 함수

STRING(문자열): 형식에 제한이 없지만 시간관련 함수들을 사용할 수 없다.

DATE: 년월일 정보만을 저장 (시간단위 저장X, 공간 절약)

DATETIME: 날짜 시간 정보를 모두 저장한다. 정밀한 시간 정보를 제공한다. (시간간의 간격을 계산할 수 있다.) 서비스나 서버에서 발생하는 이벤트를 로깅할 때 사용한다.

TIMESTAMP: DATETIME과 유사하지만 저장할 수 있는 시간 범위가 좁다. (1970.1.1 ~ 2038.1.19) 따라서 DATETIME보다 공간을 절약할 수 있다.

NOW(): 현재 날짜와 시간을 반환하는 함수

  • CURRENT_TIMESTAMP()도 같은 기능이다.

  • CURTIME()은 서버의 시간만 반환하거나 날짜도 포함해서 반환하는 경우도 있다.

  • CURRENT_DATE() = CURDATE()는 현재 날짜를 반환하고 시간은 반환하지 않는다.

  • SYSDATE()도 NOW()와 유사하지만 NOW()는 쿼리가 시작하는 시점을 반환하고 SYSDATE()는 SYSDATE()함수가 호출되는 시점을 기록한다.

  • YEAR() input이 문자열로 들어와도 적용가능한 경우가 종종 있다.

  • WEEKDAY() 오늘의 요일을 반환한다.

  • MONTHNAME(): 오늘의 월을 영어로 반환한다.

  • DAYNAME(): 오늘의 요일을 영어로 반환한다.

  • ADDDATE(): 시간 덧셈

  • DATE_ADD()와 같은 기능

  • SUBDATE(): 시간 뺄셈

  • DATE_SUB()와 같은 기능

  • CONVERT_TZ(): 나라별 시간을 구할 때 사용한다.

타입 변환

  • 서로 다른 조직이 데이터를 관리하는 경우 데이터 타입이 불일치하는 경우가 있기 때문에 타입을 변환해야한다.

타입 변환 함수

  • CAST(변환하고 싶은 것 as 변환할 타입)
  • CONVERT(변환하고 싶은 것, 변환할 타입)

조건절

조건에 따라 데이터를 처리하고 반환하기 위해 사용

조건이 참일 때 거짓일 때 각각 다르게 처리하기 위해 사용

IF(조건, 참, 거짓)

  • 값들을 특정한 구간으로 나눠야 할 때 사용한다.

  • IF문을 중첩으로 사용가능하다. (거짓 인덱스에 IF을 작성해야한다.)

  • 형식: IF(조건, 참, IF(조건, 참, 거짓))

IFNULL(조건, null을 채울 값)

  • null인 경우 지정한 값으로 채워 반환한다.

CASE WHEN

  • 조건에 따라 데이터를 처리하기 위한 구문. IF문과 비슷하지만 쿼리 작성 방식이 다르다.

방식1

CASE WHEN 조건1 THEN 조건1의 참
WHEN 조건2 THEN 조건2의 참
ELSE 거짓
END AS 별칭

방식2

CASE 컬럼명
WHEN 특정값 THEN 반환값
WHEN 특정값 THEN 반환값
ELSE 나머지
END AS 별칭

  • CASE WHEN을 중첩해서 사용가능하다. (TEHN절에서 사용한다.)

그 외 유용한 함수

  • rank(): 특정 컬럼을 기준으로 등수를 매기는 함수 (1등 1명, 2등 2명, 4등 1명…)

  • dense_rank(): 동점인 랭크가 있을 경우 랭크 숫자를 건너뛰지 않는 함수 (1등 1명, 2등 2명, 3등 1명…)

  • percent_rank(): 특정 값보다 낮은 등수를 비율로 나타내준다. (0.75면 이 값보다 높은 등수값이 75% 존재한다.)

over구문으로 어떤 컬럼을 기준으로할지 정한다.

lead()

  • 파티션 내에서 다음으로 오는 값을 찾는 함수

lag()

  • 파티션 내에서 전에 어떤 값이 왔는지 찾는 함수

기준을 잡아 파티션을 나누고 랭킹을 매기거나 다음값을 찾는 등의 함수를 윈도우 함수라고 한다.

공부하며 어려웠던 내용

SQL을 많이 다뤄보지 않아 다양한 함수를 활용해 본 경험이 없어서 어느 상황에 어느 함수를 사용해야하는지 잘 모르지만 코딩테스트나 프로젝트를 통해 능숙하게 사용할 수 있도록 노력해야겠다는 생각이 들었습니다.

profile
정연우
이전 포스트

[1기] K-Digital Training: 데이터 분석 데브코스 - TIL_Day11

다음 포스트

[1기] K-Digital Training: 데이터 분석 데브코스 - TIL_Day13

0개의 댓글