1주차
SESSION table 생성 쿼리에서 temp 빼면 sandbox용 테이블 만드는거랑 동일함
Sandbox 테이블 은 데일리로 자동 업데이트 되나? 안됨. / 생성 개수에 제한은 없나? 제한 없음.
Partition by : 뒤에 나오는 칼럼 기준으로 데이터를 구분
Cluster by : 뒤에 나오는 칼럼 기준으로 데이터를 정렬
partition과 cluster를 활용하면, 두 데이터를 조인을 시킬 때 데이터 베이스가 검색을 할 때 검색 속도가 높아짐
그럼 결과적으로 두개를 뭐를 설정을 해도 결과는 똑같다? 그렇긴 한데 구분과 정렬이 잘되는 컬럼을 넣는게 좋긴 함 (partition은 주로 날짜, cluster는 ID같이 JOIN할 때 기준값으로 사용하는 값 등)
데이터 테이블 처음에 구축할 때도 partition이랑 cluster를 어떻게 구성하느냐에 따라서 데이터 계산 속도가 달라지므로 고민하는 부분 중 하나임
sandbox 테이블 생성할 때 이름
insert랑 delete는 이미 만들어진 데이터 테이블을 테이블 전체로 날리는 게 아니라, 일자별로 쌓는 용도로 사용할 수 있음.
데이터 분석시에는 쓸 일이 별로 없지만 테이블 사이즈가 커서 전체를 날리고 새로 쌓는게 불가능할 때 유용함
insert는 기존재하는 테이블과 컬럼의 개수가 동일해야만 입력이 가능하다.
delete FROM은 행을 삭제하는 거고, 열을 삭제하는 건 modify라는 명령어가 따로 있음. 단, 테이블 구조 자체를 건드리는 거기 때문에, 분석용으로 활용시에는 테이블을 아예 새로 생성하는 게 나음.
DECLARE와 SET
DECLARE는 변수 선언 (DECLARE 변수이름 데이터형식 = DECLARE FR_DT DATE;)
SET은 변수에 값 설정 (SET FR_DT = '2023-10-01')
값만 일치한다면 함수/쿼리를 넣을 수 있음 (10월의 최대 값 MAX (ORORD_DT) 등)
GCP는 세션 모드 켜고 실행하면 DECLARE를 한번 실행한 후에는 빼고 돌려도 돌아감 (PDW는 안됨)
주요 예시
DECLARE CRITN_DT DATE;
SET CRITN_DT = (SELECT MAX (ORORD_DT) FROM SANDBOX.SQL_BASIC_EX_ORD_ITEM2);
SET NUM = NUM +1;
2주차
일자/시간 함수
current_date() -> YYYY-MM-DD
current_datetime() -> YYYY-MM-DD hh:mm:ss
current_time() -> hh:mm:ss
datetime 형식의 데이터에서 시간 빼고 연월일만 보려면 date(datetime 값)
date_add ('2023-01-01', interval 1 day)
date_add ('2023-01-01', interval -1 day)
day는 month, year로도 바꿀 수 있음
'2023-01-01'-1 = date_add ('2023-01-01', interval -1 day) 임. 단, SQL 툴에 따라 이게 되는게 있고, 안되는 게 있음
DATE_TRUNC(date종류의 값, 자를 기준)
ex. DATE_TRUNC('2024-02-28 11:22:33', MONTH) -> 2024-02-01
MONTH를 DAY, QUARTER, YEAR로도 바꿀 수 있음
만약 datetime 형식 (시분초 있는)이라서 결과값에 시분초가 나오는데 날짜만 보고 싶다면,
DATE(DATE_TRUNC('2024-02-28 11:22:33', MONTH)) 로 한번 더 묶어주면 됨.
DATE_DIFF는 각 날짜값의 간격을 계산함. 순서에 따라 A,B 순서일 경우 A-B로 계산하는데, (-)값이 나오는게 싫고 단순 간격만 보고 싶으면 ABS() = 절대값 함수를 씌워주면 됨
DATE_DIFF ('2024-01-01', '2024-01-28', DAY);
SELECT ABS(DATE_DIFF ('2024-01-01', '2024-01-28', DAY));
DATE_DIFF ('2024-01-01', '2024-01-28', DAY) = '2024-01-28' - '2024-01-01' 임. 단, SQL 툴에 따라 이게 되는게 있고, 안되는 게 있음
CASE WHEN에 나이 범위를 넣는다고 해보자. 순서대로 실행되므로,
0~19 -> CASE WHEN AGE >= 0 AND AGE <=19 THEN '10대'
20대부터는 WHEN AGE <=29 만 넣어도 됨
CASE WHEN MBR_GRD_CD = '50' THEN '1.VIP'
WHEN MBR_GRD_CD = '40' THEN '2.GOLD'
ELSE '3.FRIENDS'
명칭에 1/2/3을 앞에 붙여주면 추출했을 때 자연스럽게 1-2-3 순서대로 정렬되어 예쁘게 볼 수 있음
IFNULL(컬럼, 대체값) -> 컬럼이 null값이면 무엇으로 대체해라
full join할 때 좋음
학생 가가 A 테이블에는 없지만, B 테이블에는 있다고 했을 때, A테이블의 학생번호를 가져오면 NULL 값이 됨. 그런 경우 B 테이블의 학생번호를 가져와라라고 IFNULL (A.STU_NUM, B.STU_NUM) 을 쓸 수 있음
NULL 값 체크는 IS NULL로 함
3주차
JOIN으로 데이터를 뽑을 때, 집계 함수를 넣지 않고 JOIN된 테이블을 한번 봐보는 게 좋음
그래서 중첩된 데이터가 있는지 한번 체크해보는 점검 절차를 스스로 만듬
JOIN을 한 후 WHERE 조건 값을 보는 것과, JOIN을 할 때 ON 조건 값으로 붙이는 것은 다르다.
WHERE 조건
Table A
LEFT JOIN Table B
ON A.num = B.num
WHERE B.num = '10'
=> A와 B 값이 함께 걸러짐 (inner join과 유사한 결과가 나올 수 있음)
B.num이 10인 값만 남기고 A와 B가 함께 걸러짐
ON 조건
Table A
LEFT JOIN Table B
ON A.num = B.num
AND B.num = '10'
=> B를 가지고 올 때 B를 거른 후에 가져오므로, A값은 걸러지지 않음
B.num이 10인 값만 가져와서 붙음
ON 조건 대신 서브 쿼리를 사용해서, B 테이블을 조건을 넣어서 거른 다음에 JOIN 해도 됨 (결과는 같음)
(SELECT *
FROM SANDBOX.SQL_BASIC_EX_ORD_SAMPLE
WHERE MBR_GRD_CD = '10')
ON 조건을 범위형으로 넣을 수도 있음
FROM _SESSION.ORD A
LEFT JOIN _SESSION.ORD B
ON A.MBR_ID = B.MBR_ID
AND A.ORORD_DT < B.ORORD_DT -- =를 넣으면 첫 주문도 포함하겠다는 조건이 됨. 현재는 첫 주문 빼고
AND DATE_ADD (A.ORORD_DT, INTERVAL 60 DAY) >= B.ORORD_DT
4주차
Window 함수
ROW_NUMBER() OVER (PARTITION BY ORORD_MM ORDER BY ORD_CNT DESC) AS RN1 -> ORORD_MM 중에서 ORD_CNT 기준으로 역순으로 행 번호를 매겨라
, RANK() OVER (PARTITION BY ORORD_MM ORDER BY ORD_CNT DESC) AS RN2 -> ORORD_MM 중에서 ORD_CNT 기준으로 역순으로 순위를 매겨라
, DENSE_RANK() OVER (PARTITION BY ORORD_MM ORDER BY ORD_CNT DESC) AS RN3 -> ORORD_MM 중에서 ORD_CNT 기준으로 역순으로 순위를 매겨라
window 함수 기능은 쓸 수 있는 함수가 정해져 있음
RANK - DENSE_RANK 의 차이
RANK: 동일한 값인 경우 동일한 순위를 주고, 동일한 순위 수만큼 다음 값에 건너 띄어서 매긴다. (ex. 1위가 4개인 경우 다음 값은 5위로 시작)
DENSE_RANK: 동일한 값인 경우 동일한 순위를 주고, 동일한 순위가 많더라도 건너 띄지 않고 매긴다. (ex. 1위가 4개이더라도 다음 값은 2위로 시작)
등급 등 순위가 있는 값인 경우, 문자 값인 것보다 숫자로 바꿔서 크다-작다를 비교하면 편리함
CAST(CASE WHEN MBR_GRD_CD IN ('10','20','30') THEN '10' ELSE MBR_GRD_CD END AS INT64) AS MBR_GRD_CD -- 크다 작다 비교를 편하게 하려고 CAST를 통해 INT64 (숫자)로 바꿔줌
다음 주문일자 확인하는 방법
- JOIN할때 순번 민 기준으로 JOIN하는 방식
FROM _SESSION.MBR_ORD_ORDR A
LEFT JOIN _SESSION.MBR_ORD_ORDR B ON A.MBR_ID = B.MBR_ID AND A.EAMBR_ORD_ORDR = B.EAMBR_ORD_ORDR + 1
- LAG 윈도우 함수 쓰는 방식
이러면 애초에 순번 자체를 매길 필요가 없어서 더 편리함
1 2024-04-01 NULL
2 2024-04-12 2024-04-01
3 2024-04-17 2024-04-12
LAG(ORORD_DT) OVER (PARTITION BY MBR_ID ORDER BY ORORD_DT) AS EAMBR_BF_ORORD_DT -- 고객 별로, 주문날짜를 기준으로, 주문날짜를 한칸씩 밀어라 (같은 날짜면 주문번호는 안보고 데이터가 적재된 순서로 밈)
LAG(ORORD_DT) OVER (PARTITION BY MBR_ID ORDER BY ORORD_DT,ORORD_NO) AS EAMBR_BF_ORORD_DT
거꾸로 밀고 싶으면 ORDER BY 뒤에 DESC 추가하면 됨
LAG(ORORD_DT) OVER (PARTITION BY MBR_ID ORDER BY ORORD_DT DESC,ORORD_NO DESC) AS EAMBR_BF_ORORD_DT
2칸씩 밀고 싶으면 LAG (밀 칼럼, 2) 로 LAG 내에 숫자 넣어주면 됨LAG(ORORD_DT, 2) OVER (PARTITION BY MBR_ID ORDER BY ORORD_DT,ORORD_NO) AS EAMBR_BF_ORORD_DT
