[프로그래머스] 데이터 엔지니어 study - 2주차

0. 기억할 부분

• 현업에서 깨끗한 데이터는 존재 X
  • 항상 의심하고, 실제 레코드 몇개 살펴보기(노가다)
• 데이터 품질을 체크하는 버릇 기르기 - unit test 형태로 간단하게 만들기
  • 중복된 레코드 확인
  • 최근 데이터 여부 확인(freshness)
  • Primary key uniqueness 지켜지는지 확인
  • 값이 비어있는 컬럼 확인
• 어느 시점이 되면 데이터 테이블이 무수히 많아짐
  • 회사성장과 관련
  • 메타데이터 관리
• 이 시점부터는 데이터 Discovery 문제 발생
  • 무슨 테이블에 내가 원하는 정보가 있는지
  • 누구한테 물어봐야 하는지
  • 문제해결: 검색 서비스 이용, DataHub, Amundsen

1. SQL 기본

1-1. 배경

• 1970년대 IBM에서 개발, Structured Query Langage
• 2000년대 hadoop의 증가와 빅데이터 시대가 오며 인기 주춤
• hadoop의 map reduce가 생산성이 떨어지며 다시 인기
• hive, presto, spark sql 등 분산 시스템에서 SQL 등장 배경이 됨

1-2. 장, 단점

• 데이터 크기에 상관없이 구조화 된 데이터 처리에 좋음
• 비구조화된 데이터 처리는 힘듦
• 빅데이터 DW에서는 Primary key 지정 X

1-3. 기본사항

• 끝이 “ ; “
• 주석: - -, /* - - */
• 키워드는 대문자를 사용하는등 나름대로 format 필요 → 회사에 따라 다름
• 테이블, 필드이름 명명규칙 정하는게 중요 → 회사에 따라 다름

2. SQL 활용

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

• CREATE
  • CTAS: CREATE TABLE name.table AS SELECT → 비어 있는 테이블 만들고
  • CTAS 잘하면 SUMMARY 테이블 잘 만들 수 있음
• DROP
  • DROP TABLE IF EXISTS table_name;
  • IF EXISTS로 없는 테이블 제거시 발생하는 오류 방지
  • *DML) DELETE FROM은 테이블 존재, 레코드만 지움
• ALTER
  • 새로운 컬럼 추가: ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
  • 기존 컬럼 이름변경: ALTER TABLE 테이블이름 RENAME 현재필드이름 TO 새필드이름;
  • 기존 컬럼 제거: ALTER TABLE 테이블이름 DROP COLUMN 필드이름;
  • 테이블 이름 변경: ALTER TABLE 현재테이블이름 RENAME TO 새테이블이름;

2-2. DML(테이블 레코드 조작 언어)

• SELECT: DB에 있는 데이터를 조회 및 검색, RETRIEVE
  • WHERE(IN, LIKE/ILIKE, BETWEEN)
  • GROUP BY: DAU, WAU, MAU 구하는데 쓰임
  • ORDER BY: 디폴트 오름차순, DESC
    • ORDER BY 1 DESC, 2, 3;
    • ORDER BY 1 DESC NULLS LAST; — NULL값 맨 뒤로
• INSERT INTO: 레코드 추가
  • COPY 보다 느림
  • INSERT INTO TABLE SELECT * FROM
  • 필드 형식만 지정해주면 CTAS 보다 괜찮음
• UPDATE FROM: 레코드 필드 값 수정
• DELETE FROM: 레코드 삭제

2-3. 기타함수 및 NULL

• STRING 함수
  • LEFT(str, N), REPLACE(str, exp1, exp2), UPPER, LOWER, LEN, LPAD, RPAD, SUBSTRING
• DATE conversion
  • CONVERT_TIMEZONE(’America/Los_Angeles’, ts)
  • DATE_TRUNC, EXTRACT, DATE_PART
  • DATEDIFF, DATEADD, GET_CURRENT
• Type casting
  • cast나 :: 사용
  • category::int, cast(category as int)
  • TO_CHAR, TO_TIMESTAMP
• NULL
  • 값이 없음을 의미, python nan
  • 0이랑 비어있는 STRING 이랑은 다름
  • LEFT 조인시 매칭되는 값 확인하는데 유용
  • NULL값 변환: COALESCE, NULLIF
  • *NULL로 나누는거, 0으로 나누는거 차이?

2-4 JOIN

 

SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
WHERE A.ts >= '2019-01-01';

 

• ___: INNER, FULL, LEFT, RIGHT, CROSS
• JOIN시 고려사항
  • 스타 스키마에서는 항상 필요
  • Primary key uniqueness 보장되는지 확인, 중복 레코드 없는지 확인 (중요!)
  • JOIN하는 테이블 간의 관계 명확히
    • one to one: 완전, 한쪽 부분집합
    • one to many: 중복 체크 중요
    • many to many: 잘 없는 경우로 위의 방식으로 바꿔서 JOIN이 안전
  • 어느 테이블을 FROM의 베이스로 잡을지 정해야함

3. SQL 응용

3-1. UNION & EXCEPT

• UNION
  • 여러개의 테이블 or SELECT 결과를 하나로 합쳐줌
  • 중복 제거
  • *UNION vs UNION ALL
• EXCEPT
  • MINUS
  • 하나의 결과에서 다른 결과를 빼줌
• INTERSECT
  • 여러개의 SELECT문에서 같은 레코드만 찾아줌

3-2. COALESCE & NULLIF

• COALESCE(exp1, exp2, …)
  • exp1부터 NULL값 아닌거 찾음
  • 모두 NULL이면 NULL값 리턴
• NULLIF(exp1, exp2)
  • exp1, exp2 같으면 NULL값 리턴

3-3. DELETE FROM vs TRUNCATE

• DELETE FROM table_name WHERE ~
  • 테이블 모든 레코드 제거
  • WHERE 사용해서 특정 레코드만 제거 가능
• TRUNCATE table_name
  • 테이블 모든 레코드 제거
  • DELETE FROM보다 속도가 빨라 전체 레코드 삭제시 유리
  • 단점: WHERE 지원 x, Transaction 지원 x

3-4. SUB query(CTE)

• SELECT 하기전 임시 테이블 생성
• WITH table_a AS(SELECT DISTINCT oaid from table_b), temp AS (SELECT …
• 임시 테이블 table_a 생성

3-5. Window 함수

• function(exp) OVER ([PARTITION BY exp][ORDER BY exp])
• ROW_NUMBER, FIRST_VALUE, LAST_VALUE
• Math functions: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

3-6. Json Parsing fuctions

• Json 포맷 아는 경우에 사용가능
• Json string을 입력값으로 받아 특정 필드 값 추출
• SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6');

숙제

• 1.숙제 코랩 실습
• 2.row_number() over partition 이용 vs first_value, last_value
• join에 따른 다른결과, inner, left
• 4.매출