solvesql 배송 예정일 예측 성공과 실패 - 문제 풀이
-- 최종
SELECT strftime('%Y-%m-%d', order_purchase_timestamp) as "purchase_date",
SUM(CASE WHEN order_estimated_delivery_date >= order_delivered_customer_date THEN 1 ELSE 0 END) as "success",
SUM(CASE WHEN order_estimated_delivery_date < order_delivered_customer_date THEN 1 ELSE 0 END) as "fail"
FROM olist_orders_dataset
WHERE strftime('%Y-%m-%d', order_purchase_timestamp) LIKE '2017-01%'
GROUP BY 1
ORDER BY 1 asc;
# 접근 방식
1. success와 fail 케이스를 개별 컬럼으로 만들어야겠다.
2. SUM (혹은 COUNT도 상관없음) 집계 후 일자별 그룹화해야겠다.
3. 기타 조건들 충족 e.g., 17년 1월 데이터만, 날짜 오름차순
# 배운점
1. SQLite - strftime
처음에 sqlite 문법만 되는 줄 모르고 DATE_FORMAT을 사용했다가 Syntax Error 떠서 당황했다.
처음 써 보는 문법이므로 추측해서 때려맞춘다고 될 일이 아니라서 바로 가이드 문서를 찾았다.
빨리 + 정신없이 푸느라 제대로 안 읽고 strftime으로 썼는데, date로 했어도 상관없었다.
Date And Time Functions
1. Overview SQLite supports seven scalar date and time functions as follows: date(time-value, modifier, modifier, ...) time(time-value, modifier, modifier, ...) datetime(time-value, modifier, modifier, ...) julianday(time-value, modifier, modifier, ...) un
www.sqlite.org
2. SQLite - CASE
놀랍게도 IF문이 없다. 공식 문서에도 CASE를 사용하라고 나와 있다.
SQL Language Expressions
INTEGER When casting a BLOB value to INTEGER, the value is first converted to TEXT. When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remaind
www.sqlite.org
# 느낀점
생각을 조금 더 효율적으로 하려고 노력해야 한다. 가령 이번 문제에서 처음에는 CASE WHEN 구문을 이런 식으로 작성했었다.
CASE WHEN (order_estimated_delivery_date - order_delivered_customer_date) >= 0 THEN 1 ELSE 0 END
굳이 이럴 이유가 없다. 그리고 애초에 문법적으로 불가능하다. sqlite에서는 날짜 간에 산술연산자(+, - 등)를 직접 사용할 수 없다. 두 날짜 간 차이를 일 단위로 계산하려면 julianday 함수를 사용하는 등의 방법이 있다.
mysql 문법만 쓰다가 sqlite를 맞닥뜨려서 당황하기도 했지만, 문법이 같을 거라는 확신이 없어서 다시 생각해낸 방법이 결국 정답이어서 다행이었다.