I en verden der teknologi stadig utvikler seg, er det avgjørende å holde tritt med de nyeste trendene. Enten du er en nybegynner eller en erfaren ekspert, vil en solid forståelse av strengmanipulasjon hjelpe deg med å forberede data – for eksempel ved å konvertere dem til et format som er mer egnet for din virksomhet – og administrere dem effektivt ved hjelp av innebygde SQL-serverfunksjoner.
I tillegg til datamanipulering kan du utforske datasett, evaluere dataverdier, og kode eller dekode dem for å produsere mer meningsfull informasjon. Dette hjelper deg også med å håndtere manglende verdier i datasett, forstå hvordan de påvirker beregninger, og effektivisere den generelle dataprosessen for å unngå nullverdier som kan forstyrre dine driftsresultater.
Denne veiledningen introduserer deg til COALESCE-funksjonen i SQL, som er et nyttig verktøy for å bygge komplekse applikasjoner. Det forutsettes at du har tidligere erfaring med SQL og ønsker å utdype din kunnskap om denne spesifikke funksjonen. Vår serie med SQL-veiledninger kan hjelpe deg å komme i gang raskt.
Hva er COALESCE () i SQL, og hvordan brukes den?
COALESCE-funksjonen i SQL evaluerer parametere (argumenter) i den rekkefølgen de er spesifisert, som i en liste. Den returnerer den første verdien som ikke er NULL. Enkelt sagt skanner funksjonen listen sekvensielt og stopper ved den første ikke-null verdien. Hvis alle argumentene i listen er NULL, returnerer funksjonen NULL.
Funksjonen er bredt støttet og finnes i flere databasesystemer, inkludert MySQL, Azure SQL Database, Oracle og PostgreSQL.
Du kan bruke COALESCE i følgende situasjoner:
- For å håndtere NULL-verdier.
- For å kombinere flere spørringer til én.
- For å unngå lange og kompliserte CASE-setninger.
Når COALESCE brukes i stedet for CASE-setninger (eller ISNULL-funksjonen), kan den ta imot flere parametere, i motsetning til CASE som kun tar to. Denne tilnærmingen reduserer mengden kode som må skrives, og gjør skriveprosessen enklere.
Her er syntaksen:
COALESCE(verdiEn, verdiTo, verdiTre, …, verdiX);
COALESCE-funksjonen i SQL-server har flere egenskaper, inkludert argumenter av samme datatype. Den kan akseptere mange parametere, og argumenter av heltallstypen kan kaskaderes av en funksjon for å returnere et heltall som utdata.
Les også: Den ultimate SQL-jukselappen for senere bruk
Før vi går nærmere inn på hvordan du bruker COALESCE, la oss ta en titt på hva NULL er.
Hva er en NULL-verdi i SQL?
Den spesielle markøren NULL i SQL indikerer at en verdi ikke finnes i databasen. Du kan tenke på det som en udefinert eller ukjent verdi. Unngå å tro at det er det samme som en tom streng eller en nullverdi; det representerer fraværet av en verdi. Forekomster av NULL i tabellkolonner representerer manglende informasjon.
Et praktisk eksempel kan være at data kolonnen i en database for en e-handelsnettside fylles med NULL dersom en kunde ikke oppgir sitt ID. NULL i SQL er unik; den er en tilstand i motsetning til andre programmeringsspråk hvor det betyr «ikke peker på et bestemt objekt».
NULL-verdiene i SQL har en betydelig innvirkning på relasjonsdatabaser. For det første lar de deg ekskludere bestemte verdier mens du arbeider med andre interne funksjoner. For eksempel kan du generere en liste over totale bestillinger i et produksjonsmiljø, selv om noen bestillinger fortsatt er underveis. Ved å bruke NULL som plassholder kan den interne SUM-funksjonen legge sammen totalene.
Vurder også tilfeller der du må generere gjennomsnittet ved hjelp av AVG-funksjonen. Hvis du jobber med nullverdier, vil resultatene bli unøyaktige. I stedet kan databasen fjerne slike felt og bruke NULL, noe som resulterer i mer nøyaktige utdata.
NULL-verdier er ikke uten ulemper. De betraktes som verdier med variabel lengde, og er representert med en eller flere byte. Ettersom databasen reserverer plass for disse bytene, selv om de overskrider det som er lagret i databasen, vil databasen kreve mer plass på harddisken sammenlignet med å bruke vanlige verdier.
I tillegg må du, når du arbeider med visse funksjoner, tilpasse dem for å ekskludere NULL-verdier, noe som kan gjøre dine SQL-prosedyrer mer kompliserte.
Håndtering av NULL-verdier med COALESCE ()
Nullverdier antyder at en verdi eksisterer, men at vi ikke vet hva den skal være. Inntil du har samlet inn data for å fylle feltene dine med faktiske verdier, fungerer NULL-verdier som stedfortredere.
Selv om du kan bruke NULL-verdier for flere datatyper i databasen, inkludert desimaler, strenger, blobs og heltall, er det anbefalt å unngå dem når du arbeider med numeriske data.
Ulempen med å bruke NULL for numeriske verdier er at du sannsynligvis vil trenge avklaringer når du utvikler koden som arbeider med disse dataene. Mer om dette senere.
Her er de ulike måtene COALESCE () kan brukes for å håndtere NULL-verdier:
Bruke COALESCE () for å erstatte nullverdier med en spesifikk verdi
Du kan bruke COALESCE () for å returnere spesifikke verdier for alle nullverdier. For eksempel kan du ha en tabell kalt «ansatte» med kolonnen «lønn», som kan inneholde nullverdier dersom en ansatts lønn ikke er utbetalt. Når du utfører beregninger, vil du kanskje benytte en bestemt verdi, for eksempel null, for alle NULL-oppføringer. Slik gjør du det:
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Bruke COALESCE () for å velge den første ikke-nullverdien fra flere alternativer
Noen ganger ønsker du å bruke de første ikke-NULL-verdiene i en liste med uttrykk. I slike tilfeller har du ofte flere kolonner med relaterte data, og du ønsker å prioritere deres ikke-NULL-verdier. Syntaksen er som følger:
COALESCE (uttrykk1, uttrykk2, …)
Et praktisk eksempel er en kontakttabell med kolonnene foretrukket_navn og fullt_navn. Du ønsker å generere en liste over kontakter med enten deres foretrukne navn (hvis tilgjengelig) eller deres fulle navn. Slik løser du dette:
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts;
Hvis foretrukket_navn ikke er NULL i dette tilfellet, returneres det. Ellers returneres det fulle navnet som visningsnavn.
Strengkonkatenering med SQL COALESCE
Du kan oppleve problemer med SQL når du setter sammen strenger dersom nullverdier er involvert. I slike tilfeller returneres NULL som et uønsket resultat. Siden NULL ikke er ønsket her, kan du løse dette problemet ved å bruke COALESCE-funksjonen. Her er et eksempel:
En enkel strengkonkatenering gjøres slik:
SELECT ‘Hei, hvor er du, ‘|| ‘John ’||’?’ AS eksempel
Koden returnerer:
Eksempel Hei, hvor er du, John?
Men hvis du bruker en NULL-verdi som vist nedenfor:
SELECT ‘Hei, hvor er du, ‘ || null || ‘?’ AS eksempel
Utdataene er nå:
NULL
Siden enhver tekststrengkonkatenering som involverer en NULL-verdi returnerer NULL, blir resultatet over NULL. Problemet kan imidlertid løses ved hjelp av COALESCE(). Ved å bruke denne funksjonen returnerer du en tom streng (eller et mellomrom) i stedet for NULL. Anta for eksempel at du fører bilnavn med deres produsenter. Her er spørringen din:
SELECT car || ‘, produsent: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Hvis produsenten er NULL, vil du få «–» i stedet for NULL. Her er de forventede resultatene:
car_brandoutlander, produsent: —flying spurs, produsent: Bentleyroyal atlet, produsent: —royal saloon, produsent: Crown
Som du ser, har NULL-resultatene blitt eliminert, og vi har muligheten til å sette inn en erstatningsstrengverdi.
SQL COALESCE-funksjon og pivoting
SQL-pivoting er en teknikk for å transformere rader til kolonner. Den lar deg transponere (rotere) data fra en «normalisert» form (med mange rader og færre kolonner) til en «denormalisert» form (med færre rader og flere kolonner). COALESCE-funksjonen kan brukes med SQL-pivoting for å håndtere nullverdier i pivotresultatene.
Når du utfører PIVOT i SQL, transformeres rader til kolonner, og de resulterende kolonnene er aggregater av enkelte data. Hvis en aggregasjon gir NULL for en bestemt celle, kan du bruke COALESCE for å erstatte nullverdiene med en standardverdi eller en meningsfull representasjon. Her er et eksempel:
Se for deg en tabell, «salg», med kolonnene år, kvartal og inntekt. Du ønsker å pivotere dataene slik at du får år som kolonner og summen av inntekter for hvert kvartal som verdier. Imidlertid mangler noen kvartaler inntektsdata, noe som resulterer i nullverdier i det pivoterte resultatet. I dette tilfellet kan du bruke COALESCE til å erstatte nullverdiene i det pivoterte resultatet med null (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ær brukerdefinert funksjon og SQL COALESCE-funksjon
Du kan bruke skalære UDF-er og COALESCE sammen for å utføre kompleks logikk som håndterer nullverdier. Ved å kombinere disse funksjonene kan du oppnå mer avanserte datatransformasjoner og beregninger i SQL-spørringene dine. La oss se for oss en tabell, «ansatte», med følgende struktur:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Det kan være lurt å beregne hver ansatts totale inntekt (lønn pluss bonus). Imidlertid er det noen verdier som mangler. I dette tilfellet kan din skalære UDF håndtere beregningen av lønn og bonus, mens COALESCE håndterer nullverdiene. Her er den skalære UDF-en for total inntekt:
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;
Datavalidering ved hjelp av SQL COALESCE
Når du arbeider med databaser, kan det være nødvendig å validere numeriske verdier. La oss for eksempel si at du har kolonnene produktnavn, pris og rabatt i en tabell, «produkter». Du ønsker å hente produktnavn, priser og rabatter for hvert produkt. Du ønsker imidlertid å behandle alle NULL-rabattverdier som 0. COALESCE-funksjonen kan være nyttig her. Slik bruker du den:
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL COALESCE og beregnede kolonner
Beregnede kolonner er virtuelle kolonner som beregnes basert på uttrykk eller andre kolonner i en tabell. Siden beregnede kolonner ikke lagres fysisk i databasen, kan du benytte dem med COALESCE-funksjonen når du håndterer komplekse scenarier og transformasjoner. Her er et praktisk eksempel:
Tenk på en «produkter»-tabell med kolonnene «pris», «rabatt» og «avgiftssats». I dette tilfellet vil du opprette en beregnet kolonne, «total_pris», som representerer den endelige produktprisen etter at rabatt og avgift er brukt. Hvis rabatt eller avgift ikke er spesifisert (NULL), vil du fortsette beregningene ved å bruke null. Slik benytter du COALESCE for å oppnå dette:
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) );
Her er hva som skjer i koden over:
Oppsettet over lar deg generere total_pris, en beregnet kolonne, med den faktiske sluttprisen, til tross for at enkelte verdier mangler eller er NULL.
SQL COALESCE og CASE-uttrykk
Du kan syntaktisk bruke COALESCE gjennom CASE-uttrykket. Her er et eksempel:
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
I oppsettet over utfører CASE samme funksjon som COALESCE-funksjonen.
Det er også mulig å bruke COALESCE- og CASE-uttrykkene i samme spørring. Disse to teknikkene kan håndtere NULL-verdier og bruke betinget logikk samtidig. La oss illustrere dette med et eksempel.
Tenk deg at du har en tabell, «produkter», med kolonnene produkt-id, produktnavn, pris og rabatt. Noen av produktene dine har en spesifikk rabatt, mens andre ikke har det. Hvis et produkt har rabatt, ønsker du å vise den rabatterte prisen, ellers skal den vanlige prisen vises.
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;
I koden over sjekker «CASE» om «rabatten» er større enn null og beregner rabattprisen. Hvis rabatten er 0 eller mindre enn null, returneres en NULL. `COALESCE`-funksjonen tar resultatet fra `CASE` og `price` som sine parametere. Den returnerer den første ikke-NULL-verdien, og viser effektivt den rabatterte prisen hvis den finnes, eller den vanlige prisen hvis det ikke finnes rabatt.
Avsluttende ord
Dette innlegget har vist ulike måter å bruke COALESCE-funksjonen i databasespørringene dine. Ved å evaluere parametere i en spesifisert rekkefølge og returnere den første ikke-NULL-verdien, forenkler COALESCE-funksjonen spørringer og gjør dem mer effektive.
COALESCE er en allsidig funksjon, enten du håndterer nullverdier, strengkonkatenering, datapivotering, validering eller arbeider med beregnede kolonner. Ved å mestre COALESCE-funksjonen kan utviklere håndtere manglende data og skape feilfrie databasedesign. Husk å øve deg på teknikken; du kan trenge litt mer inngående trening.
Du kan nå utforske hvordan du oppretter fremmednøkkelbegrensninger i SQL.