6강: 데이터 정리     → NULL 처리, 중복 제거, 날짜/문자열 변환
7강: 고급 SQL 변환   → JSON 파싱, 배열 다루기, JOIN, PIVOT, 고차 함수
8강: SQL UDF        → 내 함수 만들어서 등록하고 재사용하기

---

Databricks 데이터 정리, 고급 SQL 변환, SQL UDF

지저분한 데이터를 정리하고, 복잡한 구조를 다루고, 재사용 가능한 함수를 만드는 방법 정리

---

1. 데이터 정리

NULL 값 파악하기

count(컬럼)은 NULL을 건너뜀. count(*)은 NULL이 있는 행도 포함.

-- 각 컬럼에 NULL이 몇 개인지 확인
SELECT
  count_if(user_id IS NULL) AS missing_user_ids,
  count_if(email IS NULL) AS missing_emails,
  count_if(updated IS NULL) AS missing_updates
FROM users_dirty

-- 총 행 수 vs NULL 제외 행 수 비교
SELECT count(*), count(user_id) FROM users_dirty
--      ↑ NULL 포함    ↑ NULL 제외

중복 파악하기

-- 전체 행 수
SELECT count(*) FROM users_dirty

-- 고유 행 수
SELECT count(DISTINCT(*)) FROM users_dirty

-- 특정 컬럼 기준 고유 값 수
SELECT count(DISTINCT user_id) FROM users_dirty

주의: DISTINCT는 NULL이 있는 행을 제외하지 않는다. 모든 값이 NULL인 행도 "하나의 고유한 행"으로 처리된다.

특정 컬럼 기준으로 중복 제거

-- user_id + user_first_touch_timestamp 기준으로 중복 제거
-- email은 여러 행 중 NULL이 아닌 값을 가져오기 위해 max() 사용
CREATE OR REPLACE TEMP VIEW deduped_users AS
SELECT user_id, user_first_touch_timestamp,
       max(email) AS email,
       max(updated) AS updated
FROM users_dirty
WHERE user_id IS NOT NULL          -- NULL 행 제거
GROUP BY user_id, user_first_touch_timestamp;

왜 max(email)을 쓰냐면:
같은 user_id가 여러 행이면 GROUP BY 후 email이 여러 개 존재. 그 중 NULL이 아닌 값을 가져오기 위해 max() 사용.

데이터 검증

-- user_id가 중복 없는지 확인 (true면 중복 없음)
SELECT max(row_count) <= 1 AS no_duplicate_ids FROM (
  SELECT user_id, count(*) AS row_count
  FROM deduped_users
  GROUP BY user_id
)

날짜 형식 변환 및 정규 표현식

SELECT *,
  date_format(first_touch, "MMM d, yyyy") AS first_touch_date,   -- 사람이 읽기 좋은 날짜
  date_format(first_touch, "HH:mm:ss") AS first_touch_time,      -- 시간
  regexp_extract(email, "(?<=@).+", 0) AS email_domain           -- 이메일에서 도메인 추출
FROM (
  SELECT *,
    CAST(user_first_touch_timestamp / 1e6 AS timestamp) AS first_touch  -- Unix 타임스탬프 변환
  FROM deduped_users
)

---

2. 고급 SQL 변환

JSON 데이터 다루기

Kafka 같은 시스템에서 오는 데이터는 JSON이 바이너리로 인코딩된 경우가 많다.

-- 바이너리를 문자열로 변환해서 읽기
CREATE OR REPLACE TEMP VIEW events_strings AS
  SELECT string(key), string(value)
  FROM events_raw;

JSON 안의 중첩 데이터 접근하기:

-- : 구문으로 JSON 필드 접근
SELECT value:device, value:geo:city
FROM events_strings
--    ↑ 최상위 필드  ↑ 중첩 필드 (geo 안의 city)

JSON 문자열을 구조체로 파싱하기:

-- schema_of_json으로 스키마 자동 추론 → from_json으로 파싱
CREATE OR REPLACE TEMP VIEW parsed_events AS
  SELECT from_json(value, schema_of_json('{...샘플JSON...}')) AS json
  FROM events_strings;

-- 구조체를 개별 컬럼으로 평면화
CREATE OR REPLACE TEMP VIEW new_events_final AS
  SELECT json.*       -- ← * 로 펼치기
  FROM parsed_events;

중첩 데이터 구조 접근하기

구조체(struct) 타입은 . 으로 접근:

-- ecommerce 구조체 안의 purchase_revenue_in_usd 접근
SELECT ecommerce.purchase_revenue_in_usd
FROM events
WHERE ecommerce.purchase_revenue_in_usd IS NOT NULL

배열 다루기

-- 배열 크기 필터링 (items가 2개 초과인 행만)
SELECT user_id, items
FROM events
WHERE size(items) > 2

explode — 배열을 행으로 펼치기:

-- items 배열의 각 요소를 별도 행으로 분리
SELECT user_id, explode(items) AS item
FROM events

Before: user_1 | [item_A, item_B, item_C]
After:  user_1 | item_A
        user_1 | item_B
        user_1 | item_C

collect_set, flatten, array_distinct — 배열 수집 및 정리:

Q. 이 세 함수가 뭔지 코드만 보고는 이해가 안 됐다.

단계별로 뭘 하는지 데이터로 보면 이해가 된다.

예시 데이터:

user_id | event_name | items
user_1  | click      | [A, B]
user_1  | purchase   | [B, C]
user_1  | click      | [A, D]

① collect_set(event_name)
   → 그룹 안의 값들을 배열로 모으기 (중복 제거)
   → user_1: ["click", "purchase"]  ← 중복 "click" 하나로 합쳐짐

② collect_set(items.item_id)
   → 배열 안의 값을 모으면 배열들의 배열이 됨
   → user_1: [ [A,B], [B,C], [A,D] ]

③ flatten([ [A,B], [B,C], [A,D] ])
   → 배열들의 배열을 하나의 배열로 펼치기
   → [A, B, B, C, A, D]  ← 하나로 합쳐졌지만 중복 있음

④ array_distinct([A, B, B, C, A, D])
   → 배열에서 중복 제거
   → [A, B, C, D]

세 개를 합치면:

SELECT user_id,
  collect_set(event_name) AS event_history,
  array_distinct(flatten(collect_set(items.item_id))) AS cart_history
  -- → 이 유저가 장바구니에 담았던 모든 상품 ID (중복 없이)
FROM events
GROUP BY user_id

JOIN — 조건에 맞는 행끼리 컬럼 합치기

Q. JOIN은 두 테이블의 컬럼을 합치는 거야?

거의 맞다. 정확히는 "조건에 맞는 행끼리 연결해서 컬럼을 합치는 것".

테이블 A (sales)       테이블 B (item_lookup)
order_id | item_id     item_id  | item_name
1        | M_STAN_K    M_STAN_K | Standard King Mattress
2        | P_FOAM_S    P_FOAM_S | Small Foam Pillow

JOIN ON a.item_id = b.item_id  ← item_id가 같은 행끼리 연결

결과:
order_id | item_id  | item_name
1        | M_STAN_K | Standard King Mattress
2        | P_FOAM_S | Small Foam Pillow

Q. ON에 사용한 컬럼명이 다르면 합쳐지지 않고 둘 다 컬럼으로 적히는 거 맞지?

맞다.

컬럼명 같음 (item_id = item_id)  →  하나로 합쳐져서 컬럼 1개
컬럼명 다름 (order_item_id = product_id)  →  둘 다 컬럼으로 남아서 컬럼 2개

이름이 달라도 값은 같기 때문에, 컬럼명이 다를 때 SELECT * 하면 거의 똑같은 값의 컬럼 두 개가 나란히 생겨서 지저분해 보인다.

-- explode 후 조회 테이블과 JOIN
CREATE OR REPLACE VIEW sales_enriched AS
SELECT *
FROM (
  SELECT *, explode(items) AS item
  FROM sales) a
INNER JOIN item_lookup b
ON a.item.item_id = b.item_id;

JOIN 종류:

INNER JOIN  → 양쪽 다 있는 행만 (가장 많이 씀)
LEFT JOIN   → 왼쪽 테이블 기준, 오른쪽에 없으면 NULL
RIGHT JOIN  → 오른쪽 테이블 기준, 왼쪽에 없으면 NULL

A: [1, 2, 3]  B: [2, 3, 4]
INNER → [2, 3]      공통인 것만
LEFT  → [1, 2, 3]   A 기준
RIGHT → [2, 3, 4]   B 기준

집합 연산자

-- UNION: 두 쿼리 결과 합치기
SELECT * FROM events
UNION
SELECT * FROM new_events_final

-- INTERSECT: 두 쿼리에 공통으로 있는 행만
SELECT * FROM events
INTERSECT
SELECT * FROM new_events_final

-- MINUS: 첫 번째에는 있고 두 번째에는 없는 행
SELECT * FROM events
MINUS
SELECT * FROM new_events_final

PIVOT — 행을 컬럼으로 변환

Q. PIVOT 사용법을 몰라서 문제를 못 풀었다.

PIVOT이 하는 일:

Before (행으로):               After (열로):
user_id | event_name           user_id | click | purchase | view
user_1  | click            →   user_1  | 3     | 1        | 2
user_1  | click
user_1  | purchase
user_1  | view
user_1  | view

"event_name 값들을 각각 컬럼으로 만들고, 그 안에 집계값을 넣어라"

PIVOT 문법 구조:

SELECT * FROM (
  SELECT 필요한_컬럼들        -- ① 재료 준비 (PIVOT에 필요한 컬럼만)
  FROM 테이블
) PIVOT (
  집계함수(집계할_컬럼)        -- ② 각 칸에 뭘 넣을지 (count, sum 등)
  FOR 피벗할_컬럼 IN (        -- ③ 어떤 컬럼을 기준으로 펼칠지
    '값1', '값2', '값3'       -- ④ 열로 만들 값들 (미리 알고 있어야 함)
  )
)

IN 안의 값을 어떻게 아냐면:

-- 먼저 이걸 실행해서 어떤 값들이 있는지 확인
SELECT DISTINCT event_name FROM events

문제 풀이 — "user_id로 그룹화하고 event_name을 피벗하여 각 이벤트 횟수를 열로":

"각 이벤트 유형의 개수"  →  집계함수 = count(event_name)
"event_name을 피벗"     →  FOR event_name IN (...)
"user_id로 그룹화"      →  재료에 user_id만 남기면 자동으로 그룹화

SELECT * FROM (
  SELECT user_id, event_name    -- user_id랑 event_name만 남김
  FROM events
) PIVOT (
  count(event_name)             -- 각 칸에 이벤트 개수
  FOR event_name IN (
    'cart', 'login', 'main',
    'finalize', 'payment_review',
    -- SELECT DISTINCT event_name FROM events 로 확인한 값들
    'logout', 'home', 'warehouse'
  )
)

고차 함수 — 배열을 직접 조작

일반 함수는 배열에 못 씀. 고차 함수를 쓰면 배열 안을 직접 조작 가능.

FILTER — 조건에 맞는 요소만 남기기:

SELECT
  order_id,
  FILTER(items, i -> i.item_id LIKE "%K") AS king_items
--        ↑배열  ↑반복변수  ↑ 조건 (K로 끝나는 것만 남김)
FROM sales

EXISTS — 배열 안에 조건을 만족하는 요소가 있는지 확인 (true/false 반환):

실습에서 직접 풀었던 문제: items 배열에서 item_name이 "Mattress"로 끝나면 true, "Pillow"로 끝나면 true인 부울 열 생성

CREATE OR REPLACE TABLE sales_product_flags AS
SELECT
  items,
  EXISTS(items, x -> x.item_name LIKE "%Mattress") AS mattress,
  EXISTS(items, x -> x.item_name LIKE "%Pillow") AS pillow
FROM sales;

결과:

items                                      | mattress | pillow
[{item_id: "M_PREM_K", item_name: "Premium King Mattress", ...}] | true  | false
[{item_id: "P_FOAM_S", item_name: "Standard Foam Pillow", ...}]  | false | true
[{item_id: "M_STAN_F", item_name: "Standard Full Mattress",...}] | true  | false

FILTER vs EXISTS 차이:

FILTER  → 조건에 맞는 요소만 남긴 배열 반환
          [item_A, item_B, item_C] → [item_B]

EXISTS  → 조건을 만족하는 요소가 하나라도 있으면 true, 없으면 false 반환
          [item_A, item_B, item_C] → true / false

TRANSFORM — 배열의 각 요소 변환:

SELECT
  order_id,
  TRANSFORM(king_items, k -> CAST(k.item_revenue_in_usd * 100 AS INT)) AS item_revenues
--           ↑배열       ↑반복변수  ↑ 각 요소에 적용할 변환
FROM king_size_sales

---

3. SQL UDF (사용자 정의 함수)

UDF가 뭔데?

자주 쓰는 SQL 로직을 함수로 등록해서 이름만으로 재사용하는 것.

엑셀의 사용자 정의 함수랑 같은 개념
내가 자주 쓰는 수식을 함수로 저장 → 필요할 때 함수 이름만 호출

UDF 만들기

CREATE OR REPLACE FUNCTION yelling(text STRING)
RETURNS STRING                        -- ← "STRING 타입을 반환할 거야" 라는 선언 (약속)
RETURN concat(upper(text), "!!!")     -- ← 실제로 반환하는 코드 (실행)

Q. RETURNS랑 RETURN이 둘 다 있던데 무슨 차이야?

RETURNS = 메뉴판에 "아메리카노 - 커피입니다" 라고 적어둔 것 (반환 타입 선언)
RETURN  = 실제로 커피를 내어주는 것 (실제 반환 코드)

RETURNS는 어떤 타입을 반환할지 미리 선언, RETURN은 실제로 반환하는 코드.

-- 사용하기
SELECT yelling(food) FROM foods
-- 결과: BEEF!!!, BEANS!!!, POTATOES!!!

UDF 정보 확인

DESCRIBE FUNCTION yelling           -- 기본 정보
DESCRIBE FUNCTION EXTENDED yelling  -- Body 필드에서 실제 SQL 로직 확인 가능

UDF의 특징

• 메타스토어에 저장됨 → 노트북, DBSQL, 작업 등 어디서든 재사용 가능
• 세션이 끝나도 유지됨 → TEMP VIEW와 달리 영구적
• 권한 관리 가능 → USAGE, SELECT 권한 부여 필요

CASE/WHEN — 조건 분기

SELECT *,
  CASE
    WHEN food = "beans" THEN "I love beans"
    WHEN food = "potatoes" THEN "My favorite vegetable is potatoes"
    WHEN food <> "beef" THEN concat("Do you have any good recipes for ", food, "?")
    ELSE concat("I don't eat ", food)
  END
FROM foods

CASE/WHEN을 UDF 안에 넣기

CREATE FUNCTION foods_i_like(food STRING)
RETURNS STRING
RETURN CASE
  WHEN food = "beans" THEN "I love beans"
  WHEN food = "potatoes" THEN "My favorite vegetable is potatoes"
  WHEN food <> "beef" THEN concat("Do you have any good recipes for ", food, "?")
  ELSE concat("I don't eat ", food)
END;

SELECT foods_i_like(food) FROM foods

---

전체 흐름 정리

6강 데이터 정리
  → NULL 파악 (count_if) → 중복 제거 (GROUP BY + max) → 검증 → 날짜/문자열 변환

7강 고급 SQL 변환
  → JSON 파싱 (from_json, schema_of_json)
  → 배열 조작 (explode, collect_set, flatten, array_distinct)
  → 고차 함수 (FILTER, EXISTS, TRANSFORM)
  → 테이블 결합 (JOIN, UNION, INTERSECT)
  → 형태 변경 (PIVOT)

8강 SQL UDF
  → 반복 로직을 함수로 등록 (CREATE FUNCTION)
  → RETURNS: 반환 타입 선언 / RETURN: 실제 반환 코드
  → CASE/WHEN과 결합해서 제어 흐름 함수 만들기
  → 메타스토어에 저장 → 어디서든 재사용