[TIL] 프로그래머스 데이터 분석 데브코스｜Week3｜SQL｜테이블 결합 + 다양한 SQL 함수

 

 

 

 

💡 요약

• 다양한 JOIN
  
  • INNER JOIN
    
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • SELF JOIN
    
    
• UNION / UNION ALL
  
  
• WITH
  
  
• Subquery
  • SELECT절 서브쿼리
  • FROM절 서브쿼리
  • WHERE절 서브쿼리
    
    
• 타임스탬프 함수
  • 시간을 나타내는 데이터 타입
  • 현재 시간
  • 날짜 형식화
  • 날짜 연산
    
    
• 타입 변환
  
  
• 조건문
  • IF
  • IFNULL
  • CASE
    
    
• 그 외 유용한 함수
  • RANK
  • LEAD
  • LAG

 

 

 

💭 느낀 점

테이블 결합부터 데이터 타입, 날짜/시간 관련 함수, 조건문, RANK, LEAD 등에 대해 배웠다. 오늘 역시나 JOIN과 조건문은 데이터리안에서 배운 내용이라 전혀 어렵지 않았고, 나머지 함수들은 한 번씩 보긴 했지만 어떻게 사용하는지 궁금했었는데 이번에 배울 수 있었다. 특히나 날짜/시간 관련 함수나 RANK 같은 함수는 실무에서 꽤 자주 사용할 것 같았는데, 간단해 보이지만 종류도 너무 많고 비슷비슷하게 생겨서 익숙해지기 전까진 외우기 쉽게 않겠다는 생각이 들었다. 그래서 더더욱 TIL 작성을 나만의 사전처럼 만들길 다행이다,, 하는 안도감도😵 강사님은 쿼리 작성하시는 속도가 굉장히 빠르시던데 저렇게 거침없이 써내려가려면 얼마나 숙련이 되어야 할까.. 살짝 막막해졌다ㅎ 나도 멋있게 쿼리 막힘없이 작성하고 싶다!

 

Subquery는 여태까지 SQL을 공부하면서 입력한 쿼리의 결과값을 가지고 또다시 쿼리를 입력하고 싶은데 어떻게 해야하나 궁금했던 적이 많았는데, 그 부분이 해결되어서 좋았다. 오늘은 전반적으로 실무에서 많이 사용할 것 같은 함수들을 배운 것 같아 유익했다! 특히나 그 함수들이 어떤 상황일 때, 어떤 분석을 위해 사용되는지도 공부할 수 있어 좀 더 입체적으로 생각해 볼 수 있었고, 지금 하고 있는 프로젝트에서 어떻게 적용시킬 수 있을지 고민해 볼 수 있었다.

 

사실 요즘의 고민은 강의도 수강하고 있고, 프로젝트도 시작하고 있는데 강의에서 배운 걸 막상 프로젝트에 적용하려고 하면 어떻게 해야할지 너무 막막하다는 것이다. 단순 지식으로 습득하는 걸 넘어서서 응용을 해야 하는데 직접 적용해보려고 하면 뇌가 정지되어 버림😢 많이 해보는 것이 답일까, 고민을 더 깊게 해야하는 걸까.. 앞으로 해결해야 할 숙제다.

 

 

 

 

🌳 다양한 JOIN

 

🔗 [TIL] 데이터리안 SQL 데이터 분석 캠프 입문반｜Week3｜데이터 연결하기1
🔗 [TIL] 데이터리안 SQL 데이터 분석 캠프 입문반｜Week4｜데이터 연결하기2

 

JOIN

• 두 개 이상의 테이블을 특정 Key를 기준으로 결합하는 것

 

INNER JOIN

• 두 개의 테이블에서 일치하는 행만 가져와서 결합
• 테이블 간의 교집합 반환 → 다른 JOIN 보다 비교적 빠름
•  ON  뒤에 여러 개의 조건 사용 가능
  •  FROM 테이블명1 INNER JOIN 테이블명2 ON 조건1 AND 조건2 AND 조건3 

 

LEFT JOIN

• 왼쪽 테이블의 모든 행 + 오른쪽 테이블의 일치하는 행
  • 오른쪽 테이블의 일치하지 않는 행 → NULL로 반환

 

RIGHT JOIN

• 오른쪽 테이블의 모든 행 + 왼쪽 테이블의 일치하는 행
  • 왼쪽 테이블의 일치하지 않는 행 → NULL로 반환

 

FULL OUTER JOIN

• 두 테이블의 모든 행
• 일치하는 행, 일치하지 않는 행 모두 반환
  • 일치하지 않는 행 → NULL로 반환
• DB에 과부화를 줄 수 있는 JOIN → 꼭 필요한 경우에만 제한적으로 사용
• DB마다 지원여부가 다름
  • MySQL은 지원하지 않음 →  LEFT JOIN  +  RIGHT JOIN  후  UNION  으로 결합

 

CROSS JOIN

• = Cartesian Product
• 두 테이블의 가능한 모든 조합 생성
• ON 조건이 없음
• 연산량이 매우 많기 때문에 특히 유의해서 사용해야 함
• (ex) 상품 간 유사도를 구할 때 → 연관 상품 추천
  • 유사도 구하는 방법
    • category = B_category : 일치하면 1점, 일치하지 않으면 0점
    • name = B_name : 일치하면 1점, 일치하지 않으면 0점
    • price - B_price : 차이 값이 1,000원 이하면 1점, 이상이면 0점

 

SELF JOIN

• 하나의 테이블을 자기 자신과 결합
• 별칭(Alias) 필수로 사용해야 함

 

별칭(Alias)

• 테이블에 별칭을 붙여 쿼리를 더 가독성 있게 만들 수 있음

 

필터링

• JOIN의 결과물은 테이블 → 테이블에 사용하는 구문 그대로 사용 가능
• WHERE절로 필터링

 

 

🌳 UNION / UNION ALL

 

🔗 [TIL] 데이터리안 SQL 데이터 분석 캠프 입문반｜Week3｜데이터 연결하기1

 

UNION

•  SELECT  한 결과물을 위아래로 결합
• 중복되는 행을 제거
  • 중복 기준 :  SELECT 에 포함된 Column 전체가 일치해야 함
• 각  SELECT 문의 결과 집합에 포함되는 Column 수와 데이터 타입이 일치해야 함
  • Column명이 일치할 필요는 없음
  • DB 종류에 따라 데이터 타입이 일치하지 않아도 결합 가능

 

UNION ALL

• 중복 제거를 하지 않고 결합

 

 

🌳 WITH

• = CTE (Common Table Expression)
• MySQL 8.0 버전 이상부터 지원
• 임시 결과 집합을 생성 → 쿼리를 쉽게 작성할 수 있도록 돕는 기능
• 쿼리 가독성을 높힘 → 유지보수 용이
• 같은 쿼리 블록을 여러 번 사용 가능
  • 같은 결과를 여러 번 계산해야 하는 경우 사용
• 블록 여러 개 생성 가능
• DB Optimizer → CTE를 단순한 뷰나 서브쿼리보다 더 효율적으로 처리

 

 

🌳 Subquery

Subquery 란?

• 다른 쿼리 내부에 포함된 쿼리 → 주로 더 큰 쿼리의 일부로 사용됨
• 복잡한 조건을 사용한 데이터 추출, 다양한 테이블 간의 관계 분석 등에 사용
• DB 성능에 영향을 줄 수 있음 → 쿼리 최적화에 신경써야 함
•  WITH ,  JOIN 을 사용해도 동일한 결과물 얻을 수 있음
  • 상황에 맞게 효율적인 쿼리 방식 선택

 

SELECT절 서브쿼리

• (ex) 유저별 평균 구매 가격 - 전체 평균 구매 가격 비교

 

FROM절 서브쿼리

• (ex) 스포츠/주방용품 매니저들의 클릭 이력 가져오기

•  JOIN  후 필터링 →  JOIN  대상이 되는 테이블의 크기가 클수록 부담
• Subquery 사용 시, 필터링을 먼저 하기 때문에 테이블 크기를 줄여놓고  JOIN  할 수 있음

 

WHERE절 서브쿼리

• (ex) 가장 비싼 상품에 대한 클릭 이력 가져오기

•  ALL  /  ANY  /  SOME  연산자 : Subquery와 함께 사용

 

•  EXISTS 와 함께 사용하기

•  EXISTS  :  IN  연산자와 비슷하지만 서브쿼리와 함께 사용 → True/False 반환 (True일 때 전체 쿼리 실행)
  •  IN  : 비교 대상이 되는 값을 직접 입력

 

 

🌳 타임스탬프 함수

시간을 나타내는 데이터 타입

• STRING
  • ‘yyyy-mm-dd’, ‘yyyy-mm-dd HH:MM:SS’
  • 가장 단순하게 시간 데이터를 저장할 수 있는 방식
  • 형식 제한 X
  • 시간 관련 함수 사용 불가 → 다른 데이터 타입으로 변환 후 사용

 

• DATE
  • yyyy-mm-dd
  • 연/월/일 단위까지만 저장 → 시간 단위 저장 X
  • DATETIME / TIMESTAMP 에 비해 저장공간 덜 차지

 

• DATETIME
  • yyyy-mm-dd HH:MM:SS
  • 날짜, 시간 단위 모두 저장
  • 시간 간의 연산 가능 → 시간 간의 간격을 고려한 분석 가능
  • DATE 타입보다 저장공간 많이 차지 → 어느 단위까지 저장할지 적절히 결정해야 함
  • 주로 초 단위까지 저장할 때 사용 (ex) 고객 행동로그(클릭/구매로그), 예약시스템, 서버이벤트 로깅 등

 

• TIMESTAMP
  • yyyy-mm-dd HH:MM:SS UTC
  • DATETIME 과 유사하지만 저장할 수 있는 시간 범위가 더 제한적
    • 1970-01-01 ~ 2038-01-19 까지 표현 가능
    • 저장공간 : DATETIME > TIMESTAMP
  • 시간 간 연산 가능
  • DATETIME과 다르게 타임존까지 고려 가능
  • 활용 케이스 DATETIME과 유사

 

현재 시간

•  NOW()  : UTC 기준으로 현재 시간을 가져오는 함수
  • =  CURRENT_TIMESTAMP() 
  •  CURTIME()  : 현재 시간 반환 (DB에 따라 날짜까지 반환하기도 함)
  •  CURRENT_DATE()  /  CURDATE()  : 날짜만 반환 (yyyy-mm-dd 형식)

 

•  SYSDATE()  :  SYSDATE  함수가 호출된 시간 반환
  •  NOW  /  CURRENT_TIMESTAMP  → 쿼리 실행이 시작되는 시점

 

•  YEAR()  /  MONTH()  /  DAY()  : 연/월/일 추출
  • 값이 문자열로 들어와도 적용 가능한 경우 있음

 

•  HOUR()  /  MINUTE()  /  SECOND()  : 시/분/초 추출

 

•  WEEKDAY()  : 요일 반환
  • 0~6의 숫자로 반환 (0 : 월요일 ~ 6 : 일요일)

 

•  MONTHNAME()  /  DAYNAME()  : 월/요일 영문으로 반환
  • 잘 사용하지 않음

 

날짜 형식화

•  STR_TO_DATE()  : 문자열 → 날짜 타입으로 변경

 

•  DATE_FORMAT()  : 지정된 형식으로 날짜 출력
  •  %Y  : 연도 (ex) 2023
  •  %y  : 연도 (ex) 23
  •  %M  : MySQL에서 월 이름을 영문으로 표시 (ex) may
  •  %m  : 월 (ex) 12
  •  %d  : 일 (ex) 31
  •  %T  : 시간 (ex) hh:mm:ss
  •  %H  : 시 (ex) 05 (24H 형태)
  •  %s  : 초 (ex) 30

 

날짜 연산

•  ADDDATE()  : 특정 interval 만큼 시간을 더함
  •  ADDDATE(날짜, 더할날짜수) 
  • =  DATE_ADD()  (ex)  DATE_ADD('2023-12-01', INTERVAL 3 SECOND) 

 

•  SUBDATE()  : 특정 interval 만큼 시간을 뺌
  • =  DATE_SUB() 
  • = (ex)  DATE_ADD('2023-12-01', INTERVAL -1 DAY) 

 

•  CONVERT_TZ()  : 타임존 변경하여 출력
  •  CONVERT_TZ(시간, 'from타임존', 'to타임존') 
  • (ex)  CONVERT_TZ(NOW(), '+00:00', '+09:00')  : UTC 기준 → 한국시간 기준

 

•  DATEDIFF()  : 두 날짜 간 차이를 일 단위로 반환
  •  DATEDIFF(날짜1, 날짜2) 
  • (ex)  DATEDIFF('2023-12-01', '2023-12-04')  → 3

 

•  TIMEDIFF()  : 두 시간 간 차이를 시간 단위로 반환
  •  TIMEDIFF(시간1, 시간2) 
  • (ex)  TIMEDIFF('2023-12-01 11:05:05', '2023-12-01 11:00:00')  → 00:05:05

 

•  TIME_TO_SEC()  : 시간을 초 단위로 반환
  •  TIME_TO_SEC(시간) 
  • (ex)  TIME_TO_SEC('00:05:05')  → 305

 

 

🌳 타입 변환

• 필요성
  • 데이터 타입 불일치로 인한 연산/비교 오류를 피함
  • 다양한 데이터 소스 간 호환성을 유지하기 위함

 

• 종류
  •  CAST() 
  •  CONVERT() 

 

 

🌳 조건문

 

🔗 [TIL] 데이터리안 SQL 데이터 분석 캠프 입문반｜Week2｜조건문

 

IF

•  IF(조건, 참일때반환값, 거짓일때반환값) 
• 가격, 나이 같은 숫자값을 특정한 구간으로 묶어야 할 때 사용 가능
• 조건이 2~3개일 경우 적합 → 조건이 많을 경우  CASE  사용

 

IFNULL

•  IFNULL(Column명, NULL일때반환값) 

 

CASE

•  CASE WHEN 조건 THEN 반환값1 ELSE 반환값2 END 
•  WHERE 절에서 사용 가능 →  WHERE  조건이 복잡할 때
•  ORDER BY  구문에서 사용 가능 → 직접 반환 순서를 정하고 싶을 때
  • 조건 입력한 순서대로 출력
  • 뒤에  DESC  붙이면 입력한 반대 순서대로 출력

 

 

🌳 그 외 유용한 함수

RANK

•  RANK()  : 특정 Column의 순위 매기기
  • 동일 순위가 있다면 다음 숫자 건너뜀 (ex) 1등이 2명 → 다음 순위 3
•  DENSE_RANK()  : 동일 순위가 있을 때 건너뛰지 않고 순위 설정
•  PERCENT_RANK()  : 현재 값보다 적은 값이 몇 % 있는지 반환
• MySQL 8.0 버전 이상부터 지원

 

LEAD

• 파티션 내에서 다음으로 오는 값 반환
• 고객 행동의 흐름 파악 가능 (ex) 첫 번째, 두 번째 클릭 상품의 가격 차이

 

LAG

• 파티션 전에 어떤 값이었는지 반환 ( LEAD 와 반대)

 

 

 

본 내용은 프로그래머스 '데이터 분석 데브코스' 를 수강하며 작성한 내용입니다.