1. WITH
1.0. 쿼리를 작성하다 보면
다음처럼 쿼리가 복잡해지는 경우가 있다
```
SELECT
col1, col2
FROM (
SELECT
col1, col2, col3
FROM Table
)
만약 이 쿼리가 다른 곳에서도 필요하면 복사 -> 붙여넣기해서
SELECT
*
FROM (
SELECT
col1, col2
FROM (
SELECT
col1, col2, col3
FROM Table
)
)
.
.
.
-> 이러한 작업이 반복되면
-> 쿼리는 복잡해지고
-> 가독성이 떨어지게 된다=> WITH 구문을 활용해서 가독성을 높여줄 수 있다!
1.1. WITH 구문
- WITH 절에 있는 쿼리문에 이름을 부여해서 -> 필요 시 재사용
- WITH 문의 테이블은 하나의 쿼리문이 실행되는 동안까지만 지속되는 일회성 테이블
-> WITH 문을 CTE(Common Table Expression)라고도 부른다
- 특징
- 쿼리문의 이름을 정의한 후, AS를 꼭 사용해야 한다
- 정의한 이름은 최대한 명시적인 것이 좋다
- SELECT 절 없이 쓴 WITH 문 -> 그저 테이블 정의
SELECT 절을 같이 써야 -> 데이터 탐색 및 추출을 할 수 있다
WITH sample AS ( SELECT id, player1_id, player2_id, FROM basic.battle )SELECT * FROM sample
- WITH 문을 사용한 참조는 계속 사용 가능하다
WITH sample AS ( SELECT id, player1_id, player2_id, FROM basic.battle ), sample2 AS ( SELECT id, name, hometown FROM basic.trainer WHERE id = 3 ), sample3 AS ( SELECT * FROM sample2 )SELECT * FROM sample3
- WITH 문으로 만든 테이블을 다른 테이블과 JOIN하는 것도 가능하다
WITH sample_tp AS ( SELECT trainer_id, catch_datetime, status FROM basic.trainer_pokemon ), sample_tp_t AS ( SELECT t.*, tp.* FROM basic.trainer as t LEFT JOIN sample_tp as tp ON tp.trainer_id = t.id )SELECT * FROM sample_tp_t WHERE hometown = "Seoul"
WITH 문을 통해 쿼리의 가독성을 높일 수 있지만,
회사에서의 데이터는 시간의 흐름에 따라 계속 업데이트 되기 때문에
-> WITH 문으로 저장한 쿼리를 계속해서 갱신시켜 줘야 한다
=> 쿼리를 계속 갱신시켜주거나
=> 속도가 느려도 된다면, VIEW를 사용하기도 한다
2. VIEW
- 긴 쿼리를 가상 테이블로 저장해서 FROM 절에서 사용 가능
- WITH 문: 쿼리문이 실행될 때 임시로 사용
- VIEW : 데이터셋에 저장해서 여러 사람이 활용 가능
- VIEW는 일반적으로 테이블로 저장된 경우보다 느리지만, 쿼리문을 쉽게 바꿀 수 있어 작업 초반에 사용하기 유용
- 추후 쿼리문이 고정되면 배치 파이프라인을 구성해 테이블을 저장하는 것이 좋음
VIEW로 저장하기 - 쿼리를 실행한 후, 저장->뷰 저장을 클릭
- 저장할 데이터세트와 테이블 이름을 지정하고 저장을 클릭
이제 저장된 뷰(sample_view) 확인 가능. Table과 VIEW는 이모티콘이 다른 것을 확인할 수 있다
VIEW를 클릭한 후, 세부정보 클릭하면 쿼리문 확인 가능. 쿼리 수정도 가능
3. PARTITION
쿼리의 가독성을 챙기기 위해 WITH 문을 사용한다면,
비용을 줄이고, 데이터를 잘 관리하기 위해 PARTITON으로 나뉜 테이블을 사용한다
(테이블 저장에 따라 PARTITION이 없는 테이블도 있다)
-
PARTITION 사용 시 이점
- 쿼리 성능 향상
: 전체 데이터를 스캔하는 것보다 파티션을 설정한 곳만 스캔하는 것이 빠르다 - 데이터 관리 용이
: 특정 일자의 데이터를 모두 변경하거나 삭제해야 하면 파티션을 설정해서 삭제할 수 있다 - 쿼리 탐색 비용 감소
: 파티션에 해당되는 데이터만 스캔해서 비용을 줄일 수 있다
(빅쿼리 탐색 비용: 1TB 당 $6)
- 쿼리 성능 향상
-
PARTITION을 나누는 기준
1) Ingestion time (수집 시간): 데이터 수집(로드) 날짜를 기준으로 파티션 나눔
2) DATE/TIMESTAMP : Date나 Timestamp로 파티션 나눔 - 자주 사용
3) Integer (정수) : 정수 값을 기준으로 파티션 나눔
PARTITION으로 나뉜 테이블 확인하기 - battle 테이블은 Partition을 나눈 테이블이다 - 그냥 Table과 Partition을 나눈 테이블의 이모티콘이 다른 것을 알 수 있다
PARTITION을 나눈 테이블 사용하기 - Partition 테이블 클릭 -> 세부정보 -> 파티션 정보 먼저 확인
- 쿼리 -> 새 탭에서 열기 클릭
- 다음과 같은 쿼리가 자동으로 생성
- 쿼리를 조금 더 보기 좋게 가공
: WHERE 절에 건 PARTITION 필터
- DATETIME_ADD(datetime, INTERVAL 1 DAY) : datetime에 +1day를 한 DATETIME을 추출
best.DA