Разумевање функције ЦОАЛЕСЦЕ () у СКЛ-у

by
Tweet
Share
Share
Pin

У свету који се брзо развија захваљујући технологији, кључно је да програмери буду у току са најновијим трендовима. Без обзира да ли сте почетник или искусан стручњак, солидно познавање манипулације стринговима је неопходно. Омогућава вам припрему података (на пример, креирање нових формата из постојећих, чинећи их употребљивим за ваше пословање) и ефикасно управљање њима помоћу уграђених функција СКЛ сервера.

Поред манипулације подацима, можете анализирати скупове података, процењивати вредности и кодирати или декодирати информације ради добијања значајних увида. Ово је веома корисно при прегледању недостајућих вредности у подацима, разумевању њиховог утицаја на калкулације и поједностављењу укупног процеса рада са подацима. На тај начин избегава се појава нултих вредности које могу искварити резултате.

Овај водич вас уводи у функцију спајања у СКЛ-у, која је изузетно корисна приликом израде сложених програма. Претпоставка је да већ имате основно знање о СКЛ-у и желите да проширите своје разумевање ове специфичне функције. Ако сте почетник у СКЛ-у, наша серија водича вам може помоћи да брзо почнете.

Шта је COALESCE() у СКЛ-у и како се користи?

Функција спајања у СКЛ-у анализира параметре (аргументе) по задатом редоследу, на пример, у листама, и враћа прву вредност која није нулта. Једноставније речено, функција секвенцијално прегледа вашу листу и зауставља се када наиђе на прву вредност која није нулта. Ако су сви аргументи у листи нулти, функција враћа НУЛЛ.

Важно је напоменути да је ова функција широко подржана и укључена у друге системе база података као што су MySQL, Azure SQL Database, Oracle и PostgreSQL.

Можете користити `COALESCE` у следећим ситуацијама:

  • Када треба управљати НУЛЛ вредностима.
  • Када желите да покренете више упита као један.
  • Да избегнете дугачке и компликоване `CASE` изразе.

У поређењу са `CASE` наредбама (или функцијом `ISNULL`), `COALESCE` прихвата више параметара, док `CASE` захтева само два. Овај приступ вам омогућава да пишете мање кода, што олакшава процес програмирања.

Ево синтаксе:

COALESCE(valueOne, valueTwo, valueThree, …, valueX);

Функција спајања у СКЛ серверу има неколико карактеристика, укључујући захтев да сви аргументи буду истог типа података, прихватање више параметара, и могућност да аргументи целобројног типа каскадно користе `yield` функцију и врате целобројну вредност.

Прочитајте такође: Кратки подсетник за СКЛ за каснију употребу.

Пре него што почнемо да користимо спајање, погледајмо шта је НУЛЛ.

Шта је НУЛЛ вредност у СКЛ-у?

У СКЛ-у, јединствени маркер `NULL` означава одсуство вредности у бази података. Замислите га као недефинисану или непознату вредност. Немојте га мешати са празним стрингом или нултом вредношћу; он представља одсуство вредности. Појава НУЛЛ-а у колонама табеле указује на информације које недостају.

На пример, у бази података веб-сајта за е-трговину, колона која садржи ИД клијента може бити попуњена вредношћу НУЛЛ ако клијент није унео свој ИД. НУЛЛ у СКЛ-у је јединствена ставка; то је стање, за разлику од неких других програмских језика где значи „не указује на одређени објекат“.

НУЛЛ вредности имају значајан утицај на релационе базе података. Прво, омогућавају вам да искључите одређене вредности док користите друге интерне функције. На пример, можете генерисати листу укупних поруџбина у производном окружењу, док су неке поруџбине у процесу. Коришћење НУЛЛ-а као чувара места омогућава интерној функцији `SUM` да сабира укупне вредности.

Поред тога, размотрите ситуације када треба да израчунате просек користећи функцију `AVG`. Ако радите са нултим вредностима, резултати ће бити нетачни. Уместо тога, база података може елиминисати таква поља и користити НУЛЛ, што даје тачне резултате.

НУЛЛ вредности имају и недостатке. Сматрају се вредностима променљиве дужине, обично заузимајући неколико бајтова. Пошто база података резервише простор за те бајтове ако премашују величину података у бази, база података може заузимати више простора на чврстом диску у поређењу са уобичајеним вредностима.

Такође, када користите неке функције, можда ћете морати да их прилагодите како бисте елиминисали НУЛЛ вредности, што ће ваше СКЛ процедуре учинити дужим.

Управљање НУЛЛ вредностима помоћу COALESCE()

НУЛЛ вредности указују на то да би вредност могла постојати, али тренутно не знате која је то вредност. Док не добијете податке који ће попунити ваша поља стварним вредностима, НУЛЛ вредности су привремено решење.

Иако можете користити НУЛЛ вредности за различите типове података, као што су децимале, стрингови, блоб-ови и цели бројеви, препоручује се да их избегавате при раду са нумеричким подацима.

Недостатак је што ће вам вероватно требати додатна појашњења када будете развијали код који ради са нумеричким подацима и НУЛЛ вредностима. Више о томе касније.

Различити начини на које се COALESCE() може користити за управљање НУЛЛ вредностима:

Коришћење COALESCE() за замену нултих вредности одређеном вредношћу

Можете користити `COALESCE()` да замените све нулте вредности одређеном вредношћу. На пример, имате табелу „запослени“ са колоном „плата“ која може садржати нулте вредности ако плата запослених није евидентирана. Када радите неке прорачуне, можда ћете желети да користите одређену вредност (у овом случају нулу) за све НУЛЛ уносе. Ево како то можете урадити:

SELECT COALESCE(salary, 0) AS adjusted_salary
FROM employees;

Коришћење COALESCE() за избор прве вредности која није нулта из више опција

Понекад ћете можда желети да користите прву вредност која није НУЛЛ у листи израза. У таквим ситуацијама, често имате неколико колона са повезаним подацима и желите да им дате приоритет на основу вредности које нису НУЛЛ. Синтакса остаје иста.

COALESCE(expression1, expression2, …)

На пример, претпоставимо да имате табелу контаката са колонама `преферирано_име` и `пуно_име`. Желите да генеришете листу контаката са њиховим преферираним именима (ако су доступна) или пуним именима. Ево како то можете урадити:

SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

Ако `preferred_name` није НУЛЛ, биће враћено. У супротном, `full_name` ће се користити као име за приказ.

Конкатенација стрингова са СКЛ `COALESCE`

Можете наићи на проблеме са СКЛ-ом приликом спајања стрингова ако су укључене нулте вредности. У таквим случајевима, НУЛЛ се враћа као непожељан резултат. Пошто НУЛЛ није оно што желите, можете решити проблем користећи функцију спајања. Ево примера:

Једноставна конкатенација низова се врши на следећи начин:

SELECT ‘Hello, where are you, ‘|| ‘John ’||’?’ AS example

Код враћа:

Пример: Здраво, где си, Џоне?

Међутим, ако користите НУЛЛ вредност, као што је приказано у наставку:

SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example

Резултат је сада:

Пошто било која конкатенација текстуалних стрингова која укључује вредност НУЛЛ враћа НУЛЛ, горњи резултат је НУЛЛ. Проблем се, међутим, може решити помоћу `COALESCE()`. Користећи ову функцију, враћате празан стринг (или размак) уместо НУЛЛ-а. На пример, претпоставимо да наводите имена аутомобила са њиховим произвођачима; ево вашег упита:

SELECT
car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

Ако је `manufacturer` НУЛЛ, добићете ‘—’ уместо НУЛЛ-а. Ево очекиваних резултата:

car_brand
outlander, manufacturer: —
flying spurs, manufacturer: Bentley
royal athlete, manufacturer: —
royal saloon, manufacturer: Crown

Као што видите, НУЛЛ резултати су елиминисани, уз опцију уметања ваше заменске стрингове вредности.

СКЛ `COALESCE` функција и окретање

СКЛ окретање је техника која се користи за трансформацију редова у колоне. Омогућава вам да транспонујете (ротирате) податке из „нормализованог“ облика (са много редова и мање колона) у „денормализовани“ (мање редова и више колона). Функција спајања се може користити са СКЛ окретањем за управљање нултим вредностима у резултатима окретања.

Када користите `PIVOT` у СКЛ-у, трансформишете редове у колоне; резултујуће колоне су агрегатне функције неких података. Ако агрегација даје нулту вредност за одређену ћелију, можете користити `COALESCE` да замените нулте вредности подразумеваном вредношћу. Ево примера:

Узмимо табелу `продаја` са колонама `година`, `квартал` и `приход`, а ви желите да окренете податке. Желите да имате године као колоне и збир прихода за сваки квартал као вредности. Међутим, неки квартали немају податке о приходима, што даје нулте вредности у резултатима окретања. У овом случају, можете користити `COALESCE` да замените нулте вредности у резултатима окретања нулом (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;

Скаларна кориснички дефинисана функција и СКЛ `COALESCE` функција

Можете користити скаларне УДФ-ове и спојити се за обављање сложене логике која рукује нултим вредностима. Комбиновање ових функција ће вам помоћи да постигнете софистицираније трансформације података и прорачуне у СКЛ упитима. Размотрите табелу `Запослени` са овом структуром:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

Можда ћете желети да израчунате укупну зараду сваког запосленог (плата плус бонус). Међутим, постоје неке вредности које недостају. У овом случају, ваш скаларни УДФ може да обрађује додатке плате и бонуса, док спајање управља нултим вредностима. Ево скаларног УДФ-а за укупну зараду:

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;

Валидација података помоћу СКЛ `COALESCE`

Када радите са базама података, можда ћете желети да проверите нумеричке вредности. На пример, рецимо да имате колоне `назив_производа`, `цена` и `попуст` у табели `производи`. Желите да преузмете називе производа, цене и попусте сваке ставке. Међутим, желели бисте да третирате све НУЛЛ вредности попуста као 0. Функција спајања може бити од помоћи. Ево како да је користите:

SELECT product_name, price, COALESCE(discount, 0) AS discount
FROM products

СКЛ `COALESCE` и израчунате колоне

Израчунате колоне су виртуелне колоне које се израчунавају на основу израза или других колона у табели. Пошто се израчунате колоне не складиште физички у бази података, можете их користити помоћу функције спајања приликом решавања сложених сценарија и трансформација. Ево примера:

Размотрите табелу `производи` са колонама `цена`, `попуст` и `пореска_стопа`. Желите да креирате израчунату колону `укупна_цена` која представља коначну цену производа након примене попуста и пореза. Ако попуст или порез нису наведени (НУЛЛ), требало би да наставите са прорачунима користећи нулу. Ево како да искористите спајање за ову операцију:

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)
);

У горњем коду, ево шта се дешава:

  • Израчуната колона `total_price` је дефинисана као `(COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)`.
  • Ако је цена НУЛЛ, `COALESCE(price*discount, 0)` осигурава да се третира као 0.
  • Ако је попуст нула, `COALESCE(price*discount)` осигурава да се третира као 0, а множење не утиче на прорачун.
  • Ако је `пореска_стопа` НУЛЛ, `COALESCE(1 + tax_rate, 1)` осигурава да се третира као 0, што значи да се порез не примењује, а множење не утиче на прорачун.

Наведено вам омогућава да генеришете `total_price`, израчунату колону, са стварном коначном ценом, чак и ако недостају вредности или има НУЛЛ вредности.

СКЛ `COALESCE` и `CASE` изрази

Можете синтаксички да користите спајање кроз `CASE` израз. Ево примера:

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

У горњем примеру, `CASE` поставља упите попут функције `COALESCE`.

Поред тога, могуће је користити `COALESCE` и `CASE` изразе у истом упиту. Ове две технике могу да рукују НУЛЛ вредностима и истовремено примењују условну логику. Размотримо пример:

Размотрите табелу `производи` са колонама `product_id`, `product_name`, `price` и `discount`. Неки од ваших производа имају посебан попуст, док други немају. Ако производ има попуст, желите да прикажете снижену цену, у супротном би требало да буде приказана обична.

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;

У горњем коду, `CASE` проверава да ли је `discount` већи од нуле и израчунава снижену цену, иначе враћа НУЛЛ. Функција `COALESCE` узима резултат из `CASE` и `price` као своје параметре. Враћа прву вредност која није НУЛЛ, ефективно враћајући снижену цену ако је доступна или редовну цену ако је нема.

Завршне речи

Овај чланак је приказао различите начине коришћења функције `COALESCE` у вашим упитима базе података. Процењујући параметре по одређеном редоследу и враћајући прву вредност која није НУЛЛ, функција спајања поједностављује упите, чинећи их ефикасним.

Спајање је разноврсна функција без обзира да ли управљате нултим вредностима, конкатенацијом стрингова, окретањем података, валидацијом или радите са израчунатим колонама. Савладавањем функције спајања, програмери могу да се носе са подацима који недостају и креирају базе података без грешака. Запамтите, да бисте савладали ову технику, потребно је много праксе.

Сада можете да проверите како се креирају ограничења страних кључева у СКЛ-у.