Розуміння функції COALESCE () у SQL

Оскільки технології ростуть і розвиваються, розробнику важливо бути в курсі останніх тенденцій. Незалежно від того, початківець чи експерт, тверде розуміння маніпулювання рядками допоможе підготувати дані (наприклад, створити форму, відмінну від існуючої, щоб зробити її придатною для використання у вашому бізнесі) і керувати ними за допомогою вбудованих функцій SQL-сервера.

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

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

Зміст

Що таке COALESCE () у SQL та його використання?

Функція coalesce у SQL обчислює параметри (аргументи) у визначеному порядку, як списки, і повертає перше ненульове значення. Простіше кажучи, функція послідовно оцінює ваш список і завершує роботу в разі першого ненульового значення. Якщо всі аргументи в списку нульові, функція повертає NULL.

Крім того, ця функція є інклюзивною та підтримується в інших базах даних, таких як MYSQL, Azure SQL Database, Oracle і PostgreSQL.

Ви можете використовувати Coalesce у таких випадках, коли:

Обробка значень NULL.
Виконання кількох запитів як одного.
Уникнення довгих, трудомістких операторів CASE.

Коли використовується замість операторів CASE (або функції ISNULL), coalesce приймає багато параметрів, на відміну від CASE, який приймає лише два. Цей підхід дозволяє писати менше коду та полегшує процес написання.

Ось синтаксис:

COALESCE(valueOne, valueTwo, valueThree, …, valueX);

Coalesce на сервері SQL має кілька властивостей, зокрема аргументи одного типу даних, приймання багатьох параметрів і аргументи цілочисельного типу, які каскадуються функцією yield для повернення цілого числа як результат.

Читайте також: Ultimate SQL Cheat Sheet для закладок на потім

Але перш ніж розбиратися в тому, як використовувати coalesce, давайте розберемося з NULL.

Що таке значення NULL у SQL?

Унікальний маркер NULL в SQL вказує на відсутність значення в базі даних. Ви можете думати про це як про невизначене або невідоме значення. Будь ласка, не потрапляйте в пастку, розглядаючи це як порожній рядок або нульове значення; це відсутність цінності. Значення null у стовпцях таблиці означає відсутню інформацію.

У практичному випадку стовпець даних у стовпці бази даних веб-сайту електронної комерції може бути заповнений значенням NULL, якщо клієнт не надає свій ідентифікатор. Null в SQL є унікальним; це стан, на відміну від інших мов програмування, де це означає «не вказувати на певний об’єкт».

Значення NULL у SQL мають значний вплив на реляційні бази даних. По-перше, вони дозволяють виключати окремі значення під час роботи з іншими внутрішніми функціями. Наприклад, ви можете створити список загальних замовлень у виробничому середовищі, але інші ще потрібно виконати. Використання NULL як заповнювача дозволяє внутрішній функції SUM додавати підсумки.

Чи можна зняти гроші в безкартковому банкоматі Chime?

Крім того, розглянемо випадки, коли потрібно згенерувати середнє за допомогою функції AVG. Якщо ви працюєте з нульовими значеннями, результати будуть спотвореними. Натомість база даних може видалити такі поля та використовувати NULL, що дасть точні результати.

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

Крім того, під час роботи з деякими функціями вам потрібно буде налаштувати їх, щоб усунути значення NULL. Це, як наслідок, робить ваші процедури SQL довшими.

Обробка значень NULL за допомогою COALESCE ()

Нульові значення означають, що ви можете мати значення, але ви не знаєте, яким воно має бути. Поки ви не збираєте дані, які заповнюють ваші поля реальними значеннями, значення NULL є прокураторами.

Хоча ви можете використовувати значення NULL для багатьох типів даних у вашій базі даних, включаючи десяткові числа, рядки, краплі та цілі числа, краще уникати їх під час роботи з числовими даними.

Недоліком є те, що при використанні для числових значень вам, імовірно, знадобиться пояснення під час розробки коду, який працює з даними. Про це пізніше.

Різні способи COALESCE () можна використовувати для обробки значення NULL:

Використання COALESCE () для заміни нульових значень на конкретне значення

Ви можете використовувати COALESCE (), щоб повернути певні значення для всіх нульових значень. Наприклад, у вас може бути таблиця під назвою «працівники» зі стовпцем «зарплата», яка може містити нульові значення, якщо зарплату співробітників не було зараховано. Тому, виконуючи деякі обчислення, ви можете працювати з певним значенням, у цьому випадку нулем, для всіх NULL записів. Ось як це зробити.

SELECT COALESCE(salary, 0) AS adjusted_salary
FROM employees;

Використання COALESCE () для вибору першого ненульового значення з кількох варіантів

Іноді вам може знадобитися працювати з першими значеннями, відмінними від NULL, у списку виразів. У таких випадках у вас часто є кілька стовпців із пов’язаними даними, і ви хочете встановити пріоритети їхніх значень, відмінних від NULL. Синтаксис залишається.

COALESCE (expression1, expression2, …)

У практичному випадку припустімо, що у вас є таблиця контактів зі стовпцями preferred_name і full_name. І ви хочете створити список контактів поруч із їхніми бажаними іменами (якщо доступні) або їхніми повними іменами. Ось як з цим боротися.

SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

Якщо preferred_name не NULL для цього тестового прикладу, воно буде повернено. В іншому випадку повне ім’я повертається як відображуване ім’я.

Конкатенація рядків за допомогою SQL Coalesce

Ви можете зіткнутися з проблемами SQL під час конкатенації рядків, якщо використовуються нульові значення. У таких випадках NULL повертається як небажаний результат. Тепер, коли NULL не є нашим бажаним результатом, ви можете вирішити проблему за допомогою функції coalesce. Нижче наведено приклад.

Проста конкатенація рядків виконується:

SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example

Код повертає:

Приклад: Привіт, де ти, Джоне?

Однак якщо ви використовуєте значення NULL, як показано нижче:

SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example

Вихід зараз.

Що таке брандмауер? – Вступний посібник

Оскільки кожне об’єднання текстових рядків із значенням NULL повертає NULL, наведеним вище результатом є NULL. Проблема, однак, вирішується за допомогою coalesce (). Використовуючи цю функцію, ви повертаєте порожній рядок (або пробіл) замість NULL. Наприклад, припустімо, що ви перераховуєте назви автомобілів разом із їхніми виробниками; ось ваш запит.

SELECT 
car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

Якщо виробник має значення NULL, ви матимете «–» замість NULL. Ось очікувані результати.

car_brandoutlander, виробник: —flying spurs, виробник: Bentleyroyal athlete, виробник: —royal saloon, виробник: Crown

Як бачите, результати NULL видаляються, з можливістю вставити ваше значення рядка заміни.

Функція SQL Coalesce і Pivoting

Поворот SQL — це техніка, яка використовується для перетворення рядків у стовпці. Це дозволяє транспонувати (обертати) дані з «нормалізованої» форми (з багатьма рядками та меншою кількістю стовпців) у «деноралізовану» (менша кількість рядків і більше стовпців). Функцію coalesce можна використовувати разом із зведенням SQL для обробки нульових значень у зведених результатах.

Коли ви використовуєте PIVOT у SQL, перетворюйте рядки на стовпці; результуючі стовпці є агрегатними функціями деяких даних. Якщо в будь-якому випадку агрегація призводить до нульового значення для певної комірки, ви можете використовувати `COALESCE`, щоб замінити нульові значення значенням за замовчуванням або осмисленим представленням. Нижче наведено приклад.

Розглянемо таблицю «Продажі» зі стовпцями «рік», «квартал» і «дохід», і ви захочете звести дані; таким чином, щоб у вас були роки як стовпці та сума доходу за кожен квартал як значення. Але деякі квартали не мають даних про доходи, що дає нульові значення у зведених результатах. У цьому випадку ви можете використовувати COALESCE, щоб замінити нульові значення у зведеному результаті на нуль (0).

SELECT
    year,
    COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;

Скалярна функція, визначена користувачем, і функція об’єднання SQL

Ви можете використовувати скалярні UDF і об’єднувати для виконання складної логіки, яка обробляє нульові значення. Поєднання цих функцій допоможе вам досягти більш складних перетворень даних і обчислень у запитах SQL. Розглянемо таблицю Employees із такою структурою.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

Ви можете обчислити загальний заробіток кожного працівника (зарплата плюс бонус). Однак є деякі відсутні значення. У цьому випадку ваш скалярний UDF може обробляти додавання зарплати та премії, тоді як coalesce обробляє нульові значення. Ось скаляр UDF для загального прибутку.

CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @totalEarnings INT;
    SET @totalEarnings = @salary + COALESCE(@bonus, 0);
    RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
       Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;

Перевірка даних за допомогою SQL Coalesce

Працюючи з базами даних, ви можете перевірити числові значення. Наприклад, скажімо, у вас є стовпці product_name, price і discount у таблиці products. Ви хочете отримати назви продуктів, ціни та знижки кожного товару. Але ви хотіли б розглядати всі значення знижки NULL як 0. Функція об’єднання може бути корисною. Ось як ним користуватися.

SELECT product_name, price, COALESCE(discount, 0) AS discount 
FROM products

SQL Coalesce і обчислені стовпці

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

9 Програмне забезпечення для керування інфраструктурою центру обробки даних (DCIM).

Розглянемо таблицю `products` зі стовпцями `price`, `discount` і `tax_rate`. У цьому випадку ви хочете створити обчислений стовпець `total_price`, щоб відобразити кінцеву ціну продукту після застосування знижки та податку. Якщо знижка або податок не вказано (NULL), вам слід продовжити обчислення, використовуючи нуль. Ось як використовувати коалесцію відповідно до операції.

CREATE TABLE products(
price DECIMAL(10, 2),
discount DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)
);

У наведеному вище коді відбувається ось що.

Обчислений стовпець total_price визначається як (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1).

Якщо ціна дорівнює NULL, COALESCE(ціна*знижка, 0) гарантує, що вона розглядатиметься як 0.

Якщо знижка дорівнює нулю, COALESCE(ціна*знижка) гарантує, що вона розглядається як 0, і множення не впливає на обчислення.

Якщо податкова_ставка має значення NULL, COALESCE(1 + tax_rate, 1) гарантує, що вона розглядається як 0, тобто податок не застосовується, а множення не впливає на обчислення.

Налаштування вище дозволяють створити обчислений стовпець total_price із фактичною кінцевою ціною, незважаючи на відсутність або наявність NULL значень.

SQL Coalesce і CASE Expression

Ви можете синтаксично використовувати coalesce через вираз CASE. Ось приклад:

SELECT
Productname + ‘ ’+ deliverydate productdetails,
dealer,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE ‘NA’
END
EmergencyContactNumber
FROM
dbo.tb_EmergencyContact

У наведеному вище налаштуванні запити CASE подібні до функції COALESCE.

Крім того, можливе використання виразів COALESCE і CASE в одному запиті. Ці два методи можуть обробляти значення NULL і застосовувати умовну логіку одночасно. Проілюструємо це на прикладі.

Розглянемо випадок, коли у вас є таблиця продуктів зі стовпцями product_id, product_name, price і discount. Деякі з ваших продуктів мають певну знижку, а інші – ні. Якщо на товар діє знижка, ви хочете показати ціну зі знижкою, інакше має відображатися звичайна ціна.

SELECT 
    product_id,
    product_name,
    price,
    COALESCE(
        CASE
            WHEN discount > 0 THEN price - (price * discount / 100)
            ELSE NULL
        END,
        price
    ) AS discounted_price
FROM products;

У наведеному вище коді `CASE` перевіряє, чи є `discount` більшим за нуль, і обчислює знижену ціну, інакше він повертає NULL. Функція `COALESCE` приймає результат із `CASE` і `price` як параметри. Він повертає перше значення, відмінне від NULL, фактично повертаючи ціну зі знижкою, якщо вона доступна, або звичайну ціну, якщо її немає.

Заключні слова

Ця публікація продемонструвала різні способи використання функції `COALESCE` у ваших запитах до бази даних. Оцінюючи параметри у визначеному порядку та повертаючи перше значення, відмінне від NULL, функція coalesce спрощує запити, роблячи їх ефективними.

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

Тепер ви можете перевірити, як створити обмеження зовнішнього ключа в SQL.