Pe măsură ce tehnologia crește și evoluează, este esențial să fii la curent cu cele mai recente tendințe în calitate de dezvoltator. Fie că sunteți începător sau expert, o înțelegere solidă a manipulării șirurilor vă ajută să pregătiți datele (de exemplu, să generați o formă diferită de cea existentă, făcându-l utilizabil pentru afacerea dvs.) și să le gestionați folosind funcțiile de server SQL încorporate.
Pe lângă manipularea datelor, puteți să examinați seturi de date, să evaluați valorile datelor și să le codificați sau să le decodați pentru a genera date mai semnificative. Ca rezultat, acest lucru vă ajută să navigați prin valorile lipsă din seturile de date, să înțelegeți impactul acestora asupra calculelor și să simplificați lucrul general cu procesul de date pentru a evita valorile nule care pot ruina rezultatele operațiunii.
Acest ghid vă prezintă funcția de coalesce din SQL, care vă ajută să construiți programe complexe. Postarea presupune că ați întâlnit și ați lucrat cu SQL și doar căutați să vă consolidați înțelegerea acestei funcții particulare. Seria noastră de ghiduri SQL vă poate ajuta să începeți rapid.
Cuprins
Ce este COALESCE () în SQL și utilizările sale?
Funcția coalesce din SQL evaluează parametrii (argumentele) într-o ordine specificată, cum ar fi listele, și returnează prima valoare non-nulă. Mai simplu spus, funcția îți evaluează lista secvenţial și se termină la instanţa primei valori non-nule. Dacă toate argumentele din listă sunt nule, funcția returnează NULL.
În plus, funcția este incluzivă și acceptată în alte baze de date precum MYSQL, Azure SQL Database, Oracle și PostgreSQL.
Puteți utiliza Coalesce în următoarele cazuri când:
- Gestionarea valorilor NULL.
- Rularea mai multor interogări ca una singură.
- Evitarea declarațiilor CASE lungi și consumatoare de timp.
Când este utilizat în locul instrucțiunilor CASE (sau funcției ISNULL), coalesce are mulți parametri, spre deosebire de CASE, care ia doar doi. Această abordare vă permite să scrieți mai puțin cod și ușurează procesul de scriere.
Iată sintaxa:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Coalesce în serverul SQL are mai multe proprietăți, inclusiv argumente ale aceluiași tip de date, acceptând mulți parametri și argumente de tip întreg care urmează să fie cascate de o funcție de randament pentru a returna un număr întreg ca rezultat.
Citește și: Ultimate SQL Cheat Sheet to Bookmark for Later
Dar înainte de a înțelege cum să folosiți coalesce, să înțelegem NULL.
Ce este o valoare NULL în SQL?
Markerul unic NULL în SQL indică inexistența unei valori în baza de date. Vă puteți gândi la ea ca la o valoare nedefinită sau necunoscută. Vă rugăm să nu intrați în capcana de a vă gândi la asta ca un șir gol sau o valoare zero; este absența unei valori. Apariția nulă în coloanele tabelului reprezintă informații lipsă.
Într-un caz practic de utilizare, coloana de date dintr-o coloană a bazei de date a site-ului de comerț electronic poate fi completată cu o valoare NULL dacă un client nu își oferă id-ul. Nulul în SQL este unic; este o stare, spre deosebire de alte limbaje de programare în care înseamnă „a nu indica un anumit obiect”.
Valorile NULL din SQL au un impact semnificativ asupra bazelor de date relaționale. În primul rând, vă permit să excludeți anumite valori în timp ce lucrați cu alte funcții interne. De exemplu, puteți genera o listă de comenzi totale într-un mediu de producție, dar altele trebuie să fie finalizate. Utilizarea NULL ca substituent permite funcției interne SUM să adauge totalurile.
În plus, luați în considerare cazurile în care trebuie să generați media folosind funcția AVG. Dacă lucrați cu valori zero, rezultatele sunt denaturate. În schimb, baza de date poate elimina astfel de câmpuri și poate folosi NULL, rezultând rezultate precise.
Valorile NULL nu au dezavantaje. Sunt considerate valori cu lungime variabilă, fiind octeți sau mai mulți dintre ei. Deoarece baza de date lasă loc pentru acești octeți dacă depășesc ceea ce este stocat în baza de date, rezultatul este că baza de date ocupă mai mult spațiu pe hard disk, spre deosebire de utilizarea valorilor obișnuite.
În plus, atunci când lucrați cu unele funcții, va trebui să le personalizați pentru a elimina NULL-urile. Acest lucru, ca rezultat, face procedurile dvs. SQL mai lungi.
Gestionarea valorilor NULL cu COALESCE ()
Valorile nule implică faptul că ați putea avea o valoare, dar nu știți care ar trebui să fie valoarea. Până când colectați date care vă umple câmpurile cu valori reale, valorile NULL sunt procuratorii.
Deși puteți utiliza valori NULL pentru mai multe tipuri de date din baza de date, inclusiv zecimale, șiruri, blob-uri și numere întregi, este o practică bună să le evitați atunci când aveți de-a face cu date numerice.
Dezavantajul este că atunci când este folosit pentru valori numerice, probabil că veți avea nevoie de clarificări pe măsură ce dezvoltați codul care funcționează cu date. Mai multe despre asta mai târziu.
Diferitele moduri în care COALESCE () poate fi utilizat pentru a gestiona valoarea NULL:
Utilizarea COALESCE () pentru a înlocui valorile nule cu o valoare specifică
Puteți utiliza COALESCE () pentru a returna valori specifice pentru toate valorile nule. De exemplu, este posibil să aveți un tabel numit „angajați” cu o coloană „salariu”, care poate conține valori nule dacă salariul angajaților nu a fost creditat. Deci, atunci când faceți unele calcule, poate doriți să lucrați cu o anumită valoare, zero în acest caz, pentru toate intrările NULL. Iată cum să o faci.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Folosind COALESCE () pentru a selecta prima valoare non-nulă din mai multe opțiuni
Uneori, poate doriți să lucrați cu primele valori non-NULL dintr-o listă de expresii. În astfel de cazuri, aveți adesea mai multe coloane cu date înrudite și ați dori să acordați prioritate valorilor non-NULL ale acestora. Sintaxa rămâne.
COALESCE (expression1, expression2, …)
Într-un caz practic, să presupunem că aveți un tabel de contacte cu coloanele preferred_name și full_name. Și ați dori să generați o listă de contacte alături de numele lor preferate (dacă sunt disponibile) sau numele lor complete. Iată cum să o rezolvi.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Dacă preferred_name nu este NULL pentru acest caz de testare, va fi returnat. În caz contrar, numele complet este returnat ca nume afișat.
Concatenarea șirurilor cu SQL Coalesce
Este posibil să întâmpinați probleme cu SQL la concatenarea șirurilor de caractere dacă sunt implicate valori nule. În astfel de cazuri, NULL este returnat ca rezultat nedorit. Acum că NULL nu este rezultatul dorit, puteți remedia problema folosind funcția de coalesce. Mai jos este un exemplu.
O simplă concatenare de șiruri se face prin:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
Codul returnează:
ExempluBună ziua, unde ești, John?
Cu toate acestea, dacă utilizați o valoare NULL, așa cum se arată mai jos:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
Ieșirea este acum.
Deoarece fiecare concatenare de șir de text care implică o valoare NULL returnează NULL, rezultatul de mai sus este NULL. Problema este, totuși, rezolvată folosind coalescerea (). Folosind această funcție, returnați un șir gol (sau un spațiu) în loc de NULL. De exemplu, să presupunem că enumerați numele mașinilor împreună cu producătorii acestora; iată întrebarea ta.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Dacă producătorul este NULL, veți avea „–” în loc de NULL. Iată rezultatele așteptate.
car_brandoutlander, producător: —flying pinteni, producător: Bentleyroyal athlete, producător: —royal saloon, producător: Crown
După cum puteți vedea, rezultatele NULL sunt eliminate, cu opțiunea de a introduce valoarea șirului dumneavoastră de înlocuire.
Funcția SQL Coalesce și Pivoting
Pivotarea SQL este o tehnică folosită pentru transformarea rândurilor în coloane. Vă permite să transpuneți (rotiți) datele din forma „normalizată” (cu multe rânduri și mai puține coloane) în cea „denormalizată” (mai puține rânduri și mai multe coloane). Funcția de coalesce poate fi utilizată cu pivotarea SQL pentru a gestiona valorile nule în rezultatele pivotate.
Când faceți PIVOT în SQL, transformați rândurile în coloane; coloanele rezultate sunt funcții agregate ale unor date. Dacă, în orice caz, o agregare are ca rezultat nul pentru o anumită celulă, puteți utiliza `COALESCE` pentru a înlocui valorile nule cu o valoare implicită sau o reprezentare semnificativă. Mai jos este un exemplu.
Luați în considerare un tabel, vânzări, cu coloanele an, trimestru și venit și ați dori să pivotați datele; astfel încât să aveți ani ca coloane și suma veniturilor pentru fiecare trimestru ca valori. Dar, unele trimestre nu au date despre venituri, oferind valori nule în rezultatul pivotat. În acest caz, puteți utiliza COALESCE pentru a înlocui valorile nule din rezultatul pivotat cu un zero (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;
Funcția scalară definită de utilizator și funcția SQL Coalesce
Puteți utiliza UDF-uri scalare și puteți combina pentru a realiza o logică complexă care gestionează valorile nule. Combinarea acestor caracteristici vă va ajuta să realizați transformări de date și calcule mai sofisticate în interogările SQL. Luați în considerare un tabel, angajați, cu această structură.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Poate doriți să calculați câștigurile totale ale fiecărui angajat (salariu plus bonus). Cu toate acestea, există câteva valori lipsă. În acest caz, UDF-ul tău scalar poate gestiona adăugările de salariu și bonus, în timp ce coalesce se ocupă de valorile nule. Iată UDF scalar pentru câștigurile totale.
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;
Validarea datelor folosind SQL Coalesce
Când lucrați cu baze de date, este posibil să doriți să validați valorile numerice. De exemplu, să presupunem că aveți coloanele nume_produs, preț și reducere într-un tabel, produse. Doriți să preluați numele produselor, prețurile și reducerile fiecărui articol. Dar, ați dori să tratați toate valorile de reducere NULL ca 0. Funcția de coalesce poate fi utilă. Iată cum să-l folosești.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
Coalesce SQL și coloane calculate
Coloanele calculate sunt coloane virtuale calculate pe baza expresiilor sau a altor coloane dintr-un tabel. Deoarece coloanele calculate nu sunt stocate fizic în baza de date, le puteți folosi cu funcția de coalesce atunci când gestionați scenarii și transformări complexe. Iată un exemplu practic de caz de utilizare.
Luați în considerare un tabel „produse” cu coloanele „preț”, „reducere” și „rata_taxă”. În acest caz, doriți să creați o coloană calculată, `total_price`, care să reprezinte prețul final al produsului după aplicarea reducerii și taxelor. Dacă nu este specificată nicio reducere, nici taxa (NULL), ați dori să continuați cu calculele folosind un zero. Iată cum să folosiți coalescerea pentru a se potrivi operațiunii.
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) );
În codul de mai sus, iată ce se întâmplă.
Configurația de mai sus vă permite să generați total_price, o coloană calculată, cu prețul final real, în ciuda faptului că lipsesc sau au valori NULL.
SQL Coalesce și expresie CASE
Puteți utiliza sintactic coalesce prin expresia CASE. Iată un exemplu:
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
În configurația de mai sus, interogările CASE ca funcția COALESCE.
În plus, este posibilă utilizarea expresiilor COALESCE și CASE în aceeași interogare. Cele două tehnici pot gestiona valori NULL și pot aplica logica condiționată simultan. Să ilustrăm acest lucru cu un exemplu.
Luați în considerare un caz în care aveți un tabel, produse cu coloanele product_id, product_name, preț și reducere. Unele dintre produsele tale au o reducere specifică, în timp ce altele nu. Dacă un produs are o reducere, doriți să afișați prețul redus, în caz contrar, ar trebui să fie afișat obișnuit.
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;
În codul de mai sus, `CASE` verifică dacă `reducerea` este mai mare decât zero și calculează prețul redus, altfel returnează un NULL. Funcția `COALESCE` ia ca parametri rezultatul din `CASE` și `price`. Returnează prima valoare non-NULL, returnând efectiv prețul redus dacă este disponibil sau prețul obișnuit dacă nu există.
Cuvinte finale
Această postare a demonstrat diferite moduri de a utiliza funcția `COALESCE` în interogările bazei de date. Evaluând parametrii într-o ordine specificată și returnând prima valoare non-NULL, funcția de coalescență simplifică interogările făcându-le eficiente.
Coalesce este o funcție versatilă, indiferent dacă gestionați valori nule, concatenarea șirurilor, pivotarea datelor, validarea sau lucrați cu coloane calculate. Prin stăpânirea funcției de coalesce, dezvoltatorii pot naviga prin datele lipsă și pot crea baze de date fără erori. Amintiți-vă, pentru a stăpâni tehnica; este posibil să aveți nevoie de o practică mai aprofundată.
Acum puteți verifica cum să creați constrângeri de cheie străină în SQL.