2024-01-02 TIL
1. LAG 함수에 관하여 (sql)
LAG 및 LEAD 함수는 SQL에서 윈도우 함수 중 하나로, 각각 현재 행의 이전 행과 다음 행의 값을 가져오는 데 사용된다.
이 함수들은 특히 시계열 데이터나 순서가 있는 데이터에서 사용된다.
먼저, LAG 함수는 현재 행 이전의 행에 있는 값을 가져오기위해 사용되며
아래와 같이 사용할 수 있다.
LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
column_name : 가져올 값이 있는 열의 이름
offset : 몇 행 전의 값을 가져올지를 지정하는 매개변수 (기본값은 1)
default_value : 만약 이전 행이 존재하지 않을 때 반환할 기본값
PARTITION BY : 윈도우를 나눌 때 사용되는 명령어 (선택사항)
ORDER BY : 정렬 순서를 지정하는 명령어
반대로 LEAD 함수는 현재 행 다음의 행에 있는 값을 가져올 때 사용할 수 있으며
다음과 같은 방식으로 사용할 수 있다
LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
column_name : 가져올 값이 있는 열의 이름
offset : 몇 행 다음의 값을 가져올지를 지정하는 매개변수 (기본값은 1)
default_value : 만약 이전 행이 존재하지 않을 때 반환할 기본값
PARTITION BY : 윈도우를 나눌 때 사용되는 명령어 (선택사항)
ORDER BY : 정렬 순서를 지정하는 명령어
---------------------------------------------------------------------------------------------------------------------
2. DATEDIFF 함수와 날짜 데이터 계산 중 발생하는 오류에 대하여 (sql)
날짜데이터를 이용해야 할 때, 데이터의 전처리를 위해서 서브쿼리문을 사용하는 것이 일반적이다.
예를 들면 서브쿼리문에서
STR_TO_DATE (바꿔줄 컬럼명, 원하는 날짜 데이터 포맷) as "새로운 컬럼명"
함수를 이용하여 전처리를 해준 뒤에
외부 쿼리문에서 간단하게
Current_Date - Previous_Date 만 해서 날짜 간 간격 일수를 구할 수 있어야 하지만
이따금씩 오류로 인해 위의 데이터를 다시 문자열로 하려 계산해주는 경우가 있을 수 있다.
이 경우에는 당황하지말고
select 절에서 다시 DATEDIFF 함수를 이용하여 연산을 정확하게 진행해주면 된다.
----------------------------------------------------------------------------------------------------------------------
3. 고객 생애 가치 - CLV (Customers Lifetime Value) 란 무엇인가와 이를 계산하는 방법
고객 생애 가치(Customer Lifetime Value, CLV)란, 고객이 평생 우리 기업에 어느 정도의 가치를 가져올 수 있을지를 나타내는 지표다. 쉽게 말해, 특정 비즈니스에서 특정 기간동안 고객 한 명에게 기대할 수 있는 매출과 수익을 의미함.
고객 생애 가치 | 그루비
Customer Lifetime Value(CLV)
groobee.net
참고 : https://www.zoho.com/billing/guides/what-is-customer-lifetime-value-clv.html
What is Customer Lifetime Value (CLV) | Definition, Formula & Calculation
CLV is the total revenue you expect to generate from a customer during their lifetime. Tracking it helps you increase profitability and brand loyalty.
www.zoho.com
----------------------------------------------------------------------------------------------------------------------------
4. 고객 생애 가치 CLV 예제 해설 (풀이 예시)
문제 : 고객 평생 가치(CLV) 분석
당신은 list_of_orders와 order_details 두 테이블을 가지고 있으며, 이를 통해 고객별 평생 가치를 분석하고자 합니다. list_of_orders 테이블은 고객 이름(CustomerName), 주문 날짜(order_date), 주문 ID를 포함하고, order_details 테이블은 각 주문의 금액(Amount)과 수익(Profit)을 포함합니다. 고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고, 이를 기반으로 고객의 평생 가치를 분석하세요.
고객 평생 가치(CLV) 분석을 하기 위해 필요한 공식은 '고객 생애 가치 = 고객 가치 X 평균 고객 수명' 이다
고객가치 = 평균 구매 가치 (=특정 기간 내의 총 수익 / 특정 기간 내 총 구매 횟수)
* 평균 구매 빈도 (=특정 기간 내 구매 횟수 / 특정 기간 내 총 고객 수) 이고
평균 고객 수명 = 고객이 지속적으로 구매한 기간 / 총 고객 수, 이므로 각각을 서브 쿼리문으로 계산해 모두 곱해주면 된다.
이는 서브쿼리문 b에 해당하는 내용이며,
이 서브 쿼리문b를 수행하기 위해서는 반드시 서브쿼리문 a에 해당하는 내용을 통해서 데이터의 전처리를 해주어야한다.
가장 먼저 해주어야 할것은 위에 공식에 자주 등장하는 이 특정 기간이 얼만큼인가에 대해서 알아내는 일이다.
select min(str_to_date(L.order_Date, '%d-%m-%Y')),
max(str_to_date(L.order_Date, '%d-%m-%Y')),
datediff(max(str_to_date(L.order_Date, '%d-%m-%Y')), min(str_to_date(L.order_Date, '%d-%m-%Y')) )
from list_of_orders L inner join order_details D on L.order_id = D.order_id
결과 =
위의 쿼리문을 통하여 데이터 테이블의 최초 주문일자와 마지막 주문일자를 조회하고,
DATEDIFF ( 기준 날짜 컬럼, 이전 날짜 컬럼)함수를 이용하여 기준 날짜로부터 이전 날짜 사이의 간격이 몇 일인지를 계산해줄 수 있다.
결과를 참고하면 이 데이터는 364일간의 데이터,
즉 약 1년간의 데이터가 수집되어있는 테이블이라는 것을 알 수 있다.
그 뒤에 할일은 위의 공식에 대입할 데이터를 구하기 위한 데이터의 전처리 과정이다.
select L.order_id,
L.CustomerName,
count(1) cnt_order,
sum(D.Profit) total_profit,
min(str_to_date(L.order_Date, '%d-%m-%Y')) first_order_date,
max(str_to_date(L.order_Date, '%d-%m-%Y')) last_order_date,
DATEDIFF(max(str_to_date(L.order_Date, '%d-%m-%Y')), min(str_to_date(L.order_Date, '%d-%m-%Y')))
as customer_lifespan
from list_of_orders L inner join order_details D
on L.order_id = D.order_id
group by L.CustomerName
결과 =
위의 쿼리문을 통해 이미지와 같이 각 고객 별로
첫 번째 주문 날짜와 마지막 주문날짜, 그리고 그 사이의 간격인 고객 수명,
고객별 총 주문 횟수, 고객별 총 이윤
에 해당 하는 데이터를 얻을 수 있다.
그리고 데이터 전처리 쿼리문을 a 서브 쿼리문이라고 지정해주겠다
이후 앞의 a 서브 쿼리문을 활용하여 최종 공식에 들어갈 요소들을 연산해주는
또다른 서브 쿼리문을 작성할 수 있으며 다음과 같은 결과를 얻을 수 있다.
이를 b 서브 쿼리문이라고 지정해주겠다.
select sum(a.total_profit) total_profit,
sum(a.cnt_order) total_cnt_orders,
sum(a.customer_lifespan) total_lifespan,
count(a.customername) total_cnt_customer,
sum(a.total_profit) / sum(a.cnt_order) avg_purchase_value,
sum(a.cnt_order) / count(a.customername) avg_purchase_freq,
sum(a.customer_lifespan) / count(a.customername) avg_customer_lifespan
from
(
select L.order_id,
L.CustomerName,
count(1) cnt_order,
sum(D.Profit) total_profit,
min(str_to_date(L.order_Date, '%d-%m-%Y')) first_order_date,
max(str_to_date(L.order_Date, '%d-%m-%Y')) last_order_date,
DATEDIFF(max(str_to_date(L.order_Date, '%d-%m-%Y')), min(str_to_date(L.order_Date, '%d-%m-%Y')))
as customer_lifespan
from list_of_orders L inner join order_details D
on L.order_id = D.order_id
group by L.CustomerName
결과=
여기까지 진행했다면 이미 공식에 들어갈 요소들을 모두 알고 있으므로
마지막으로 b 서브 쿼리문을 활용하여 최종 결과를 연산할 수 있다.
따라서
완성된 쿼리문(최종 쿼리문) =
select b.avg_purchase_value * b.avg_purchase_freq * b.avg_customer_lifespan as "고객 평생 가치(CLV)",
b.avg_purchase_value * b.avg_purchase_freq as "고객 가치",
b.avg_customer_lifespan as "평균 고객 수명"
from
(
select sum(a.total_profit) total_profit,
sum(a.cnt_order) total_cnt_orders,
sum(a.customer_lifespan) total_lifespan,
count(a.customername) total_cnt_customer,
sum(a.total_profit) / sum(a.cnt_order) avg_purchase_value,
sum(a.cnt_order) / count(a.customername) avg_purchase_freq,
sum(a.customer_lifespan) / count(a.customername) avg_customer_lifespan
from
(
select L.order_id,
L.CustomerName,
count(1) cnt_order,
sum(D.Profit) total_profit,
min(str_to_date(L.order_Date, '%d-%m-%Y')) first_order_date,
max(str_to_date(L.order_Date, '%d-%m-%Y')) last_order_date,
DATEDIFF(max(str_to_date(L.order_Date, '%d-%m-%Y')), min(str_to_date(L.order_Date, '%d-%m-%Y')))
as customer_lifespan
from list_of_orders L inner join order_details D
on L.order_id = D.order_id
group by L.CustomerName
)a
)b
결과 =
-----------------------------------------------------------------------------------------------------------------------------------