실무 SQL 공부 3-1 - SQL로 데이터 가공

하나ㅣ 값 조작

1. 코드 값을 레이블로 변경하기

저장된 코드값을 그대로 집계에 사용 시, 리포트 가독성이 낮아짐

그래서 변환 작업이 필요하다.

집계시 미리 코드 값을 레이블로 변경하는 방법

case문 사용하기

CASE WHEN <조건식> THEN <조건 만족 시 값>
END

조건식에 해당하는 값이 없는 경우 NULL
아니면 ELSE <값>을 사용해서 디폴트 값을 지정할 수 있다.

SELECT
    user_id,
    CASE
        WHEN register_device = 1 THEN '데스크톱'
        WHEN register_device = 2 THEN '스마트폰'
        WHEN re
        gister_device = 3 THEN '애플리케이션'
        -- ELSE 디폴트 값
    END AS device_name
FROM mst_users;

2. URL에서 요소 추출하기

분석 시, URL로 저장해두는 경우가 있음

정규식을 사용해서 처리할 수 있음

Redshift는 따로 정규표현식에 그룹을 사용할 수 없어서 좀 더 복잡한 형태가 된다.

SELECT
    stamp,
    -- host 이름 부분 추출하기
    substring(referrer from 'https?://([^/]*)') AS referrer_host -- PostgreSQL
    , regexp_replace(regexp_substr(referrer, 'https?://[^/]*'), 'https?://', '') AS referrer_host -- redshift
FROM access_log;

SELECT
    stamp,
    -- URL 경로 또는 GET 매개변수의 id 추출하기
    --PostgreSQL
    substring(url from '//[^/]+([^?#]+)') AS path,
    substring(url from 'id=([^&]*)') AS id,
    --redshift
    regexp_replace(regexp_substr(url, '//[^/]+[^?#]+'), '//[^/]+', '') AS path,
    regexp_replace(regexp_substr(url, 'id=[^&]*'), 'id=', '') AS id
FROM access_log;

3. 문자열을 배열로 분해하기

문자열 자료형은 범용적인 자료형이므로 더 세부적으로 분해해서 사용해야하는 경우가 많다.

쉼표로 연결된 문장을 자르거나, 공백으로 구분하는 등

URL경로를 슬래시로 분할하여 계층을 추출하는 예제

SELECT
    stamp, url,
    -- postgreSQL n번째 요소 추출하기
    split_part(substring(url from '//[^/]+([^?#]+)'), '/', 2) AS path1,
    split_part(substring(url from '//[^/]+([^?#]+)'), '/', 3) AS path2,
    -- redshift n번째 요소 추출하기
    split_part(regexp_replace(
        regexp_substr(url, '//[^/]+[^?#]+'), '//[^/]+', ''), '/', 2) AS path1,
    split_part(regexp_replace(
        regexp_substr(url, '//[^/]+[^?#]+'), '//[^/]+', ''), '/', 2) AS path2
FROM access_log;

4. 날짜와 타임스탬프 다루기

현재 날짜와 타임스탬프 추출하기

postgerSQL에서는 CURRENT_TIMESTAMP를 사용하여 타임존이 적용된 타임스탬프를 찍을 수 있다.

하지만 이 외의 미들웨어에서는 타임존 없는 타임스탬프를 사용하기 때문에 자료형을 맞출 수 있도록 LOCALTIMESTAMP를 사용하는 것이 좋다.

BigQuery는 UTC시간을 반환하기 떄문에 한국 시각과 달라 예상치 못한 문제를 만날 수 있다.

-- redshift의 경우 현재 날짜는 current_date, 현재 타임 스탬프는 getdate()
SELECT CURRENT_DATE as dt, -- yyyy-mm-dd
    GETDATE() AS stamp     -- yyyy-mm--dd HH:MM:SS

지정한 값의 날짜/시각 데이터 추출하기

일반적으로 CAST(값 as 타입)을 사용한다.

SELECT
    -- 1. CAST(value as type)
    CAST('2016-01-30' AS date) AS dt,
    CAST('2016-01-30 12:00:00' AS timestamp) AS stamp
    -- 2. type value
    date '2016-01-30' AS dt,
    timestamp '2016-01-30 12:00:00' AS stamp
    -- 3. value::type
    '2016-01-30'::date AS dt
    '2016-01-30'::timestamp AS stamp

특정 필드 추출하기

EXTRACT 함수를 사용한다.

SELECT stamp,
    EXTRACT(YEAR FROM stamp) AS year,
    EXTRACT(MONTH FROM stamp) AS month,
    EXTRACT(DAY FROM stamp) AS day,
    EXTRACT(HOUR FROM stamp) AS hour,
FROM
    (SELECT CAST('20216-01-30 12:00:00' AS timestamp) AS stamp) AS t

연, 월, 일을 문자열에서 추출하는 쿼리

substring 함수를 사용한다.

SELECT stamp,
    substring(stamp, 1, 4) AS year
    substring(stamp, 6, 2) AS month
    substring(stamp, 9, 2) AS day
    substring(stamp, 12, 2) AS hour
    , substring(stamp, 1, 7) AS year_month -- 연,월 함께 추출
FROM
    (SELECT CAST('2016-01-30' AS text) AS stamp) AS t

5. 결측값 디폴트 값으로 대치하기

NULL 값 처리

NULL값과 사칙연산은 NULL이기 때문에 혹시 연산을 하는 경우라면 처리를 고려해야한다.

COALESCE함수 사용하기

SELECT purchase_id, amount, coupon, amount - coupon AS discount_amount1,
    amount - COALESCE(coupon, 0) AS discount_amount2
FROM purchase_log_with_coupon