[22일차] SQL을 이용한 데이터 분석

isnotnull·2023년 12월 21일

sql 국비 데이터분석 프로그래머스

프로그래머스-데이터분석1기

목록 보기

18/21

Redshift 론치 데모

예제 테이블

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보 (1)

사용자 ID: 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
세션 ID: 세션마다 부여되는 ID
- 세션: 사용자의 방문을 논리적인 단위로 나눈 것
  - 사용자가외부링크(보통광고)를타고오거나직접방문해서올경우세션을생성
  - 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
- 즉하나의사용자는여러개의세션을가질수있음
- 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠
  - 마케팅 관련 기여도 분석을 위함
- 또한세션이생긴시간도기록

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보 (2)

이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능
- 마케팅 관련, 사용자 트래픽 관련
- DAU, WAU, MAU 등의 일주월별 Active User 차트
- Marketing Channel Attribution 분석
  - 어느 채널에 광고를 하는 것이 가장 효과적인가?

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보 (3)

사용자 ID 100번: 총 3개의 세션(파란 배경)을 갖는 예제
- 세션1:구글키워드광고로시작한세션
- 세션2:페이스북광고를통해생긴세션
- 세션3:네이버광고를통해생긴세션

관계형 데이터베이스 예제 - 데이터베이스와 테이블(1)

관계형 데이터베이스 예제 - 데이터베이스와 테이블(2)

관계형 데이터베이스 예제 - 데이터베이스와 테이블(3)

SQL 소개

SQL 기본

다수의 SQL문을 실행할 때 세미콜론으로 분리
- SQL문1;SQL문2;SQL3
SQL 주석
- -- : 인라인 한줄짜리 주석, 자바에서 //에 해당
- /*--*/ : 여러 줄에 걸쳐 사용 가능한 주석
SQL 키워드는 대문자를 사용하는 등 나름대로의 포맷팅 필요
- 팀 프로젝트라면 팀에서 사용하는 공통 포맷 필요
테이블/필드 이름의 명명 규칙을 정하는 것이 중요
- 단수형 vs 복수형
  - User vs Users
- _ vs CamelCassing
  - user_sessiong_channel vs UserSessionChannel

SQL DDL - 테이블 구조 정의 언어(1)

CREATE TABLE
Primary key 속성을 지정할 수 있으나 무시된다
- Primary key uniqueness
  - Big Data 데이터웨어하우스에서는 지켜지지 않음 (Redshift, Snowflake, BigQuery)
CTAS : CREATE TABLE table_name AS SELECT
- vs CREATE TABLE and then INSERT

CREATE TABLE raw_data.user_session_channel ( userid int,
sessionid varchar(32) primary key,
channel varchar(32) );

SQL DDL - 테이블 구조 정의 언어(2)

DROP TABLE
- DROP TABLE table_name;
  - 없는테이블을지우려고하는경우에러를냄
- DROP TABLE IF EXISTS table_name;
- vs. DELETE FROM
  - DELETE FROM은 조건에 맞는 레코드들을 지움 (테이블 자체는 존재)

SQL DDL - 테이블 구조 정의 언어(3)

ALTER TABLE
- 새로운 컬럼 추가:
  - ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
- 기존컬럼이름변경:
  - ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름
- 기존 컬럼 제거:
  - ALTER TABLE 테이블이름 DROP COLUMN 필드이름;
- 테이블 이름 변경:
  - ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;

SQL DML - 테이블 데이터 조작 언어(1)

레코드 질의 언어: SELECT
- SELECT FROM: 테이블에서 레코드와 필드를 읽어오는데 사용
- WHERE를 사용해서 레코드 선택 조건을 지정
- GROUP BY를 통해 정보를 그룹 레벨에서 뽑는데 사용하기도 함
  - DAU, WAU, MAU 계산은 GROUP BY를 필요로 함
- ORDER BY를 사용해서 레코드 순서를 결정하기도 함
- 보통 다수의 테이블의 조인해서 사용하기도 함

SQL DML - 테이블 데이터 조작 언어(2)

레코드 수정 언어:
- INSERT INTO: 테이블에 레코드를 추가하는데 사용
- UDATE FROM: 테이블 레코드의 필드 값 수정
- DELETE FROM: 테이블에서 레코드를 삭제
  - vs TRUNCATE

SQL 실습 환경

실습할 때 주의할 점

현업에서 깨끗한 데이터 존재하지 않음
- 항상 데이터를 믿을 수 있는지 의심
- 실제 레코드를 몇 개 살펴보기 ➡️ 노가다
데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요
- 중복된 레코드 체크
- 최근 데이터의 존재 여부 체크 (freshness)
- Primary key uniqueness가 지켜지는지 확인
- 값이 비어있는 컬럼 체크
너무나 많은 테이블 존재
- 회사 성장과 관련
- 중요 테이블과 그 테이블의 메타 정보를 관리하는 것이 중요
Data Discovey 문제 발생
- 어떤 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?
- 테이블에 대해 질문 하고 싶은데 누구에게 해야하나?
위의 문제들을 해결하기 위한 다양한 오픈소스와 서비스 출현
- DataHub(LinkedIN), AMundsen(Lyft),...
- Select Star, DataFraem,...

SELECT

테이블(들)에서 레코드들(혹은 레코드수)을 읽어오는데 사용
WHERE를 사용해 조건을 만족하는 레코드

SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
ORDER BY 필드이름 [ASC|DESC] -- 필드 이름 대신에 숫자 사용 가능 LIMIT N;

CASE WHEN

필드값의변환을위해사용가능
- CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일때 값 END 필드이름
여러 조건을 사용하여 변환하는 것도 가능

CASE
WHEN 조건1 THEN 값1 WHEN 조건2 THEN 값2 ELSE 값3
END 필드이름

NULL

값이 존재하지 않음을 나타내는 상수. 0 혹은 ""과는 다름
필드 지정시 값이 없는 경우 NULL로 지정 가능
- 테이블 정의시 디폴트 값으로도 지정 가능
어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함
- field1 is NULL 혹은 field1 is not NULL
NULL이 사칙연산에 사용되면 그 결과는?
- SELECT 0 + NULL, 0 - NULL, 0 * NULL, 0/NULL
- 모두 NULL

COUNT

value
NULL
1
1
0
0
4
3

테이블 : count_test

SELECT COUNT(1) FROM count_test ➡️ 7
- 모든 행 개수 세기
SELECT COUNT(0) FROM count_test ➡️ 7
- 모든 행 개수 세기
SELECT COUNT(NULL) FROM count_test ➡️ 0
- 특정 열에서 NULL이 아닌 값을 가진 행의 개수 세기
- NULL값을 count하려면 SELECT COUNT(*) FROM count_test WHERE value IS NULL;
- COUNT(NULL) 에러 발생
SELECT COUNT(value) FROM count_test ➡️ 6
- NULL값 제외하고 count
SELECT COUNT(DISTINCT value) FROM count_test ➡️ 4
- NULL값 제외하고 중복 값 제외 COUNT

WHERE

IN
- WHERE channel in (‘Google’, ‘Youtube’)
  - WHERE channel = ‘Google’ OR channel = ‘Youtube’
- NOTIN
LIKE and ILIKE
- LIKE is a case sensitive string match. ILIKE is a case-insensitive string match
- WHERE channel LIKE ‘G%’ -> ‘G*’
- WHERE channel LIKE ‘%o%’ -> ‘*o*’
- NOT LIKE or NOT ILIKE
BETWEEN
- Used for date range matching
위의 오퍼레이터들은 CASE WHEN 사이에서도 사용가능

IN & LIKE / ILIKE

[1]
SELECT COUNT(1)
FROM raw_data.user_session_channel WHERE channel in ('Google','Facebook');
➡️ raw_data.user_session_channel 테이블에서 channel 열의 값이 'Google' 또는 'Facebook'인 행의 개수 count
[2]
SELECT COUNT(1)
FROM raw_data.user_session_channel WHERE channel ilike 'Google' or channel ilike 'Facebook';
➡️ raw_data.user_session_channel 테이블에서 channel 열의 값이 'Google'이나 'Facebook' (대소문자 구분 없이) 중 하나와 일치하는 행의 개수를 count
[3]
SELECT DISTINCT channel
FROM raw_data.user_session_channel WHERE channel ILIKE '%o%';
➡️ raw_data.user_session_channel 테이블에서 channel 열의 값 중에서 대소문자를 무시하고 '%o%' 패턴을 포함하는 행들을 선택하고, 그 중 중복을 제거한 후에 유일한 값을 반환
[4]
SELECT DISTINCT channel
FROM raw_data.user_session_channel WHERE channel NOT ILIKE '%o%';
➡️ raw_data.user_session_channel 테이블에서 channel 열의 값 중에서 대소문자를 무시하고 '%o%' 패턴을 포함하지 않는 행들을 선택하고, 그 중 중복을 제거한 후에 유일한 값을 반환

STRING Functions

LEFT(str, N)
REPLACE(str, exp1, exp2)
UPPER(str)
LOWER(str)
LEN(str)
LPAD, RPAD
SUBSTRING

ORDER BY

Default ordering is ascending
- ORDER BY 1 ASC
Descending requires “DESC”
- ORDER BY 1 DESC
Ordering by multiple columns:
- ORDER BY 1 DESC, 2, 3
NULL 값 순서는?
- NULL 값들은 오름차순 일 경우 (ASC), 마지막에 위치함
- NULL 값들은 내림차순 일 경우 (DESC) 처음에 위치함
- 이를 바꾸고 싶다면 NULLS FIRST 혹은 NULLS LAST를 사용

타입 변환

DATE Conversion:
- 타임존 관련 변환
  - CONVERT_TIMEZONE('America/Los_Angeles', ts)
  - select pg_timezone_names();
- DATE, TRUNCATE
- DATE_TRUNC
  - 첫번째 인자가 어떤 값을 추출하는지 지정 (week, month, day, ...)
- EXTRACT or DATE_PART: 날짜시간에서 특정 부분의 값을 추출가능
- DATEDIFF
- DATEADD
- GETDATE
- ...
TO_CHAR, TO_TIMESTAMP
Type Casting
1/2의 결과는?
- 0이 됨. 정수간의 연산은 정수가 되어야하기 때문
  - 분자나 분모 중의 하나를 float로 캐스팅해야 0.5가 나옴
  - 이는 프로그래밍 언어에서도 일반적으로 동일하게 동작함
- 뒤에서 예제를 살펴볼 예정
:: 오퍼레이터를 사용
- category::float
cast 함수를 사용
- cast(category as float)

이 글에서 소개된 내용은 프로그래머스 데이터분석1기에서 진행된 한기용 강사님의 온라인 강의를 참조하여 작성되었습니다.

isnotnull

이전 포스트

[21일차] SQL을 이용한 데이터 분석

다음 포스트

[22일차] SQL을 이용한 데이터 분석

프로그래머스-데이터분석1기

Redshift 론치 데모

예제 테이블

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보 (1)

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보 (2)

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보 (3)

관계형 데이터베이스 예제 - 데이터베이스와 테이블(1)

관계형 데이터베이스 예제 - 데이터베이스와 테이블(2)

관계형 데이터베이스 예제 - 데이터베이스와 테이블(3)

SQL 소개

SQL 기본

SQL DDL - 테이블 구조 정의 언어(1)

SQL DDL - 테이블 구조 정의 언어(2)

SQL DDL - 테이블 구조 정의 언어(3)

SQL DML - 테이블 데이터 조작 언어(1)

SQL DML - 테이블 데이터 조작 언어(2)

SQL 실습 환경

실습할 때 주의할 점

SELECT

SELECT

CASE WHEN

NULL

COUNT

WHERE

IN & LIKE / ILIKE

STRING Functions

ORDER BY

타입 변환

Type Casting

[21일차] SQL을 이용한 데이터 분석

[23일차] SQL을 이용한 데이터 분석

0개의 댓글