배송 예정일 예측 성공과 실패 : solvesql (RE)

-- 배송 완료 시각, 배송 예정 시각 = NULL -> 제외
SELECT  *
FROM    olist_orders_dataset AS o
WHERE   order_delivered_customer_date IS NOT NULL 
        OR order_estimated_delivery_date IS NOT NULL

일차 조건 완료 → 이 테이블 안에서 다시 찾으면 된다.

WITH cte AS (
  SELECT  order_purchase_timestamp AS purchase_date,
          order_delivered_customer_date AS odc,
          order_estimated_delivery_date AS oed
  FROM    olist_orders_dataset
  WHERE   order_delivered_customer_date IS NOT NULL 
          OR order_estimated_delivery_date IS NOT NULL
)

SELECT  purchase_date,
        CASE WHEN DATEDIFF(oed, odc) >= 0 THEN count(*) END AS success,
        CASE WHEN DATEDIFF(oed, odc) < 0 THEN count(*) END AS fail
FROM    cte
GROUP BY
        purchase_date

이렇게 풀었을 때, 다음과 같은 에러가 나온다.
Error 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cte.oed' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

개별 행을 체크하고 그에 대해서 카운트를 진행하려고 했지만 이미 그룹화가 되어 있어 접근이 불가하다. 따라서 그룹 내부의 개별 행들을 체크해서 1 또는 NULL로 바꾼 후에 그 결과들을 COUNT로 집계하는 방식을 사용해야 한다.

WITH cte AS (
  SELECT  order_purchase_timestamp AS purchase_date,
          order_delivered_customer_date AS odc,
          order_estimated_delivery_date AS oed
  FROM    olist_orders_dataset
  WHERE   order_delivered_customer_date IS NOT NULL 
          OR order_estimated_delivery_date IS NOT NULL
          AND DATE_FORMAT(order_purchase_timestamp, '%Y-%m') = '2017-1'
)

SELECT  purchase_date,
        COUNT(CASE WHEN DATEDIFF(oed, odc) >= 0 THEN 1 END) AS success,
        SUM(CASE WHEN DATEDIFF(oed, odc) < 0 THEN 0 END) AS fail
FROM    cte
GROUP BY
        purchase_date

쿼리 결과 크기 초과가 나오는데..배송 예정 시각에 대해서 '2017-01'인 행들만 필터링하고, 날짜별로 그룹화를 진행했는데 어떻게 쿼리 결과가 5000개가 넘었는지 모르겠다.

AND 연산자가 OR 연산자보다 우선순위가 높아서 OR 연산자가 나중에 계산되니 사실상 의미가 없는 필터링이었다.

• 구매 시간이 timestamp도 포함이라 DATE 함수로 시간이 안 나오고 구매일로만 판단되도록 해야 한다.

WITH cte AS (
  SELECT  DATE(order_purchase_timestamp) AS purchase_date,
          order_delivered_customer_date AS odc,
          order_estimated_delivery_date AS oed
  FROM    olist_orders_dataset
  WHERE   (order_delivered_customer_date IS NOT NULL 
          OR order_estimated_delivery_date IS NOT NULL)
          AND DATE_FORMAT(order_purchase_timestamp, '%Y-%m') = '2017-01'
)

SELECT  purchase_date,
        SUM(CASE WHEN DATEDIFF(oed, odc) >= 0 THEN 1 END) AS success,
        COUNT(CASE WHEN DATEDIFF(oed, odc) < 0 THEN 0 END) AS fail
FROM    cte
GROUP BY
        purchase_date
ORDER BY
        purchase_date ASC

계속 답이 틀렸는데, 실제로는 컬럼에 시간 데이터가 들어가있기 때문에 DATEDIFF를 사용해서는 안 된다. DATEDIFF 함수는 시:분:초를 무시한다. 따라서 등호 비교로 체크하는 것이 정확하며, DATE_FORMAT보다는 등호 비교로 날짜를 필터링하는 것이 데이터를 가공하지 않아도 되기 때문에 더 빠르다.

answer

SELECT  DATE(order_purchase_timestamp) AS purchase_date,
        COUNT(CASE WHEN order_estimated_delivery_date >= order_delivered_customer_date THEN 1 END) AS success,
        COUNT(CASE WHEN order_estimated_delivery_date < order_delivered_customer_date THEN 1 END) AS fail
FROM  olist_orders_dataset
WHERE order_estimated_delivery_date IS NOT NULL 
      AND order_delivered_customer_date IS NOT NULL
      AND order_purchase_timestamp >= '2017-01-01'
      AND order_purchase_timestamp < '2017-02-01'
GROUP BY
      purchase_date
ORDER BY
      purchase_date ASC;

Tip

날짜 비교할 때, 등호 비교하기가 귀찮으면 LIKE 연산자를 사용하는 것도 방법이다.

order_purchase_timestamp LIKE '2017-01%'