Vzhledem k tomu, že technologie rostou a vyvíjejí se, je nezbytné, abyste jako vývojáři byli informováni o nejnovějších trendech. Ať už jste začátečník nebo expert, dobrá znalost manipulace s řetězci vám pomůže připravit data (například generovat jiný formulář než ten stávající, aby byl použitelný pro vaše podnikání) a spravovat je pomocí vestavěných funkcí SQL serveru.
Kromě manipulace s daty můžete zkoumat datové sady, vyhodnocovat datové hodnoty a kódovat nebo dekódovat je, abyste získali smysluplnější data. Ve výsledku vám to pomůže procházet chybějícími hodnotami v souborech dat, pochopit jejich dopad na výpočty a zefektivnit celkovou práci s datovým procesem, abyste se vyhnuli hodnotám Null, které mohou zničit výsledky operací.
Tato příručka vás provede funkcí sloučení v SQL, která pomáhá vytvářet složité programy. Příspěvek předpokládá, že jste se setkali a pracovali s SQL a jen se snažíte posílit své porozumění této konkrétní funkci. Naše série příruček SQL vám může pomoci rychle začít.
Table of Contents
Co je COALESCE () v SQL a jeho použití?
Slučovací funkce v SQL vyhodnocuje parametry (argumenty) v určeném pořadí, jako jsou seznamy, a vrací první nenulovou hodnotu. Jednoduše řečeno, funkce vyhodnotí váš seznam postupně a skončí v instanci první nenulové hodnoty. Pokud jsou všechny argumenty v seznamu null, funkce vrátí NULL.
Funkce je navíc zahrnutá a podporovaná v jiných databázích, jako je MYSQL, Azure SQL Database, Oracle a PostgreSQL.
Coalesce můžete použít v následujících případech, když:
- Zpracování hodnot NULL.
- Spuštění několika dotazů jako jednoho.
- Vyhýbejte se zdlouhavým a časově náročným příkazům CASE.
Při použití namísto příkazů CASE (nebo funkce ISNULL) sloučení vyžaduje mnoho parametrů, na rozdíl od příkazu CASE, který vyžaduje pouze dva. Tento přístup vám umožňuje psát méně kódu a usnadňuje proces zápisu.
Zde je syntaxe:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Coalesce na serveru SQL má několik vlastností, včetně argumentů stejného datového typu, přijímání mnoha parametrů a argumentů typu integer, které mají být kaskádovány funkcí výnosu, aby jako výstup vrátily celé číslo.
Přečtěte si také: Ultimate SQL Cheat Sheet do záložek na později
Ale než se dostaneme k tomu, jak používat sloučení, pochopme NULL.
Co je hodnota NULL v SQL?
Jedinečná značka NULL v SQL označuje neexistenci hodnoty v databázi. Můžete si to představit jako nedefinovanou nebo neznámou hodnotu. Nenechte se, prosím, do toho úskalí myslet na to jako na prázdný řetězec nebo nulovou hodnotu; je to absence hodnoty. Výskyt null ve sloupcích tabulky představuje chybějící informace.
V praktickém případě lze datový sloupec ve sloupci databáze webových stránek elektronického obchodu vyplnit hodnotou NULL, pokud zákazník neposkytne své ID. Null v SQL je jedinečný; je to stav, na rozdíl od jiných programovacích jazyků, kde to znamená „neukazovat na konkrétní objekt“.
Hodnoty NULL v SQL mají významný dopad na relační databáze. Za prvé vám umožňují vyloučit určité hodnoty při práci s jinými interními funkcemi. Můžete například vygenerovat seznam celkových objednávek v produkčním prostředí, ale další je třeba ještě dokončit. Použití NULL jako zástupného symbolu umožňuje interní funkci SUM přidat součty.
Dále zvažte případy, kdy potřebujete vygenerovat průměr pomocí funkce AVG. Pokud pracujete s nulovými hodnotami, jsou výsledky zkreslené. Místo toho může databáze taková pole odstranit a použít hodnotu NULL, což vede k přesným výstupům.
Hodnoty NULL nemají nevýhody. Jsou považovány za hodnoty s proměnnou délkou, jsou to bajty nebo několik z nich. Protože databáze ponechává prostor pro tyto bajty, pokud překračují to, co je uloženo v databázi, výsledkem je, že vaše databáze zabere více místa na pevném disku než použití běžných hodnot.
Při práci s některými funkcemi je navíc budete muset upravit, abyste odstranili NULLS. To ve výsledku prodlužuje vaše SQL procedury.
Zpracování hodnot NULL pomocí COALESCE ()
Hodnoty Null znamenají, že byste mohli mít hodnotu, ale nevíte, jaká by tato hodnota měla být. Dokud neshromáždíte data, která vyplní vaše pole skutečnými hodnotami, prokuristy jsou hodnoty NULL.
I když můžete použít hodnoty NULL pro více typů dat v databázi, včetně desetinných míst, řetězců, objektů blob a celých čísel, je dobré se jim při práci s číselnými daty vyhnout.
Nevýhodou je, že při použití pro číselné hodnoty budete pravděpodobně potřebovat vysvětlení při vývoji kódu, který pracuje s daty. Více o tom později.
Různé způsoby, jak lze COALESCE () použít ke zpracování hodnoty NULL:
Použití COALESCE () k nahrazení hodnot Null konkrétní hodnotou
Pomocí COALESCE () můžete vrátit konkrétní hodnoty pro všechny hodnoty null. Můžete mít například tabulku nazvanou „zaměstnanci“ se sloupcem „plat“, který může obsahovat nulové hodnoty, pokud mzda zaměstnanců nebyla připsána. Takže když provádíte některé výpočty, možná budete chtít pracovat s konkrétní hodnotou, v tomto případě nulou, pro všechny položky NULL. Zde je návod, jak na to.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Pomocí COALESCE () vyberte první nenulovou hodnotu z více možností
Někdy můžete chtít pracovat s prvními hodnotami, které nejsou 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 hodnoty, které nejsou NULL. Syntaxe zůstává.
COALESCE (expression1, expression2, …)
V praktickém případě předpokládejme, že máte tabulku kontaktů se sloupci preferované_jméno a celé_jméno. A chtěli byste vygenerovat seznam kontaktů vedle sebe s jejich preferovanými jmény (pokud jsou k dispozici) nebo jejich úplnými jmény. Zde je návod, jak to řešit.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Pokud preferovaný_název není pro tento testovací případ NULL, bude vrácen. V opačném případě se jako zobrazovaný název vrátí celé jméno.
Zřetězení řetězců s SQL Coalesce
Můžete narazit na problémy s SQL při zřetězení řetězců, pokud se jedná o hodnoty null. V takových případech je vrácena hodnota NULL jako nežádoucí výsledek. Nyní, když NULL není naším požadovaným výsledkem, můžete problém vyřešit pomocí funkce sloučení. Níže je uveden příklad.
Jednoduché zřetězení řetězců se provádí takto:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
Kód vrátí:
Příklad Ahoj, kde jsi, Johne?
Pokud však použijete hodnotu NULL, jak je uvedeno níže:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
Výstup je nyní.
Protože každé zřetězení textového řetězce obsahující hodnotu NULL vrátí hodnotu NULL, je výsledek výše NULL. Problém je však vyřešen pomocí koalesce (). Pomocí této funkce vrátíte prázdný řetězec (nebo mezeru) namísto NULL. Předpokládejme například, že uvádíte názvy automobilů s jejich výrobci; zde je váš dotaz.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Pokud je výrobce NULL, budete mít ‚–‘ místo NULL. Zde jsou očekávané výsledky.
car_brandoutlander, výrobce: —flying spurs, výrobce: Bentleyroyal athlete, výrobce: —royal seloon, výrobce: Crown
Jak můžete vidět, výsledky NULL jsou eliminovány s možností vložení hodnoty náhradního řetězce.
SQL Coalesce Function a Pivoting
SQL pivoting je technika používaná pro transformaci řádků na sloupce. Umožňuje transponovat (rotovat) data z „normalizovaného“ formuláře (s mnoha řádky a méně sloupci) do „denormalizovaného“ (méně řádků a více sloupců). Funkci sloučení lze použít s pivotováním SQL ke zpracování hodnot null v pivotovaných výsledcích.
Když PIVOT v SQL, transformujte řádky na sloupce; výsledné sloupce jsou agregované funkce některých dat. Pokud je v každém případě výsledkem agregace pro konkrétní buňku hodnota null, můžete pomocí příkazu `COALESCE` nahradit hodnoty null výchozí hodnotou nebo smysluplným vyjádřením. Níže je uveden příklad.
Vezměme si tabulku, tržby, se sloupci rok, čtvrtletí a příjmy, a vy byste chtěli seřadit data; tak, že máte roky jako sloupce a součet příjmů za každé čtvrtletí jako hodnoty. Některá čtvrtletí však nemají žádné údaje o tržbách, takže v pivotovaném výsledku jsou nulové hodnoty. V tomto případě můžete použít COALESCE k nahrazení hodnot null v pivotovaném výsledku nulou (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;
Skalární uživatelsky definovaná funkce a funkce SQL Coalesce
Skalární UDF a sloučení můžete použít k provádění složité logiky, která zpracovává hodnoty null. Kombinace těchto funkcí vám pomůže dosáhnout sofistikovanějších transformací dat a výpočtů v dotazech SQL. Zvažte tabulku, Zaměstnanci, s touto strukturou.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Možná budete chtít vypočítat celkový výdělek každého zaměstnance (plat plus bonus). Některé hodnoty však chybí. V tomto případě může váš skalární UDF zpracovat přidání platu a bonusu, zatímco sloučení zvládá nulové hodnoty. Zde je skalární UDF pro celkové příjmy.
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;
Ověření dat pomocí SQL Coalesce
Při práci s databázemi možná budete chtít ověřit číselné hodnoty. Řekněme například, že máte sloupce název_produktu, cena a sleva v tabulce produkty. Chcete načíst názvy produktů, ceny a slevy každé položky. Rádi byste však se všemi hodnotami NULL slevy zacházeli jako s 0. Funkce sloučení může být užitečná. Zde je návod, jak jej používat.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL sloučení a vypočítané sloupce
Vypočítané sloupce jsou virtuální sloupce vypočítané 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 využít pomocí funkce sloučení při zpracování složitých scénářů a transformací. Zde je praktický příklad použití.
Zvažte tabulku `produktů` se sloupci `cena`, `sleva` a `daňová_sazba`. V tomto případě chcete vytvořit vypočítaný sloupec `celková_cena`, který bude představovat konečnou cenu produktu po uplatnění slevy a daně. Pokud není zadána sleva nebo daň (NULL), budete chtít ve výpočtech pokračovat s nulou. Zde je návod, jak využít koalesce, aby vyhovovala provozu.
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) );
Ve výše uvedeném kódu se děje zde.
Výše uvedené nastavení vám umožňuje vygenerovat vypočítaný sloupec total_price se skutečnou konečnou cenou, i když chybí nebo mají hodnoty NULL.
SQL Coalesce a CASE Expression
Syntakticky můžete použít sloučení prostřednictvím výrazu CASE. Zde je příklad:
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
Ve výše uvedeném nastavení se CASE dotazuje jako funkce COALESCE.
Navíc je možné použít výrazy COALESCE a CASE ve stejném dotazu. Tyto dvě techniky mohou zpracovávat hodnoty NULL a současně aplikovat podmíněnou logiku. Ukažme si to na příkladu.
Zvažte případ, kdy máte tabulku, produkty se sloupci product_id, product_name, price a sleva. Některé z vašich produktů mají konkrétní slevu, zatímco jiné nikoli. Pokud má produkt slevu, chcete zobrazit zlevněnou cenu, jinak by se měla zobrazit běžná cena.
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;
Ve výše uvedeném kódu „CASE“ zkontroluje, zda je „sleva“ větší než nula, a vypočítá zlevněnou cenu, jinak vrátí NULL. Funkce `COALESCE` přebírá výsledek z `CASE` a `price` jako své parametry. Vrátí první hodnotu, která není NULL, a efektivně vrátí zlevněnou cenu, pokud je k dispozici, nebo běžnou cenu, pokud žádná neexistuje.
Závěrečná slova
Tento příspěvek demonstroval různé způsoby použití funkce `COALESCE` ve vašich databázových dotazech. Vyhodnocením parametrů v zadaném pořadí a vrácením první hodnoty, která není NULL, funkce sloučení zjednodušuje dotazy a zefektivňuje je.
Coalesce je všestranná funkce, ať už zpracováváte hodnoty null, zřetězení řetězců, pivotování dat, ověřování nebo práci s vypočítanými sloupci. Díky zvládnutí funkce sloučení mohou vývojáři procházet chybějícími daty a vytvářet bezchybné návrhy databází. Pamatujte, abyste zvládli techniku; možná budete potřebovat hlubší praxi.
Nyní se můžete podívat, jak vytvořit omezení cizího klíče v SQL.