Pochopení funkce COALESCE () v SQL

autor:
Tweet
Share
Share
Pin

Vzhledem k dynamickému rozvoji technologií je pro vývojáře klíčové, aby měli aktuální přehled o nejnovějších trendech. Bez ohledu na to, zda jste začátečník, nebo zkušený odborník, solidní porozumění manipulaci s textovými řetězci vám umožní efektivně připravovat data (například transformovat je do jiného formátu, než je původní, aby vyhovoval vašim specifickým potřebám) a spravovat je pomocí funkcí integrovaných do SQL serveru.

Mimo rámec samotné manipulace s daty můžete také provádět analýzu datových sad, vyhodnocovat datové hodnoty a provádět kódování a dekódování, abyste získali smysluplnější informace. To vám následně usnadní identifikaci a řešení chybějících hodnot v datech, pochopení jejich vlivu na výpočty a celkově zlepší váš proces zpracování dat, abyste se vyhnuli situacím, kdy by hodnoty NULL mohly negativně ovlivnit výsledky operací.

Tato příručka vás seznámí s funkcí sloučení (COALESCE) v SQL, která je neocenitelná při tvorbě komplexních programů. Předpokládá se, že již máte základní zkušenosti s SQL a hledáte způsoby, jak prohloubit své znalosti o této konkrétní funkci. Pokud s SQL teprve začínáte, naše série průvodců pro SQL vám může pomoci rychle se zorientovat.

Co je funkce COALESCE() v SQL a jak se používá?

Funkce sloučení (COALESCE) v SQL prochází seznam parametrů (argumentů) v zadaném pořadí a vrací první hodnotu, která není NULL. Jednoduše řečeno, funkce prochází seznam postupně, dokud nenarazí na první nenulovou hodnotu. Pokud jsou všechny argumenty v seznamu NULL, funkce vrátí NULL.

Tato funkce je navíc podporována v mnoha dalších databázových systémech, jako jsou MySQL, Azure SQL Database, Oracle a PostgreSQL.

Funkci COALESCE můžete využít v následujících situacích:

  • Při práci s hodnotami NULL.
  • Při spojování více dotazů do jednoho.
  • Pro zjednodušení dotazů a nahrazení složitých příkazů CASE.

Na rozdíl od příkazů CASE (nebo funkce ISNULL), které vyžadují pouze dva parametry, funkce COALESCE umožňuje použití více parametrů. To vám umožňuje psát méně kódu a zjednodušuje proces psaní dotazů.

Syntaxe funkce COALESCE je následující:

COALESCE(hodnota1, hodnota2, hodnota3, …, hodnotaN);

Funkce COALESCE v SQL serveru má několik charakteristik, včetně nutnosti shody datových typů argumentů, schopnosti přijímat libovolný počet parametrů a možnost použití celočíselných argumentů, kdy výstupem bude rovněž celé číslo.

Doporučujeme také prostudovat: Kompletní tahák pro SQL pro pozdější použití.

Než se podíváme na praktické použití funkce COALESCE, pojďme si nejprve objasnit, co znamená NULL.

Co je hodnota NULL v SQL?

Hodnota NULL v SQL je specifický indikátor, který reprezentuje neexistenci hodnoty v databázi. Představte si ji jako nedefinovanou nebo neznámou hodnotu. Je důležité si uvědomit, že NULL není prázdný řetězec ani nulová hodnota; je to absence jakékoli hodnoty. Výskyt NULL ve sloupcích tabulky signalizuje chybějící informaci.

Praktickým příkladem může být databázový sloupec v e-commerce webu, kde hodnota NULL může být uložena v případě, že zákazník neposkytne své ID. V SQL je NULL specifický koncept, stav, na rozdíl od jiných programovacích jazyků, kde může znamenat „neodkazování na žádný objekt“.

Hodnoty NULL mají v relačních databázích významný dopad. Umožňují vám vyloučit určité hodnoty při práci s jinými interními funkcemi. Například, pokud generujete celkový součet objednávek v produkčním prostředí, ale některé objednávky ještě nejsou dokončeny, použití NULL jako zástupného symbolu umožní funkci SUM správně spočítat součty.

Dále, uvažujte o situaci, kdy potřebujete vypočítat průměr pomocí funkce AVG. Pokud byste pracovali s nulovými hodnotami, výsledky by byly zkreslené. Databáze místo toho může odstranit taková pole a použít NULL, což vede k přesným výstupům.

Hodnoty NULL nemají jen výhody. Jsou považovány za hodnoty s proměnnou délkou, a to několik bajtů. Vzhledem k tomu, že databáze vyhradí prostor pro tyto bajty, i když přesahují uložená data, může vaše databáze zabírat více místa na disku, než při použití běžných hodnot.

Navíc při práci s některými funkcemi může být nutné upravit dotazy, abyste zohlednili hodnoty NULL. To může nakonec prodloužit dobu trvání vašich SQL procedur.

Zpracování hodnot NULL pomocí COALESCE()

Hodnoty NULL indikují, že by hodnota mohla existovat, ale zatím nevíte, jaká by měla být. Dokud neshromáždíte data, která vyplní vaše pole skutečnými hodnotami, jsou hodnoty NULL dočasným řešením.

Přestože hodnoty NULL můžete použít pro různé datové typy v databázi, včetně desetinných čísel, textových řetězců, objektů blob a celých čísel, je doporučeno se jim vyhýbat při práci s numerickými daty.

Při použití s číselnými hodnotami může být nutné přidat dodatečná vysvětlení ve vašem kódu. O tom si povíme více později.

Funkci COALESCE() lze použít různými způsoby pro zpracování hodnot NULL:

Použití COALESCE() k nahrazení hodnot NULL konkrétní hodnotou

Pomocí funkce COALESCE() můžete pro všechny hodnoty NULL nahradit konkrétní hodnoty. Představte si tabulku s názvem „zaměstnanci“ se sloupcem „plat“, který může obsahovat hodnoty NULL v případě, že zaměstnanci ještě nebyli vyplaceni. Pro určité výpočty budete možná chtít pracovat s konkrétní hodnotou, v tomto případě nulou, pro všechny položky NULL. Zde je ukázka, jak na to:

SELECT COALESCE(plat, 0) AS upraveny_plat
FROM zamestnanci;

Použití COALESCE() k výběru první nenulové hodnoty z více možností

Někdy můžete chtít pracovat s první hodnotou, která není NULL v seznamu výrazů. V takových případech často máte více sloupců se souvisejícími daty a chcete upřednostnit jejich nenulové hodnoty. Syntaxe zůstává stejná.

COALESCE (vyraz1, vyraz2, …)

V praktickém příkladu, představte si, že máte tabulku kontaktů se sloupci preferovane_jmeno a cele_jmeno. Chcete vygenerovat seznam kontaktů s jejich preferovanými jmény (pokud jsou k dispozici) nebo celými jmény. Zde je ukázka řešení:

SELECT COALESCE(preferovane_jmeno, cele_jmeno) AS zobrazene_jmeno
FROM kontakty;

Pokud `preferovane_jmeno` není NULL, bude vráceno. V opačném případě se jako zobrazované jméno použije `cele_jmeno`.

Zřetězení textových řetězců pomocí SQL COALESCE

Při zřetězení textových řetězců v SQL můžete narazit na problémy, pokud se v nich vyskytují hodnoty NULL. V takových případech je jako výsledek vrácena nežádoucí hodnota NULL. Pokud NULL není vaším požadovaným výsledkem, můžete tento problém vyřešit pomocí funkce sloučení. Níže naleznete příklad.

Jednoduché zřetězení textových řetězců se provádí takto:

SELECT 'Ahoj, kde jsi, ' || 'Jana' || '?' AS priklad

Tento kód vrátí:

Příklad Ahoj, kde jsi, Jano?

Pokud ovšem použijete hodnotu NULL, jak je uvedeno níže:

SELECT 'Ahoj, kde jsi, ' || null || '?' AS priklad

Výstup je nyní:

NULL

Protože jakékoliv zřetězení textového řetězce obsahující hodnotu NULL vede k hodnotě NULL, výsledek výše je NULL. Tento problém však lze vyřešit pomocí funkce COALESCE(). Pomocí této funkce vrátíte prázdný řetězec (nebo mezeru) namísto NULL. Pokud například chcete vypsat názvy aut s jejich výrobci, použijte tento dotaz:

SELECT
auto || ', výrobce: ' || COALESCE(vyrobce, '—') AS znacka_auta
FROM sklad

Pokud je `vyrobce` NULL, zobrazí se namísto NULL symbol ‚—‘. Zde jsou očekávané výsledky:

znacka_autaoutlander, výrobce: —flying spurs, výrobce: Bentleyroyal athlete, výrobce: —royal seloon, výrobce: Crown

Jak můžete vidět, hodnoty NULL byly odstraněny a nahrazeny náhradní hodnotou.

Funkce SQL COALESCE a Pivotování

SQL pivotování je technika používaná pro transformaci řádků na sloupce. Umožňuje transponovat data z „normalizovaného“ formátu (s mnoha řádky a méně sloupci) do „denormalizovaného“ formátu (méně řádků a více sloupců). Funkci COALESCE lze použít při pivotování SQL ke zpracování hodnot NULL v pivotovaných výsledcích.

Při pivotování v SQL se řádky transformují na sloupce, přičemž výsledné sloupce jsou agregovanými funkcemi některých dat. Pokud je výsledkem agregace pro konkrétní buňku NULL, můžete použít příkaz `COALESCE` k nahrazení hodnot NULL výchozí hodnotou nebo smysluplným vyjádřením. Níže je uveden příklad.

Představte si tabulku `tržby` se sloupci `rok`, `čtvrtletí` a `příjmy`. Chcete seřadit data tak, aby roky byly zobrazeny jako sloupce a součet příjmů za každé čtvrtletí jako hodnoty. Některá čtvrtletí však nemusí mít žádné údaje o tržbách, takže v pivotovaném výsledku se objeví nulové hodnoty. V takovém případě můžete použít COALESCE k nahrazení těchto nulových hodnot nulou (0).

SELECT
    rok,
    COALESCE(SUM(CASE WHEN čtvrtletí="Q1" THEN příjmy END), 0) AS Q1_Tržby,
    COALESCE(SUM(CASE WHEN čtvrtletí="Q2" THEN příjmy END), 0) AS Q2_Tržby,
    COALESCE(SUM(CASE WHEN čtvrtletí="Q3" THEN příjmy END), 0) AS Q3_Tržby,
    COALESCE(SUM(CASE WHEN čtvrtletí="Q4" THEN příjmy END), 0) AS Q4_Tržby
FROM tržby
GROUP BY rok;

Skalární uživatelsky definovaná funkce a funkce SQL COALESCE

Skalární uživatelsky definované funkce (UDF) a COALESCE lze použít k implementaci složité logiky pro zpracování hodnot NULL. Kombinace těchto funkcí vám umožní provádět sofistikovanější transformace dat a výpočty v dotazech SQL. Uvažte tabulku `Zaměstnanci` s následující strukturou:

CREATE TABLE Zamestnanci (
    ID_zamestnance INT PRIMARY KEY,
    Jmeno VARCHAR(50),
    Prijmeni VARCHAR(50),
    Plat INT,
    Bonus INT
);

Možná budete chtít vypočítat celkový výdělek každého zaměstnance (plat plus bonus), avšak některé hodnoty mohou chybět. V takovém případě může vaše skalární UDF zpracovat sčítání platu a bonusu, zatímco COALESCE zajistí správné zpracování hodnot NULL. Zde je příklad skalární UDF pro celkové příjmy:

CREATE FUNCTION dbo.VypocitejCelkovePrijmy (@plat INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @celkovePrijmy INT;
    SET @celkovePrijmy = @plat + COALESCE(@bonus, 0);
    RETURN @celkovePrijmy;
END;
Poté můžete skalární UDF použít s funkcí COALESCE v dotazu:
SELECT ID_zamestnance, Jmeno, Prijmeni,
       Plat, Bonus, dbo.VypocitejCelkovePrijmy(Plat, Bonus) AS CelkovePrijmy
FROM Zamestnanci;

Ověření dat pomocí SQL COALESCE

Při práci s databázemi můžete chtít ověřovat číselné hodnoty. Řekněme, že máte sloupce `nazev_produktu`, `cena` a `sleva` v tabulce `produkty`. Chcete načíst názvy produktů, ceny a slevy každé položky. Všechny hodnoty NULL ve sloupci `sleva` chcete ošetřit jako 0. Funkce COALESCE vám s tím pomůže. Zde je návod, jak ji použít:

SELECT nazev_produktu, cena, COALESCE(sleva, 0) AS sleva
FROM produkty

SQL COALESCE a vypočítané sloupce

Vypočítané sloupce jsou virtuální sloupce, které se počítají na základě výrazů nebo jiných sloupců v tabulce. Vzhledem k tomu, že vypočítané sloupce nejsou fyzicky uloženy v databázi, můžete je použít s funkcí COALESCE při řešení komplexních situací a transformací. Zde je praktický příklad použití.

Představte si tabulku `produkty` se sloupci `cena`, `sleva` a `danova_sazba`. Chcete vytvořit vypočítaný sloupec `celkova_cena`, který bude představovat konečnou cenu produktu po uplatnění slevy a daně. Pokud sleva nebo daň není zadána (NULL), chcete ve výpočtech pokračovat s nulou. Zde je ukázka využití COALESCE:

CREATE TABLE produkty(
cena DECIMAL(10, 2),
sleva DECIMAL(10, 2),
danova_sazba DECIMAL(5, 2),
celkova_cena AS (COALESCE(cena, 0) – COALESCE(cena*sleva, 0))* COALESCE(1+danova_sazba, 1)
);

V kódu výše se děje následující:

  • Vypočítaný sloupec `celkova_cena` je definován jako `(COALESCE(cena, 0) – COALESCE(cena*sleva, 0))* COALESCE(1+danova_sazba, 1)`.
  • Pokud je `cena` NULL, `COALESCE(cena, 0)` zajistí, že bude brána jako 0.
  • Pokud je `sleva` NULL, `COALESCE(cena*sleva, 0)` zajistí, že bude brána jako 0 a násobení nebude mít vliv na výpočet.
  • Pokud je `danova_sazba` NULL, `COALESCE(1+danova_sazba, 1)` zajistí, že bude brána jako 0, což znamená, že se nepoužije žádná daň a násobení nebude mít vliv na výpočet.

    • Výše uvedené nastavení vám umožní vygenerovat vypočítaný sloupec `celkova_cena` s reálnou konečnou cenou, i když chybí některé hodnoty nebo jsou NULL.

    SQL COALESCE a CASE výrazy

    Funkci COALESCE můžete syntakticky použít i prostřednictvím CASE výrazu. Zde je příklad:

    SELECT
NazevProduktu + ' ' + DatumDodani detaily_produktu,
prodejce,
CASE
WHEN mobil is NOT NULL Then mobil
WHEN pracovni_telefon is NOT NULL Then pracovni_telefon
ELSE 'N/A'
END
kontaktni_cislo
FROM
dbo.tab_KontaktniOsoby

    V tomto nastavení dotaz CASE funguje podobně jako funkce COALESCE.

    Je také možné použít výrazy COALESCE a CASE ve stejném dotazu. Tyto dvě techniky umožňují zpracovávat hodnoty NULL a zároveň aplikovat podmíněnou logiku. Ukážeme si to na příkladu:

    Uvažujte o případu, kdy máte tabulku `produkty` se sloupci `id_produktu`, `nazev_produktu`, `cena` a `sleva`. Některé produkty mají specifickou slevu, jiné nikoli. Chcete zobrazit zlevněnou cenu, pokud je sleva u produktu k dispozici, jinak by se měla zobrazit běžná cena.

    SELECT
    id_produktu,
    nazev_produktu,
    cena,
    COALESCE(
        CASE
            WHEN sleva > 0 THEN cena - (cena * sleva / 100)
            ELSE NULL
        END,
        cena
    ) AS zlevnena_cena
FROM produkty;

    V kódu výše „CASE“ zkontroluje, zda je „sleva“ větší než nula, a vypočítá zlevněnou cenu, jinak vrátí NULL. Funkce `COALESCE` bere výsledek z `CASE` a `cena` jako své parametry. Vrátí první hodnotu, která není NULL, a efektivně tak vrátí zlevněnou cenu, pokud je k dispozici, jinak vrátí běžnou cenu.

    Závěrem

    V tomto článku jsme si ukázali různé způsoby využití funkce `COALESCE` ve vašich databázových dotazech. Díky vyhodnocování parametrů v zadaném pořadí a vracení první nenulové hodnoty funkce COALESCE zjednodušuje a zefektivňuje dotazy.

    Ať už se jedná o zpracování hodnot NULL, zřetězení textových řetězců, pivotování dat, ověřování nebo práci s vypočítanými sloupci, COALESCE je velmi univerzální funkce. Zvládnutím této funkce mohou vývojáři efektivně řešit situace s chybějícími daty a vytvářet robustní databázové návrhy. Pamatujte, že k dokonalému zvládnutí této techniky je potřeba praxe.

    Nyní se můžete podívat, jak vytvořit omezení cizího klíče v SQL.