A technológia növekedésével és fejlődésével elengedhetetlen, hogy fejlesztőként naprakész maradjon a legújabb trendekkel. Akár kezdő, akár szakértő, a karakterlánc-manipuláció alapos ismerete segít az adatok előkészítésében (például a meglévőtől eltérő űrlap létrehozásában, így az Ön vállalkozása számára használhatóvá tételében) és a beépített SQL-kiszolgáló funkciókkal történő kezelésében.
Az adatkezelés mellett megvizsgálhatja az adatkészleteket, kiértékelheti az adatértékeket, és kódolhatja vagy dekódolhatja azokat, hogy értelmesebb adatokat generáljon. Ennek eredményeként ez segít eligazodni az adatkészletekben található hiányzó értékek között, megérteni ezek hatását a számításokra, és egyszerűsíteni az adatfolyamatokkal való általános munkát, elkerülve a null értékeket, amelyek tönkretehetik a műveleti eredményeket.
Ez az útmutató végigvezeti az SQL összevonási funkcióján, amely segít összetett programok felépítésében. A bejegyzés feltételezi, hogy találkozott és dolgozott az SQL-lel, és csak szeretné jobban megérteni ezt a funkciót. SQL-útmutatóink segíthetnek a gyors kezdésben.
Tartalomjegyzék
Mi az a COALESCE () az SQL-ben és felhasználásai?
Az SQL egyesítése függvénye meghatározott sorrendben értékeli ki a paramétereket (argumentumokat), például a listákat, és az első nem nulla értéket adja vissza. Egyszerűen fogalmazva, a függvény szekvenciálisan értékeli a listát, és az első nem nulla érték előfordulásakor fejeződik be. Ha a listában minden argumentum nulla, a függvény NULL-t ad vissza.
Ezenkívül a funkció átfogó és támogatott más adatbázisokban, mint például a MYSQL, az Azure SQL Database, az Oracle és a PostgreSQL.
A Coalesce a következő esetekben használható, ha:
- NULL értékek kezelése.
- Több lekérdezés egyként futtatása.
- Kerülje a hosszadalmas, időigényes CASE-utasításokat.
Ha a CASE utasítások (vagy az ISNULL függvény) helyett használjuk, az összevonás sok paramétert vesz igénybe, ellentétben a CASE-val, amely csak kettőt vesz igénybe. Ezzel a megközelítéssel kevesebb kódot írhat, és megkönnyíti az írási folyamatot.
Íme a szintaxis:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Az SQL-kiszolgáló összevonása számos tulajdonsággal rendelkezik, beleértve az azonos adattípusú argumentumokat, amelyek számos paramétert elfogadnak, valamint az egész típusú argumentumokat, amelyeket egy hozamfüggvény lépcsőzetessé kell tenni, hogy egy egész számot adjon vissza kimenetként.
Olvassa el még: Ultimate SQL Cheat Sheet későbbi könyvjelzőkhöz
Mielőtt azonban rátérnénk a koalesce használatára, értsük meg a NULL-t.
Mi az a NULL érték az SQL-ben?
Az egyedi NULL jelző az SQL-ben azt jelzi, hogy nincs érték az adatbázisban. Tekintheti ezt egy meghatározatlan vagy ismeretlen értéknek. Kérlek, ne ess abba a csapdába, hogy üres karakterláncnak vagy nulla értéknek gondold; ez az érték hiánya. A táblázat oszlopaiban szereplő nulla előfordulás hiányzó információkat jelent.
Gyakorlati felhasználás esetén az e-kereskedelmi webhely adatbázis oszlopának adatoszlopa NULL értékkel tölthető fel, ha az ügyfél nem adja meg az azonosítóját. Az SQL-ben a nulla egyedi; ez egy állapot, ellentétben más programozási nyelvekkel, ahol azt jelenti, hogy „nem mutat egy adott objektumra”.
Az SQL NULL értékei jelentős hatással vannak a relációs adatbázisokra. Először is lehetővé teszik bizonyos értékek kizárását, miközben más belső funkciókkal dolgozik. Például létrehozhat egy listát az összes rendelésről egy éles környezetben, de a többit még be kell fejezni. A NULL helyőrzőként való használata lehetővé teszi a belső SUM függvény számára, hogy összeadja az összegeket.
Ezenkívül fontolja meg azokat az eseteket, amikor az AVG függvény segítségével kell átlagot generálnia. Ha nulla értékekkel dolgozik, az eredmények torzak lesznek. Ehelyett az adatbázis eltávolíthatja az ilyen mezőket, és NULL-t használhat, ami pontos kimenetet eredményez.
A NULL értékek nem tartalmaznak hátrányokat. Ezeket változó hosszúságú értékeknek tekintik, amelyek bájtok vagy több bájtok is lehetnek. Mivel az adatbázis helyet hagy ezeknek a bájtoknak, ha azok meghaladják az adatbázisban tárolt mennyiséget, az eredmény az, hogy az adatbázis több helyet foglal a merevlemezen, szemben a normál értékek használatával.
Ezenkívül, amikor egyes funkciókkal dolgozik, testre kell szabnia azokat a NULL-ok kiküszöbölése érdekében. Ennek eredményeként az SQL-eljárások hosszabbak lesznek.
NULL értékek kezelése COALESCE () segítségével
A nullértékek azt jelentik, hogy lehet értéke, de nincs tisztában azzal, hogy mi legyen az érték. Amíg nem gyűjt olyan adatokat, amelyek valódi értékekkel töltik ki a mezőket, a NULL értékek a megbízók.
Bár az adatbázisban található több adattípushoz is használhat NULL értékeket, beleértve a tizedesjegyeket, karakterláncokat, blobokat és egész számokat, célszerű ezeket elkerülni a numerikus adatok kezelésekor.
A hátrány az, hogy ha numerikus értékekre használjuk, akkor valószínűleg pontosításra lesz szüksége az adatokkal együttműködő kód fejlesztése során. Erről majd később.
A COALESCE () különböző módokon használható a NULL érték kezelésére:
A COALESCE () használata nullértékek specifikus értékre cseréjéhez
A COALESCE () segítségével minden null érték adott értéket ad vissza. Például előfordulhat, hogy van egy „alkalmazottak” nevű táblája „fizetés” oszloppal, amely null értékeket tartalmazhat, ha az alkalmazottak fizetését nem írták jóvá. Tehát bizonyos számítások elvégzésekor érdemes lehet egy adott értékkel dolgozni, ebben az esetben nullával az összes NULL bejegyzéshez. Íme, hogyan kell csinálni.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
A COALESCE () segítségével válassza ki az első nem nulla értéket a több lehetőség közül
Néha előfordulhat, hogy érdemes a kifejezések listájának első nem NULL értékeivel dolgozni. Ilyen esetekben gyakran több oszlop is található a kapcsolódó adatokkal, és ezek nem NULL értékeit szeretné előnyben részesíteni. A szintaxis megmarad.
COALESCE (expression1, expression2, …)
Gyakorlati esetben tegyük fel, hogy van egy névjegytáblája a preferált_név és a teljes_név oszlopokkal. És létre szeretne hozni egy listát a névjegyekről, amelyek egymás mellett szerepelnek a preferált nevükkel (ha elérhető) vagy a teljes nevükkel. Íme, hogyan kell kezelni.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Ha a preferált_név nem NULL ebben a tesztesetben, akkor a rendszer visszaadja. Ellenkező esetben a teljes név jelenik meg megjelenített névként.
Karakterlánc összefűzése SQL-egyesítéssel
Problémák adódhatnak az SQL-lel a karakterláncok összefűzésekor, ha null értékekről van szó. Ilyen esetekben a NULL nem kívánatos eredményként jelenik meg. Most, hogy a NULL nem a kívánt eredmény, a problémát az összevonás funkcióval javíthatja. Alább egy példa.
Egy egyszerű karakterlánc-összefűzés a következőképpen történik:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
A kód visszaad:
Példa Hello, hol vagy, John?
Ha azonban NULL értéket használ, az alábbiak szerint:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
A kimenet most.
Mivel minden NULL értéket tartalmazó szöveglánc-összefűzés NULL-t ad vissza, a fenti eredmény NULL. A problémát azonban a koalesce () segítségével oldjuk meg. Ezzel a funkcióval egy üres karakterláncot (vagy szóközt) ad vissza a NULL helyett. Tegyük fel például, hogy autóneveket sorol fel a gyártókkal együtt; itt a kérdésed.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Ha a gyártó NULL, akkor a NULL helyett a „–” lesz. Íme a várt eredmények.
car_brandoutlander, gyártó: —repülő sarkantyú, gyártó: Bentleyroyal atléta, gyártó: —royal limuzin, gyártó: Crown
Amint látja, a NULL eredmények kimaradnak, és lehetőség van a helyettesítő karakterlánc értékének beszúrására.
SQL összevonási függvény és elforgatás
Az SQL pivoting egy technika, amelyet a sorok oszlopokká alakítására használnak. Lehetővé teszi az adatok transzponálását (forgatását) a „normalizált” (sok sorral és kevesebb oszloppal) a „denormalizált” (kevesebb sor és több oszlop) formáról. A koalesce függvény használható az SQL pivoting-tal, hogy kezelje a null értékeket az elforgatott eredményekben.
Amikor PIVOT-ot végez SQL-ben, alakítsa át a sorokat oszlopokká; az eredő oszlopok egyes adatok összesített függvényei. Ha mindenesetre egy összesítés nullát eredményez egy adott cellában, akkor a `COALESCE’ segítségével lecserélheti a null értékeket egy alapértelmezett értékre vagy értelmes ábrázolásra. Alább egy példa.
Tekintsen egy táblázatot, az értékesítéseket, az év, negyedév és bevétel oszlopokkal, és szeretné elforgatni az adatokat; úgy, hogy az évek oszlopai és az egyes negyedéves bevételek összege az értékek. Néhány negyedévben azonban nincsenek bevételi adatok, így null értékeket adnak meg a forgatható eredményben. Ebben az esetben a COALESCE segítségével lecserélheti a null értékeket az elforgatott eredményben nullára (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áris, felhasználó által definiált függvény és SQL összevonási függvény
A skaláris UDF-ek és az összevonás segítségével összetett logikát hajthat végre, amely null értékeket kezel. E szolgáltatások kombinálásával kifinomultabb adatátalakításokat és számításokat érhet el az SQL-lekérdezésekben. Vegyünk egy táblázatot, Alkalmazottak, ezzel a szerkezettel.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Érdemes lehet kiszámítani az egyes alkalmazottak teljes keresetét (fizetés plusz bónusz). Vannak azonban hiányzó értékek. Ebben az esetben a skalár UDF kezeli a fizetés és a bónusz hozzáadását, míg az összevonás a null értékeket. Íme a skalár UDF a teljes bevételhez.
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;
Adatérvényesítés SQL Coalesce használatával
Amikor adatbázisokkal dolgozik, érdemes lehet a numerikus értékeket érvényesíteni. Tegyük fel például, hogy a termék_neve, ár és kedvezmény oszlopok szerepelnek egy termékek táblázatban. Az egyes cikkek termékneveit, árait és kedvezményeit szeretné lekérni. De az összes NULL diszkontértéket 0-ként szeretné kezelni. Az egyesülési függvény hasznos lehet. Így kell használni.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL egyesítése és kiszámított oszlopok
A kiszámított oszlopok olyan virtuális oszlopok, amelyeket a táblázaton belüli kifejezések vagy más oszlopok alapján számítanak ki. Mivel a kiszámított oszlopokat fizikailag nem tárolja az adatbázis, összetett forgatókönyvek és átalakítások kezelésekor kihasználhatja őket az összevonás funkcióval. Íme egy gyakorlati használati példa.
Vegyünk egy „termékek” táblázatot a „price”, „discount” és „tax_rate” oszlopokkal. Ebben az esetben létre szeretne hozni egy kiszámított oszlopot, a „teljes_ár”, amely a termék végső árát képviseli a kedvezmény és az adó alkalmazása után. Ha a kedvezmény vagy az adó nincs megadva (NULL), akkor a számításokat nullával kell folytatnia. Az alábbiakban bemutatjuk, hogyan lehet kihasználni az egyesülést, hogy megfeleljen a műveletnek.
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) );
A fenti kódban ez történik.
A fenti beállítás lehetővé teszi a total_price, egy számított oszlop létrehozását a tényleges végső árral, annak ellenére, hogy hiányzik vagy NULL értékek vannak.
SQL egyesítése és CASE kifejezés
A CASE kifejezésen keresztül szintaktikailag használhatja az összevonást. Íme egy példa:
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
A fenti beállításban a CASE lekérdezések, például a COALESCE függvény.
Ezenkívül lehetséges a COALESCE és a CASE kifejezések használata ugyanabban a lekérdezésben. A két technika egyszerre képes NULL értékeket kezelni és feltételes logikát alkalmazni. Illusztráljuk ezt egy példával.
Tekintsünk egy olyan esetet, amikor van egy táblázat, a termékek a termék_azonosítója, a termék_neve, az ár és a kedvezmény oszlopokkal. Egyes termékei speciális kedvezményt kapnak, míg mások nem. Ha egy termék akciós, akkor a kedvezményes árat szeretné megjeleníteni, ellenkező esetben a normál árat kell megjeleníteni.
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;
A fenti kódban a „CASE” ellenőrzi, hogy a „kedvezmény” nagyobb-e nullánál, és kiszámolja a kedvezményes árat, különben NULL-t ad vissza. A „COALESCE” függvény a „CASE” és a „price” eredményét veszi át paramétereiként. Az első nem NULL értéket adja vissza, ténylegesen a kedvezményes árat, ha van ilyen, vagy a normál árat, ha nincs ilyen.
Végső szavak
Ez a bejegyzés különféle módokat mutatott be a `COALESCE` függvény használatára az adatbázis-lekérdezésekben. A paraméterek meghatározott sorrendben történő kiértékelésével és az első nem NULL érték visszaadásával az egyesülési függvény leegyszerűsíti a lekérdezéseket, és hatékonyabbá teszi azokat.
Az összevonás egy sokoldalú függvény, függetlenül attól, hogy nullértékeket kezel, karakterlánc-összefűzést, adatelforgatást, érvényesítést vagy számított oszlopokkal dolgozik. Az összevonás funkció elsajátításával a fejlesztők navigálhatnak a hiányzó adatok között, és hibamentes adatbázisterveket hozhatnak létre. Ne feledje, elsajátítani a technikát; lehet, hogy elmélyültebb gyakorlatra van szüksége.
Most megtudhatja, hogyan hozhat létre idegenkulcs-megszorításokat az SQL-ben.