(2) 데이터 분석을 위한 SQL 레시피 – 데이터 처리를 위한 SQL: 하위 문자열, split_part, 캐스트, 추출 및 병합 기능 사용.

by


색인

3. 데이터 처리를 위한 SQL

3-1 단일 값 편집

3-2 여러 값 조작

3-3 테이블 작업

4. 매출 파악을 위한 데이터 추출

4-1 시계열을 기반으로 데이터 집계

4-2 다각적인 축으로 데이터 집계

5. 사용자를 알기 위한 데이터 추출

5-1 사용자의 특성 및 트렌드 파악

5-2 시계열을 통해 모든 사용자의 상태 변화 찾기

5-3 시계열에 따른 사용자의 개별 행동 분석

6. 웹사이트에서 행동 데이터 추출

6-1 웹사이트에서 기능/트렌드 찾기

6-2 웹사이트에서 사용자 행동 이해하기

6-3 입력 양식 최적화

7. 데이터 활용의 정확도를 높이는 분석 기술

7-1 데이터를 결합하여 새로운 데이터 생성

7-2 이상값 감지

7-3 중복 데이터 감지

7-4 여러 레코드 비교

8. 데이터를 무기화하는 분석

8-1 검색 평가

8-2 데이터 수집

8-3 자극

8-4 점수 계산

9. 지식을 행동으로 옮기기

9-1 데이터 사용 영역

오늘 수업 요약
1. CASE 구문다음을 사용하여 보고서 가독성을 위해 문자열을 변환합니다.
두 번째 하위 문자열 기능과 정규식다음을 사용하여 URL에서 요소 추출
3. split_part, 분할 문자열을 함수로 분해
4. 날짜 및 타임스탬프 – 변환, 추출, 하위 문자열 기능 활용
제5연합함수로 누락된 값 처리

3. 데이터 조작을 위한 SQL

데이터를 처리해야 하는 경우

  • DB로 코드 값컴퓨터에 저장, 그 의미는 다른 테이블에서 관리됩니다.만약에
  • 액세스 로그하나의 행동이다 선의 형태로 표현되는 경우가 많습니다.
  • 로그 데이터와 비즈니스 데이터를 함께 취급하는 경우 형식이 일치하지 않기 때문에 계산 시 유사한 형식으로 변환됩니다. 해야만한다

하나. 데이터 변환에 일반적으로 사용되는 CASE 구문

  • 로그/작업 데이터로 저장된 코드 값을 수정하지 않고 집계에 사용하면 보고서 가독성이 떨어집니다.
  • 보고서 작성 시 가독성을 높이기 위해 코드 값을 레이블로 변경일해야

데스크톱, 스마트폰, 애플리케이션은 각각 코드값 1, 2, 3으로 저장된다. 라벨을 만들어보자


-- 코드를 레이블로 변경하는 쿼리 
SELECT
	user_id,
	CASE
	WHEN register_device = 1 THEN '데스크톱'
	WHEN register_device = 2 THEN '스마트폰'
	WHEN register_device = 3 THEN '애플리케이션'
	-- 디폴트 값 지정시 ELSE구문 사용, 여기서는 해당 없음
	-- ELSE ''
	END AS device_name
FROM mst_users
;


2. URL 함수, 정규식

< Elemente aus URLs extrahieren >

다음과 같은 액세스 로그 테이블을 사용하여 URL 관련 조작을 수행해 보겠습니다.


  • 위와 같이 옆에서 세면 복잡해지기 때문에 ‘호스트당‘(아래 사진 referrer_host)는 일반적으로 계산됩니다. 
-- 레퍼러 도메인을 추출하는 쿼리
SELECT
	stamp
	-- referrer의 호스트 이름 부분 추출
, substring(referrer FROM 'https?//)(^/)*)') AS referrer_host
FROM access_log
;
  • PostgreSQL왜냐하면 하위 문자열 함수 및 정규식 사용tat(Redshift, Hive 및 SparkSQL의 경우 표현식이 다릅니다!)


access_log 테이블(이전)

리퍼러 도메인 추출(후)

< Extrahieren von Pfad- und Anforderungsparameterwerten aus URL >

  • 상품에 대한 보고서 작성 시 표시된 상품의 특정 ID를 보고 싶은데 데이터가 저장되지 않는 경우, 로그 데이터로 저장했다면 URL 경로를 처리하여 제품 보고서를 생성할 수 있습니다.
-- URL 경로와 GET 매개변수에 있는 특정 키 값을 추출하는 쿼리
SELECT
	stamp
	,url
	, substring(url FROM '//(^/)+((^?#)+)') AS PATH
	, substring(url FROM 'id=)(^&)*)') AS id
FROM access_log
;


URL 경로 및 GET 요청 매개변수에서 특정 키 추출

삼. 함수 SPLIT_PART 및 SPLIT

  • 문자열 데이터 타입은 보편적인 데이터 타입이기 때문에 좀 더 자세히 분해해서 사용해야 하는 경우가 많습니다.

이전에 사용한 액세스 로그 예제를 기반으로 페이지 계층 구조를 분석해 보겠습니다.

-- URL 경로를 슬래시로 분할해서 계층을 추출하는 쿼리
SELECT
	stamp
	, url
	-- 경로가 반드시 슬래시로 시작하므로, 2번째 요소가 마지막 계층이다
	, split_part(substring(url FROM '//(^/)+((^?#)+)'), '/', 2) AS path1
	, split_part(substring(url FROM '//(^/)+((^?%)+)'), '/', 3) AS path2
FROM access_log
;


URL 경로의 레이어가 추출됩니다.

4. 날짜/시간 함수, 날짜 데이터 유형, 타임스탬프 데이터 유형, 문자열 함수

  • 미들웨어에 따라 추출 방법이 다르므로 동일한 쿼리를 작성하더라도 반환 값이 다를 수 있습니다.
  • PostgreSQL에서는 CURRENT_TIMESTAMP의 반환 값으로 시간대가 있는 타임스탬프 데이터 유형이 반환되고 나머지는 시간대가 없는 타임스탬프를 반환합니다.
    • 반환 값의 데이터 유형을 일치시키기 위해 PostgreSQL 현지 타임 스탬프사용하기 좋습니다
-- 현재 날짜와 타임스탬프를 추출하는 쿼리
SELECT
	CURRENT_DATE AS dt
	, CURRENT_TIMESTAMP AS stamp
	-- 타임존을 사용하고 싶지 않으면 locatimestamp 사용, 
	-- LOCALTIMESTAMP AS stamp
;


추출된 날짜 및 타임스탬프

<붓다 기능으로 지정된 값의 날짜/시간 데이터 추출>

  • 현재 시간이 아닌 문자열로 지정된 날짜와 시간을 기준으로 날짜 데이터형과 타임스탬프 데이터형을 구성할 때
-- 문자열을 날짜/타임스탬프로 변환하기
SELECT
	CAST('2016-01-30' AS date) AS dt
	, CAST('2016-01-30 12:00:00' AS timestamp) AS stamp
;


결과

<발췌 기능으로 날짜/시간에서 특정 필드 추출 >

  • 타임스탬프를 문자열로 취급하고 문자열 조작으로 필드를 추출할 수 있습니다.
-- 타임스탬프 자료형의 데이터에서 연,월,일 등을 추출하는 쿼리
SELECT
	stamp
	, EXTRACT (YEAR FROM stamp) AS YEAR
	, EXTRACT (MONTH FROM stamp) AS MONTH
	, EXTRACT (DAY FROM stamp) AS DATE
	, EXTRACT (HOUR FROM stamp) AS HOUR
FROM
	(SELECT CAST('2016-01-30 12:00:00' AS timestamp) AS stamp) AS t
;


날짜형 데이터에서 년, 월, 일, 시간 추출

<하위 문자열 함수로 특정 필드 값 추출>

  • 연도와 월을 동시에 추출 월별보고제조에 많이 사용나무 상자
  • 이러한 문자열을 사용하는 코드는 미들웨어에 따라 크게 다르지 않습니다.
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 12:00:00' AS text) AS stamp) AS t
;
  • 하위 문자열 함수 대신 substr사용하셔도 상관없습니다


실행 결과

날짜 및 시간 정보는 로그 데이터의 필수 정보입니다. 시간대를 고려하다 미들웨어별 차이점관찰하다

5. COALESCE 함수로 null 값 처리

< Fehlende Werte durch Standardwerte ersetzen >

  • 문자열이나 숫자를 다룰 때 NULL 값은 산술 연산에서 NULL이 되어 처리하기 어렵습니다.

구매 금액과 NULL을 포함한 쿠폰 금액이 저장되어 있는 테이블에서 쿠폰으로 할인 판매 금액을 계산하면 다음과 같습니다.


0이 있는 테이블

-- 구매액에서 할인 쿠폰 값을 제외한 매출 금액을 구하는 쿼리
SELECT
	purchase_id
	, amount
	, coupon
	, amount - coupon AS discount_amount1
	, amount - COALESCE(coupon, 0) AS discount_amount2
FROM
	purchase_log_with_coupon
;


  • discount_amount1은 가격에서 쿠폰을 뺀 값입니다. 즉, 쿠폰이 null 레코드인 경우 null이 됩니다.
  • 할인_금액2 쿠폰이 null인 경우 병합 함수를 사용하여 0으로 대체되어 쿠폰을 사용하지 않는 레코드(null)도 올바른 값으로 계산됩니다.