Кейс у MySQL: 11 практичних прикладів

Інформатек Діджитал » Бази даних » Кейс у MySQL: 11 практичних прикладів

Функція CASE в MySQL дозволяє виконувати умовні обчислення та повертати власні результати.
З реченням WHEN можна використовувати кілька умов, а значення null можна обробляти за допомогою ELSE.
CASE можна поєднувати з функціями агрегації для оптимізації запитів.
Дотримання найкращих практик під час використання CASE є важливим для підтримки продуктивності та читабельності коду.

Функція CASE в MySQL є потужним інструментом, який дозволяє виконувати умовні операції в ваших запитах. За допомогою CASE ви можете оцінювати різні умови та повертати конкретні результати залежно від того, виконуються ці умови чи ні. Ми збираємося пояснити цю функцію на практичних прикладах, які допоможуть вам освоїти використання CASE в MySQL і вдосконалити свої навички в управлінні базами даних.

Що таке функція CASE в MySQL?

Функція CASE в MySQL — це умовний вираз, який дозволяє оцінювати різні умови та повертати певні результати залежно від того, чи ці умови виконані. Це дуже корисний інструмент для виконання логічних операцій у запитах та отримання налаштованих результатів на основі певних критеріїв.

CASE працює подібно до серії операторів IF-THEN-ELSE, де ви можете вказати кілька умов і значення, які повертаються, коли ці умови виконуються. Якщо жодна з умов не виконується, ви можете визначити значення за замовчуванням за допомогою пропозиції ELSE.

Базовий синтаксис CASE в MySQL

Основний синтаксис функції CASE в MySQL такий:

CASE
    WHEN condición1 THEN resultado1
    WHEN condición2 THEN resultado2
    ...
    WHEN condiciónN THEN resultadoN
    ELSE resultado_predeterminado
END

Ось пояснення кожної частини синтаксису:

WHEN: визначає умову для оцінки.
THEN: вказує на результат, який буде повернуто, якщо відповідна умова виконана.
ELSE: (Необов’язково) вказує результат, який повертається, якщо не виконується жодна з наведених вище умов.
END: Позначає кінець виразу CASE.

Тепер, коли ви знаєте базовий синтаксис, давайте розглянемо кілька практичних прикладів!

Приклад 1: класифікуйте учнів відповідно до їх середнього рівня

Припустімо, у вас є таблиця під назвою «students» із такими стовпцями: «id», «name» і «avg». Ви хочете ранжувати студентів за їх середнім балом за допомогою функції CASE. Ви можете зробити це таким чином:

SELECT nombre,
       CASE
           WHEN promedio >= 90 THEN 'Sobresaliente'
           WHEN promedio >= 80 THEN 'Notable'
           WHEN promedio >= 70 THEN 'Bien'
           WHEN promedio >= 60 THEN 'Suficiente'
           ELSE 'Insuficiente'
       END AS clasificacion
FROM estudiantes;

У цьому прикладі ми використовуємо CASE, щоб оцінити середній показник кожного студента та призначити відповідний ранг. Якщо середнє значення більше або дорівнює 90, воно класифікується як "Видатне". Якщо він між 80 і 89, він класифікується як «Чудовий» і так далі. Якщо середнє значення менше 60, воно класифікується як «Недостатньо».

Приклад 2: Призначення категорій товарів

Уявіть, що у вас є таблиця під назвою «products» зі стовпцями «id», «name» і «price». Ви хочете призначити категорію кожному продукту на основі його ціни за допомогою функції CASE. Ви можете зробити це таким чином:

SELECT nombre,
       CASE
           WHEN precio > 1000 THEN 'Premium'
           WHEN precio > 500 THEN 'Gama alta'
           WHEN precio > 100 THEN 'Gama media'
           ELSE 'Económico'
       END AS categoria
FROM productos;

У цьому прикладі ми використовуємо CASE, щоб оцінити ціну кожного продукту та призначити відповідну категорію. Якщо ціна перевищує 1000, вона класифікується як «Преміум». Якщо він становить від 500 до 1000, він класифікується як «High-End» і так далі. Якщо ціна менша або дорівнює 100, вона класифікується як «Економна».

Приклад 3: Розрахунок знижок на основі купленої кількості

Припустімо, у вас є таблиця під назвою «продажі» зі стовпцями «id», «product» і «quantity». Ви хочете обчислити знижку, застосовану до кожного продажу, на основі кількості, придбаної за допомогою функції CASE. Ви можете зробити це таким чином:

SELECT producto,
       CASE
           WHEN cantidad >= 100 THEN 0.20
           WHEN cantidad >= 50 THEN 0.15
           WHEN cantidad >= 20 THEN 0.10
           ELSE 0
       END AS descuento
FROM ventas;

У цьому прикладі ми використовуємо CASE для оцінки придбаної кількості кожного продукту та розрахунку відповідної знижки. Якщо кількість більше або дорівнює 100, застосовується знижка 20%. Якщо від 50 до 99, застосовується знижка 15% і так далі. Якщо кількість менше 20, знижка не поширюється.

Приклад 4: Перетворення числових значень у діапазони

Уявіть, що у вас є таблиця під назвою «співробітники» зі стовпцями «id», «name» і «age». Ви хочете перетворити вік співробітників на діапазони за допомогою функції CASE. Ви можете зробити це таким чином:

SELECT nombre,
       CASE
           WHEN edad >= 60 THEN 'Senior'
           WHEN edad >= 40 THEN 'Mediana edad'
           WHEN edad >= 20 THEN 'Joven'
           ELSE 'Menor de edad'
       END AS rango_edad
FROM empleados;

У цьому прикладі ми використовуємо CASE, щоб оцінити вік кожного працівника та призначити відповідний діапазон. Якщо вік більше або дорівнює 60, він класифікується як "Старший". Якщо вам від 40 до 59, вас класифікують як «середнього віку» тощо. Якщо вік менше 20 років, він класифікується як «Неповнолітній».

Де в MySQL із прикладами: основні умовні запити

Приклад 5: Присвоєння міток на основі кількох умов

Припустімо, у вас є таблиця під назвою «замовлення» зі стовпцями «id», «customer», «total» і «status». Ви хочете призначити мітки кожному замовленню на основі його підсумку та статусу за допомогою функції CASE з кількома умовами. Ви можете зробити це таким чином:

SELECT cliente,
       CASE
           WHEN total > 1000 AND estado = 'Entregado' THEN 'VIP'
           WHEN total > 500 AND estado = 'Entregado' THEN 'Prioritario'
           WHEN estado = 'Pendiente' THEN 'En proceso'
           ELSE 'Regular'
       END AS etiqueta
FROM pedidos;

У цьому прикладі ми використовуємо CASE з кількома умовами, щоб оцінити загальну суму та статус кожного замовлення та призначити відповідну мітку. Якщо загальна сума перевищує 1000 і статус «Доставлено», він позначається як «VIP». Якщо загальна сума перевищує 500 і статус «Доставлено», він позначається як «Пріоритет». Якщо статус «Очікує на розгляд», він позначається як «В процесі». В іншому випадку він позначається як «Звичайний».

Приклад 6: Обробка нульових значень за допомогою CASE

Уявіть, що у вас є таблиця під назвою «клієнти» зі стовпцями «id», «name» та «email». Деякі клієнти можуть не мати електронної пошти, що призведе до нульових значень у стовпці «електронна адреса». Ви можете використовувати функцію CASE, щоб належним чином обробити ці нульові значення. Наприклад:

SELECT nombre,
       CASE
           WHEN email IS NULL THEN 'Sin correo electrónico'
           ELSE email
       END AS informacion_contacto
FROM clientes;

У цьому прикладі ми використовуємо CASE, щоб визначити, чи стовпець «email» є нульовим. Якщо значення null, відображається текст «Немає електронної пошти». В іншому випадку відображається фактичне значення стовпця "email". Це дозволяє нам грамотно розглядати випадки, коли контактна інформація відсутня.

Приклад 7: Поєднання CASE з агрегатними функціями

Функцію CASE також можна поєднувати з агрегатними функціями, такими як SUM, AVG, COUNT тощо. Припустімо, у вас є таблиця під назвою «продажі» зі стовпцями «id», «product», «quantity» і «price». Ви хочете обчислити загальний обсяг продажів за категорією продукту за допомогою CASE і SUM. Ви можете зробити це таким чином:

SELECT
    SUM(CASE WHEN precio > 1000 THEN cantidad ELSE 0 END) AS ventas_premium,
    SUM(CASE WHEN precio <= 1000 THEN cantidad ELSE 0 END) AS ventas_regulares
FROM ventas;

У цьому прикладі ми використовуємо CASE у функції SUM, щоб обчислити загальний обсяг продажів за категорією продукту. Якщо ціна перевищує 1000, сума додається до «premium_sales». Якщо ціна менша або дорівнює 1000, сума додається до «regular_sales». Це дозволяє нам отримати проміжні підсумки на основі конкретних умов.

Приклад 8: Використання CASE в реченнях WHERE

Функцію CASE також можна використовувати в реченні WHERE для фільтрації записів на основі певних умов. Припустімо, у вас є таблиця під назвою «співробітники» зі стовпцями «id», «name», «department» і «salary». Ви хочете орієнтуватися на співробітників, чия зарплата вища за середню в їх відділі. Ви можете зробити це таким чином:

SELECT nombre, departamento, salario
FROM empleados
WHERE salario > (
    SELECT AVG(CASE WHEN e.departamento = empleados.departamento THEN e.salario ELSE NULL END)
    FROM empleados e
);

У цьому прикладі ми використовуємо CASE у підзапиті для обчислення середньої зарплати за підрозділами. Підзапит порівнює відділ кожного працівника з поточним відділом і враховує лише зарплати працівників того самого відділу для обчислення середнього значення. Потім в основному запиті ми відфільтровуємо співробітників, зарплата яких вища за середню, розраховану для їх відділу.

Приклад 9: Створення обчислюваних стовпців за допомогою CASE

Функцію CASE також можна використовувати для створення обчислюваних стовпців на основі конкретних умов. Припустімо, у вас є таблиця під назвою «замовлення» зі стовпцями «id», «customer», «total» і «date». Ви хочете створити додатковий стовпець під назвою «знижка», який застосовує різні відсотки знижки залежно від загальної суми замовлення. Ви можете зробити це таким чином:

SELECT id, cliente, total,
       CASE
           WHEN total > 1000 THEN total * 0.10
           WHEN total > 500 THEN total * 0.05
           ELSE 0
       END AS descuento,
       fecha
FROM pedidos;

У цьому прикладі ми використовуємо CASE для створення обчисленого стовпця «знижка». При сумі замовлення більше 1000 надається знижка 10%. Якщо загальна сума перевищує 500, застосовується знижка 5%. В інших випадках знижка не надається. Цей обчислюваний стовпець можна використовувати для подальшого аналізу або для відображення додаткової інформації в результатах запиту.

Об'єктно-орієнтовані бази даних: характеристики та використання

Приклад 10: Реалізація складної логіки з вкладеними операторами CASE

У деяких випадках може знадобитися реалізувати складнішу умовну логіку за допомогою вкладених операторів CASE. Припустімо, у вас є таблиця під назвою «students» зі стовпцями «id», «name», «math_grade» і «language_grade». Ви хочете призначити категорію кожному учневі на основі їхніх оцінок з математики та мови. Ви можете зробити це таким чином:

SELECT nombre,
       CASE
           WHEN nota_matematicas >= 90 AND nota_lenguaje >= 90 THEN 'Excelente'
           WHEN nota_matematicas >= 80 AND nota_lenguaje >= 80 THEN 'Notable'
           ELSE
               CASE
                   WHEN nota_matematicas >= 70 OR nota_lenguaje >= 70 THEN 'Regular'
                   ELSE 'Necesita mejorar'
               END
       END AS categoria
FROM estudiantes;

У цьому прикладі ми використовуємо вкладені оператори CASE для реалізації більш складної умовної логіки. Спочатку ми оцінюємо, чи є оцінки з математики та мови вищими або рівними 90. Якщо так, призначається категорія «Відмінно». Потім ми оцінюємо, чи є обидві оцінки більшими або дорівнюють 80. Якщо так, призначається категорія «Помітний». Якщо жодна з наведених вище умов не виконується, ми переходимо до наступного рівня вкладеного CASE. Тут ми оцінюємо, чи принаймні одна з оцінок (математика чи мова) перевищує або дорівнює 70. Якщо так, призначається категорія «Звичайна». Якщо жодна з умов не виконується, присвоюється категорія «Потребує вдосконалення».

Приклад 11: Оптимізація запитів за допомогою CASE

Функцію CASE також можна використовувати для оптимізації запитів і уникнення кількох окремих запитів. Припустімо, у вас є таблиця під назвою «продажі» зі стовпцями «id», «product», «quantity» і «date». Ви хочете отримати загальний обсяг продажів за місяць і загальний обсяг продажів за рік в одному запиті. Ви можете зробити це таким чином:

SELECT
    SUM(CASE WHEN MONTH(fecha) = 1 THEN cantidad ELSE 0 END) AS ventas_enero,
    SUM(CASE WHEN MONTH(fecha) = 2 THEN cantidad ELSE 0 END) AS ventas_febrero,
    -- ... (continúa para los demás meses)
    SUM(CASE WHEN YEAR(fecha) = 2022 THEN cantidad ELSE 0 END) AS ventas_2022,
    SUM(CASE WHEN YEAR(fecha) = 2023 THEN cantidad ELSE 0 END) AS ventas_2023
FROM ventas;

У цьому прикладі ми використовуємо CASE у функції SUM, щоб обчислити загальні продажі за місяць і рік в одному запиті. Для кожного місяця ми оцінюємо, чи збігається місяць дати продажу з конкретним місяцем, і додаємо відповідну суму. Подібним чином для кожного року ми оцінюємо, чи збігається рік дати продажу з конкретним роком, і додаємо відповідну суму. Це дозволяє нам отримати всі підсумки в одному ефективному запиті.

Найкращі практики використання CASE в MySQL

Використовуйте CASE лише за необхідності та уникайте його надмірного використання, оскільки надмірне використання може вплинути на продуктивність запиту.
Намагайтеся, щоб вирази CASE були максимально простими та зрозумілими. Якщо логіка стає надто складною, подумайте про розбиття її на кілька виразів CASE або використання підзапитів.
Використовуйте CASE в поєднанні з іншими пропозиціями та функціями MySQL, щоб повною мірою скористатися їхнім потенціалом, наприклад WHERE, ORDER BY, GROUP BY і функції агрегації.
Будьте обережні, вкладаючи кілька виразів CASE, оскільки це може ускладнити читання та підтримку вашого коду. При необхідності додати пояснювальні коментарі.

Поширені помилки при використанні CASE і як їх уникнути

Забудьте про пункт ELSE: Обов’язково додайте пропозицію ELSE для обробки випадків, коли жодна з умов не виконується. Якщо не вказано, NULL буде призначено за замовчуванням.
Не завершуйте вираз CASE за допомогою END: Не забувайте завжди закінчувати вираз CASE ключовим словом END. Інакше ви отримаєте синтаксичну помилку.
Використання несумісних типів даних: Переконайтеся, що результати, які повертає кожна умова WHEN, належать до одного типу даних. Якщо змішати типи даних, ви можете отримати несподівані результати або помилки.
Не враховуючи порядок умов: Умови в CASE оцінюються в тому порядку, в якому вони з’являються. Обов’язково розмістіть конкретніші умови перед загальнішими, щоб отримати бажані результати.

База даних Informix: прихована перлина в управлінні даними

Альтернативи CASE в MySQL

Хоча CASE є потужною функцією, є кілька альтернатив, які ви можете розглянути в певних випадках:

Вирази IF: Функція IF в MySQL дозволяє оцінити умову та повернути значення, якщо умова істинна, і інше значення, якщо вона хибна. Це простіша альтернатива для випадків унікальних умов.
Пошукові таблиці: У деяких випадках ви можете використовувати окремі таблиці пошуку для зберігання умов і відповідних результатів. Потім ви можете об’єднати ці таблиці з основною таблицею, щоб отримати бажані результати.
Збережені подання або функції: Якщо у вас є складні запити, які періодично використовують CASE, ви можете створити представлення або збережені функції щоб інкапсулювати цю логіку та спростити наступні запити.

Часті запитання про CASE в MySQL

1. Чи можу я використовувати CASE в поєднанні з іншими функціями MySQL?

Так, ви можете використовувати CASE в поєднанні з іншими функціями MySQL, такими як агрегатні функції (SUM, AVG, COUNT тощо), функції дати й часу (YEAR, MONTH, DAY тощо), рядкові функції (CONCAT, SUBSTRING, LENGTH тощо) тощо.

2. Чи є обмеження на кількість умов WHEN, які я можу використовувати у виразі CASE?

Немає конкретного обмеження на кількість умов WHEN, які можна використовувати у виразі CASE. Однак майте на увазі, що велика кількість умов може вплинути на читабельність коду та продуктивність запитів. Якщо у вас багато умов, подумайте про спрощення логіки або розбиття її на кілька виразів CASE.

3. Чи можна використовувати підзапити у виразі CASE?

Так, ви можете використовувати підзапити у виразі CASE як в умовах WHEN, так і в результатах THEN. Це дозволяє виконувати більш складні обчислення або порівняння на основі результатів інших запитів.

4. Як я можу обробляти нульові значення у виразі CASE?

Ви можете обробляти нульові значення у виразі CASE за допомогою умови IS NULL або IS NOT NULL. Наприклад, ви можете використовувати CASE WHEN column IS NULL THEN 'Null value' ELSE column END, щоб призначити певне значення, коли стовпець має нуль, і повернути фактичне значення, якщо воно не є.

Висновки в Mysql

Функція CASE в MySQL є потужним і універсальним інструментом, який дозволяє вам виконувати умовні операції в ваших запитах. За допомогою CASE ви можете оцінювати різні умови та повертати конкретні результати залежно від того, виконуються ці умови чи ні. Приклади, представлені в цій статті, дають вам міцну основу для початку використання CASE у ваших власних запитах і адаптації його до ваших конкретних потреб.

Пам'ятайте про необхідність дотримуватися найкращих практик під час використання CASE, таких як: дотримуватися простоти та читабельності виразів, використовувати CASE в поєднанні з іншими реченнями та функціями MySQL , а також розглядати альтернативи, коли це доречно. Завдяки практиці та експериментам ви зможете повною мірою скористатися перевагами CASE в MySQL та покращити ефективність і читабельність ваших запитів.

Якщо у вас є додаткові запитання або вам потрібні додаткові приклади, сміливо шукайте додаткові ресурси або зверніться до офіційної документації MySQL. Продовжуйте досліджувати та використовувати потужність CASE у своїх проектах баз даних!

Додаткові ресурси: