저번 취치와 마찬가지로 SQL 쿼리 역량 증진을 위한 목적으로 '데이터분석을 위한 SQL 레시피' 를 활용한 공부 내용을 정리해보고자 합니다.
0. 변수간 연산을 통한, 새로운 지표 정의
어떤 페이지가 출력된 횟수를 <페이지뷰>, 어떤 페이지를 출력한 사용자수를 <방문자 수> 라고 정의했을때, 우리는 이를 기반으로 사용자 한명의 평균 페이지 방문수 <페이지뷰>/<방문자 수> 라는 새로운 지표를 계산할 수 있다.
실제 웹사이트에서는 이와같이, 사용자 수 중 특정한 행동을 진행한 사용자 비율을 CTR(Click Through Rate; 클릭 비율), CVR(Conversion Rate; 변환 비율) 라는 새로운 지표로서 정의하고 활용하는 경우가 많다.
이처럼 단순히 큰 숫자간의 비교만을 주목하지 않고, '개인별', '비율' 이라는 지표를 사용하게 되면 다양한 관점에서의 데이터를 바라볼 수 있다.
1. 문자열 연결
두개의 문자열 컬럼을 합치는 경우 Concat 또는 || 문자를 사용할 수 있다. Concat의 경우 모든 SQL에서 동일하게 사용가능하다. 그러나 일부 예외사항이 있는데, MySQL의 경우에는 || 를 boolean 으로만 활용할 수 있기에 본 목적으로 활용이 불가능하고, redshift의 경우에는 Concat 인자로 매개변수를 2개밖에 받지 못하므로 || 를 적극 활용하자.
활용 예 ) 시 / 군 컬럼을 하나의 컬럼으로 합치기, first name 과 last name을 하나의 full name 으로 만들기.
select
user_id,
concat(pref_name, city_name) as pref_city
from
mst_user_location;
2. 대소비교 (분기별 매출 증감, 각 년도 최대/최소 매출액 계산)
아래와 같은 1년 간의 4분기 매출이 들어있는 테이블이 존재한다. 각 분기의 매출은 분기별로 컬럼으로 표현되어 있다.
이때 1분기 매출액(q1) 과 2분기 매출액(q2) 간의 차이를 바탕으로 매출증감을 계산하고 늘어난 경우 '+', 같은 경우 공백, 줄어든 경우 '-' 를 나타나는 diff_q1_q2 컬럼을 계산해보자.
select
year,
q1,
q2,
-- 매출 변화 판별
case
when q1 < q2 then '+'
when q1 = q2 then ' '
else '-'
end as judge_q1_q2,
-- 매출액 차이 계산
q2 - q1 as diff_q2_q1,
sign(q2 - q1) as sign_q2_q1
from
quarterly_sales
order by
year ;
* SIGN() : 매개변수의 부호값을 기준으로 1/0/-1 로 표현하는 함수.
만약 이후 년도의 매출액이 다른 레코드 존재하여, 이번년도 4분기 매출액(q4) 와 내년 1분기 매출액(q1) 과의 매출액 비교시 코드를 여러 레코드를 한번에 가져와서 계산하는 방식으로 구현해야 한다.
+ 최댓값 / 최솟값 계산 (greatest / least)
대소비교에는 최대/최소를 안 구할수가 없다. 3개 이상의 컬럼을 비교할 때, 기존의 두 값에 대한 비교를 복잡하게 짜 줄 필요 없이 greatest, least 함수를 통해 최대최소를 계산할 수 있다. 이 두 함수는 비록 SQL 표준에는 포함되진 않지만, 모든 SQL 엔진에서 동일한 방식으로 활용할 수 있다.
+ 다만 코드를 따라하는 중 문제가 발생했는데, MySQL 의 경우 대부분의 처리 함수에서 인자로 Null 값을 포함하는 경우, 출력값이 Null 이 출력된다. 즉 현재 예제와 같이 최대값/최솟값 계산에 null 값이 포함되는 경우 결측치가 아닌 값 중 최댓값/최솟값을 간단하게 계산할 수 없다는 것이다.
얼핏 보면 coalesce(A, 0) 를 활용하여 해결 가능할 것 같지만, least를 계산할 때 대체값 0이 최솟값 탐색을 방해하는 문제가 발생한다. 따라서 아래와 같은 방식이 내가 생각했을때 최선의 방식으로 생각된다.
SELECT
year,
q1, q2, q3, q4,
GREATEST(COALESCE(q1, 0), COALESCE(q2, 0), COALESCE(q3, 0), COALESCE(q4, 0)) AS greatest_sales,
LEAST(
COALESCE(q1, (SELECT greatest_sales FROM quarterly_sales WHERE year = t.year)),
COALESCE(q2, (SELECT greatest_sales FROM quarterly_sales WHERE year = t.year)),
COALESCE(q3, (SELECT greatest_sales FROM quarterly_sales WHERE year = t.year)),
COALESCE(q4, (SELECT greatest_sales FROM quarterly_sales WHERE year = t.year))
) AS least_sales
FROM
quarterly_sales AS t;
내가 구성한 코드는 위와같이, geatest 계산으로 계산된 최댓값을 대체값으로 활용하는 방식이다.
3. 평균 계산, 비율 계산
Null 값이 포함된 컬럼이 존재하는 경우 평균 계산에서도 제약이 있다. (변수의 총합) / (변수의 갯수) 의 측면에서 변수의 총합은 coalesce 를 이용해 Null 값을 0으로 대체 가능하다. 변수의 갯수의 경우, 값이 있는 경우 갯수를 셈, Null 값은 계산에서 제외되어야 하므로, coalesce 와 sign을 이용하여 아래와 같이 처리해줄 수 있다.
-- 연 매출 평균 계산
select
year,
q1, q2, q3, q4,
(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 ;
CTR(Click Through Rate; 클릭률) 은 광고에 있어 중요한 지표중 하나이다. 이는 <클릭수>/<노출수> 로 계산 가능하다. 그러나 아래의 (dt = '2017-04-02', ad_id = 1) 의 경우와 같이 분모인 <노출수> 의 값이 0을 갖는 경우에 대한 처리가 팔요하다. (왜인지 workbench에서는 에러 없이 잘 작동함)
이때는 CASE WHEN 을 사용하여 분모에 0이 들어갈 경우에 대한 에러처리를 해주는 방식, NULLIF를 활용하여 분모에 0이 나오면 NULL로 변환하여 계산식 전체를 NULL로 날려버리는 방식이 존재한다.
select
dt,
ad_id,
impressions,
clicks,
case
when impressions > 0 then 100.0 * clicks / impressions
end as ctr_as_percent_by_case,
100.0 * clicks / nullif(impressions, 0) as ctr_as_percent_by_null
from
advertising_stats
order by
dt, ad_id
;
4. 두 값 사이의 거리 / 날짜 연산
두 값 사이의 거리로 우리는 일반적으로 euclidean distance를 활용한다. 이는 이동한 모든 축으로의 이동거리의 제곱합의 제곱근으로 표현 가능하다. 이는 타 프로그래밍 언어와 유사하게 power, sqrt 를 활용하여 구현할 수 있다.
select
x1, x2, y1, y2,
sqrt(power(x1 - x2, 2) + power(y1 - y2, 2)) as dist
-- (postageSQL : point(x1, y1) <-> point(x2, y2))
from
location_2d
;
시간의 계산, 시간 이동의 경우 조금 복잡해지는 부분이 바로 각 SQL 쿼리 언어마다 다르게 작동된다.
postageSQL -> timestamp, interval 활용 (참고)
Redshift -> dateadd
BigQuery -> timestamp_add/sub, date_add/sub
Hive/SparkSQL -> nuixtime 변환 후 초단위 계산, 이후 다시 타임스탬프 변환
MySQL -> date_add, date_sub (참고)
MySQL의 경우 기존에 사용했던 CAST, 위에서 언급한 DATE_ADD(날짜 덧셈), DATE_SUB(날짜 뺄셈) 를 활용하여 간단하게 계산해볼 수 있다.
select
user_id,
register_stamp,
birth_date,
-- 날짜 이동
cast(register_stamp as datetime) as register_stamp,
date_add(cast(register_stamp as datetime), interval 1 hour) as after_1_hour,
date_sub(cast(register_stamp as datetime), interval 30 minute) as before_30_minutes,
cast(register_stamp as date) as register_date,
date_add(cast(register_stamp as date), interval 1 day) as after_1_day,
date_sub(cast(register_stamp as date), interval 1 month) as before_1_month
from
mst_users_with_dates
5. 집약 함수 사용법 & 그룹화 (group by, partition by)
그룹화 이전에, 집약함수를 알아 둬야 우리는 그룹화를 실시하여 각 그룹에 대한 특성을 하나의 요약된 값으로 비교해 볼 수 있다. 집약함수에는 레코드의 수 세기, 레포드 갑 합계, 평균, 최대, 최소를 계산하는 간단한 함수부터, 통계적 처리를 활용해 하나의 통계지표를 출력해주는 함수도 포함되어 있다.
또한 SQL:2003 에서 도입된 윈도 함수(분석 함수) 를 활용하면 기존에 하기 힘들었던, 순서를 고려하는 처리, 여러게의 레코드 대상의 처리를 비교적 휩게 처리할 수 있다.
예제 테이블은 상품평가 테이블로, 각 유저별, 상품별 리뷰 점수를 기록한 테이블이다.
먼저 집계함수를 활용하여 각 자료에 대한 간단한 집계 사례를 살펴보고 (after1), 각 유저ID 별 집계를 그룹화 하여 계산 (after2), 마지막으로 윈도 함수를 활용해 원래의 값과 집약된 값의 비교를 통해 새로운 인사이트를 도출하는 사례를 살펴볼 것이다(after3.)
select
count(*) as total_count,
count(distinct user_id) as user_count,
count(distinct product_id) as product_count,
sum(score) as sum,
avg(score) as avg,
max(score) as max,
min(score) as min
from
review;
GROUP BY 는 간단하게 이후 그룹화하고자 하는 키를 지정해주는 쿼리를 추가함으로서 간단하게 구현할 수 있다. 다만, 주의할 점은, GROUP BY 를 활용한 쿼리에는 그룹화된 키, 집약함수 외에는 컬럼으로 표시할 수 없다.(<=> 집약전 값과 집약 후 값을 동시에 보여줄 수 없음) 예를들어 각 유저의 평균 리뷰 점수와, 유저의 개별 상품별 리뷰점수를 함께 표시할 수 없음을 의미한다.
select
user_id,
count(*) as total_count,
count(distinct user_id) as user_count,
count(distinct product_id) as product_count,
sum(score) as sum,
avg(score) as avg,
max(score) as max,
min(score) as min
from
review
group by
user_id ;
그러나 윈도 함수를 이용하여 그룹화 하는 경우 집약 함수와 집약 전 값을 동시에 표시할 수 있다. 윈도 함수를 활용한 집약함수의 사용법은 간단하게 집약 함수 뒤 OVER(PARTITION BY {그룹화 할 컬럼 명}) 를 활용하여 지정할 수 있다. 이때 PARTITION BY 가 없다면, OVER() 는 전체 열에 대한 집약함수를 적용하여 동작한다. (그룹화 없이, after1의 결과와 같음)
select
user_id,
product_id,
-- 개별 리뷰 점수
score,
-- 전체 리뷰 평균 점수
avg(score) over() as avg_score,
-- 개별 유저 평균 리뷰 점수
avg(score) over(partition by user_id) as user_avg_score,
-- 개별 유저 평균 점수 대비 리뷰 점수 차이
score - avg(score) over(partition by user_id) as user_avg_score_diff
from
review;
6. 그룹핑과 순서정렬 활용
윈도우 함수는 그룹핑 외에도 ORDER BY 순서 정렬 기능을 지원한다. 윈도 함수 이전에는 개별 ROW 별 순서 개념이 없었기에 복잡한 방식을 적용하여 열별 대소비교 후 정렬이 가능했지만, 윈도 함수를 이용하면 이를 활용한 다양한 활용방식을 사용할 수 있다.
여기서 소개할 방식은 아래와 같다.
1) 특정 행 기준 정렬 및 순서 매기기
2) 순서 정렬과 집약함수 활용
2) 윈도우 프레임 지정을 통해 테이블 내 상대적 위치를 기준으로한 윈도 지정
3) 그루핑과 정렬 동시에 활용하기 (각 카테고리별 상위 N개 추출)
활용할 데이터는 제품별 카테고리와 리뷰점수로, 아래와 같다.
after1 에서는 순서 정렬, 순위 부여 함수, 윈도우 프레임 지정 함수에 대해 다루는데, 미리 이에 대해 짚고 넘어가고자 한다. 순서 정렬은 위의 윈도우함수를 활용했던 방식 그대로, OVER(ORDER BY <정렬 기준 열> {DESC}) 으로 활용할 수 있다. (Desc : 내림차순)
다음으로, 순위 부여 함수이다. 대표적인 순위 함수는 총 세가지로 ROW_NUMBER, RANK, DENSE_RANK 가 있다. 다만 주의할 점은 이러한 함수들이 정렬기능까지 수행하는 것이 아니기 때문에 일반적으로 ORDER BY와 함께 사용된다.
- ROW_NUMBER() : 각각 유일한 서로다른 순위를 부여 (ex 1,2,3,4)
- RANK() : 동 순위 허용, 순서대로 번호 부여 (ex 1,2,2,3)
- DENSE_RANK() : 동 순위 허용, 같은 순위가 존재할 경우 다음 순위를 건너뛰고 순위 부여 (ex 1,2,2,4)
다음으로 윈도우 프레임 지정함수이다. 여기서 프레임 지정이란, 현재 열(레코드) 를 기준으로 상대적인 윈도우 범위를 정의하는 구문이다. 예를들면 현재 열 기준 바로 위의 열, 바로 아래열, 현재 열부터 마지막 열까지 등 상대적인 위치기반 그룹 범위를 지정하는 방식이다. 간단하게 사용되는 함수로 아래와 같다.
- LAG(<기준 행>, n) : 현재 열을 기준으로, n번째 앞에 있는 열의 <기준 행> 값을 추출.
- LEAD(<기준 행>, n) : 현재 열을 기준으로, n번째 뒤에 있는 열의 <기준 행> 값을 추출.
- ROWS BETWEEN <start> AND <end> : start 부터 end 열까지의 모든 범위에 대해 윈도우 프레임 지정.
- CURRENT_ROW : 현재 기준 행
- n PRECEDING : 현재 행 기준 n 행 앞
- n FOLLOWING : 현재 행 기준 n 행 뒤
- UNBOUNDED PRECEDING : 이전 행 전부
- UNBOUNDED FOLLOWING : 이후 행 전부
여기까지 정리가 됐으니 after1 을 보자.
select
product_id,
score,
-- 점수가 높은순서대로 유일한 순서 부여
ROW_NUMBER() OVER(ORDER BY score DESC) as row_num,
-- 점수가 높은순서대로 동 순위를 인정한 순서 부여
RANK() OVER(ORDER BY score DESC) AS rank_val,
-- 점수가 높은순서대로 동 순위 시 다음 순위를 건너뛰고 점수 부여
DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank_val,
-- 현재 행보다 앞선(윗 행) 값 추출
LAG(product_id) OVER(ORDER BY score DESC) AS lag1,
LAG(product_id,2) OVER(ORDER BY score DESC) AS lag2,
-- 현재 행보다 뒤의(아래 행) 값 추출
LEAD(product_id) OVER(ORDER BY score DESC) AS lead1,
LEAD(product_id,2) OVER(ORDER BY score DESC) AS lead2
from
popular_products
order by
row_num;
after2 는 정렬과 집약함수의 동시 활용방안, 추가적으로 윈도우 프레임 지정에 대해 조금 더 연습하기 위한 쿼리이다.
select
product_id,
score,
-- 고유 순서 부여
ROW_NUMBER() over(order by score desc) as row_num,
-- 순위 상위부터의 누계 점수 계산
sum(score) over(order by score desc
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cum_sum,
-- 현재 행의 앞 뒤까지의 평균
avg(score) over(order by score desc
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as local_avg
from
popular_products
order by
row_num ;'My SQL' 카테고리의 다른 글
| [데이터 분석을 위한 SQL 레시피] 데이터 분석 목적과 분석 방향 정리 (1) | 2024.06.18 |
|---|---|
| My SQL "데이터 분석을 위한 SQL 레시피" - part3 테이블 구조 변경, 다중 테이블 조작 (0) | 2024.03.22 |
| MySQL "데이터 분석을 위한 SQL 레시피" - part1 값 조작하기 (0) | 2024.02.27 |
| 비전공자 SQLD 시험 후기 (1) | 2023.03.24 |
| SQL이란 무엇인가? (SQL을 배워야 하는 이유) (0) | 2023.01.25 |