Боби 7. Пайваст кардани ҷадвалҳо

Дар бобҳои 1 ва 3 мафҳуми пойгоҳи додаҳои релятсионӣ-ро муаррифӣ кардем: барномае, ки нигоҳдории додаҳоро дар чанд ҷадвали ба ҳам алоқаманд дастгирӣ мекунад. Дар модели релятсионӣ ҳар ҷадвал одатан додаҳои як навъи объектро нигоҳ медорад — масалан, донишҷӯён, автомобилҳо, харидҳо ё хонаҳо. Ҳар сатри ҷадвал яке аз ҳамин объектҳоро тавсиф мекунад. Амале, ки пайвасткунии ҷадвалҳо ё JOIN ном дорад, имкон медиҳад сатрҳои як ҷадвалро бо сатрҳои ҷадвалҳои дигар пайванд диҳем.

Мафҳуми пойгоҳи додаҳои релятсиониро донишманди бритониёии илмҳои компютерӣ Эдгар Ф. Кодд пешниҳод кардааст. Ӯ соли 1970, ҳангоми кор дар IBM, мақолаеро бо номи «Модели релятсионии додаҳо барои бонкҳои бузурги муштараки додаҳо» нашр кард. Андешаҳои ӯ дар тарҳрезии пойгоҳҳои додаҳо дигаргунии бузург ба вуҷуд оварданд ва ба таҳияи SQL замина гузоштанд. Бо модели релятсионӣ метавон ҷадвалҳое сохт, ки такрори додаҳоро бартараф мекунанд, нигоҳдориашон осонтар аст ва барои навиштани дархостҳое, ки маҳз додаҳои лозимаро мегиранд, имкониятҳои бештар медиҳанд.

Пайваст кардани ҷадвалҳо бо JOIN

Барои пайваст кардани ҷадвалҳо дар дархост мо сохтори JOIN ... ON-ро истифода мебарем. Дар ин боб шаклҳои дигари JOIN-ро низ меомӯзем.

JOIN, ки қисми стандарти ANSI SQL аст, як ҷадвали пойгоҳи додаҳоро бо ҷадвали дигар тавассути ифодаи мантиқӣ дар банди ON пайваст мекунад. Шакли маъмули он баробарии қиматҳоро месанҷад:

SELECT *  
FROM table_a JOIN table_b  
ON table_a.key_column = table_b.foreign_key_column;  

Ин ба дархости одии SELECT, ки аллакай омӯхтед, монанд аст. Аммо ба ҷойи он ки дар банди FROM танҳо як ҷадвалро нишон диҳем, аввал номи як ҷадвал, баъд калимаи калидии JOIN ва сипас номи ҷадвали дуюмро менависем.

Баъдан банди ON меояд, ки дар он ифодаеро бо оператори муқоисаи баробарӣ ҷойгир мекунем. Ҳангоми иҷрои дархост сатрҳои ҳар ду ҷадвале баргардонда мешаванд, ки барои онҳо ифодаи банди ON натиҷаи true медиҳад, яъне қиматҳои сутунҳои нишон додашуда баробаранд.

Метавонед ҳар ифодаеро истифода баред, ки натиҷааш яке аз қиматҳои мантиқии true ё false бошад. Масалан, метавонед сатрҳоеро пайваст кунед, ки қимати як сутун аз қимати сутуни дигар калонтар ё ба он баробар аст:

ON table_a.key_column >= table_b.foreign_key_column  

Ин кам истифода мешавад, вале агар таҳлили шумо талаб кунад, чунин имконият вуҷуд дорад.

Алоқаманд кардани ҷадвалҳо тавассути сутунҳои калидӣ

Мисоли зеринро тасаввур кунед: шумо таҳлилгари додаҳо ҳастед ва бояд хароҷоти музди меҳнати як муассисаи давлатиро аз рӯйи шуъбаҳо санҷед. Мувофиқи қонун дар бораи озодии дастрасӣ ба иттилоот, шумо маълумоти музди меҳнати муассисаро дархост мекунед. Интизоред ҷадвали одие гиред, ки ҳар корманд ва маоши ӯро чунин нишон медиҳад:

Аммо маълумоти гирифтаатон чунин нест. Муассиса ба шумо содироти додаҳо аз низоми ҳисобкунии музди меҳнатро мефиристад: дувоздаҳ файли CSV, ки ҳар кадом як ҷадвали пойгоҳи додаҳоро ифода мекунад.

Шумо ҳуҷҷати шарҳи сохтори додаҳоро мехонед — ҳатман ҳамеша чунин ҳуҷҷатро низ дархост кунед! — ва тадриҷан маънои сутунҳои ҳар ҷадвалро мефаҳмед. Ду ҷадвал диққати шуморо ҷалб мекунанд: яке бо номи employees ва дигаре бо номи departments.

Бо коди намунаи 7-1 нусхаҳои ин ҷадвалҳоро месозем, ба онҳо сатрҳо илова мекунем ва тарзи пайваст кардани додаҳои онҳоро меомӯзем. Дар пойгоҳи додаҳои analysis, ки барои ин машқҳо сохтаед, тамоми кодро иҷро кунед. Сипас додаҳоро бо дархости одии SELECT ё бо пахш кардани номи ҷадвал дар pgAdmin ва интихоби View/Edit Data ▶ All Rows бинед.

CREATE TABLE departments (  
    dept_id integer,  
    dept text,  
    city text,  
    CONSTRAINT dept_key PRIMARY KEY (dept_id),  
    CONSTRAINT dept_city_unique UNIQUE (dept, city)  
);

CREATE TABLE employees (  
    emp_id integer,  
    first_name text,  
    last_name text,  
    salary numeric(10,2),  
    dept_id integer REFERENCES departments (dept_id),  
    CONSTRAINT emp_key PRIMARY KEY (emp_id)  
);

INSERT INTO departments  
VALUES  
    (1, 'Tax', 'Atlanta'),  
    (2, 'IT', 'Boston');

INSERT INTO employees  
VALUES  
    (1, 'Julia', 'Reyes', 115300, 1),  
    (2, 'Janet', 'King', 98000, 1),  
    (3, 'Arthur', 'Pappas', 72700, 2),  
    (4, 'Michael', 'Taylor', 89500, 2);  

Намунаи 7-1: Сохтани ҷадвалҳои departments ва employees.

Ин ду ҷадвал ба модели релятсионии Кодд мувофиқат мекунанд, зеро ҳар кадом хусусиятҳои як навъи объектро тавсиф мекунад: шуъбаҳо ва кормандони муассиса.

Дар ҷадвали departments бояд додаҳои зеринро бинед:

Сутуни dept_id калиди асосӣ-и ҷадвал аст. Калиди асосӣ як сутун ё маҷмӯи сутунҳоест, ки қиматҳояшон ҳар сатри ҷадвалро ба таври ягона муайян мекунанд. Барои калиди асосӣ маҳдудиятҳои зерин амал мекунанд:

• Қимати сутун ё маҷмӯи сутунҳо бояд барои ҳар сатр беназир бошад.
• Сутун ё маҷмӯи сутунҳо набояд қимати мавҷуднабуда дошта бошанд.

Мо калидҳои асосии departments ва employees-ро бо калимаи калидии CONSTRAINT муайян мекунем. Дар боби 8 ин калима ва навъҳои дигари маҳдудиятҳоро муфассал меомӯзем.

Қиматҳои dept_id ҳар сатри ҷадвали departments-ро ба таври ягона муайян мекунанд. Гарчанде дар ин мисол танҳо номи шуъба ва шаҳр оварда шудааст, дар шароити воқеӣ ҷадвал эҳтимол маълумоти иловагӣ, ба монанди суроға ё маълумоти тамосро низ дар бар мегирад.

Ҷадвали employees бояд чунин бошад:

Қиматҳои emp_id ҳар сатри ҷадвали employees-ро ба таври ягона муайян мекунанд. Барои нишон додани он ки ҳар корманд дар кадом шуъба кор мекунад, ҷадвал сутуни dept_id дорад. Қиматҳои ин сутун ба қиматҳои калиди асосии ҷадвали departments ишора мекунанд.

Инро калиди хориҷӣ меномем. Ҳангоми сохтани ҷадвал онро ҳамчун маҳдудият илова мекунем:

dept_id integer REFERENCES departments (dept_id)  

Маҳдудияти калиди хориҷӣ талаб мекунад, ки қиматҳои он дар сутунҳои истинодшаванда аллакай вуҷуд дошта бошанд. Одатан ин сутунҳо калиди асосии ҷадвали дигар мебошанд, вале калиди хориҷӣ метавонад ба ҳар маҷмӯи сутунҳое ишора кунад, ки қиматҳояшон барои ҳар сатр беназиранд.

Пас, қиматҳои dept_id дар ҷадвали employees бояд дар сутуни dept_id-и ҷадвали departments вуҷуд дошта бошанд. Дар акси ҳол, онҳоро илова карда наметавонед. Ин ба нигоҳ доштани якпорчагии додаҳо кумак мекунад.

Бар хилофи калиди асосӣ, сутуни калиди хориҷӣ метавонад қимати NULL дошта бошад ва қиматҳои такроршавандаро низ дар бар гирад.

Дар ин мисол dept_id-и корманд Julia Reyes ба 1 баробар аст. Ин ба қимати 1 дар калиди асосии dept_id-и ҷадвали departments ишора мекунад. Аз ин мефаҳмем, ки Julia Reyes дар шуъбаи андоз — Tax — воқеъ дар Atlanta кор мекунад.

Эзоҳ: Қиматҳои калиди асосӣ бояд танҳо дар дохили ҳамон ҷадвал беназир бошанд. Аз ин рӯ, истифода шудани рақамҳои якхела дар калидҳои асосии ҷадвалҳои employees ва departments мушкил надорад.

Ҷадвали departments инчунин маҳдудияти UNIQUE дорад. Онро дар бахши «Маҳдудияти UNIQUE»-и боби оянда муфассал меомӯзем. Ба таври кӯтоҳ, ин маҳдудият кафолат медиҳад, ки қиматҳои як сутун ё таркиби қиматҳои чанд сутун беназир бошанд.

Дар ин ҷо талаб мешавад, ки ҳар сатр ҷуфти беназири қиматҳои dept ва city дошта бошад. Ин ба пешгирии такрори додаҳо кумак мекунад. Масалан, ҷадвал наметавонад дар Atlanta ду шуъба бо номи Tax дошта бошад.

Аксар вақт чунин таркибҳои беназирро метавон ҳамчун калиди табиӣ барои калиди асосӣ истифода бурд. Ин мавзуъро низ дар боби оянда баррасӣ мекунем.

Шояд пурсед: ҷудо кардани додаҳо ба чунин қисмҳо чӣ бартарӣ дорад? Биёед бубинем, агар додаҳо ҳамон тавре, ки аввал интизор будед, дар як ҷадвал меомаданд, чӣ мешуд:

Аввалан, вақте додаҳои объектҳои гуногунро дар як ҷадвал ҷойгир мекунед, ногузир баъзе маълумот такрор мешавад. Дар ин ҷо номи шуъба ва ҷойгиршавии он барои ҳар корманд аз нав навишта шудааст. Барои ҷадвали чорсатра ё ҳатто 4 000-сатра ин шояд қобили қабул бошад. Аммо вақте ҷадвал миллионҳо сатр дорад, такрори сатрҳои матнии дароз зиёдатӣ аст ва фазои гаронбаҳои нигоҳдориро беҳуда сарф мекунад.

Дуюм, дар як ҷадвал ҷамъ кардани ҳамаи ин додаҳо идоракунии онҳоро душвор мекунад. Агар шуъбаи Marketing номашро ба Brand Marketing иваз кунад, чӣ мешавад? Ҳар сатри марбут ба он дар ҷадвал бояд нав карда шавад. Агар касе тасодуфан танҳо як қисми сатрҳоро нав кунад, хато ба вуҷуд меояд. Дар модели ҷудошуда иваз кардани номи шуъба осонтар аст: танҳо як сатри ҷадвалро тағйир медиҳем.

Ниҳоят, дар чанд ҷадвал ташкил ё нормализатсия шудани маълумот монеъ намешавад, ки онро якҷо бинем. Ҳамеша метавон бо JOIN сутунҳои ҷадвалҳоро ба ҳам овард.

Акнун, ки асосҳои робитаи байни ҷадвалҳоро медонед, мебинем онҳоро дар дархост чӣ гуна пайваст кардан мумкин аст.

Эзоҳи тарҷумон: Дар матни аслӣ байни ҷадвали намунавии аввал ва додаҳои INSERT номувофиқатӣ вуҷуд дорад: дар ҷадвали аввал Julia ва Janet ба IT / Boston мансубанд, вале дар код ва шарҳи минбаъда ба Tax / Atlanta. Намунаҳо дар тарҷума мувофиқи асл нигоҳ дошта шудаанд.

Дархост аз чанд ҷадвал бо JOIN

Ҳангоми пайваст кардани ҷадвалҳо дар дархост, пойгоҳи додаҳо сатрҳои ҳар ду ҷадвалро дар ҳолате пайваст мекунад, ки қиматҳои сутунҳои интихобшуда ифодаи банди ON-ро ба true табдил диҳанд.

Агар дар дархост талаб карда бошед, натиҷа сутунҳои ҳар ду ҷадвалро дар бар мегирад. Ҳамчунин метавонед сутунҳои ҷадвалҳои пайвастшударо дар банди WHERE барои полоиши натиҷаҳо истифода баред.

Синтаксиси дархостҳои пайвасткунандаи ҷадвалҳо ба дархостҳои одии SELECT монанд аст. Фарқ дар он аст, ки дархост инчунин инҳоро муайян мекунад:

• Ҷадвалҳо ва сутунҳое, ки бояд пайваст шаванд — бо сохтори JOIN ... ON.
• Навъи пайвасткунӣ — бо шаклҳои гуногуни калимаи калидии JOIN.

Аввал сохтори JOIN ... ON ва сипас навъҳои гуногуни пайвасткуниро меомӯзем. Барои пайваст кардани ҷадвалҳои намунавии employees ва departments ва дидани ҳамаи додаҳои алоқаманди онҳо, дархости намунаи 7-2-ро нависед:

SELECT *  
FROM employees JOIN departments  
ON employees.dept_id = departments.dept_id  
ORDER BY employees.dept_id;  

Намунаи 7-2: Пайваст кардани ҷадвалҳои employees ва departments.

Дар ин мисол ситорачаи * дар SELECT ҳамаи сутунҳои ҳамаи ҷадвалҳои дар дархост истифодашударо интихоб мекунад.

Сипас дар банди FROM калимаи калидии JOIN-ро байни номи ду ҷадвале мегузорем, ки мехоҳем пайваст кунем. Ниҳоят, дар банди ON ифодаи санҷиширо муайян мекунем. Барои ҳар ҷадвал номи ҷадвал, нуқта ва номи сутуни дорои қиматҳои калидиро менависем. Байни ду истиноди ҷадвал.сутун аломати баробарӣ меояд.

Вақте дархостро иҷро мекунед, натиҷа ҳамаи қиматҳои ҳар ду ҷадвалро барои сатрҳое дар бар мегирад, ки қиматҳои dept_id-и онҳо мувофиқанд. Ҳатто сутуни dept_id ду бор пайдо мешавад, зеро ҳамаи сутунҳои ҳар ду ҷадвалро интихоб кардаед:

Ҳамин тавр, гарчанде додаҳо дар ду ҷадвали ҷудогона нигоҳ дошта мешаванд ва ҳар кадом маҷмӯи муайяни сутунҳоро дорад, метавонед бо дархост маълумоти алоқаманди онҳоро дубора якҷо кунед.

Дар бахши «Интихоби сутунҳои мушаххас ҳангоми пайвасткунӣ» нишон медиҳам, ки аз ҳар ду ҷадвал танҳо сутунҳои лозимаро чӣ гуна гирифтан мумкин аст.

Фаҳмидани навъҳои JOIN

Дар SQL чанд роҳи пайваст кардани ҷадвалҳо вуҷуд дорад. Навъи интихобкардаатон аз он вобаста аст, ки додаҳоро чӣ гуна гирифтан мехоҳед.

Ҳангоми баррасии навъҳо тасаввур кунед, ки ду ҷадвал паҳлуи ҳаманд: яке дар тарафи чапи калимаи JOIN, дигаре дар тарафи рост.

Биёед кори ин пайвасткуниҳоро бо додаҳо бинем. Фарз мекунем ду ҷадвали одӣ дорем, ки номи мактабҳои як ҳавзаи маорифро барои банақшагирии қабули хонандагон дар оянда нигоҳ медоранд: district_2020 ва district_2035.

Дар district_2020 чор сатр ҳаст:

Дар district_2035 панҷ сатр ҳаст:

Аҳаммият диҳед, ки ҳавза бо гузашти вақт тағйиротро интизор аст. Танҳо мактабҳои дорои id-ҳои 1, 2 ва 6 дар ҳар ду ҷадвал вуҷуд доранд. Мактабҳои дигар танҳо дар яке аз онҳо ҳастанд.

Чунин ҳолат маъмул аст. Яке аз вазифаҳои аввалини таҳлилгари додаҳо — махсусан ҳангоми кор бо ҷадвалҳои хеле калонтар — истифодаи SQL барои муайян кардани мактабҳоест, ки дар ҳар ду ҷадвал мавҷуданд. Навъҳои гуногуни пайвасткунӣ ба ёфтани ин мактабҳо ва ҷузъиёти дигар кумак мекунанд.

Боз дар пойгоҳи додаҳои analysis коди намунаи 7-3-ро иҷро кунед, то ин ду ҷадвал сохта ва бо додаҳо пур карда шаванд:

CREATE TABLE district_2020 (  
    id integer CONSTRAINT id_key_2020 PRIMARY KEY,  
    school_2020 text  
);

CREATE TABLE district_2035 (  
    id integer CONSTRAINT id_key_2035 PRIMARY KEY,  
    school_2035 text  
);

INSERT INTO district_2020 VALUES  
    (1, 'Oak Street School'),  
    (2, 'Roosevelt High School'),  
    (5, 'Dover Middle School'),  
    (6, 'Webutuck High School');

INSERT INTO district_2035 VALUES  
    (1, 'Oak Street School'),  
    (2, 'Roosevelt High School'),  
    (3, 'Morrison Elementary'),  
    (4, 'Chase Magnet Academy'),  
    (6, 'Webutuck High School');  

Намунаи 7-3: Сохтани ду ҷадвал барои омӯзиши навъҳои JOIN.

Мо ду ҷадвал месозем ва онҳоро пур мекунем. Сохтори фармонҳо бояд то ин ҷо бароятон шинос бошад, вале як ҷузъи нав ҳаст: ба ҳар ҷадвал калиди асосӣ илова мекунем.

Пас аз эълони сутуни id дар ҳар ду ҷадвал, калимаҳои калидии зерин нишон медиҳанд, ки ин сутунҳо калиди асосии ҷадвали худ мешаванд:

CONSTRAINT key_name PRIMARY KEY  

Ин маънои онро дорад, ки сутуни id дар ҳар сатри ҳар ду ҷадвал бояд қимат дошта бошад ва он қимат дар дохили ҷадвали худ беназир бошад.

Ниҳоят, бо фармонҳои шиноси INSERT додаҳоро ба ҷадвалҳо илова мекунем.

JOIN

Вақте мехоҳем танҳо сатрҳои ҳар ду ҷадвалро гирем, ки дар сутунҳои пайвастшаванда қиматҳои мувофиқ доранд, аз JOIN ё INNER JOIN истифода мебарем.

Барои дидани мисол, коди намунаи 7-4-ро иҷро кунед. Он ду ҷадвали навсохтаро пайваст мекунад:

SELECT *  
FROM district_2020 JOIN district_2035  
ON district_2020.id = district_2035.id  
ORDER BY district_2020.id;  

Намунаи 7-4: Истифодаи JOIN.

Мисли усули намунаи 7-2, номи ду ҷадвалро дар ду тарафи калимаи JOIN менависем. Сипас дар банди ON ифодаи пайвасткуниро муайян мекунем. Дар ин маврид, баробарии қиматҳои сутунҳои id-и ҳар ду ҷадвалро месанҷем.

Се id-и мактаб дар ҳар ду ҷадвал вуҷуд доранд. Аз ин рӯ, дархост танҳо се сатри мувофиқро бармегардонад. Мактабҳое, ки танҳо дар яке аз ҷадвалҳо ҳастанд, дар натиҷа пайдо намешаванд.

Ҳамчунин аҳаммият диҳед, ки сутунҳои ҷадвали тарафи чапи JOIN дар тарафи чапи натиҷа нишон дода мешаванд:

JOIN-ро кай бояд истифода бурд? Одатан вақте ки бо маҷмӯаҳои додаҳои хуб сохторёфта ва дуруст нигоҳдоришаванда кор мекунед ва бояд сатрҳоеро ёбед, ки дар ҳамаи ҷадвалҳои пайвастшаванда вуҷуд доранд.

Азбаски JOIN сатрҳои танҳо дар як ҷадвал мавҷудбударо нишон намедиҳад, барои дидани ҳамаи додаҳои як ё чанд ҷадвал аз навъҳои дигари пайвасткунӣ истифода баред.

JOIN бо USING

Агар сутунҳои дар банди ON истифодашаванда дар ҳар ду ҷадвал номи якхела дошта бошанд, метавонед ба ҷойи ON аз USING истифода баред. Ин синтаксиси дархостро содатар мекунад ва сутунҳои такрории натиҷаро кам мекунад:

SELECT *  
FROM district_2020 JOIN district_2035  
USING (id)  
ORDER BY district_2020.id;  

Намунаи 7-5: JOIN бо USING.

Пас аз номи ҷадвалҳои пайвастшаванда, USING ва дар қавс номи сутуни муштараки пайвасткуниро менависем — дар ин ҷо id.

Агар аз рӯйи чанд сутун пайваст кунем, номи онҳоро дар қавс бо вергул ҷудо мекунем.

Дархостро иҷро кунед. Натиҷа бояд чунин бошад:

Аҳаммият диҳед, ки id, ки дар ҳар ду ҷадвал ҳаст ва дар ин пайвасткунӣ қиматҳои якхела дорад, танҳо як бор нишон дода мешавад. Ин шакли кӯтоҳнависии сода ва қулай аст.

LEFT JOIN ва RIGHT JOIN

LEFT JOIN ва RIGHT JOIN ҳамаи сатрҳои яке аз ҷадвалҳоро бармегардонанд. Агар дар ҷадвали дигар сатри дорои қимати мувофиқ бошад, қиматҳои он низ ба натиҷа дохил мешаванд. Дар акси ҳол, сутунҳои он тараф NULL мешаванд.

Аввал кори LEFT JOIN-ро мебинем. Коди намунаи 7-6-ро иҷро кунед:

SELECT *  
FROM district_2020 LEFT JOIN district_2035  
ON district_2020.id = district_2035.id  
ORDER BY district_2020.id;  

Намунаи 7-6: Истифодаи LEFT JOIN.

Натиҷа ҳамаи чор сатри district_2020-ро, ки дар тарафи чапи пайвасткунӣ аст, нишон медиҳад. Ҳамчунин се сатри мувофиқи district_2035-ро меорад.

Азбаски дар сутуни id-и district_2035 қимати 5 вуҷуд надорад, мувофиқате ёфт намешавад. LEFT JOIN сатри ҷадвали чапро нигоҳ медорад ва дар сутунҳои тарафи рост NULL мегузорад. Ҳангоми истифодаи JOIN чунин сатр пурра аз натиҷа хориҷ мешуд.

Сатрҳои district_2035, ки дар district_2020 мувофиқат надоранд, ба натиҷа дохил намешаванд:

Бо RIGHT JOIN рафтори монандро аз тарафи муқобил мебинем:

SELECT *  
FROM district_2020 RIGHT JOIN district_2035  
ON district_2020.id = district_2035.id  
ORDER BY district_2035.id;  

Намунаи 7-7: Истифодаи RIGHT JOIN.

Ин дафъа дархост ҳамаи сатрҳои district_2035-ро, ки дар тарафи рост аст, бармегардонад. Аз district_2020 танҳо сатрҳое дохил мешаванд, ки қиматҳои id-и онҳо мувофиқанд.

Сатри district_2020, ки аз рӯйи id дар district_2035 мувофиқат надорад, ба натиҷа дохил намешавад:

Ин навъҳои пайвасткуниро дар ҳолатҳои зерин истифода мебаред:

• Мехоҳед натиҷаи дархост ҳамаи сатрҳои яке аз ҷадвалҳоро дар бар гирад.
• Мехоҳед қиматҳои дар яке аз ҷадвалҳо мавҷуднабударо ёбед. Масалан, ҳангоми муқоисаи додаҳои як навъи объект барои ду давраи гуногун.
• Медонед, ки баъзе сатрҳои ҷадвали пайвастшаванда мувофиқат нахоҳанд дошт.

Мисли JOIN, агар ҷадвалҳо ба шартҳои зарурӣ мувофиқ бошанд, метавонед ON-ро бо USING иваз кунед.

FULL OUTER JOIN

Агар хоҳед ҳамаи сатрҳои ҳар ду ҷадвалро новобаста аз мавҷуд будан ё набудани мувофиқат бинед, аз FULL OUTER JOIN истифода баред:

SELECT *  
FROM district_2020 FULL OUTER JOIN district_2035  
ON district_2020.id = district_2035.id  
ORDER BY district_2020.id;  

Намунаи 7-8: Истифодаи FULL OUTER JOIN.

Натиҷа ҳамаи сатрҳои ҷадвали чапро меорад: барои сатрҳои мувофиқ додаҳои ҳар ду тараф ва барои сатрҳои бе мувофиқат NULL дар тарафи рост нишон дода мешавад. Сипас сатрҳои боқимондаи ҷадвали рост, ки дар ҷадвали чап мувофиқат надоранд, низ дохил мешаванд:

Пайвасткунии пурраи берунӣ нисбат ба пайвасткуниҳои дохилӣ, чап ё рост камтар истифода мешавад. Бо вуҷуди ин, барои баъзе вазифаҳо муфид аст: масалан, пайваст кардани ду манбаи додаҳое, ки қисман маълумоти муштарак доранд, ё дидани он ки ҷадвалҳо то чӣ андоза қиматҳои мувофиқ доранд.

CROSS JOIN

Дар дархости CROSS JOIN ҳар сатри ҷадвали чап бо ҳар сатри ҷадвали рост ҷуфт мешавад. Натиҷа, ки онро ҳосили зарби декартӣ низ меноманд, ҳамаи таркибҳои имконпазири сатрҳоро нишон медиҳад.

Азбаски ин пайвасткунӣ ба ҷустуҷӯи мувофиқат байни сутунҳои калидӣ ниёз надорад, банди ON лозим нест:

SELECT *  
FROM district_2020 CROSS JOIN district_2035  
ORDER BY district_2020.id, district_2035.id;  

Намунаи 7-9: Истифодаи CROSS JOIN.

Натиҷа 20 сатр дорад: чор сатри ҷадвали чап зарби панҷ сатри ҷадвали рост.

Агар нияти танаффуси бисёр дароз барои қаҳванӯшӣ надошта бошед, тавсия медиҳам аз CROSS JOIN дар ҷадвалҳои калон худдорӣ кунед. Ду ҷадвале, ки ҳар кадом 250 000 сабт доранд, натиҷаи 62,5 миллиардсатра месозанд ва ҳатто ба сервери пуриқтидор фишори зиёд меоранд.

Истифодаи амалии муносибтар — тавлиди додаҳо барои рӯйхати санҷишӣ аст. Масалан, сохтани ҳамаи таркибҳои рангҳое, ки мехоҳед барои чанд навъи курта дар мағоза пешниҳод кунед.

Истифодаи NULL барои ёфтани сатрҳои бе мувофиқат

Ҳар боре ҷадвалҳоро пайваст мекунед, хуб аст санҷед, ки қиматҳои калидии як ҷадвал дар ҷадвали дигар вуҷуд доранд ё не ва кадом қиматҳо намерасанд.

Тафовутҳо бо сабабҳои гуногун пайдо мешаванд. Баъзе додаҳо метавонанд бо гузашти вақт тағйир ёбанд. Масалан, ҷадвали маҳсулоти нав эҳтимол кодҳое дошта бошад, ки дар ҷадвали кӯҳна нестанд. Сабабҳои дигар метавонанд хатои воридкунии маълумот ё содироти нопурра аз пойгоҳи додаҳо бошанд. Барои хулосабарории дуруст аз додаҳо донистани ҳамаи ин муҳим аст.

Вақте шумораи сатрҳо кам аст, сатрҳои бе мувофиқатро бо чашм ёфтан осон аст, чунон ки дар мисолҳои қаблӣ кардем. Барои ҷадвалҳои калонтар стратегияи беҳтар лозим мешавад: полоиш кардани натиҷа, то танҳо сатрҳои бе мувофиқат нишон дода шаванд. Барои ин аз NULL истифода мебарем.

Дар SQL, NULL нишондиҳандаи махсусест, ки набудани дода ё номаълум будани қиматро ифода мекунад. Масалан, агар шахс ҳангоми пур кардани варақаи суроға майдони «Ҳарфи аввали номи миёна»-ро холӣ гузорад, ба ҷойи нигоҳ доштани сатри матнии холӣ дар пойгоҳи додаҳо, мо NULL-ро барои нишон додани қимати номаълум истифода мебарем.

Дар хотир доред, ки NULL аз 0 ё сатри матнии холӣ, ки бо ду нохунак — '' — навишта мешавад, фарқ мекунад. Ҳар дуи ин қиматҳо метавонанд маънои муайяне дошта бошанд ва нодуруст тафсир шаванд. Аз ин рӯ, барои нишон додани номаълум будани қимат аз NULL истифода мебарем.

Бар хилофи сифр ё сатри матнии холӣ, NULL-ро дар сутунҳои навъҳои гуногуни додаҳо истифода бурдан мумкин аст.

Вақте пайвасткунии SQL барои яке аз ҷадвалҳо сатри мувофиқ намеёбад, сутунҳои он тараф бо NULL баргардонда мешаванд.

Дар намунаи 7-10 мо ин сатрҳоро бо илова кардани банди WHERE меёбем. Барои сутуни id-и ҷадвали district_2035 шарти IS NULL-ро истифода мебарем. Агар мехостем қиматҳои мавҷудбударо ёбем, аз IS NOT NULL истифода мекардем:

SELECT *  
FROM district_2020 LEFT JOIN district_2035  
ON district_2020.id = district_2035.id  
WHERE district_2035.id IS NULL;  

Намунаи 7-10: Полоиш бо IS NULL барои нишон додани қиматҳои мавҷуднабуда.

Акнун натиҷа танҳо ҳамон як сатри ҷадвали чапро нишон медиҳад, ки дар ҷадвали рост мувофиқат надорад. Ин усулро одатан anti-join, яъне ҷустуҷӯи сатрҳои бе мувофиқат, меноманд.

Баръакс кардани натиҷа низ осон аст: метавонед сатрҳои ҷадвали ростро ёбед, ки дар ҷадвали чап мувофиқат надоранд. Барои ин RIGHT JOIN-ро истифода бурда, шарти WHERE-ро чунин тағйир медиҳед:

WHERE district_2020.id IS NULL  

Эзоҳ: pgAdmin қиматҳои NULL-ро дар ҷадвали натиҷаҳо бо нишонаи [null] нишон медиҳад. Агар аз абзори сатри фармонии psql истифода баред, қиматҳои NULL ба таври пешфарз ҳамчун ҷойҳои холӣ намоиш меёбанд. Барои намоиши онҳо мисли pgAdmin, дар psql фармони зеринро иҷро кунед:

\pset null '[null]'  

Фаҳмидани се навъи робитаи байни ҷадвалҳо

Як қисми илм — ё ба гуфтаи баъзеҳо, ҳунар — дар пайваст кардани ҷадвалҳо ин фаҳмидани он аст, ки тарроҳи пойгоҳи додаҳо робитаи байни ҷадвалҳоро чӣ гуна пешбинӣ кардааст. Ин сохторро модели релятсионии пойгоҳи додаҳо низ меноманд.

Се навъи робитаи байни ҷадвалҳо вуҷуд дорад:

• Як-ба-як.
• Як-ба-бисёр.
• Бисёр-ба-бисёр.

Робитаи як-ба-як

Дар мисоли JOIN-и намунаи 7-4, дар ҳеҷ кадом ҷадвал қиматҳои такрории id вуҷуд надоранд: дар district_2020 танҳо як сатр id = 1 дорад ва дар district_2035 низ танҳо як сатр чунин id дорад.

Яъне ҳар id-и яке аз ҷадвалҳо дар ҷадвали дигар бештар аз як мувофиқат пайдо намекунад. Дар истилоҳоти пойгоҳи додаҳо ин робитаи як-ба-як ном дорад.

Мисоли дигар: пайваст кардани ду ҷадвали додаҳои барӯйхатгирии аҳолӣ аз рӯйи иёлатҳо. Яке метавонад маълумоти даромади хонаводаҳо ва дигаре маълумоти сатҳи таҳсилотро дошта бошад.

Ҳар ду ҷадвал 51 сатр доранд: як сатр барои ҳар иёлат ва як сатр барои Вашингтон, ҳавзаи Колумбия. Агар онҳоро аз рӯйи калиде, ба монанди номи иёлат, ихтисораи номи он ё коди стандартии ҷуғрофӣ пайваст кунем, барои ҳар қимати калидӣ дар ҳар ҷадвал танҳо як мувофиқат пайдо мешавад.

Робитаи як-ба-бисёр

Дар робитаи як-ба-бисёр, як қимати калидӣ дар як ҷадвал метавонад дар сутуни пайвастшавандаи ҷадвали дигар чанд мувофиқат дошта бошад.

Пойгоҳи додаҳои автомобилҳоро тасаввур кунед. Як ҷадвал маълумоти истеҳсолкунандагонро нигоҳ медорад: барои Ford, Honda, Tesla ва ғайра яксатрӣ.

Ҷадвали дуюм номи моделҳо — Mustang, Civic, Model 3, Accord ва ғайраро дар бар мегирад. Дар ин ҷадвал чанд сатр метавонад ба як сатри ҷадвали истеҳсолкунандагон мувофиқат кунад.

Робитаи бисёр-ба-бисёр

Робитаи бисёр-ба-бисёр вақте вуҷуд дорад, ки чанд объекти як ҷадвал метавонанд бо чанд объекти ҷадвали дигар алоқаманд бошанд ва баръакс.

Масалан, дар лигаи бейсбол ҳар бозигар метавонад дар чанд мавқеъ бозӣ кунад ва дар ҳар мавқеъ чанд бозигар метавонанд бозӣ кунанд.

Аз сабаби чунин сохтор, робитаи бисёр-ба-бисёр одатан тавассути ҷадвали сеюм — ҷадвали миёнарав — амалӣ мешавад.

Дар мисоли бейсбол пойгоҳи додаҳо метавонад инҳоро дошта бошад:

• Ҷадвали players барои бозигарон.
• Ҷадвали positions барои мавқеъҳо.
• Ҷадвали players_positions бо ду сутуни пайвасткунанда: id аз players ва id аз positions.

Фаҳмидани ин робитаҳо зарур аст, зеро ба мо имкон медиҳад муайян кунем, ки натиҷаи дархостҳо сохтори пойгоҳи додаҳоро дуруст инъикос мекунад ё не.

Интихоби сутунҳои мушаххас ҳангоми пайвасткунӣ

То ин ҷо мо ситорачаи *-ро барои интихоби ҳамаи сутунҳои ҳар ду ҷадвал истифода бурдем. Барои санҷиши фаврии додаҳо ин муносиб аст, аммо бештар вақт мехоҳед танҳо қисми муайяни сутунҳоро интихоб кунед.

Бо ин роҳ диққатро ба додаҳои лозима равона мекунед. Ҳамчунин агар касе ба ҷадвал сутуни нав илова кунад, натиҷаи дархости шумо нохоста тағйир намеёбад.

Тавре дар дархостҳои якҷадвала омӯхтед, барои интихоби сутунҳои мушаххас пас аз SELECT номи сутунҳои дилхоҳро менависед.

Ҳангоми пайваст кардани ҷадвалҳо беҳтар аст ҳамроҳи номи сутун номи ҷадвалро низ нишон диҳед. Сабаб он аст, ки чанд ҷадвал метавонанд сутунҳои ҳамном дошта бошанд — дар мисолҳои то ин ҷо дидаамон низ ҳамин тавр аст.

Дархости зерин кӯшиш мекунад сутуни id-ро бе нишон додани номи ҷадвал гирад:

SELECT id  
FROM district_2020 LEFT JOIN district_2035  
ON district_2020.id = district_2035.id;  

Азбаски id ҳам дар district_2020 ва ҳам дар district_2035 вуҷуд дорад, сервер хато медиҳад. Паёми хато чунин аст:

column reference "id" is ambiguous  

Яъне «истинод ба сутуни id номуайян аст». Маълум нест, ки кадом id дар назар дошта шудааст.

Барои ислоҳи хато бояд пеш аз номи сутун номи ҷадвалро илова кунем, ҳамон тавре ки дар банди ON мекунем.

Дар намунаи 7-11 муайян мекунем, ки сутуни id-ро аз district_2020 мехоҳем. Ҳамчунин номи мактабҳоро аз ҳар ду ҷадвал мегирем:

SELECT district_2020.id,  
       district_2020.school_2020,  
       district_2035.school_2035  
FROM district_2020 LEFT JOIN district_2035  
ON district_2020.id = district_2035.id  
ORDER BY district_2020.id;  

Намунаи 7-11: Интихоби сутунҳои мушаххас дар пайвасткунӣ.

Мо танҳо пеш аз номи ҳар сутун номи ҷадвали манбаи онро менависем. Қисми дигари синтаксиси дархост ҳамон аст.

Натиҷа сутунҳои дархостшударо аз ҳар ҷадвал меорад:

Ҳамчунин метавонем калимаи калидии AS-ро, ки қаблан бо додаҳои барӯйхатгирӣ истифода карда будем, илова кунем. Бо ин роҳ дар натиҷа равшан мешавад, ки id аз district_2020 гирифта шудааст:

SELECT district_2020.id AS d20_id, ...  

Дар натиҷа номи сутуни id-и ҷадвали district_2020 ҳамчун d20_id нишон дода мешавад.

Сода кардани синтаксиси JOIN бо номҳои кӯтоҳи ҷадвалҳо

Нишон додани номи ҷадвал барои сутун душвор нест, вале такрори номи дарози ҷадвал барои чандин сутун кодро серҳаҷм ва хонданашро душвор мекунад.

Яке аз беҳтарин роҳҳои кумак ба ҳамкорон навиштани коди хонданбоб аст. Онҳо набояд маҷбур шаванд номи як ҷадвалро, ки барои 25 сутун такрор шудааст, пайваста бихонанд!

Барои кӯтоҳтар навиштани код метавон аз номҳои мустаори ҷадвалҳо ё table aliases истифода бурд.

Барои сохтани номи мустаор ҳангоми эълони ҷадвал дар банди FROM, пас аз номи он як ё ду аломат мегузорем. Номи мустаор метавонад дарозтар ҳам бошад, вале агар мақсад содатар кардани код бошад, аз ҳад нагузаред.

Баъдан дар ҳар ҷойи код, ки ба он ҷадвал муроҷиат мекунем, метавонем номи мустаорро ба ҷойи номи пурра истифода барем:

SELECT d20.id,  
       d20.school_2020,  
       d35.school_2035  
FROM district_2020 AS d20 LEFT JOIN district_2035 AS d35  
ON d20.id = d35.id  
ORDER BY d20.id;  

Намунаи 7-12: Сода кардани код бо номҳои мустаори ҷадвалҳо.

Дар банди FROM бо истифода аз AS номи мустаори d20-ро барои district_2020 ва d35-ро барои district_2035 муайян мекунем.

Ҳар ду ном аз номи пурраи ҷадвал кӯтоҳтаранд, вале ҳанӯз маънояшон фаҳмост. Пас аз муайян кардани онҳо, дар тамоми қисмҳои дигари код метавонем номҳои мустаорро истифода барем. Дар натиҷа SQL-и мо фавран кӯтоҳтар мешавад.

Аҳаммият диҳед, ки дар ин маврид калимаи AS ихтиёрӣ аст: ҳангоми муайян кардани номи мустаори ҷадвал ё сутун метавонед онро нанависед.

Пайваст кардани чанд ҷадвал

Албатта, пайвасткуниҳо дар SQL бо ду ҷадвал маҳдуд нестанд. То вақте ки сутунҳои дорои қиматҳои мувофиқ дорем, метавонем ба дархост ҷадвалҳои дигар илова кунем.

Фарз мекунем боз ду ҷадвали марбут ба мактабҳоро гирифтаем ва мехоҳем онҳоро бо district_2020 пайваст кунем, то натиҷа аз се ҷадвал таркиб ёбад.

Ҷадвали district_2020_enrollment шумораи хонандагони ҳар мактабро дорад:

Ҷадвали district_2020_grades синфҳои дар ҳар бино ҷойгиршударо нишон медиҳад:

Барои навиштани дархост аз намунаи 7-13 истифода мебарем: ҷадвалҳоро месозем, додаҳоро бор мекунем ва онҳоро бо district_2020 пайваст мекунем.

CREATE TABLE district_2020_enrollment (  
    id integer,  
    enrollment integer  
);

CREATE TABLE district_2020_grades (  
    id integer,  
    grades varchar(10)  
);

INSERT INTO district_2020_enrollment  
VALUES  
    (1, 360),  
    (2, 1001),  
    (5, 450),  
    (6, 927);

INSERT INTO district_2020_grades  
VALUES  
    (1, 'K-3'),  
    (2, '9-12'),  
    (5, '6-8'),  
    (6, '9-12');

SELECT d20.id,  
       d20.school_2020,  
       en.enrollment,  
       gr.grades  
FROM district_2020 AS d20 JOIN district_2020_enrollment AS en  
    ON d20.id = en.id  
JOIN district_2020_grades AS gr  
    ON d20.id = gr.id  
ORDER BY d20.id;  

Намунаи 7-13: Пайваст кардани чанд ҷадвал.

Пас аз иҷрои қисмҳои CREATE TABLE ва INSERT, ду ҷадвали нави district_2020_enrollment ва district_2020_grades дорем. Сабтҳои ҳар кадоми онҳо бо district_2020, ки пештар сохта будем, алоқаманданд. Сипас ҳар се ҷадвалро пайваст мекунем.

Дар дархости SELECT, аввал district_2020-ро бо district_2020_enrollment тавассути сутунҳои id пайваст мекунем. Барои кӯтоҳ нигоҳ доштани код номҳои мустаор низ муайян мекунем.

Баъдан дархост district_2020-ро бо district_2020_grades, боз аз рӯйи сутунҳои id, пайваст мекунад.

Акнун натиҷаи мо сутунҳои ҳар се ҷадвалро дар бар мегирад:

Агар лозим бошад, бо пайвасткуниҳои иловагӣ ҷадвалҳои бештарро низ ба дархост дохил карда метавонед. Вобаста ба робитаи ҷадвалҳо, метавонед аз рӯйи сутунҳои гуногун пайваст кунед.

Дар худи SQL барои шумораи ҷадвалҳои дар як дархост пайвастшаванда ҳадди ягонаи қатъӣ муқаррар нашудааст, вале баъзе низомҳои пойгоҳи додаҳо метавонанд маҳдудият дошта бошанд. Ҳуҷҷатҳои низоми истифодашавандаро санҷед.

Якҷо кардани натиҷаҳои дархостҳо бо операторҳои маҷмӯӣ

Баъзан лозим мешавад додаҳоро тавре аз нав ташкил кунем, ки сутунҳои ҷадвалҳои гуногун мисли натиҷаи JOIN паҳлуи ҳам наоянд, балки сатрҳои онҳо дар як натиҷаи умумӣ ҷамъ шаванд.

Масалан, чунин шакл метавонад барои тасвирсозии додаҳо бо JavaScript ё таҳлил бо китобхонаҳои забонҳои R ва Python талаб шавад.

Яке аз роҳҳои чунин коркарди додаҳо истифодаи операторҳои маҷмӯии стандарти ANSI SQL аст: UNION, INTERSECT ва EXCEPT.

Ин операторҳо натиҷаҳои чанд дархости SELECT-ро якҷо мекунанд:

Барои ҳар яке аз ин операторҳо, ду дархост бояд шумораи баробари сутунҳоро баргардонанд ва навъҳои додаҳои сутунҳои ҳаммавқеъ бо ҳам созгор бошанд.

Барои мисолҳои кӯтоҳи тарзи кори онҳо истифодаи ҷадвалҳои мактабҳоро идома медиҳем.

UNION ва UNION ALL

Дар намунаи 7-14 аз UNION истифода мебарем, то натиҷаҳои дархостҳои ҳамаи сатрҳои district_2020 ва district_2035-ро якҷо кунем:

SELECT * FROM district_2020  
UNION  
SELECT * FROM district_2035  
ORDER BY id;  

Намунаи 7-14: Якҷо кардани натиҷаҳои дархостҳо бо UNION.

Дархост аз ду фармони пурраи SELECT иборат аст, ки байни онҳо калимаи калидии UNION ҷойгир шудааст.

ORDER BY id пас аз амали маҷмӯӣ иҷро мешавад ва дар ин шакли дархост барои натиҷаи умумӣ навишта мешавад.

Аз кор бо ин додаҳо аллакай медонед, ки дар натиҷаи ҳар ду дархост чанд сатри якхела вуҷуд хоҳад дошт. Аммо ҳангоми якҷо кардан бо UNION сатрҳои такрорӣ бартараф мешаванд:

Аҳаммият диҳед, ки номи ҳамаи мактабҳо дар сутуни school_2020 нишон дода шудааст. Ин номи сутун аз натиҷаи дархости аввал гирифта шудааст.

Номи мактабҳо аз сутуни school_2035-и ҷадвали district_2035 танҳо ба натиҷаи дархости аввал илова шуданд. Аз ин рӯ, шумораи сутунҳои дархости дуюм бояд ба якум мувофиқ бошад ва навъҳои додаҳои сутунҳои ҳаммавқеъ бо ҳам созгор бошанд.

Агар хоҳем натиҷа сатрҳои такрориро низ дар бар гирад, ба ҷойи UNION аз UNION ALL истифода мебарем:

SELECT * FROM district_2020  
UNION ALL  
SELECT * FROM district_2035  
ORDER BY id;  

Намунаи 7-15: Якҷо кардани натиҷаҳои дархостҳо бо UNION ALL.

Ин ҳамаи сатрҳоро, аз ҷумла такрорҳоро, медиҳад:

Ниҳоят, бисёр вақт мувофиқи ниёз тағйир додани натиҷаи якҷошуда муфид аст. Масалан, мехоҳед бидонед, ки қиматҳои ҳар сатр аз кадом ҷадвал омадаанд ё мехоҳед баъзе сутунҳоро дохил ё хориҷ кунед.

Намунаи 7-16 як мисолро бо UNION ALL нишон медиҳад:

SELECT '2020' AS year,  
       school_2020 AS school  
FROM district_2020

UNION ALL

SELECT '2035' AS year,  
       school_2035  
FROM district_2035  
ORDER BY school, year;  

Намунаи 7-16: Мутобиқ кардани дархости UNION.

Дар SELECT-и дархости аввал сатри матнии '2020'-ро ҳамчун қимати сутуни year муайян мекунем. Дар дархости дуюм низ ҳамин корро бо '2035' мекунем.

Сипас номи сутуни school_2020-ро ба school иваз мекунем, зеро он мактабҳои ҳар ду солро нишон медиҳад.

Дархостро иҷро кунед:

Акнун дархост барои ҳар мактаб солро нишон медиҳад. Масалан, мебинем, ки сатри Dover Middle School аз натиҷаи дархости ҷадвали district_2020 омадааст.

INTERSECT ва EXCEPT

Акнун, ки истифодаи UNION-ро медонед, метавонед ҳамин мафҳумҳоро ба INTERSECT ва EXCEPT татбиқ кунед.

Намунаи 7-17 ҳар дуро нишон медиҳад. Онҳоро ҷудогона иҷро кунед, то фарқи натиҷаҳоро бинед:

SELECT * FROM district_2020  
INTERSECT  
SELECT * FROM district_2035  
ORDER BY id;

SELECT * FROM district_2020  
EXCEPT  
SELECT * FROM district_2035  
ORDER BY id;  

Намунаи 7-17: Якҷо кардани натиҷаҳои дархостҳо бо INTERSECT ва EXCEPT.

Дархости дорои INTERSECT танҳо сатрҳоеро бармегардонад, ки дар натиҷаи ҳар ду дархост мавҷуданд. Такрорҳо бартараф мешаванд:

Дархости дорои EXCEPT сатрҳоеро бармегардонад, ки дар натиҷаи дархости аввал ҳастанд, вале дар натиҷаи дуюм нестанд. Агар такрорҳо бошанд, онҳо низ бартараф мешаванд:

Дар баробари UNION, операторҳои INTERSECT ва EXCEPT барои тартиб додан ва таҳқиқи додаҳо имкониятҳои зиёде медиҳанд.

Акнун кӯтоҳ ба пайвасткуниҳо бармегардем, то бубинем бо ададҳои ҷадвалҳои гуногун чӣ гуна ҳисоб кардан мумкин аст.

Иҷрои ҳисобҳои математикӣ бо сутунҳои ҷадвалҳои пайвастшуда

Функсияҳои математикиеро, ки дар боби 6 омӯхтем, ҳангоми кор бо ҷадвалҳои пайвастшуда низ истифода бурдан мумкин аст.

Ҳангоми муроҷиат ба сутун дар амали ҳисобӣ, бояд номи ҷадвалро низ нишон диҳем, ҳамон тавре ки ҳангоми интихоби сутунҳо мекардем.

Агар бо додаҳое кор кунед, ки мунтазам нусхаи нав мегиранд, ин усул барои пайваст кардани ҷадвали нав ба ҷадвали кӯҳна ва таҳқиқи тағйироти қиматҳо муфид аст.

Ман ва бисёр рӯзноманигорон ҳар боре маҷмӯи нави додаҳои барӯйхатгирӣ нашр мешавад, маҳз ҳамин корро мекунем. Додаҳои навро бор мекунем ва мекӯшем тамоюлҳои афзоиш ё коҳиши аҳолӣ, даромад, таҳсилот ва нишондиҳандаҳои дигарро пайдо кунем.

Барои дидани тарзи иҷрои ин кор ба ҷадвали districts, ки дар боби 6 сохта будем, бармегардем. Шумораи аҳолии ҳамон ноҳияҳоро барои соли 2020 ба ҷадвали нав ворид мекунем ва ду ҷадвалро пайваст мекунем.

Коди намунаи 7-18-ро иҷро кунед:

CREATE TABLE districts_pop_2020 (  
    district_name text,  
    region_name text,  
    pop_2020 integer,  
    CONSTRAINT districts_pop_2020_key PRIMARY KEY (district_name, region_name)  
);

INSERT INTO districts_pop_2020  
VALUES  
    ('Боғистон',  'Шимол',  46920),  
    ('Кӯҳсор',    'Шимол',  22310),  
    ('Обшор',     'Шимол',  31480),  
    ('Дарёбод',   'Марказ', 64150),  
    ('Сабзазор',  'Марказ', 28760),  
    ('Шаҳри Нав', 'Марказ', 801200),  
    ('Ширинкӯл',  'Ҷануб',  17890),  
    ('Регзор',    'Ҷануб',  40230),  
    ('Санглох',   'Ҷануб',  16420),  
    ('Гулдара',   'Ҷануб',  52610),  
    ('Тозаоб',    'Шимол',  23980),  
    ('Мевазор',   'Марказ', 37040);

SELECT d2024.district_name,  
       d2024.region_name,  
       d2024.pop_2024,  
       d2020.pop_2020,  
       d2024.pop_2024 - d2020.pop_2020 AS raw_change,  
       round(  
           (d2024.pop_2024::numeric - d2020.pop_2020)  
           / d2020.pop_2020 * 100,  
           1  
       ) AS pct_change  
FROM districts AS d2024  
JOIN districts_pop_2020 AS d2020  
ON d2024.district_name = d2020.district_name  
   AND d2024.region_name = d2020.region_name  
ORDER BY pct_change DESC;  

Намунаи 7-18: Иҷрои ҳисобҳо бо ҷадвалҳои пайвастшудаи аҳолӣ.

Дар ин код мо аз асосҳои қаблан омӯхта истифода мебарем. Фармони шиноси CREATE TABLE ҷадвали districts_pop_2020-ро бо се сутун месозад: номи ноҳия, номи минтақа ва шумораи аҳолӣ дар охири соли 2020. Калиди асосии ҷадвал аз ду сутун иборат аст — ин навъи калидро дар боби 8 муфассал меомӯзем. Фармони INSERT барои ҳамаи 12 ноҳия сатр илова мекунад.

Акнун, ки шумораи аҳолиро барои солҳои 2020 ва 2024 дорем, метавонем фоизи тағйироти аҳолии ҳар ноҳияро байни ин солҳо ҳисоб кунем. Кадом ноҳияҳо аз рӯйи афзоиш пешсаф буданд? Дар кадом ноҳияҳо шумораи аҳолӣ коҳиш ёфтааст?

Барои ҷавоб аз формулаи тағйироти фоизӣ, ки дар боби 6 истифода бурдем, кор мегирем.

Фармони SELECT номи ноҳия ва минтақаро аз ҷадвали districts мегирад. Барои он номи мустаори d2024 муайян шудааст.

Сипас сутунҳои шумораи аҳолии солҳои 2024 ва 2020 меоянд.

Барои гирифтани тағйироти мутлақи аҳолӣ, шумораи соли 2020-ро аз шумораи соли 2024 тарҳ мекунем:

d2024.pop_2024 - d2020.pop_2020  

Барои ҳисоб кардани тағйироти фоизӣ аз формулаи зерин истифода мебарем ва натиҷаро то як рақам пас аз вергул гирд мекунем:

round(  
    (d2024.pop_2024::numeric - d2020.pop_2020)  
    / d2020.pop_2020 * 100,  
    1  
)  

Ҷадвалҳоро бо мувофиқ кардани қиматҳои ду сутун пайваст мекунем: district_name ва region_name.

Сабаби истифодаи ду сутун ба ҷойи як сутун ин аст, ки дар кишвар метавонанд ду ноҳияи ҳамном дар минтақаҳои гуногун бошанд. Танҳо таркиби номи ноҳия ва номи минтақа ноҳияи мушаххасро ба таври ягона муайян мекунад.

Ду шартро бо оператори мантиқии AND якҷо мекунем:

ON d2024.district_name = d2020.district_name  
   AND d2024.region_name = d2020.region_name  

Бо ин синтаксис сатрҳо танҳо вақте пайваст мешаванд, ки ҳар ду шарт иҷро шаванд.

Ниҳоят, натиҷаҳоро аз рӯйи тағйироти фоизӣ бо тартиби камшавӣ ҷобаҷо мекунем, то ноҳияҳои дорои афзоиши баландтарин дар боло бошанд:

ORDER BY pct_change DESC;  

Натиҷа чунин аст:

Ба ду сатри аввал диққат диҳед. Аз рӯйи шумораи мутлақ Шаҳри Нав беш аз ҳама афзудааст — 137 200 нафар. Аммо аз рӯйи фоиз Дарёбод пешсаф аст: аҳолии он дар чор сол 22,2 фоиз зиёд шудааст. Дар ду ноҳияи кӯҳӣ — Кӯҳсор ва Санглох — аҳолӣ коҳиш ёфтааст. Агар ба ҷадвали districts баргардед, сабабашро мебинед: муҳоҷирати софи онҳо манфӣ аст.

Ҳамин тавр, тағйироти мутлақ ва фоизӣ ба як савол ҷавобҳои гуногун медиҳанд — дар таҳлил ҳар дуро бинед.

Хулоса

Азбаски робитаи байни ҷадвалҳо асоси сохтори пойгоҳи додаҳоро ташкил медиҳад, омӯхтани пайвасткунии ҷадвалҳо дар дархостҳо имкон медиҳад бо бисёр маҷмӯаҳои мураккаби додаҳо кор кунед.

Озмудани навъҳои гуногуни пайвасткунӣ метавонад дар бораи тарзи ҷамъоварии додаҳо маълумоти зиёд диҳад ва мушкилоти сифати онҳоро ошкор кунад. Ҳангоми шиносоӣ бо маҷмӯи нави додаҳо, санҷидани пайвасткуниҳои гуногунро ба як амали доимӣ табдил диҳед.

Дар бобҳои оянда ин мафҳумҳои асосиро идома дода, ба ҷустуҷӯи маълумот дар маҷмӯаҳои додаҳо амиқтар ворид мешавем. Ҳамчунин нозукиҳои кор бо навъҳои додаҳо ва роҳҳои таъмини сифати онҳоро меомӯзем.

Аммо пеш аз он, як унсури асосии дигарро баррасӣ мекунем: истифодаи усулҳои беҳтарин барои сохтани пойгоҳҳои додаҳои боэътимод ва босуръат бо SQL.

Худатон бисанҷед

1. Бо LEFT JOIN санҷед, ки оё дар districts ноҳияе ҳаст, ки дар districts_pop_2020 мувофиқ надорад.
2. Ноҳияҳоеро ёбед, ки аҳолии онҳо аз соли 2020 то 2024 кам шудааст.
3. Номҳои минтақаҳоро аз ҳар ду ҷадвал бо UNION ва баъд бо UNION ALL якҷо кунед. Чаро шумораи сатрҳо фарқ мекунад?

Ҷавобҳо дар замима, бахши «Ҷавобҳо ба машқҳо», оварда шудаанд.