[24년 2월 1째주 회고] 기준일이 다른 데이터들의 datediff 구할 때 유의할 점

주니어 데이터 분석가로서 시행착오를 겪고, 보완해나가는 과정을 기록하는 중입니다

---

등록일이 모두 다른 콘텐츠의 D+0부터 D+6까지 각 날짜별 매출액을 구해달라는 요청을 받았다.
콘텐츠의 등록일을 쭉 살펴보니 모두 2023년도에 등록되었다.
 
예시) 
(제가 어렸을 때 좋아하던 애니메이션들로 가상의 데이터를 만들어봤습니다 :) )

no	product_id	product_name	registration date
1	ABC1001	핑구	2023-01-15
2	ABC1002	페트와 매트	2023-05-05
3	ABC1003	월레스와 그로밋	2023-07-01
4	ABC1004	둘리	2023-12-01
5	ABC1005	두치와 뿌꾸	2023-12-29

 
우선 요청받은 리스트를 각 필드마다 구분자를 "|"로 설정해서 .txt 파일로 저장 후, 
create table, load data into 구문으로 임시 테이블을 생성, 데이터 업로드를 했다.
 
그리고 매출 정보를 가지고 있는 상품 구매 테이블은 파티션이 buy_date로 되어 있기 때문에, 
조회할 기간을 먼저 설정해야 쿼리 실행 속도가 빠르다.
(buy_date 필드의 데이터타입은 string)
 
여기서 내가 한 실수는 쿼리를 작성할 때, 
조회 기간을 20230101 ~ 20231231 로 설정했다는 것이다.
예를 들면 아래와 같은 쿼리로.....

WITH CONTENTS_BUY_TMP AS (
	SELECT BUY_DATE
    	, PRODUCT_ID
        , SUM(SALES_AMT) AS SALES
    FROM BUY_TABLE
    WHERE BUY_DATE BETWEEN '20230101' AND '20231231' -- 23년도 매출
    AND PRODUCT_ID IN ('ABC1001', 'ABC1002', 'ABC1003', 'ABC1004', 'ABC1005')
    GROUP BY BUY_DATE
    	, PRODUCT_ID 
    )
SELECT *
FROM (
    SELECT A.*
        , INT(DATEDIFF(A.BUY_DATE, B.REGISTRATION_DATE)) AS DF	-- hiveql 내장함수
    FROM CONTENTS_BUY_TMP A
    LEFT JOIN CONTENTS_LIST_TMP B ON A.PRODUCT_ID = B.PRODUCT_ID 
    ) C
WHERE DF BETWEEN 0 AND 6
;

 
등록일을 포함한 일주일동안의 일별 매출을 구했을 때, 
예시)

product_id	product_name	registration date	sales (D+0)	sales (D+1)	sales (D+2)	sales (D+3)	sales (D+4)	sales (D+5)	sales (D+6)
ABC1001	핑구	2023-01-15	100		150	160	200		300
ABC1002	페트와 매트	2023-05-05	300	200	400	450	460
ABC1003	월레스와 그로밋	2023-07-01	10	20	300	310	200	100	50
ABC1004	둘리	2023-12-01	50	50	300	400	400	500	1000
ABC1005	두치와 뿌꾸	2023-12-29	100	100	130

 
매출이 발생하지 않는 날짜도 있는 것으로 보였으나, 
이중에서 'ABC1005' 콘텐츠는 D+0부터 D+2까지만 추출되다보니, 
이후에는 매출이 발생하지 않은 것으로 보이게끔 데이터를 잘못 뽑은 것이다 ㅠㅠ
SQL 튜닝을 생각해서... 필요한 기간만 추려서 쿼리를 돌려본다는 것이 또다른 실수를 가져왔다.
 
[피드백 및 보완점]
이와 같은 실수에 대한 피드백으로 
1. 조회 기간을 설정할 때 가장 최근 날짜까지로 설정한다. 
2. 등록일의 MIN, MAX 값을 찾은 후, MIN - 20일, MAX + 20일과 같이 넉넉한 조회기간을 설정한다.
 
추출할 콘텐츠 수가 상당할 경우, 
방법 1은 쿼리 실행 시간이 오래걸릴 것을 고려해서 2번을 자동화 시킬 방법을 좀 더 연구해야겠다.
 
이번주도 이렇게 어떤 것을 고려하며 데이터를 봐야할지 하나 배워갑니다 !__!