데브코스(DE)/데이터 웨어하우스와 SQL, 데이터분석

02. 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습(2)

jxwxnk 2024. 4. 24. 00:12
반응형
학습 주제:  SQL(DDL과 DML), SQL 실습, Redshift, SELECT 소개

SQL 소개

     : Reshift 중심으로 DDL과 DML 소개

  • 기본
  • 다수의 SQL문 실행시, 세미콜론으로 분리

         - SQL문1; SQL문2; SQL문3;

  • SQL 주석

         - --: 인라인 한줄짜리 주석

         - /*--*/: 여러 줄에 걸쳐 사용 가능한 주석

  • SQL 키워드는 대문자를 사용(권장)
  • 팀 프로젝트의 경우 공통 포맷, 테이블/ 필드 이름 명명 규칙 정하는 것 중요

         - 단수형 vs. 복수형

            ex) User vs. Users

         - _(언더바) vs. CamelCasing

            ex) user_session_channel vs. UserSessionChannel

    

  • DDL: 테이블 구조 정의 언어
  • CREATE TABLE

         - Primary key uniqueness: Primary key 속성 지정 가능하나 무시됨

            : 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)
    );

 

  • DROP TABLE

         - DROP TABLE table_name: 존재하지 않는 테이블을 지우려는 경우 에러 발생

         - DROP TABLE IF EXISTS table_name;

         - DROP TABLE : 테이블 구조 자체를 삭제

              vs. DELETE FROM: 조건에 맞는 레코드 삭제(테이블 자체는 존재함)

 

  • ALTER TABLE

         - 새로운 컬럼 추가: ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;

         - 기존 컬럼 이름 변경: ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름;

         - 기존 컬럼 제거: ALTER TABLE 테이블이름 DROP COLUMN 필드이름;

         - 테이블 이름 변경: ALTER TABLE 현재테이블이름 RENAME to 새필드이름

 

  • DML: 테이블 데이터 조작 언어
  • SELECT: 레코드 질의 언어

         - SELECT FROM: 테이블에서 레코드와 필드를 읽어옴

         - WHERE: 레코드 선택 조건 지정

         - GROUP BY: 데이터 그룹화(그룹화된 데이터 SELECT할 때)

             : DAU, WAU, MAU 계산은 GROUP BY 필요로 함

         - ORDER BY: 레코드 순서 지정(ASC: 오름차순, DESC: 내림차순)

         - 다수의 테이블 조인해서 사용하기도 함

  • 레코드 수정 언어

         - INSERT INTO: 테이블에 레코드 추가(삽입)

         - UPDATE FROM: 테이블에 레코드 필드 값 수정

         - DELETE FROM: 테이블에서 레코드 삭제(행 단위 작업), 트랜잭션 로그 생성 O -> 롤백 가능

               vs. TRUNCATE: 테이블 전체 비우기(테이블 단위 작업), 트랜잭션 로그 생성 X -> 롤백 불가능, 삭제 후 용량 감소

 

데이터 살펴보기

  • 데이터 품질 확인 사항

         - Unit test의 형태로 함수를 이용해 아래의 사항 확인을 자동화 가능

  • 중복 레코드의 존재여부
  • 최근 데이터의 존재여부(freshness)
  • Primary key uniqueness가 지켜지는지 확인
  • 값이 비어있는 컬럼이 존재하는지 확인
  • 다수의 테이블이 존재할 때

         - 회사가 성장할수록 테이블 또한 늘어남

         - 중요 테이블 파악 및 테이블 메타 정보 관리 중요

SELECT

     : 테이블에서 레코드를 읽어오는데 사용

     : WHERE을 통해 가져올 레코드의 조건 지정

  • 기본 틀
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
ORDER BY 필드이름[ASC|DESC] --필드 이름 대신 숫자 사용 가능
LIMIT N; --테이블 위에서부터 몇 개 데이터 가져올지

 

  • 다양한 SELECT 예시
  • SELECT *: 테이블에서 조건에 해당하는 레코드의 모든 속성에 대하여 데이터 가져오기
SELECT *
FROM raw_data.user_session_channel;

 

  • SELECT [속성; 열]: 테이블에서 조건에 해당하는 레코드의 [속성; 열]에 대하여 데이터 가져오기
SELECT userId, sessionId, channel
FROM raw_data.user_session_channel;

     

  • LIMIT [N]: SELECT 결과 행 10개로 제한
SELECT *
FROM raw_data.user_session_channel
LIMIT 10;

 

  • DISTINCT [속성; 열]: 속성에 대하여 레코드의 중복값 제거
SELECT DISTINCT channel --채널 이름 중복 제거
FROM raw_data.user_session_channel;

 

  • COUNT (1): NULL이 아닌 모든 행의 수를 COUNT

         - 채널별 존재하는 행의 수 COUNT

SELECT channel, COUNT(1)    -- 채널별 카운트하고 싶은 경우
FROM raw_data.user_session_channel
GROUP BY 1;

 

         - 테이블에 존재하는 모든 행의 수 COUNT

SELECT COUNT(1)    -- 테이블의 모든 레코드 수 카운트
FROM raw_data.user_session_channel;

 

         - 테이블에 존재하는 모든 행의 수 COUNT

SELECT COUNT(1)    
FROM raw_data.user_session_channel
WHERE channel = 'Facebook'; -- channel 이름이 Facebook인 경우의 레코드 카운트

 

  • CASE WHEN

         - 필드 값 변환을 위해 사용 가능

         - CASE WHEN 조건 THEN A ELSE B END 필드이름: 조건이 참일 때 A, 거짓이면 B

SELECT
  channel,
  CASE
    WHEN channel in ('Facebook', 'Instagram') THEN 'Social-Media'
    WHEN channel in ('Google', 'Naver') THEN 'Search-Engine'
    ELSE 'Something-Else'
END channel_type
FROM raw_data.user_session_channel;

 

  • NULL

출처: https://namu.wiki/w/Null%28%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%B0%8D%20%EC%96%B8%EC%96%B4%29

         - 값이 존재하지 않음("", 0과는 다름)

         - 필드 지정시 값이 없는 경우 NULL로 지정(테이블 정의시 default 값으로도 지정 가능)

 

         - NULL 값 판단을 위한 문법

             : field1 is NULL - field1이 NULL값을 가지고 있는지 확인; 해당 필드에 대해 NULL 값을 가진 행 선택

             : field1 is  not NULL - field1이 NULL이 아닌 값을 가지고 있는지 확인; 해당 필드에 대해 NULL이 아닌 값을 가진 행 선택

 

         - NULL의 사칙 연산

             : NULL + 0 - NULL 반환

             : NULL - 0 - NULL 반환

             : NULL * 0 - NULL 반환

             : 0 / NULL - 정의 불가(에러 발생, NULL 반환)

 

  • COUNT

COUNT 예시 테이블

  • SELECT COUNT(1) FROM count_test: 7

         - 모든 레코드의 수 COUNT

  • SELECT COUNT(0) FROM count_test: 7

         - 모든 레코드의 수 COUNT; 0은 NULL이 아님

  • SELECT COUNT(NULL) FROM count_test: 0

         - 인자 값이 NULL이면 레코드 수 COUNT X

  • SELECT COUNT(value) FROM count_test: 6

         - NULL 값 제외하고 COUNT

  • SELECT COUNT(DISTINCT value) FROM count_test: 4

         - DISTINCT value: 레코드 중복 제외 -> NULL, 1, 0, 4, 3

         - COUNT(DISTINCT value): NULL 값 제외하고 COUNT -> 1, 0, 4, 3 ...총 4개

 

  • WHERE

     : 조건절

     : 아래 연산자들 CASE ~ WHEN에서도 사용 가능

  • IN

         - WHERE channel IN ('Google', 'Youtube'): channel 열의 값이 'Google' 또는 'Youtube'와 일치하는 가 확인

             : WHERE channel = 'Google' OR channel = 'Youtube'와 동일하나 IN 사용하면 더 간결하게 표현 가능

         - NOT IN: 불일치 확인

 

  • LIKE

         - 특정 패턴을 가진 문자열 찾기

         - '%': 임의의 문자열을 나타냄

             : WHERE channel LIKE 'G%' - G로 시작하는 데이터(문자열) 검색

             : WHERE channel LIKE '%E' - E로 끝나는 데이터(문자열)검색

             : WHERE channel LIKE '%o%' - o를 포함하는 데이터(문자열) 검색

             : NOT LIKE - 해당 조건의 문자열을 포함하지 않는 데이터(문자열) 검색

         - '-': 하나의 문자를 나타냄

 

  • BETWEEN

         - 비교 연산자, 주어진 범위 내 값 선택

             : WHERE column_name BETWEEN 10 AND 20 - column_name 열의 값이 10과 20 사이인 행 선택(경계값도 포함)

 

  • STRING Functions
  • LEFT(str, N): 문자열 'str'의 왼쪽부터 N개의 문자 반환
  • REPLACE(str, exp1, exp2): 문자열 'str'에서 'exp1'과 일치하는 부분 'exp2'로 대체하여 새 문자열 반환
  • UPPER(str): 문자열 'str'의 모든 문자를 대문자로 변환한 새 문자열 반환
  • LOWER(str): 문자열 'str'의 모든 문자를 소문자로 변환한 새 문자열 반환
  • LEN(str): 문자열 'str'의 길이(문자 수) 반환
  • LPAD(str, N, pad_string): 문자열 'str'의 왼쪽에 'pad_string'을 추가해 길이가 N이 되도록 함
  • RPAD(str, N, pad_string): 문자열 'str'의 오른쪽에 'pad_string'을 추가해 길이가 N이 되도록 함
  • SUBSTRING(str, start, length): 문자열 'str'에서 시작 위치 'start'부터 지정된 길이인 'length'만큼의 부분 문자열 반환
  • ORDER BY
  • ORDER BY 1 ASC: 속성 1 기준으로 오름차순 정렬
  • ORDER BY 1 DESC: 속성 1 기준으로 내림차순 정렬
  • ORDER BY 1, DESC 2, 3: 속성 1기준으로 오름차순 정렬 후, 속성 2기준 내림차순 정렬 후, 속성 3 기준 내림차순 정렬
  • NULL 값의 순서

         - 오름차순(ASC)일 경우: NULL값 가장 마지막에 위치

         - 내림차순(DESC)일 경우: NULL값 가장 처음에 위치

         - NULLS FIRST, NULLS LAST를 통해 NULL 값 순서 바꾸기 가능

 

  • 데이터 타입 변환: DATE
  • TIMEZONE 관련 변환

         - CONVERT_TIMEZONE('America/Los_Angeles', ts): 타임존 변환

                 : America/Los_Angeles - 변환하려는 타임존

                 : ts - 타임 스탬프 값

         - select pg_timezone_names(): 사용 가능한 타임존 목록 반환

  • DATE: 날짜 데이터 유형의 값 생성 또는 날짜 부분 추출
  • TRUNCATE: 숫자 값을 지정된 자릿수로 반올림 또는 버림하여 반환
  • DATE_TRUC: 날짜 또는 시간 값을 지정된 단위로 자름, 해당 부분 이하 모든 값들은 0으로 만듦
  • EXTRACT or DATE_PART: 날짜 또는 시간 값에서 특정 필드(ex. 연도, 월, 일) 값 추출
  • DATEDIFF: 두 날짜 또는 시간 값의 차(일, 시간, 분 등) 계산
  • DATEADD: 날짜 또는 시간 값에 특정 시간 간격(일, 시간, 분 등) 추가
  • GETDATE: 현재 날짜와 시간 반환(현재 시스템 기준)
  • TO_CHAR(value, format): 숫자, 날짜 또는 시간 값을 문자열로 반환
  • TO_TIMESTAMP(string, format): 문자열을 타임스탬프 값으로 변환
  • 데이터 타입 변환: 숫자
  • :: 오퍼레이터: category::float
  • cast 함수: cast(category as float)
  • 예시) 1/2 => 0, 1/2::float =>0.5
공부하며 느낀점

 

NULL 연산, COUNT 함수와 같은 평소에 헷갈리는 것들을 확실히 정리할 수 있어서 좋았다. 또 SELECT에 사용되는 다양한 함수들의 사용법을 실습을 통해 확인할 수 있었다.

반응형