데이터분석

22. SQL 코드 작성

장수우 2023. 12. 29. 17:13
학습주제
  • 예제 테이블 소개
  • SQL 소개 (DDL, DML)
주요 메모 사항 소개
  • 관계형 데이터베이스 예제
    • 웹서비스 사용자 / 세션 정보
      1. 사용자 ID : 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
      2. 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
        - 하나의 사용자는 여러개의 세션을 가질 수 있다.
        - 보통 세션을 만들어낸 접점(경유지)를 채널이랑 이름으로 기록해둔다.
        - 마케팅 관련 기여도 분석에 사용한다.
        - 세션이 생긴 시간도 기록한다.
    • 세션 정보를 바탕으로 다양한 데이터 분석과 지표 설정이가능하다
      1. 마케팅관련,  사용자 트래픽 관련
      2. DAU, WAU, MAU  등의 일주일별 Active User 차트
      3. Marketing Channel Attribution 분석
        - 어느 채널에 광고를 하는 것이 가장 효과적인가?
    • 예시
      1. 사용자 ID 100번 : 총 3개의 세션
      2. 세션 1 : 구글 광고 타고 방문 09:30AM (구글 키워드 광고로 시작한 세션)
                      ↓ - 상품 광고 클릭   09:31AM
                      ↓ - 상품 리뷰 페이지 클릭 09:33AM
      3. 세션 2 : 페이스북 타고 방문 09:45AM(페이스북 광고를 통해 생긴 세션)
                      ↓ - 다른 상품 광고 클릭 09:47AM
      4. 세션 3: 네이버 광고 타고 방문10:30AM(네이버 광고를 통해 생긴 세션)
                      ↓ - 상품 구매 10:35AM
        예시 1
  • SQL 기본 (Redshift 중심으로)
    • 다수의 SQL 문을 실행 한다면 세미콜론으로 분리 필요
      - SQL문1; SQL문2; SQL문3;
    • SQL 주석
      - '--' : 인라인 한줄짜리 주석, 자바에서 //에 해당
      - /*--*/ : 여러 줄에 걸쳐 사용 가능한 주석
    • SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅이 필요하다.
      - 팀 프로젝트라면 팀에서 사용하는 공통 포맷이 필요
    • 테이블 / 필드 이름의 명명 규칙을 정하는 것이 ㅈ ㅜㅇ요
      - 단수형 vs 복수형 (User vs. Users)
      - '__' vs. CamelCasing (user_session_channel vs. UserSessionChannel)
  • SQL DDL - 테이블 구조 정의 언어
    • CREATE TABLE
      1. Pirmary Key 속성을 지정할 수 있으나 무시된다 (Primary key uniqueness)
        - Big Data 데이터웨어하우스에서는 지켜지지 않는다. (Redshift, Snowflake, BigQuery)
      2. CTAS : CREATE TABLE table_name AS SELECT
        - vs. CREATE TABLE and then INSERT
      3. CREATE TABLE raw_data.user_sessiong_channel(
             userId int,
             sessionId varchar(32) primary key,
             channel varchar(32)
        );
    • DROP TABLE
      1. DROP TABLE table_name;
        - 없는 테이블을 지우려고 하는 경우 에러를 낸다.
      2. DROP TABLE IF EXISTS table_name;
      3. vs. DELETE FROM
        - DELETE FROM은 조건에 맞는 레콛드들을 지운다.(테이블 자체는 존제한다.)
    • ALTER TABLE
      1. 새로운 컬럼을 추가한다
        - ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
      2. 기존 컬럼 이름 변경
        - ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름;
      3. 기존 컬럼 제거
        - ALTER TABLE 테이블 이름 DROP COLUMN 필드이름;
      4. 테이블 이름 변경
        - ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;
  • SQL DML - 테이블 데이터 조작언어
    • 레코드 질의 언어(SELECT)
    • SELECT 필드이름1, 필드이름2,...
      FROM 테이블이름
      WHERE 선택조건
      GROUP BY 필드이름1, 필드이름2,...
      ORDER BY 필드이름[ASC|DESC] -- 필드 이름 대신에 숫자 사용 가능
      LIMIT N;
      1. SELECT FROM : 테이블에서 레코드와 필드를 읽어오는데 사용
      2. WHERE를 사용해서 레코드 선택 조건을 지정
      3. GROUP BY를 통해 정보를 그룹 레벨에서 뽑는데 사용하기도 한다.
        - DAU, WAU, MAU 계산은 GROUP BY를 필요로 한다.
      4. ORDER BY를 사용해서 레코드 순서를 결정하기도 한다.
      5. 보통 다수의 테이블을 조인해서 사용하기도 한다.
    • 레코드 수정 언어
      • INSERT INTO : 테이블에 레코드를 추가하는데 사용
      • UPDATE FROM : 테이블 레코드 필드 값 수정
      • DELETE FROM : 테이블에서 레코드를 삭제
        vs. TRUNCATE
  • 실습에 들어가기에 앞서 기억할 점
    • 현업에서 깨끗한 데이터란 존재하지 않는다.
      • 항상 데이터를 믿을 수 있는지 의심
      • 노가다로 전부 살펴보는것이 효과적이다
    • 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요하다
      • 중복된 레코드 체크하기
      • 최근 데이터 존재 여부 체크하기
      • Primary key uniqueness 가 지켜지는지 체크하기
      • 값이 비어있는 컬럼들이 있는지 체크하기
      • 위크 체크는 코딩의 unit test 형태로 만들어 매번 쉽게 체크해볼 수있 다.
    • 어느 시점이 되면 너무나 많은 테이블들이 존재하게 된다.
      • 회사 성장과 밀접한 관련이 있다.
      • 중요 테이블들이 무엇이고 그것들의 메타정보를 잘 관리하는 것이 중요하다.
    • 그 시점부터는 Data Discovery 문제들이 생겨남
      • 무슨 테이블에 내가 원하고 신뢰할 수 있는 정보가 들어있나?
      • 테이블에 대해 질문을 하고 싶은데 누구에게 질문을 해야하나?
    • 이 문제를 해결하기 위한 다양한 오픈소스와 서비스들이 출현
      • DataHub (LinkedIn), Amundsen (Lyft), ...
      • Select Star, DataFrame, ...
  • CASE WHEN 
    • 필드 값의 변환을 위해 사용 가능하다.
      1. CASE WHEN 조건 THEN 참일 떄 값 ELSE 거짓일떄 값 END 필드이름
      2. 여러 조건을 사용하여 변환 가능하다.
  • NULL 이란?
    • 값이 존재하지 않음을 나타내는 상수, 0 혹은 " " 과는 다르다.
    • 필드 지정시 값이 없는 경우 NULL로 지정 가능
      - 테이블 정의시 디폴트 값으로도 지정 가능
    • 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 한다.
      - field 1 is NULL or field 1 is not NULL
    • NULL 이 사칙연산에 사용되면 결과도 NULL
  • WHERE 
    • IN
    • LIKE and ILIKE : 문자열 매칭
    • BETWEEN 
    • 위 3개 오퍼레이터들은 CASE WHEN 사이에서도 사용가능하다.
  • STRING Functions
    • LEFT (str, N)
    • REPLACE (str, exp1, exp2) 
    • UPPER(str) : 대문자 변경
    • LOWER(str) : 소문자 변경
    • LEN(str) : 길이 추출
    • LPAD, RPAD : (Left, Right) 문자 붙히기 
    • SUBSTRING
  • NULL 값 순서는 ?
    • 오름차순(ASC) 일 경우, 마지막에 위치
    • 내림차순(DESC) 일 경우, 처음에 위치
    • 바꾸고 싶다면 NULLS FIRST, NULLS LAST를 사용한다.
  • 타입 변환
    • DATE Conversion
    • To_CHAR, TO_TIMESTAMP
  • Type Casting
    • 1/2 결과는?
      = 0 이된다. 정수간의 연산은 정수가 되어야하기 때문이다.
      - 분자나 분모중의 하나를 float로 캐스팅 해야 0.5가 나온다.
    • "::" 오퍼레이터를 사용
      - category::float
    • cast 함수를 사용한다.
      - cast(category as float)

 

 

 

공부하며 어려웠던 내용
  • 개념을 익히고 사용하는데 어려웠다 개념을 아는 것과 활용하는 것은 다른 것 같다. 꾸준한 연습으로 실력 향상시켜야 한다고 생각이 들었다. :)
반응형