Nik в мире данных: “SQL и CASE WHEN CASE - знакомое многим выражение SQL для условий. Я хо…”

SQL и CASE WHEN

CASE - знакомое многим выражение SQL для условий. Я хотел бы показать варианты использования CASE WHEN, о которых может быть не известно

Все примеры из public datasets в Bigquery. В основном будем использовать таблицу заказов для примеров. То, что можно так сделать с точки зрения синтаксиса не значит, что так стоит делать, всегда думайте о поддерживаемости и читабельности

CASE в агрегатной функции
Нужно вывести для каждого пользователя последние даты отмененного, возвращенного и завершенного заказов.




SELECT user_id,
MAX(case when status = 'Cancelled' THEN created_at END)
  AS last_cancelled_dt,
MAX(case when status = 'Returned' THEN returned_at END) 
  AS last_returned_dt,
MAX(case when status = 'Complete' THEN delivered_at END) 
  AS last_complted_at
FROM bigquery-public-data.thelook_ecommerce.orders
GROUP BY user_id

CASE в Order by оконной функции

Для каждого пользователя вывести последнюю дату отменненого заказа, а если у него не было отменненых заказов, последнюю дату возвращенного заказа





SELECT distinct user_id,
LAST_VALUE(CASE WHEN status = 'Cancelled' THEN created_at
                WHEN Status = 'Returned' THEN returned_at END)
  OVER
  (PARTITION BY user_id
  ORDER BY
   CASE WHEN status = 'Cancelled' THEN 2 
        WHEN status = 'Returned' THEN 1 
        ELSE 0 END,
    CASE WHEN status = 'Cancelled' THEN created_at
            WHEN Status = 'Returned' THEN returned_at 
        END
  ROWS BETWEEN UNBOUNDED PRECEDING
   AND UNBOUNDED FOLLOWING )
FROM bigquery-public-data.thelook_ecommerce.orders

CASE в джоинах

Убедиться что дата активности заказа не произошла на выходной день




SELECT ord.user_id, count(distinct ord.order_id)
FROM `bigquery-public-data.thelook_ecommerce.orders` ord
LEFT JOIN `bigquery-public-data.ml_datasets.holidays_and_events_for_forecasting` h
ON h.region = 'DE'
AND CASE WHEN status = 'Cancelled' THEN DATE(created_at)
         WHEN status = 'Returned' THEN DATE(returned_at)
         WHEN status = 'Complete' THEN DATE(delivered_at)
    END = h.primary_date
WHERE h.primary_date IS NOT NULL
GROUP by ord.user_i

CASE в QUALIFY
Последний пример более искусственный, но показывает пример применения в QUALIFY











SELECT distinct user_id
FROM `bigquery-public-data.thelook_ecommerce.orders` 
QUALIFY 
  CASE WHEN user_id > 100 
    THEN 
    SUM(num_of_item * 
                CASE WHEN status = 'Complete' THEN 1 
                           WHEN status = 'Returned' THEN -1 
                           ELSE 0 
                END) 
         OVER (PARTITION BY user_id 
                     ORDER BY created_at 
                     ROWS BETWEEN UNBOUNDED PRECEDING 
                                 AND UNBOUNDED FOLLOWING)
    ELSE  
    SUM(num_of_item * 
                 CASE WHEN status = 'Complete' THEN 2 
                           WHEN status = 'Returned' THEN -0.5 
                            ELSE 0 
                  END) 
        OVER (PARTITION BY user_id 
                    ORDER BY created_at 
                    ROWS BETWEEN UNBOUNDED PRECEDING 
                               AND UNBOUNDED FOLLOWING)
  END > 2