위 문제를 가지고 푸는 과정에서 새롭게 알게된 것들을 기록하고자 한다.
우선 이 테이블(sales_transaction_v)에서 DATE컬럼이 날짜 타입이 아니고 문자 타입이었어서 그 타입을 바꾸고 시작하고 싶었다.
테이블 컬럼의 타입을 바꾸고 싶다면 크게 두가지 방법이 있다.
1) 테이블에 새로운 컬럼을 추가하고 그 컬럼을 새롭게 지정해준다.
(현재 이 테이블에서 DATE컬럼은 문자열로 12/9/2019 이런식으로 되어있었다.)
2) 테이블 해당 컬럼의 타입만 바꾼다.
(문자열을 날짜 타입으로 바꿀때 MYSQL에서는 STR_TO_DATE 라는 함수를 쓴다.)
그리하여 DBEAVER에 아래와 같이 했더니 실행이 안되었다...
alter table sales_transaction_v add column new_date DATE
update sales_transaction_v
set new_date = date_format(str_to_date(Date, '%m/%d/%Y'),'%Y-%m-%d')
여기서 나는 'ALTER 문법에는 ; 을 끝에 붙여야하는구나' 와 'DATE_FORMAT 을 쓰면 문자열로 반환되는구나' 를 새롭게 알게되었다.
또한 DATEDIFF 로는 MYSQL에서 일(DAY)차이만 알 수 있고 월 단위 차이는 알 수 없었다.
월 차이를 알고 싶다면 직접 아래처럼 계산해야했다.
나는 깔끔하게 DATE를 '%Y-%m' 형태로 바꾸어 월 차이를 구하고 싶었는데 그렇게 하였을 때 STR_TO_DATE 함수가 쓰여지지 못하는 점을 알게 되었다.
그래서 나는 모든 DATE 컬럼 값을 '%Y-%m-%01' 값으로 DATE_FORMAT을 시킨 뒤 다시 결과값(문자)을 STR_TO_DATE를 통해 날짜 타입으로 바꾸어주었다.
그리하여 결국 아래와 같은 코드로 이 문제를 풀었다.
alter table sales_transaction_v add column new_date DATE;
DESCRIBE sales_transaction_v;
UPDATE sales_transaction_v
SET new_date = STR_TO_DATE(`Date`, '%m/%d/%Y');
SELECT *
FROM sales_transaction_v
order by new_date;
with date_a as (
select customerno,
min(new_date) over(partition by customerno order by new_date) as first_order_month,
date_format(new_date, '%Y-%m-%01') as order_month
from sales_transaction_v
), date_b as (
select customerno,
date_format(first_order_month, '%Y-%m-%01') as first_order_month,
str_to_date(order_month, '%Y-%m-%d') as order_month
from date_a
), date_c as (
select customerno,
str_to_date(first_order_month,'%Y-%m-%d') as first_order_month,
order_month
from date_b
), date_d as (
select customerno,
first_order_month,
order_month,
(YEAR(order_month) - YEAR(first_order_month)) * 12 + MONTH(order_month) - MONTH(first_order_month) as month_diff
from date_c
)
select first_order_month,
count(distinct case when month_diff = 0 then customerno end) as month0,
count(distinct case when month_diff = 1 then customerno end) as month1,
count(distinct case when month_diff = 2 then customerno end) as month2,
count(distinct case when month_diff = 3 then customerno end) as month3,
count(distinct case when month_diff = 4 then customerno end) as month4,
count(distinct case when month_diff = 5 then customerno end) as month5,
count(distinct case when month_diff = 6 then customerno end) as month6,
count(distinct case when month_diff = 7 then customerno end) as month7,
count(distinct case when month_diff = 8 then customerno end) as month8,
count(distinct case when month_diff = 9 then customerno end) as month9,
count(distinct case when month_diff = 10 then customerno end) as month10,
count(distinct case when month_diff = 11 then customerno end) as month11,
count(distinct case when month_diff = 12 then customerno end) as month12
from date_d
where customerno is not null
group by first_order_month이 한 문제를 푸느라 정말 많은 시간을 들였다.. 중간중간 계속 막힐때마다 정말 짜증났지만 마지막에 딱 문제를 풀었을 때 정말 큰 쾌감이 따라왔다😁
아래는 튜터님이 직접 푸신 답안지이다.
WITH first AS (
SELECT customer_id
,order_id
,order_date
,MIN(order_date) OVER(PARTITION BY customer_id ORDER BY order_date) AS first_order_date
FROM orders
WHERE customer_id IS NOT NULL
),mon AS (
SELECT customer_id
,order_id
,order_date
,DATE_FORMAT(order_date,'%Y-%m-%01')as order_month
,DATE_FORMAT(first_order_date,'%Y-%m-%01') as first_order_month
FROM first
)
SELECT first_order_month
,COUNT(DISTINCT customer_id) as month0
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 1 month)=order_month THEN customer_id ELSE NULL END)month1
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 2 month)= order_month THEN customer_id ELSE NULL END)month2
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 3 month)= order_month THEN customer_id ELSE NULL END)month3
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 4 month)= order_month THEN customer_id ELSE NULL END)month4
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 5 month)= order_month THEN customer_id ELSE NULL END)month5
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 6 month)= order_month THEN customer_id ELSE NULL END)month6
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 7 month)= order_month THEN customer_id ELSE NULL END)month7
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 8 month)= order_month THEN customer_id ELSE NULL END)month8
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 9 month)= order_month THEN customer_id ELSE NULL END)month9
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 10 month)= order_month THEN customer_id ELSE NULL END)month10
,COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month,INTERVAL 11 month)= order_month THEN customer_id END)month11
FROM mon
GROUP BY first_order_month
ORDER BY first_order_month위 답안지는 SQL환경이 달라 약간 다르지만 나와 크게 다른점은 DATE_ADD 함수를 쓰신 부분이다. 
MYSQL 환경에서 DATE_ADD를 쓰는 문법은 위에 참고해주시길 바란다.
반대로 DATE를 빼고 싶다면 DATE_SUB 함수를 쓰면되고 문법은 DATE_ADD와 같다.
