Wraz z rozwojem i ewolucją technologii ważne jest, aby jako programista być na bieżąco z najnowszymi trendami. Niezależnie od tego, czy jesteś początkującym, czy ekspertem, solidne zrozumienie manipulacji ciągami znaków pomaga w przygotowaniu danych (na przykład wygenerowaniu innego formularza niż istniejący, aby był użyteczny w Twojej firmie) i zarządzaniu nimi za pomocą wbudowanych funkcji serwera SQL.
Oprócz manipulacji danymi można badać zestawy danych, oceniać wartości danych oraz kodować lub dekodować je w celu uzyskania bardziej znaczących danych. W rezultacie pomaga to poruszać się po brakujących wartościach w zestawach danych, zrozumieć ich wpływ na obliczenia i usprawnić ogólną pracę z procesem danych, aby uniknąć wartości Null, które mogą zrujnować wyniki operacji.
Ten przewodnik przeprowadzi Cię przez funkcję koalescencji w SQL, która pomaga budować złożone programy. W poście założono, że spotkałeś się z SQL i pracowałeś z nim i chcesz po prostu lepiej zrozumieć tę konkretną funkcję. Nasza seria przewodników SQL pomoże Ci szybko rozpocząć pracę.
Spis treści:
Co to jest COALESCE () w SQL i jego zastosowaniach?
Funkcja koalescencji w języku SQL oblicza parametry (argumenty) w określonej kolejności, na przykład listy, i zwraca pierwszą wartość różną od null. Mówiąc najprościej, funkcja ocenia listę sekwencyjnie i kończy działanie w przypadku wystąpienia pierwszej wartości innej niż null. Jeśli wszystkie argumenty na liście są puste, funkcja zwraca NULL.
Ponadto funkcja obejmuje i jest obsługiwana w innych bazach danych, takich jak MYSQL, Azure SQL Database, Oracle i PostgreSQL.
Możesz użyć Coalesce w następujących przypadkach, gdy:
- Obsługa wartości NULL.
- Uruchamianie kilku zapytań jako jednego.
- Unikanie długich, czasochłonnych instrukcji CASE.
W przypadku użycia zamiast instrukcji CASE (lub funkcji ISNULL) funkcja koalescencji przyjmuje wiele parametrów, w przeciwieństwie do instrukcji CASE, która wymaga tylko dwóch. Takie podejście pozwala pisać mniej kodu i ułatwia proces pisania.
Oto składnia:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Łączenie w serwerze SQL ma kilka właściwości, w tym argumenty tego samego typu danych, akceptujące wiele parametrów oraz argumenty typu całkowitego, które mają być kaskadowane przez funkcję yield w celu zwrócenia liczby całkowitej jako wyniku.
Przeczytaj także: Ultimate SQL Cheat Sheet do zakładki na później
Ale zanim przejdziemy do korzystania z koalescencji, zrozummy NULL.
Co to jest wartość NULL w SQL?
Unikalny znacznik NULL w SQL wskazuje na nieistnienie wartości w bazie danych. Możesz myśleć o tym jako o niezdefiniowanej lub nieznanej wartości. Proszę nie wpadać w pułapkę myślenia o tym jako o pustym łańcuchu lub wartości zerowej; to brak wartości. Wystąpienie wartości null w kolumnach tabeli reprezentuje brakujące informacje.
W praktycznym przypadku kolumna danych w kolumnie bazy danych witryny handlu elektronicznego może zostać wypełniona wartością NULL, jeśli klient nie poda swojego identyfikatora. Null w SQL jest unikalny; jest stanem, w przeciwieństwie do innych języków programowania, gdzie oznacza „niewskazanie na konkretny obiekt”.
Wartości NULL w SQL mają znaczący wpływ na relacyjne bazy danych. Po pierwsze, umożliwiają wykluczenie określonych wartości podczas pracy z innymi funkcjami wewnętrznymi. Na przykład można wygenerować listę wszystkich zamówień w środowisku produkcyjnym, ale inne nadal muszą zostać zrealizowane. Użycie NULL jako symbolu zastępczego umożliwia wewnętrznej funkcji SUMA dodawanie sum.
Ponadto rozważ przypadki, w których musisz wygenerować średnią za pomocą funkcji AVG. Jeśli pracujesz z wartościami zerowymi, wyniki są przekrzywione. Zamiast tego baza danych może usuwać takie pola i używać NULL, co daje dokładne wyniki.
Wartości NULL nie mają wad. Są one uważane za wartości o zmiennej długości, będące bajtami lub kilkoma bajtami. Ponieważ baza danych pozostawia miejsce na te bajty, jeśli przekraczają one ilość przechowywaną w bazie danych, w rezultacie baza danych zajmuje więcej miejsca na dysku twardym niż zwykłe wartości.
Ponadto podczas pracy z niektórymi funkcjami należy je dostosować, aby wyeliminować NULL. W rezultacie wydłuża to procedury SQL.
Obsługa wartości NULL za pomocą COALESCE ()
Wartości null oznaczają, że możesz mieć wartość, ale nie wiesz, jaka powinna być. Dopóki nie zbierzesz danych, które wypełnią twoje pola prawdziwymi wartościami, wartości NULL są prokuratorami.
Chociaż wartości NULL można używać w bazie danych dla wielu typów danych, w tym liczb dziesiętnych, łańcuchów, obiektów blob i liczb całkowitych, dobrą praktyką jest unikanie ich w przypadku danych liczbowych.
Wadą jest to, że gdy jest używany do wartości liczbowych, prawdopodobnie będziesz potrzebować wyjaśnienia podczas opracowywania kodu, który działa z danymi. Więcej o tym później.
Różne sposoby COALESCE () mogą być użyte do obsługi wartości NULL:
Używanie COALESCE () do zastępowania wartości pustych określoną wartością
Możesz użyć COALESCE (), aby zwrócić określone wartości dla wszystkich wartości pustych. Na przykład możesz mieć tabelę o nazwie „pracownicy” z kolumną „wynagrodzenie”, która może zawierać wartości puste, jeśli wynagrodzenie pracowników nie zostało zaksięgowane. Tak więc podczas wykonywania niektórych obliczeń możesz chcieć pracować z określoną wartością, w tym przypadku zerem, dla wszystkich wpisów NULL. Oto jak to zrobić.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Użycie COALESCE () do wybrania pierwszej wartości innej niż null z wielu opcji
Czasami możesz chcieć pracować z pierwszymi wartościami innymi niż NULL na liście wyrażeń. W takich przypadkach często masz wiele kolumn z powiązanymi danymi i chcesz nadać priorytet ich wartościom innym niż NULL. Składnia pozostaje.
COALESCE (expression1, expression2, …)
W praktycznym przypadku załóżmy, że masz tabelę kontaktów z kolumnami nazwa_preferowana i nazwa_pełna. I chcesz wygenerować listę kontaktów obok ich preferowanych nazw (jeśli są dostępne) lub ich pełnych nazw. Oto jak sobie z tym poradzić.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Jeśli preferowana nazwa nie ma wartości NULL dla tego przypadku testowego, zostanie zwrócona. W przeciwnym razie pełna nazwa jest zwracana jako nazwa wyświetlana.
Konkatenacja łańcuchów z koalescencją SQL
Możesz napotkać problemy z SQL podczas łączenia łańcuchów, jeśli w grę wchodzą wartości null. W takich przypadkach zwracana jest wartość NULL jako niepożądany wynik. Teraz, gdy NULL nie jest naszym pożądanym wynikiem, możesz rozwiązać problem za pomocą funkcji koalescencji. Poniżej znajduje się przykład.
Prosta konkatenacja łańcuchów jest wykonywana przez:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
Kod zwraca:
PrzykładCześć, gdzie jesteś, John?
Jeśli jednak użyjesz wartości NULL, jak pokazano poniżej:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
Wyjście jest teraz.
Ponieważ każda konkatenacja ciągu tekstowego zawierająca wartość NULL zwraca wartość NULL, powyższy wynik to NULL. Problem jest jednak rozwiązany za pomocą funkcji koalescencji (). Używając tej funkcji, zwracasz pusty ciąg znaków (lub spację) zamiast NULL. Załóżmy na przykład, że wymieniasz nazwy samochodów wraz z ich producentami; oto twoje zapytanie.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Jeśli producent ma wartość NULL, zamiast wartości NULL pojawi się znak „–”. Oto oczekiwane rezultaty.
car_brandoutlander, producent: —latające ostrogi, producent: Bentleyroyal atleta, producent: —royal sedan, producent: Crown
Jak widać, wyniki NULL są eliminowane, z opcją wstawienia zastępczej wartości ciągu.
Funkcja koalescencyjna SQL i obracanie
Przestawianie SQL to technika używana do przekształcania wierszy w kolumny. Pozwala na transpozycję (rotację) danych z postaci „znormalizowanej” (z wieloma wierszami i mniejszą liczbą kolumn) do postaci „znormalizowanej” (mniej wierszy i więcej kolumn). Funkcja koalescencji może być używana z przestawianiem SQL do obsługi wartości pustych w przestawionych wynikach.
Kiedy wykonujesz PIVOT w SQL, przekształcaj wiersze w kolumny; kolumny wynikowe są funkcjami agregującymi niektórych danych. Jeśli w każdym przypadku agregacja da w wyniku wartość null dla określonej komórki, możesz użyć funkcji `COALESCE`, aby zastąpić wartości puste wartością domyślną lub sensowną reprezentacją. Poniżej znajduje się przykład.
Rozważ tabelę, sprzedaż, z kolumnami rok, kwartał i przychód, i chcesz przestawić dane; tak, że masz lata jako kolumny i sumę przychodów dla każdego kwartału jako wartości. Jednak niektóre kwartały nie zawierają danych o przychodach, co daje wartości zerowe w wyniku przestawnym. W takim przypadku możesz użyć COALESCE, aby zastąpić wartości null w wyniku przestawnym zerem (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;
Skalarna funkcja zdefiniowana przez użytkownika i funkcja koalescencyjna SQL
Możesz użyć skalarnych UDF i koalescencji, aby wykonać złożoną logikę, która obsługuje wartości null. Połączenie tych funkcji pomoże Ci uzyskać bardziej wyrafinowane transformacje danych i obliczenia w zapytaniach SQL. Rozważmy tabelę Pracownicy z taką strukturą.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Możesz chcieć obliczyć całkowite zarobki każdego pracownika (pensja plus premia). Brakuje jednak pewnych wartości. W takim przypadku twój skalarny UDF może obsłużyć dodawanie wynagrodzenia i premii, podczas gdy koalescencja obsługuje wartości zerowe. Oto skalarny UDF łącznych zarobków.
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;
Walidacja danych za pomocą SQL Coalesce
Podczas pracy z bazami danych możesz chcieć sprawdzić poprawność wartości liczbowych. Załóżmy na przykład, że masz kolumny nazwa_produktu, cena i rabat w tabeli produkty. Chcesz pobrać nazwy produktów, ceny i rabaty dla każdego elementu. Ale chciałbyś traktować wszystkie wartości rabatu NULL jako 0. Pomocna może być funkcja koalescencji. Oto jak go używać.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
Łączenie SQL i kolumny obliczeniowe
Kolumny obliczeniowe to wirtualne kolumny obliczane na podstawie wyrażeń lub innych kolumn w tabeli. Ponieważ kolumny obliczeniowe nie są fizycznie przechowywane w bazie danych, można je wykorzystać za pomocą funkcji łączenia podczas obsługi złożonych scenariuszy i transformacji. Oto praktyczny przykład użycia.
Rozważmy tabelę „produkty” z kolumnami „cena”, „rabat” i „stawka_podatku”. W takim przypadku chcesz utworzyć kolumnę obliczeniową „cena_całkowita”, która będzie przedstawiać ostateczną cenę produktu po zastosowaniu rabatu i podatku. Jeśli nie określono rabatu ani podatku (NULL), należy kontynuować obliczenia przy użyciu zera. Oto jak wykorzystać koalescencję, aby dostosować ją do operacji.
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) );
W powyższym kodzie, oto co się dzieje.
Powyższa konfiguracja pozwala wygenerować total_price, kolumnę obliczeniową z rzeczywistą ceną końcową, pomimo braku lub posiadania wartości NULL.
Łączenie SQL i wyrażenie CASE
Możesz składniowo użyć koalescencji poprzez wyrażenie CASE. Oto przykład:
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
W powyższej konfiguracji zapytania CASE są podobne do funkcji COALESCE.
Dodatkowo możliwe jest użycie wyrażeń COALESCE i CASE w tym samym zapytaniu. Te dwie techniki mogą jednocześnie obsługiwać wartości NULL i stosować logikę warunkową. Zilustrujmy to przykładem.
Rozważ przypadek, w którym masz tabelę, produkty z kolumnami id_produktu, nazwa_produktu, cena i rabat. Niektóre z Twoich produktów mają określoną zniżkę, a inne nie. Jeśli produkt ma zniżkę, chcesz pokazać obniżoną cenę, w przeciwnym razie powinna zostać wyświetlona cena regularna.
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;
W powyższym kodzie `CASE` sprawdza, czy `rabat` jest większy od zera i oblicza obniżoną cenę, w przeciwnym razie zwraca NULL. Funkcja `COALESCE` przyjmuje jako parametry wynik z `CASE` i `price`. Zwraca pierwszą wartość różną od NULL, skutecznie zwracając cenę z rabatem, jeśli jest dostępna, lub normalną cenę, jeśli jej nie ma.
Ostatnie słowa
W tym poście pokazano różne sposoby użycia funkcji `COALESCE` w zapytaniach do bazy danych. Oceniając parametry w określonej kolejności i zwracając pierwszą wartość różną od NULL, funkcja koalescencji upraszcza zapytania, czyniąc je wydajnymi.
Coalesce to wszechstronna funkcja, niezależnie od tego, czy obsługujesz wartości puste, łączenie ciągów, przestawianie danych, sprawdzanie poprawności, czy pracę z kolumnami obliczeniowymi. Dzięki opanowaniu funkcji koalescencji programiści mogą poruszać się po brakujących danych i tworzyć bezbłędne projekty baz danych. Pamiętaj, aby opanować technikę; możesz potrzebować bardziej dogłębnej praktyki.
Możesz teraz sprawdzić, jak tworzyć ograniczenia klucza obcego w SQL.