[Programmers]실리콘밸리에서 날아온 DE 스타터 키트2️⃣주차

🈯 숙제 해설

1주차 복습

• 타임스탬프 변환하는 방법

  1. TO_CHAR(테이블명, 'YYYY-MM')
  2. LEFT(테이블명, 자릿수)
  3. DATE_TRUNC('날짜키워드', 테이블명)
  4. SUBSTRING(테이블명, 시작자리, 끝자리)

• DATE_TRUNC의 예시

select date_trunc('month', '2019-04-01 12:12:12::timestamp')   
->  2019-04-01 00:00:00
select date_trunc('month', '2019-04-05 12:12:12::timestamp')   
->  2019-04-01 00:00:00

• 타임스탬프와 관련해서 extract라는 것도 있음. 참고

• ORDER BY와 GROUP BY에 필드명

  • GROUP BY 1 : 첫 번째 필드로 그룹
  • GROUP BY 필드명 : 필드명으로 그룹
  • 데이터가 커지면 첫 번째 방법으로 하면 헷갈리니 때에 따라 잘 구분해서 쓰자.

• COUNT함수의 이해

  • COUNT(1) : NULL값도 셈
  • COUNT(필드명) : NULL 안 셈
  • COUNT(DISTINCT 필드명) : NULL 안 세고 고유한 값만 셈

🧐 2주차

기본 SQL

• DDL : Data Definition Language : CREATE TABLE, DROP TABLE, ALTER TABLE
• DML : Data Manipulation Language : SELECT, INSERT INTO

🉐 데이터를 다룰 때 특별히 주의할 것 🉐

1. 의데이터증 : 데이터를 항상 의심하라. 노가다로 체크.
2. 데이터의 품질을 체크하라.
   • 중복된 레코드 체크
   • 최근 데이터 있는지 확인
   • Primary key의 uniqueness 체크
   • Null값 체크
   • Unit test도 좋은 방법
3. 너무 많은 테이블은 Data Discovery 문제를 야기. 때 되면 잘 정리하자.
4. 테이블/필드에 관한 컨벤션을 정하는 것도 중요.

• Redshift를 포함한 DW에서는 Primary key uniqueness를 보장하지 ❌.

• CTAS란 결과를 가지고 바로 테이블 만들기 : CREATE TABLE 테이블명 AS SELECT

• DROP TABLE는 테이블 삭제
  DELETE FROM은 레코드 삭제
  TRUNCATE TABLE는 안의 레코드 전부 삭제. 참고

• ALTER : 필드 추가/이름 변경/제거, 테이블 이름 변경

• LIKE는 대소문자 구분O / ILIKE는 대소문자 구분X

• LPAD와 RPAD는 왼쪽이나 왼쪽에 0 채우기(python의 zfill)

• INSERT INTO는 한 줄씩 레코드 넣기 / COPY는 파일로 bulk insert(대용량을 한 번에⭕).

• Redshift의 경우 NULL을 가장 큰 값으로 취급하기 때문에 맨 뒤로 빼로 싶으면 NULLS LAST 명령어 추가.

• Type Cast : :: 붙여주면 됨. ex) 필드명::int

• NULL 취급에 주의! 특히 IS TRUE와 IS NOT FALSE는 같지 ❌.

• JOIN :

  • 중복 레코드가 없어야 하고, Primary key의 uniqueness가 보장되는지 체크❗❗

2주차

심화 SQL

• UNION은 복수의 SELECT를 합쳐서 볼 수 있음.

  • UNION은 합집합.
    • UNION은 중복값 제거, UNION ALL은 중복값 포함.
  • EXCEPT는 차집합.
    • 하나의 SELECT 결과에서 다른 SELECT 결과를 제외.
  • INTERSECT는 교집합.
    • 복수의 SELECT 중에서 겹치는 것만 보여줌.

• NULL 처리 :

  • ISNULL (A,B) : A가 NULL이면 B로 출력
  • NULLIF (A,B) : A,B 같으면 NULL, 아니면 A 출력
  • COALESCE(A,B,C....) : 데이터 중 NULL이아닌 첫번째 값 반환

• WINDOW

  SELECT 
  	WINDOW_FUNCTION(필드명) 
    	OVER(
    	PARTITION BY 그룹지을 필드명 
    	ORDER BY 정렬할 필드명
    	) 
  FROM 테이블명

  • 하나의 창(window) 프레임 안에 넣어서 그 안에서 집계나 순위를 계산.
  • 윈도우 프레임 : ORDER BY랑 집계함수를 조합할 때 쓰이는 하나의 창. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 이런식으로 써줘야 함.
  • 참고

• SUB Query : 임시 테이블 만들어서 사용. ex) WITH 테이블 이름 AS (SELECT 어쩌구)

2주차

실습

• 중복 레코드 확인

# 두 번째 필드가 1보다 높으면 중복값 있다는 뜻
SELECT 확인할 필드명, COUNT(1) 
FROM 테이블
GROUP BY 확인할 필드명

• TIMESTAMP 필드가 있으면 MIN과 MAX로 항상 확인

• 예약어나 공백이 들어있는 필드명은 ""에 넣어주면 됨. 단, SELECT에서 부를 때도 ""를 붙여서 불러야함.

• NULL은 기본연산자가 아니라 IS NULL과 IS NOT NULL써야함.

• NULL 연산하면 항상 NULL