[데이터 분석을 위한 SQL 레시피] - 3장 데이터 가공을 위한 SQL 01

 

데이터를 가공해야 되는 이유

1. 다룰 데이터가 데이터 분석용도로 상정되지 않은 경우

2. 연산할 때 비교 가능한 상태로 만들고 오류를 회피하기 위한 경우

 

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

로그 데이터 또는 업무 데이터로 저장된 코드 값을 그대로 집계에 사용하면 리포트의 가독성이 굉장히 낮아집니다. 따라서 리포트를 작성할 때 변환하는 등의 작업을 해야하는데요. 아예 집계할때 미리 코드의 값을 레이블로 변경하는 방법을 살펴봅시다.

 

다음 데이터처럼 회원 등록 때 사용한 장치를 저장하는 컬럼(register_device)이 코드 값(1:데스크톱, 2:스마트폰, 3:애플리케이션)으로 저장되어 있습니다. 이를 레이블로 바꿔봅시다.

 

5-1 사용자 마스터(mst_users)테이블

user_id	register_date	register_device
U001	2016-08-26	1
U002	2016-08-26	2
U003	2016-08-27	3

 

코드 값을 레이블로 변경하는 것처럼 특정 조건을 기반으로 값을 결정할 때는 CASE 식을 사용합니다. CASE 식의 구문은 CASE 뒤에 'WHEN <조건식> THEN <조건을 만족할 때의 값>' 을 나열하고, 마지막을 END로 끝내는 형태입니다. 만약 조건식에 해당하는 경우가 없다면 NULL이 되지만, 마지막에 'ELSE <값>' 형태를 사용해서 디폴트 값을 별도로 지정해 줄 수 있습니다.

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
;

 

실행결과

user_id	device_name
U001	데스크톱
U002	스마트폰
U003	애플리케이션

 

정리: CASE 식은 기본적인 SQL 구문이지만, 데이터 변환에 굉장히 많이 사용됩니다. 복잡한 구문과 CASE 식을 조합해서 사용하는 경우가 많으므로, CASE 식을 어떻게 사용하는지 확실하게 기억해두기 바랍니다.

2.URL에서 요소 추출하기

분석 현장에서는 서비스 런칭때에 로그 조건과 분석 요건을 제대로 검토하지 못하고 일단 최소한의 요건으로 레퍼러와 페이지 URL을 저장해두는 경우가 있습니다. 그리고 이후에 저장한 URL을 기반으로 요소들을 추출합니다.

여기서는 다음과 같은 샘플 데이터를 사용해 URL과 관련된 조작을 해봅시다.

 

5-2 접근 로그(access_log)테이블

-[ RECORD 1] ---------------------------------------
stamp    l 2016-08-26 12:02:00
referrer l http://www.other.com/path1/index.php?k1=v1&k2=v2#Ref1
url      l http://www.example.com/video/detail?id=001
-[ RECORD 2] ---------------------------------------
stamp    l 2016-08-26 12:02:00
referrer l http://www.other.com/path1/index.php?k1=v1&k2=v2#Ref1
url      l http://www.example.com/video#ref
-[ RECORD 3] ---------------------------------------
stamp    l 2016-08-26 12:02:00
referrer l http://www.other.com/
url      l http://www.example.com/book/detail?id=002

 

레퍼러로 어떤 웹페이지를 거쳐 넘어왔는지 판별하기

참고로 Hive 또는 BigQuery에는 URL을 다루는 함수가 있습니다. 하지만 구현되지 않은 미들웨어에서는 정규 표현식으로 호스트 이름의 패턴을 추출해야 합니다. 또한 Redshift에서는 정규 표현식에서 괄호로 그룹화하는 기능이 없으므로, 정규 표현식을 약간 복잡하게 써야 합니다.

SELECT
	stamp
  --referrer의 호스트 이름 부분 추출하기
  --PostgreSQL의 경우 substring 함수와 정규표현식 사용하기
, substring(referrer from 'https?://([^/]*)') AS referrer_host
  -- Redshift의 경우 정규 표현식에 그룹을 사용할 수 없으므로
  -- , regexp_substr 함수과 regexp_replace 함수를 조합해서 사용하기
  -- , regexp_replace(regexp_substr(referrer, 'https?://[^/]*'), "https?://','')
  -- AS referrer_host
  -- Hive, SparkSQL의 경우 parse_url 함수로 호스트 이름 추출하기
  -- , parse_url(referrer, 'HOST') AS referrer_host
  -- BigQuery의 경우는 host 함수 사용하기
  -- , host(referrer) AS referrer_host
 FROM access_log
 ;

 

실행결과

stamp	referrer_host
2016-08-26 12:02:00	www.other.com
2016-08-26 12:02:01	www.other.net
2016-08-26 12:02:01	www.other.com

 

URL에서 경로와 요청 매개변수 값 추출하기

상품과 관련된 리포트를 작성할 때, 어떤 상품이 열람되는지 특정하는 ID를 데이터로 따로저장해두지 않은 경우가 있을 수 있습니다. 그래도 URL을 로그 데이터로 저장해두었다면 URL경로를 가공해서 상품 리포트를 만들 수 있습니다.

다음 코드 예는 URL 경로와 GET 요청 매개변수에 있는 특정 키를 추출하는 쿼리입니다.

 

5-3 URL 경로와 GET 매개변수에 있는 특정 키 값을 추출하는 쿼리

SELECT
	stamp
   ,url
-- URL 경로 또는 GET 매개변수의 id 추출하기
-- PostgreSQL의 경우 substring 함수와 정규 표현식 사용하기
, substring(url from '//[^/] + ([^?#]+)') AS path
, substring(url from 'id=([^&]*)') AS id
-- Redshift의 경우 regexp_substr 함수와 regexp_replace 함수를 조합해서 사용하기
-- ,regexp_extract(url, '//[^/]+([^?#]+)') AS path
-- ,regexp_extract(url, 'id=([^&]*)') AS id
-- Hive, SparkSQL의 경우 parse_url 함수로 URL 경로 부분 또는 쿼리 매개변수 부분의 값 추출하기
-- ,parse_url(url, 'PATH') AS path
-- ,parse_url(url, 'QUERY', 'id') AS id
FROM access_log
;

실행결과

stamp	url	path	id
2016-08-26 12:02:00	http://www.example.com/video/detail?id=001	/video/detail	001
2016-08-26 12:02:01	http://www.example.com/video#ref	/video
2016-08-26 12:02:01	http://www.example.com/book/detail?id=002	/book/detail	002

 

정리

URL 처리는 웹 서비스 로그 분석에서 자주 사용되는 기술입니다. 하지만 미들웨어에 따라서 함수 이름이 다르거나, 정규 표현식의 작성 방법이 달라 자주 문제가 발생하는 부분 입니다. 이 책에서도 URL을 다루는 예가 여러 가지 나오므로, 일단 지금 다루었던 기초적인 내용부터 확실하게 기억하도록 합시다.

문자열을 배열로 분해하기

빅데이터 분석에서 가장 많이 사용되는 자료형은 문자열이지만, 문자열 자료형은 범용적인 자료형이므로 더 세부적으로 분해해서 사용해야 하는 경우가 많습니다. 예를 들어 영어 문장을 공백으로 분할해서 하나하나의 단어로 구분하는 경우, 쉼표로 연결된 데이터를 잘라 하나하나의 값을 추출하는 경우 등입니다.

 

여기서는 앞에서 사용한 접근 로그 샘플을 기반으로 페이지 계층을 나누어 봅시다. 다음 코드 예는 URL 경로를 슬래시로 분할해서 계층을 추출하는 쿼리입니다.

 

5-4 URL 경로를 슬래시로 분할해서 계층을 추출하는 쿼리

SELECT
	stamp
    ,url
 -- 경로를 슬래시로 잘라 배열로 분할하기
 -- 경로가 반드시 슬래시로 시작하므로 2번째 요소가 마지막 계층
 -- PostgreSQL의 경우 split_part로 n번째 요소 추출하기
 , split_part(substring(url from '//[^/]+([^?#]+)'), '/', 2) AS part1
 , split_part(substring(url from '//[^/]+([^?#]+)'), '/', 3) AS part2
 
-- Redshift도 split_part로 n번째 요소 추출하기
-- , split_part(regexp_replace(
--  regexp_substr(url, '//[^/] + [^?#]+ '), '//[^/]+', ''), '/', 2) AS part1
-- , split_part(regexp_replace(
-- 	regexp_substr(url, '//[^/]+[^?#]+'), '/')[SAFE_ORDINAL(2)] AS part1
-- 	regexp_substr(url, '//[^/]+[^?#]+'), '/')[SAFE_ORDINAL(3)] AS part2
-- Hive, SparkSQL 도 split 함수를 사용해 배열로 자름
-- 다만 배열의 인덱스가 0부터 시작하므로 주의하기
-- , split(parse_url(url, 'PATH'), '/')[1] AS path1
-- , split(parse_url(url, 'PATH'), '/')[2] AS path2
FROM access_log
;

 

실행결과

stamp	url	path1	path2
2016-08-26 12:02:00	http://www.example.com/video/detail?id=001	video	detail
2016-08-26 12:02:01	http://www.example.com/video#ref	video
2016-08-26 12:02:01	http://www.example.com/book/detail?id=002	book	detail

 

정리

Redshift는 공식적으로는 배열 자료형의 지원하지 않지만, split_part 함수를 사용해 문자열을 분할한 뒤 n번째 요소를 추출할 수 있습니다.

배열의 인덱스는 일반적으로 1부터 시작하지만, BigQuery의 경우 배열의 값에 접근하는 방법이 조금 특이합니다. 배열의 인덱스를 0부터 시작하려면 OFFSET, 1부터 시작하려면 ORDINAL을 지정합니다. 추가로 배열 길이 이상의 인덱스에 접근하면 일반적으로 NULL을 리턴하지만, BigQuery는 오류를 리턴합니다. NULL을 리턴하게 하려면 SAFE_OFFSET 또는 SAFE_ORDINAL을 지정해야 합니다.

 

날짜와 타임스탬프 다루기

로그 데이터를 처리할 때는 날짜 또는 타임스탬프 등의 시간 정보가 굉장히 많이 활용됩니다. 하지만 미들웨어에 따라서 시간 정보를 다루는 자료형 또는 함수에 큰 차이가 있습니다. 지금 부터 날짜와 타임스탬프를 다룰 때 사용하는 방법을 살펴보겠습니다.

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

현재 날짜와 타임스탬프를 추출하는 쿼리는 다음과 같습니다(코드5-5). 미들웨어에 따라 추출 방법이 다르므로 주의해주세요. 추가로 같은 쿼리를 작성해도 미들웨어마다 리턴값이 달라지는 경우가 있습니다.

 

PostgreSQL에서는 CURRENT_TIMESTAMP의 리턴 값으로 타임존이 적용된 타임 스탬프 자료형이 나옵니다. 하지만 이 이외의 미들웨어는 타임존 없는 타임스탬프를 리턴합니다. 따라서 리턴 값의 자료형을 맞출 수 있게 PostgreSQL에서는 LOCALTIMESTAMP를 사용하는 것이 좋습니다. 추가로 BigQuery는 UTC 시간을 리턴합니다. 따라서 CURRENT_TIMESTAMP로 리턴되는 시각이 한국 시각과 다르므로, 예상하지 못한 곳에서 문제가 발생할 수 있으므로 주의해 주세요.

 

5-5 현재 날짜와 타임스탬프를 추출하는 쿼리

SELECT
-- PostgreSQL, Hive, BigQuery의 경우
-- CURRENT_DATE 상수와 CURRENT_TIMESTAMP 상수 사용하기
   CURRENT_DATE AS dt
,  CURRENT_TIMESTAMP AS stamp
-- HIVE, BigQuery, SparkSQL의 경우
-- CURRENT_DATE() 함수와 CURRENT_TIMESTAMP() 함수 사용하기
-- CURRENT_DATE() AS dt
-- , CURRENT_TIMESTAMP() AS stamp
-- Redshift의 경우 현재 날짜는 CURRENT_DATE, 현재 타임 스탬프는 GETDATE() 사용하기
   CURRENT_DATE AS dt
  ,GETDATE() AS stamp
-- PostgreSQL의 경우 CURRENT_TIMESTAMP는 타임존이 적용된 타임스탬프
-- 타임존을 적용하고 싶지 않으면 LOCALTIMESTAMP 사용하기
-- , LOCALTIMESTAMP AS stamp
;

실행결과

dt	stamp
2017-01-30	2017-01-30 18:42:57.584993

 

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

현재 시각이 아니라 문자열로 지정한 날짜와 시각을 기반으로 날짜 자료형과 타임스탬프 자료형의 데이터를 만드는 경우가 있습니다. 미들웨어에 따라서 다양한 방법이 있는데요. 다음 코드처럼 CAST 함수를 사용하는 방법이 가장 범용적이라고 할 수 있습니다. 더 자세한 내용은 다음 코드의 주석을 참고해주세요.

 

5-6 문자열을 날짜 자료형,타임스탬프 자료형으로 변환하는 쿼리

-- 문자열을 날짜/타임스탬프로 변환하기
SELECT
	-- PostgreSQL,Hive, Redshift, BigQuery, SparkSQL 모두 'CAST(value AS type)' 사용하기
    CAST('2016-01-30' AS date) AS dt
   ,CAST('2016-01-30 12:00:00' AS timestamp) AS stamp
   
   -- Hive,BigQuery의 경우 'type(value)' 사용하기
   -- data('2016-01-30') AS dt
   --, timestamp('2016-01-30 12:00:00') AS stamp
   
   -- PostgreSQL, Hive, Redshift, BigQuery, SparkSQL 모두 'type value' 사용하기
   -- 다만 value는 상수이므로 컬럼 이름으로 지정할 수 없음
   -- date '2016-01-30' AS dt
   --, timestamp '2016-01-30 12:00:00' AS stamp
   
   -- PostgreSQL, Redshift의 경우 'value::type' 사용하기
   -- '2016-01-30' :: date AS dt
   --, '2016-01-30 12:00:00 ::timestamp AS stamp
;

실행결과

dt	stamp
2016-01-30	2016-01-30 12:00:00

 

날짜/시각에서 특정 필드 추출하기

타임스탬프 자료형의 데이터에서 년과 월 등의 특정 필드 값을 추출할 때는 EXTRACT 함수를 사용합니다. EXTRACT 함수를 지원하지 않는 Hive와 SparkSQL은 각각의 필드를 추출하는 별도의 함수가 제공됩니다. 자세한 내용은 다음 코드를 참고해주세요.

 

5-7 타임스탬프 자료형의 데이터에서 연,월,일 등을 추출하는 쿼리

SELECT
	stamp
-- PostgreSQL, Redshift, Bigquery의 경우 EXTRACT 함수 사용하기
  , EXTRACT(YEAR FROM stamp) AS year
  , EXTRACT(MONTH FROM stamp) AS month
  , EXTRACT(DAY FROM stamp) AS day
  , EXTRACT(HOUR FROM stamp) AS hour
  
  
-- Hive, SparkSQL의 경우 EXTRACT 함수 대신 다음과 같은 함수 사용하기
-- ,YEAR(stamp) AS year
-- ,MONTH(stamp) AS month
-- ,DAY(stamp) AS day
-- ,HOUR(stamp) AS hour

FROM 
  (SELECT CAST('2016-01-30 12:00:00' AS timestamp) AS stamp) AS t
;

 

실행결과

stamp	year	month	day	hour
2016-01-30 12:00:00	2016	1	30	12

 

5-8 타임스탬프를 나타내는 문자열에서 연,월,일 등을 추출하는 쿼리

SELECT
   stamp
-- PostgreSQL, Hive, Redshift, SparkSQL 에서는 substring 함수 사용하기
, 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

-- PostgreSQL, Hive, BigQuery, SparkSQL 에서는 substr 함수 사용하기
-- , substr(stamp, 1, 4) AS year
-- , substr(stamp, 6, 2) AS month
-- , substr(stamp, 9, 2) AS day
-- , substr(stamp, 12, 2) AS hour
-- , substr(stamp, 1, 7) AS year_month

FROM 
-- PostgreSQL, Redshift의 경우 문자열 자료형으로 text 사용하기
 (SELECT CAST('2016-01-30 12:00:00' AS text) AS stamp) AS t
-- Hive, BigQuery, SparkSQL의 경우 문자열 자료형으로 string 사용하기
-- (SELECT CAST('2016-01-30 12:00:00' AS string) AS stamp) AS t
;

 

실행결과

stamp	year	month	day	hour	year_month
2016-01-30 12:00:00	2016	01	30	12	2016-01

정리

날짜와 시간 정보는 로그 데이터에서 빠지지 않는 정보입니다. 타임존을 고려해야 하고, 미들웨어들의 차이를 주의해야 합니다. 중요한 내용이므로 배운 내용을 꼭 기억해주세요.

 

결손 값을 디폴트 값으로 대치하기

문자열 또는 숫자를 다룰때는 중간에 NULL이 들어있는 경우를 주의해야 합니다. NULL과 문자열을 결합하면 NULL이 되며, NULL과 숫자를 사칙연산해도 NULL이 됩니다. 처리 대상인 데이터가 우리가 원하는 형태가 아닐 경우에는 반드시 데이터를 가공해야 합니다. 구매액과 NULL을 포함하는 쿠폰금액이 저장된 테이블이 있을때 다음 코드는 쿠폰으로 할인했을 때의 매출 금액을 구하는 쿼리입니다.

Purchase_id	amount	coupon
10001	3280	NULL
10002	4650	500
10003	3870	NULL

 

5-9 구매액에서 할인 쿠폰 값을 제외한 매출 금액을 구하는 쿼리

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

 

실행결과

purchase_id	amount	coupon	discount_amount1	discount_amount2
10001	3280	NULL	NULL	3280
10002	4650	500	4150	4150
10003	3870	NULL	NULL	3870

 

이 코드 예에서 discount_price1은 price에서 coupon을 그대로 뺀 값입니다 따라서 coupon이 NULL 레코드라면 NULL이 됩니다. discount_price2는 coupon이 NULL일 때 COALESCE 함수를 사용해 0으로 대치하므로, coupon을 사용하지 않은 경우에도 제대로 된 값이 계산됩니다.

---

여러개의 값에 대한 조작

 

1. 문자열 연결하기

리포트를 작성할때는 용도에 맞게 여러개의 데이터를 연결해서 다루기 쉬운 형식으로 만드는 경우가 많습니다.

여기서는 아래의 샘플 데이터를 대상으로 주소를 연결해 보겠습니다. 예를들어 'U001, 서울시,강서구'이라는 데이터가 있으면 '서울시강서구'라는 형태로 문자열 연결하는 것입니다. 이러한 SQL은 다음과 같습니다.

 

6-1 사용자의 주소 정보(mst_user_location)테이블

user_id	pref_name	city_name
U001	서울특별시	강서구
U002	경기도수원시	장안구
U003	제주특별자치도	서귀포시

 

6-1 문자열을 연결하는 쿼리

SELECT
	user_id
    -- PostgreSQL, Hive, Redshift, BigQuery, SparkSQL 모두 CONCAT 함수 사용 가능
    -- 다만 Redshift의 경우는 매개변수를 2개 밖에 못받음
   , CONCAT(pref_name, city_name) AS pref_city
   -- PostgreSQL, Redshift의 경우는 ll 연산자도 사용 가능
   -- , pref_name ll city_name AS pref_city

FROM
	mst_user_location
;

 

실행결과

user_id	pref_city
U001	서울특별시강서구
U002	경기도수원시장안구
U003	제주특별자치도서귀포시

 

정리

대부분의 미들웨어에서 CONCAT 함수를 사용해 원하는 만큼의 문자열을 연결할 수 있습니다. 하지만 Redshift의 CONCAT은 매개변수에 2개의 문자열만 전달할 수 있습니다. 대신 Redshift의 경우는 CONCAT 함수 대신 || 연산자를 사용하면 됩니다.

 

2. 여러 개의 값 비교하기

여기서는 하나의 레코드에 포함된 여러 개의 값을 비교하는 방법을 소개하겠습니다. 다음 데이터는 4분기 매출(quarterly_sales)을 나타내는 테이블입니다. 각 연도의 4분기 매출(q1~q4)이 컬럼으로 표현되어 있습니다.

 

2017년 3분기와 4분기(q3와 q4)는 매출 금액이 확정되지 않아서 NULL입니다. 이러한 테이블을 샘플로 여러가지 예를 살펴봅니다.

 

6-2 4분기 매출(quaterly_sales) 테이블

year	q1	q2	q3	q4
2015	82000	83000	78000	83000
2016	85000	85000	80000	81000
2017	92000	81000

 

분기별 매출 증감 판정하기

일단 분기마다 매출이 증가했는지 감소했는지 판정해보겠습니다. SQL에서는 하나의 레코드에 포함된 값을 SELECT 구문으로 한꺼번에 참조할 수 있습니다.

 

각 컬럼의 크고 작음을 비교하려면 다음 코드 예의 judge_q1_q2 컬럼처럼 CASE 식을 사용해 조건을 기술하고, 그러한 조건에 맞게 값을 지정합니다. q1보다 q2의 매출이 많은 경우에는 '+', 같은 경우에는 공백, 적은경우에는 '-'를 출력했습니다.

 

추가로 값의 차이를 구하려면 diff_q2_q1 컬럼처럼 간단하게 컬럼을 빼면 됩니다. 이러한 차이와 SIGN 함수를 조합하면, CASE 식보다 간단하게 값의 증감 판정을 할 수 있습니다. SIGN 함수는 매개변수가 양수라면 1, 0이라면 0, 음수라면 -1을 리턴하는 함수입니다.

 

6-2 q1,q2 컬럼을 비교하는 쿼리

SELECT
   YEAR
  ,q1
  ,q2
  -- Q1과 Q2의 매출 변화 평가하기
  ,CASE
    WHEN q1 < q2 THEN '+'
    WHEN q1 = q2 THEN ''
    ELSE '-'
  END AS judge_q1_q2
  -- Q1과 Q2의 매출액 차이 계산하기
  , q2 - q1 AS diff_q2_q1
  -- Q1 과 Q2의 매출 변화를 1, 0, -1로 표현하기
  , SIGN(q2 - q1) AS sign_q2_q1
FROM
  quarterly_sales
ORDER BY
  year
;

 

실행결과

year	q1	q2	judge_q1_q2	diff_q2_q1	sign_q2_q1
2015	82000	83000	+	1000	1
2016	85000	85000		0	0
2017	92000	81000	-	-11000	-1

 

코드 예의 q1,q2 비교와 마찬가지로 q2와 q3, q3와 q4도 비교할 수 있습니다. 다만 작년의 q4와 다음년도의 q1을 비교할 때는 이 방법을 사용할 수 없습니다. 이번에 사용한 샘플 테이블에서는 하나의 레코드에 같은 연도의 4분기 매출만 저장되어 있으므로, 여러 레코드에 걸쳐있는 값을 비교하려면 여러 개의 레코드를 조합해야 합니다. 이 방법은 7강에서 소개하겠습니다.

 

연간 최대/최소 4분기 매출 찾기

지금까지 2개의 컬럼을 대소 비교하는 방법을 소개했습니다. 3개이상의 컬럼을 비교할 때도 마찬가지의 방법을 사용하면 되지만,

컬럼의 수가 많아지면 코드가 복잡해집니다.

 

컬럼 값에서 최댓값 또는 최솟값을 찾을 때는 greatest 함수 또는 least 함수를 사용합니다. greatest 함수와 least 함수는 SQL 표준에 포함되지 않지만, 대부분의 SQL 쿼리 엔진에서 구현하고 있습니다.

 

6-3 연간 최대/최소 4분기 매출을 찾는 쿼리

SELECT
   year
   -- Q1~Q4의 최대 매출 구하기
  , greatest(q1,q2,q3,q4) AS greatest_sales
   -- Q1~Q4의 최소 매출 구하기
  , least(q1,q2,q3,q4) AS least_sales
FROM
  quarterly_sales
ORDER BY
  year
;

실행결과

year	greatest_sales	least_sales
2015	83000	78000
2016	85000	80000
2017	92000	81000

 

연간 평균 4분기 매출 계산하기

greatest 함수 또는 least 함수는 기본 제공되는 함수입니다. 이를 활용해 여러 개의 컬럼에 처리하는 경우를 생각해봅시다. 예를 들어 Q1에서 Q4의 매출 평균을 계산하려면 어떻게 해야할까요? 단순하게 다음 코드처럼 q1부터 q4까지의 값을 계산하고 4로 나누면 q1부터 q4의 평균값을 구할 수 있습니다.

 

6-4 단순한 연산으로 평균 4분기 매출을 구하는 쿼리

SELECT
   year
  ,(q1 + q2 + q3 + q4) / 4 AS average
FROM
  quarterly_sales
ORDER BY
  year
;

실행결과

year	average
2015	81500
2016	82750
2017

 

NULL 값을 사칙 연산하려면 앞서 5강에서 소개했던 COALESCE 함수를 사용해 적절한 값으로 변환해야 합니다. 일단 예의 쿼리 결과처럼 2017년의 평균 매출을 구할 수 있습니다. 그런데 q3와 q4의 매출을 0으로 변환해버리면, q1과 q2의 매출 합계를 4로 나누게 되어 평균값이 크게 낮아집니다.

 

6-5 COALESCE를 사용해 NULL을 0으로 변환하고 평균값을 구하는 쿼리

SELECT
  year
  (COALESCE(q1, 0) + COALESCE(q2, 0) + COALESCE(q3, 0) + COALESCE(q4, 0)) / 4
  AS average
FROM
 quarterly_sales
ORDER BY
 year
;

 

실행결과

year	average
2015	81500
2016	82750
2017	43250

 

2017년의 q1과 q2 매출만으로 평균을 구하려면 NULL이 아닌 컬럼의 수를 세서 나눠야 합니다. COALESCE 함수와 SIGN 함수를 조합해서 분모의 값을 적절하게 계산합니다. 코드 예는 다음과 같습니다. 이 쿼리는 q1과 q2만으로 평균을 구해 계산합니다.

 

6-6 NULL이 아닌 컬럼만을 사용해서 평균값을 구하는 쿼리

SELECT
   year
  , (COALESCE(q1, 0) + COALESCE(q2, 0) + COALESCE(q3, 0) + COALESCE(q4, 0))
  / (SIGN(COALESCE(q1, 0)) + SIGN(COALESCE(q2, 0))
  + SIGN(COALESCE(q3, 0)) + SIGN(COALESCE(q4, 0)))
  AS average
FROM
 quarterly_sales
ORDER BY
 year
;

실행결과

year	average
2015	81500
2016	82750
2017	86500

 

정리

하나의 레코드 내부에 있는 값끼리 연산할 때는 여러 개의 컬럼에 있는 비교/계산 처리가 굉장히 간단합니다. 하지만 여러 레코드에 걸쳐 있는 값들을 처리할 때는 이후 7강에서 소개할 집약 함수를 사용해서 데이터를 가공하고 처리해야 합니다. 이와 관련된 내용은 이후에 다시 살펴보겠습니다.