Како технологија расте и еволуира, неопходно је да будете у току са најновијим трендовима као програмер. Било да сте почетник или стручњак, добро разумевање манипулације стринговима помаже вам да припремите податке (на пример, да генеришете другачији образац од постојећег, чинећи га употребљивим за ваше пословање) и управљате њима помоћу уграђених функција СКЛ сервера.
Поред манипулације подацима, можете испитати скупове података, проценити вредности података и кодирати их или декодирати да бисте добили значајније податке. Као резултат, ово вам помаже да се крећете кроз недостајуће вредности у скуповима података, да схватите њихов утицај на прорачуне и поједноставите укупан рад са процесом података како бисте избегли нулте вредности које могу да покваре резултате рада.
Овај водич вас води кроз функцију спајања у СКЛ-у, која помаже у изградњи сложених програма. Пост претпоставља да сте се сусрели и радили са СКЛ-ом и да само желите да ојачате своје разумевање ове одређене функције. Наша серија СКЛ водича може вам помоћи да брзо почнете.
Преглед садржаја
Шта је ЦОАЛЕСЦЕ () у СКЛ-у и његова употреба?
Функција спајања у СКЛ-у процењује параметре (аргументе) одређеним редоследом, као што су листе, и враћа прву вредност која није нулта. Једноставно речено, функција процењује вашу листу секвенцијално и завршава се на инстанци прве вредности која није нулта. Ако су сви аргументи на листи нулл, функција враћа НУЛЛ.
Поред тога, функција је инклузивна и подржана у другим базама података као што су МИСКЛ, Азуре СКЛ база података, Орацле и ПостгреСКЛ.
Можете користити Цоалесце у следећим случајевима када:
- Руковање НУЛЛ вредностима.
- Покретање неколико упита као један.
- Избегавање дугих, дуготрајних ЦАСЕ изјава.
Када се користи уместо наредби ЦАСЕ (или функције ИСНУЛЛ), коалесце узима много параметара, за разлику од ЦАСЕ, који захтева само два. Овај приступ вам омогућава да пишете мање кода и олакшава процес писања.
Ево синтаксе:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Спајање у СКЛ серверу има неколико својстава, укључујући аргументе истог типа података, прихватање многих параметара и аргументе целобројног типа који се каскадирају помоћу функције ииелд да би се као излаз вратио цео број.
Такође прочитајте: Ултимате СКЛ Цхеат Схеет за обележавање за касније
Али пре него што почнемо да користимо спајање, хајде да разумемо НУЛЛ.
Шта је НУЛЛ вредност у СКЛ-у?
Јединствени маркер НУЛЛ у СКЛ-у указује на непостојање вредности у бази података. Можете о томе размишљати као о недефинисаној или непознатој вредности. Молимо вас да не улазите у замку размишљања о томе као о празном низу или нулти вредности; то је одсуство вредности. Појава нуле у колонама табеле представља информације које недостају.
У практичном случају употребе, колона података у колони базе података веб-сајта е-трговине може бити попуњена НУЛЛ вредношћу ако клијент не унесе свој ИД. Нулл у СКЛ-у је јединствен; то је стање, за разлику од других програмских језика где значи „не указује на одређени објекат“.
НУЛЛ вредности у СКЛ-у имају значајан утицај на релационе базе података. Прво, омогућавају вам да искључите одређене вредности док радите са другим интерним функцијама. На пример, можете да генеришете листу укупних поруџбина у производном окружењу, али друге још увек треба да се доврше. Коришћење НУЛЛ као чувара места омогућава интерној функцији СУМ да дода укупне вредности.
Поред тога, узмите у обзир случајеве када треба да генеришете просек користећи АВГ функцију. Ако радите са нултим вредностима, резултати су искривљени. Уместо тога, база података може уклонити таква поља и користити НУЛЛ, што резултира тачним резултатима.
НУЛЛ вредности немају недостатке. Они се сматрају вредностима променљиве дужине, будући да су бајтови или неколико њих. Пошто база података оставља простора за ове бајтове ако премашују оно што је ускладиштено у бази података, резултат је да ваша база података заузима више простора на чврстом диску за разлику од уобичајених вредности.
Поред тога, када радите са неким функцијама, мораћете да их прилагодите да бисте елиминисали НУЛЛС. Ово, као резултат, чини ваше СКЛ процедуре дужим.
Руковање НУЛЛ вредностима помоћу ЦОАЛЕСЦЕ ()
Нулл вредности подразумевају да бисте могли да имате вредност, али нисте свесни колика би вредност требало да буде. Док не прикупите податке који испуњавају ваша поља стварним вредностима, НУЛЛ вредности су прокуристи.
Иако можете да користите НУЛЛ вредности за више типова података у вашој бази података, укључујући децимале, стрингове, блоб-ове и целе бројеве, добра је пракса да их избегавате када радите са нумеричким подацима.
Недостатак је у томе што када се користи за нумеричке вредности, вероватно ће вам требати појашњење док будете развијали код који ради са подацима. Више о томе касније.
Различити начини на које се ЦОАЛЕСЦЕ () може користити за руковање НУЛЛ вредношћу:
Коришћење ЦОАЛЕСЦЕ () за замену нул вредности одређеном вредношћу
Можете користити ЦОАЛЕСЦЕ () да вратите одређене вредности за све нулте вредности. На пример, можете имати табелу под називом „запослени“ са колоном „плата“, која може да садржи нулте вредности ако плата запослених није призната. Дакле, када радите неке прорачуне, можда ћете желети да радите са одређеном вредношћу, у овом случају нула, за све НУЛЛ уносе. Ево како да то урадите.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Коришћење ЦОАЛЕСЦЕ () за избор прве вредности која није нулта из више опција
Понекад ћете можда желети да радите са првим вредностима које нису НУЛЛ на листи израза. У таквим случајевима, често имате више колона са повезаним подацима и желели бисте да дате приоритет њиховим вредностима које нису НУЛЛ. Остаје синтакса.
COALESCE (expression1, expression2, …)
У практичном случају, претпоставимо да имате табелу контаката са колонама преферирано_име и пуно_име. Такође бисте желели да генеришете листу контаката упоредо са њиховим жељеним именима (ако су доступна) или њиховим пуним именима. Ево како да се носите са тим.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Ако преферирано_наме није НУЛЛ за овај тест случај, биће враћено. У супротном, пуно име се враћа као име за приказ.
Конкатенација низова са СКЛ коалесце
Можда ћете наићи на проблеме са СКЛ-ом приликом спајања стрингова ако су укључене нулте вредности. У таквим случајевима, НУЛЛ се враћа као непожељан резултат. Сада када НУЛЛ није наш жељени исход, можете решити проблем помоћу функције спајања. Испод је пример.
Једноставну конкатенацију низова врши:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
Код враћа:
Пример: Здраво, где си, Џоне?
Међутим, ако користите вредност НУЛЛ, као што је приказано у наставку:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
Излаз је сада.
Пошто свака конкатенација текстуалних стрингова која укључује вредност НУЛЛ враћа НУЛЛ, горњи исход је НУЛЛ. Проблем је, међутим, решен помоћу коалесце (). Користећи ову функцију, враћате празан стринг (или размак) уместо НУЛЛ. На пример, претпоставимо да наводите имена аутомобила са њиховим произвођачима; ево вашег упита.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Ако је произвођач НУЛЛ, имаћете ‘–’ уместо НУЛЛ. Ево очекиваних резултата.
цар_брандоутландер, произвођач: —летеће мамузе, произвођач: Бентлеироиал атхлете, произвођач: —краљевски салон, произвођач: Цровн
Као што видите, НУЛЛ резултати су елиминисани, уз опцију уметања ваше заменске вредности стринга.
СКЛ коалесце функција и окретање
СКЛ окретање је техника која се користи за трансформацију редова у колоне. Омогућава вам да транспонујете (ротирате) податке из „нормализованог“ облика (са много редова и мање колона) у „денормализовани“ (мање редова и више колона). Функција спајања се може користити са СКЛ окретањем за руковање нултим вредностима у заобљеним резултатима.
Када ПИВОТ у СКЛ-у, трансформишите редове у колоне; резултујуће колоне су агрегатне функције неких података. Ако, у сваком случају, агрегација резултира нултом за одређену ћелију, можете користити `ЦОАЛЕСЦЕ` да замените нул вредности подразумеваном вредношћу или смисленим приказом. Испод је пример.
Узмите у обзир табелу, продаја, са колонама година, квартал и приход, а ви бисте желели да заокренете податке; тако да имате године као колоне и збир прихода за сваки квартал као вредности. Међутим, неки квартали немају податке о приходима, што даје нулте вредности у заобљеном резултату. У овом случају, можете да користите ЦОАЛЕСЦЕ да замените нулте вредности у заобљеном резултату нулом (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;
Скаларна кориснички дефинисана функција и СКЛ коалесце функција
Можете користити скаларне УДФ-ове и спојити се за извођење сложене логике која рукује нултим вредностима. Комбиновање ових функција ће вам помоћи да постигнете софистицираније трансформације података и прорачуне у СКЛ упитима. Размотрите табелу, Запослени, са овом структуром.
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;
Валидација података помоћу СКЛ Цоалесце
Када радите са базама података, можда ћете желети да проверите нумеричке вредности. На пример, рецимо да имате колоне назив_производа, цена и попуст у табели производи. Желите да преузмете називе производа, цене и попусте сваке ставке. Али, желели бисте да третирате све НУЛЛ вредности попуста као 0. Функција спајања може бити од помоћи. Ево како да га користите.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
СКЛ коалесце и рачунарске колоне
Израчунате колоне су виртуелне колоне израчунате на основу израза или других колона у табели. Пошто израчунате колоне нису физички ускладиштене у бази података, можете их искористити помоћу функције спајања када рукујете сложеним сценаријима и трансформацијама. Ево практичног примера употребе.
Размотрите табелу `производи` са колонама `цена`, `попуст` и `так_рате`. У овом случају, желите да креирате израчунату колону, `тотал_прице`, која представља коначну цену производа након примене попуста и пореза. Ако попуст или порез нису наведени (НУЛЛ), требало би да наставите са прорачунима користећи нулу. Ево како да искористите спајање да би одговарало операцији.
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) );
У горњем коду, ево шта се дешава.
Горенаведено подешавање вам омогућава да генеришете тотал_прице, израчунату колону, са стварном коначном ценом, упркос томе што недостаје или има НУЛЛ вредности.
СКЛ Цоалесце и ЦАСЕ Екпрессион
Можете синтаксички да користите спајање кроз израз ЦАСЕ. Ево примера:
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
У горњем подешавању, ЦАСЕ поставља упите попут функције ЦОАЛЕСЦЕ.
Додатно, могуће је коришћење израза ЦОАЛЕСЦЕ и ЦАСЕ у истом упиту. Две технике могу да рукују НУЛЛ вредностима и истовремено примењују условну логику. Хајде да то илуструјемо примером.
Размотрите случај када имате табелу, производе са колонама продуцт_ид, продуцт_наме, прице и дисцоунт. Неки од ваших производа имају посебан попуст, док други немају. Ако производ има попуст, желите да прикажете снижену цену, у супротном би требало да буде приказана обична.
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;
У горњем коду, `ЦАСЕ` проверава да ли је `попуст` већи од нуле и израчунава снижену цену, иначе враћа НУЛЛ. Функција `ЦОАЛЕСЦЕ` узима резултат из `ЦАСЕ` и `прице` као своје параметре. Враћа прву вредност која није НУЛЛ, ефективно враћајући снижену цену ако је доступна или редовну цену ако је нема.
Завршне речи
Овај пост је демонстрирао различите начине коришћења функције `ЦОАЛЕСЦЕ` у вашим упитима базе података. Проценом параметара одређеним редоследом и враћањем прве вредности која није НУЛЛ, функција спајања поједностављује упите чинећи их ефикасним.
Спајање је свестрана функција без обзира да ли рукујете нултим вредностима, конкатенацију стрингова, закретање података, валидацију или радите са израчунатим колонама. Савладавањем функције спајања, програмери могу да се крећу кроз податке који недостају и креирају дизајн базе података без грешака. Запамтите, да савладате технику; можда ће вам требати детаљнија пракса.
Сада можете да проверите како да креирате ограничења страног кључа у СКЛ-у.