CASE WHEN, COALESCE и NULLIF в SQL

Проблем

Платена, а не paid

На страницата „Моите поръчки“ клиентът вижда състоянието както е в базата: paid, shipped. Кодовете на английски са удобни за програмата, но клиентът очаква „платена“ и „изпратена“.

Сайтът иска и филтър „евтини, средни, скъпи“. В базата няма такава колона, има само цена.

Идеята

CASE WHEN

CASE дава различна стойност според условие:

SQLбаза: магазин

SELECT name, price,
       CASE
         WHEN price < 2 THEN 'евтин'
         WHEN price < 10 THEN 'среден'
         ELSE 'скъп'
       END AS price_group
FROM products
WHERE category_id IN (4, 8);

Условията се проверяват отгоре надолу. Първото вярно WHEN дава стойността на THEN до него, и останалите не се гледат. ELSE е за всички, за които нищо не е вярно. END затваря израза, а AS му дава име.

Когато сравняваш една колона с няколко стойности, има по-кратък запис: колоната се пише веднъж след CASE, а след всяко WHEN стои само стойността:

SQLбаза: магазин

SELECT id, status,
       CASE status
         WHEN 'new' THEN 'нова'
         WHEN 'paid' THEN 'платена'
       END AS status_text
FROM orders
WHERE id BETWEEN 57 AND 59;

Пробвай

Състоянията с думи

Покажи номера, датата и състоянието с думи на всяка поръчка, в колона status_text: new е „нова“, paid е „платена“, shipped е „изпратена“, delivered е „доставена“, cancelled е „отказана“.

SQLбаза: магазин

SELECT id, ordered_at, status FROM orders;

Решение

SELECT id, ordered_at,
       CASE status
         WHEN 'new' THEN 'нова'
         WHEN 'paid' THEN 'платена'
         WHEN 'shipped' THEN 'изпратена'
         WHEN 'delivered' THEN 'доставена'
         WHEN 'cancelled' THEN 'отказана'
       END AS status_text
FROM orders;

Виж го

Скъпите изчезнаха

Колега пренаписва ценовите групи с >. Пусни и виж кухнята:

SQLбаза: магазин

SELECT name, price,
       CASE
         WHEN price > 2 THEN 'среден'
         WHEN price > 10 THEN 'скъп'
         ELSE 'евтин'
       END AS price_group
FROM products
WHERE category_id IN (4, 8);

1. Защо тиганът за 24,90 € излезе „среден“?Защото CASE не работи с дробни числаЗащото първото вярно WHEN печели: 24,90 > 2 е вярно, и до „> 10“ изобщо не се стигаЗащото ELSE хваща всичко

Още нещо: ако няма ELSE, а нито едно WHEN не е вярно, резултатът е NULL. Хлябът има бележка само когато е изчерпан или е малко:

SQLбаза: магазин

SELECT name, stock,
       CASE
         WHEN stock = 0 THEN 'изчерпан'
         WHEN stock < 30 THEN 'последни бройки'
       END AS note
FROM products
WHERE category_id = 3;

Понякога точно това искаш. Ако не, напиши ELSE.

Идеята

COALESCE и NULLIF

COALESCE(a, b, …) връща първата стойност, която не е NULL. Така се слага дума на мястото на липсващото:

SQLбаза: магазин

SELECT name, COALESCE(city, 'неизвестен') AS city
FROM customers
WHERE id BETWEEN 10 AND 13;

Понякога „липсва“ е записано не като NULL, а като празен текст. В двата примера отдолу базата има още един клиент, Анна, записана през формуляр, който праща '' вместо NULL. COALESCE не я хваща, защото празният текст е стойност:

SQLбаза: магазин

SELECT name, COALESCE(city, 'неизвестен') AS shown, city IS NULL AS no_city
FROM customers
WHERE id >= 12 AND (city IS NULL OR city = '');

NULLIF(a, b) дава NULL, когато a е равно на b, иначе дава a. Така празният текст става NULL, а COALESCE хваща и двамата:

SQLбаза: магазин

SELECT name, COALESCE(NULLIF(city, ''), 'неизвестен') AS shown
FROM customers
WHERE id >= 12 AND (city IS NULL OR city = '');

По-точно, за любопитните

NULLIF се ползва и за делене: total / NULLIF(count, 0). Когато count е 0, делителят става NULL и резултатът е NULL. В SQLite делението на нула и без това дава NULL, но в PostgreSQL е грешка и спира цялата заявка.

Пробвай

Редът за куриера

Поправи реда „име, град“ от урока за текст: когато градът липсва, на мястото му да пише „без град“. Колоната да се казва contact.

SQLбаза: магазин

SELECT name || ', ' || city AS contact FROM customers;

Решение

SELECT name || ', ' || COALESCE(city, 'без град') AS contact FROM customers;

Упражнение

Две задачи

1. До всеки продукт сайтът пише наличността с думи: „изчерпан“ при 0, „последни бройки“ под 10, иначе „в наличност“. Покажи името, наличността и думите в колона availability.

Наличността с думи за всеки продукт:

SQLбаза: магазин

Решение

SELECT name, stock,
       CASE
         WHEN stock = 0 THEN 'изчерпан'
         WHEN stock < 10 THEN 'последни бройки'
         ELSE 'в наличност'
       END AS availability
FROM products;

2. Складът работи в този ред: първо опакова платените (paid), после гледа новите (new), накрая проверява изпратените (shipped). Покажи номера, състоянието и датата на тези поръчки в реда на работа, а в едно състояние първо най-старата. При една и съща дата първо по-малкият номер.

Отворените поръчки в реда, в който складът ги обработва:

SQLбаза: магазин

Решение

SELECT id, status, ordered_at
FROM orders
WHERE status IN ('paid', 'new', 'shipped')
ORDER BY CASE status WHEN 'paid' THEN 1 WHEN 'new' THEN 2 ELSE 3 END, ordered_at, id;

По-точно, за любопитните

SQLite има и IIF(условие, ако е вярно, ако не е): кратък CASE с едно WHEN и ELSE. Има го и в SQL Server, но не и в PostgreSQL. CASE е стандартният запис и работи навсякъде.

SQLбаза: магазин

SELECT name, stock, IIF(stock = 0, 'изчерпан', 'има') AS shown
FROM products
WHERE category_id = 3;

Тест

Провери се

1. Какво дава CASE, когато нито едно WHEN не е вярно и няма ELSE?Празен текстГрешкаNULL

2. Какво връща COALESCE(NULL, NULL, 'София', 'Варна')?СофияВарнаNULL

3. Какво връща NULLIF(city, '') за град, който е празен текст?NULL''Грешка

4. Къде може да стои CASE?Само в WHEREСамо в SELECTНавсякъде, където може израз: в SELECT, WHERE, ORDER BY

CASE и липсващите стойности

Бисквитки