Боби 6. Математика ва омори ибтидоӣ бо SQL

Агар додаҳои шумо яке аз навъҳои ададиеро дар бар гиранд, ки дар боби 5 омӯхтем — ададҳои бутун, даҳӣ ё нуқташиновар — дер ё зуд ҳангоми таҳлил ба ҳисобу китоб ниёз пайдо мекунед. Шояд хоҳед миёнаи ҳамаи маблағҳои долларии як сутунро муайян кунед ё қиматҳои ду сутунро ҷамъ карда, барои ҳар сатр маблағи умумиро ҳосил намоед. SQL ин ва бисёр ҳисобҳои дигарро, аз математикаи одӣ то омори пешрафта, иҷро карда метавонад.

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

Фаҳмидани операторҳо ва функсияҳои математикӣ

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

Ҷадвали 6-1 нӯҳ оператори математикиеро нишон медиҳад, ки ҳангоми ҳисобу китоб бештар истифода мебаред. Чор оператори аввал — ҷамъ, тарҳ, зарб ва тақсим — ба стандарти ANSI SQL дохил мешаванд ва дар ҳамаи низомҳои пойгоҳи додаҳо татбиқ шудаанд.

Операторҳои боқимонда махсуси PostgreSQL мебошанд, ҳарчанд аксари низомҳои дигари идоракунии пойгоҳи додаҳо низ эҳтимол барои иҷрои ҳамин амалҳо функсия ё оператор доранд. Масалан, оператори бақияи тақсим — % — ҳам дар PostgreSQL ва ҳам дар Microsoft SQL Server ва MySQL кор мекунад. Агар аз низоми дигар истифода баред, ҳуҷҷатҳои онро санҷед.

Ҷадвали 6-1: Операторҳои асосии математикӣ

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

Дастурҳоро дар psql ё дар абзори дархости pgAdmin якто-якто иҷро кунед.

Фаҳмидани ҳисобу китоб ва навъҳои додаҳо

Ҳангоми иҷрои мисолҳо ба навъи додаҳои ҳар натиҷа диққат диҳед. pgAdmin онро дар ҷадвали натиҷаҳо, зери номи ҳар сутун, нишон медиҳад. Дар psql навъро бо функсияи pg_typeof() дидан мумкин аст: масалан, SELECT pg_typeof(11 / 6); натиҷаи integer-ро медиҳад.

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

• Амал байни ду адади бутун натиҷаи бутун медиҳад.
• Агар дар як ё ҳар ду тарафи оператор адади навъи numeric бошад, натиҷа numeric мешавад.
• Ҳангоми иштирок кардани адади нуқташиновар, натиҷа метавонад навъи нуқташиновари double precision дошта бошад.

Аммо операторҳо ва функсияҳои дараҷа, реша ва факториал қоидаҳои алоҳида доранд. Онҳо метавонанд ҳатто барои адади бутуни воридшуда натиҷаи навъи numeric ё нуқташиновар баргардонанд.

Баъзан навъи натиҷа ба талаботи шумо мувофиқ меояд. Дар ҳолатҳои дигар мумкин аст онро бо CAST тағйир диҳед, чунон ки дар бахши «Табдил додани қиматҳо аз як навъ ба навъи дигар бо CAST» дар боби 5 баррасӣ кардем. Масалан, шояд натиҷаро ба функсияе диҳед, ки танҳо навъи муайяни додаҳоро қабул мекунад. Дар давоми китоб чунин ҳолатҳоро нишон медиҳам.

Эзоҳ: PostgreSQL аргументҳое, ки операторҳо қабул мекунанд, функсияҳои дохилии даъватшаванда ва навъҳои натиҷаро дар ҷадвале бо номи pg_operator муайян мекунад. Масалан, оператори + барои қабул кардани ададҳои бутун як таъриф дорад, барои numeric таърифи дигар ва ғайра.

Ҷамъ, тарҳ ва зарб

Аз ҷамъ, тарҳ ва зарби содаи ададҳои бутун оғоз мекунем. Намунаи 6-1 се мисолро нишон медиҳад. Дар ҳар кадом пас аз калимаи калидии SELECT ифодаи математикӣ меояд.

Аз боби 4 инҷониб мо SELECT-ро барои вазифаи асосиаш — гирифтани додаҳо аз ҷадвал — истифода мекардем. Аммо дар PostgreSQL, Microsoft SQL Server, MySQL ва баъзе низомҳои дигар метавонед номи ҷадвалро нанависед ва амалҳои содаи математикӣ ё кор бо сатрҳоро иҷро кунед.

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

SELECT 2 + 2;  
SELECT 9 - 1;  
SELECT 3 * 4;  

Намунаи 6-1: Ҷамъ, тарҳ ва зарби одӣ бо SQL

Ин дастурҳо ҳеҷ мураккабӣ надоранд. Аз ин рӯ, тааҷҷубовар нест, ки иҷрои SELECT 2 + 2; дар Query Tool натиҷаи 4 медиҳад. Мисолҳои тарҳ ва зарб низ натиҷаҳои интизорӣ — 8 ва 12 — медиҳанд.

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

?column?  
--------  
       4  

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

SELECT 3 * 4 AS result;  

Иҷрои тақсим ва ёфтани бақияи он

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

Барои равшан кардани масъала намунаи 6-2 чор мисолро нишон медиҳад.

SELECT 11 / 6;

SELECT 11 % 6;

SELECT 11.0 / 6;

SELECT CAST(11 AS numeric(3,1)) / 6;  

Намунаи 6-2: Тақсими ададҳои бутун ва даҳӣ бо SQL

Дар дастури аввал оператори / адади бутуни 11-ро ба адади бутуни 6 тақсим мекунад. Агар дар зеҳн ҳисоб кунед, медонед, ки ҳосил 1 ва бақия 5 аст.

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

Агар хоҳед бақияро ҳамчун адади бутун гиред, ҳамин ҳисобро бо оператори % иҷро кунед, чунон ки дар дастури дуюм мебинем. Он танҳо бақияро бармегардонад, ки дар ин ҷо 5 аст.

Дар ин мисолҳо барои гирифтани ҳосили бутуни тақсим ва бақия ду ифодаи алоҳида истифода мекунем.

Бақияи тақсим танҳо барои ёфтани бақия муфид нест. Онро ҳамчун шарти санҷиш низ истифода бурдан мумкин аст. Масалан, барои муайян кардани ҷуфт будани адад амали % 2-ро иҷро кунед. Агар натиҷа 0 бошад, адад ҷуфт аст.

Ду роҳ вуҷуд дорад, ки ҳангоми тақсими ду адад натиҷаи навъи numeric гирем.

Роҳи аввал: як ё ҳар ду ададро ҳамчун numeric пешниҳод кунед. Дар ин ҳолат натиҷа низ ба таври пешфарз numeric мешавад. Дар дастури сеюм ҳамин ҳолат рӯй медиҳад:

SELECT 11.0 / 6;  

Натиҷа тақрибан чунин аст:

1.83333  

Шумораи рақамҳои намоён пас аз нуқтаи даҳӣ метавонад вобаста ба танзимоти PostgreSQL ва низоми шумо фарқ кунад.

Роҳи дуюм: агар додаҳо ҳамчун ададҳои бутун нигоҳ дошта шуда бошанд, яке аз онҳоро бо CAST ба numeric табдил диҳед:

SELECT CAST(11 AS numeric(3,1)) / 6;  

Ин дархост низ натиҷаи тақрибан 1.83333 медиҳад.

Истифодаи дараҷаҳо, решаҳо ва факториалҳо

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

Намунаи 6-3 ин амалҳоро нишон медиҳад.

SELECT 3 ^ 4;

SELECT |/ 10;  
SELECT sqrt(10);

SELECT ||/ 10;

SELECT factorial(4);  

Намунаи 6-3: Дараҷаҳо, решаҳо ва факториалҳо бо SQL

Оператори дараҷа — ^ — имкон медиҳад адади асосро ба дараҷаи муайян бардоред. Масалан:

SELECT 3 ^ 4;  

Яъне «се ба дараҷаи чор». Натиҷа 81 мешавад.

Решаи квадратии ададро бо ду роҳ ёфтан мумкин аст:

SELECT |/ 10;  

ё:

SELECT sqrt(10);  

Барои решаи кубӣ оператори ||/-ро истифода баред:

SELECT ||/ 10;  

Операторҳои реша префиксӣ мебошанд: онҳо пеш аз як қимат меоянд.

Барои ёфтани факториал аз функсияи factorial(n) истифода баред. Дар китобҳои кӯҳна оператори пасвандии ! — масалан, 4 ! — низ вомехӯрад, вале он аз PostgreSQL 14 сар карда хориҷ шудааст ва дар PostgreSQL 18-и мо хато медиҳад.

Факториалҳо дар бисёр бахшҳои математика истифода мешаванд. Яке аз истифодаҳои маъмултарини онҳо муайян кардани шумораи тарзҳои ба тартиб гузоштани якчанд ашё мебошад.

Фарз кунем чор акс доред. Онҳоро дар девор бо чанд тарзи гуногун ҷойгир карда метавонед?

Барои ҷавоб факториали шумораи ашёро ҳисоб мекунем: аз худи адад оғоз карда, онро ба ҳамаи ададҳои бутуни мусбати аз он хурдтар зарб мекунем.

Пас:

factorial(4) = 4 × 3 × 2 × 1 = 24  

Яъне чор аксро бо 24 тарзи гуногун ҷойгир кардан мумкин аст. Беҳуда нест, ки оро додани хона баъзан ин қадар вақт мегирад!

Ин операторҳо махсуси PostgreSQL мебошанд ва ба стандарти SQL дохил намешаванд. Агар низоми дигари пойгоҳи додаҳоро истифода баред, тарзи иҷрои ин амалҳоро дар ҳуҷҷатҳои он санҷед.

Риояи тартиби иҷрои амалҳо

Шояд аз дарсҳои аввали математика тартиби иҷрои амалҳо, ё авлавияти операторҳо, дар хотир дошта бошед.

SQL кадом ҳисобро аввал иҷро мекунад? Он аз қоидаҳои маъмули математикӣ пайравӣ мекунад. Барои амалҳои то ин ҷо баррасишуда тартиби умумӣ чунин аст:

1. Дараҷа — ^.
2. Зарб, тақсим ва бақияи тақсим.
3. Ҷамъ ва тарҳ.

Операторҳои решаи |/ ва ||/ ба ин рӯйхат дохил намешаванд: дар PostgreSQL онҳо ба гурӯҳи «операторҳои дигар» тааллуқ доранд ва авлавияташон аз *, /, + ва - пасттар аст. Аз ин рӯ, SELECT |/ 4 + 5; натиҷаи 7-ро не, балки 3-ро медиҳад: аввал 4 + 5 = 9 ҳисоб мешавад ва баъд решаи он гирифта мешавад. Барои реша функсияҳои sqrt() ва cbrt()-ро истифода баред ё ифодаро ба қавс гиред: SELECT sqrt(4) + 5; натиҷаи 7-ро медиҳад.

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

SELECT 7 + 8 * 9;

SELECT (7 + 8) * 9;  

Ифодаи аввал 79 медиҳад, зеро зарб аз ҷамъ авлавият дорад:

7 + 72 = 79  

Ифодаи дуюм 135 медиҳад, зеро қавсҳо ҷамъро пеш аз зарб иҷро мекунанд:

15 × 9 = 135  

Мисоли дигар бо дараҷа:

SELECT 3 ^ 3 - 1;

SELECT 3 ^ (3 - 1);  

Ба дараҷа бардоштан аз тарҳ авлавият дорад. Аз ин рӯ, дар ифодаи аввал нахуст 3 ба дараҷаи 3 бардошта мешавад ва баъд аз натиҷа 1 тарҳ мегардад:

27 - 1 = 26  

Дар ифодаи дуюм қавсҳо сабаб мешаванд, ки аввал тарҳ иҷро шавад:

3 ^ 2 = 9  

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

Ҳисобу китоб бо сутунҳои ҷадвали аҳолӣ

Акнун операторҳои бештар истифодашавандаи SQL-ро бо додаҳои ҷадвал месанҷем. Барои ин ҷадвали districts-ро месозем, ки маълумоти аҳолии 12 ноҳияи як кишвари фарзиро нигоҳ медорад. Номҳо ва ададҳо сохтаанд, вале сохтори ҷадвал ба маълумоти воқеии омори аҳолӣ монанд аст: шумораи аҳолӣ дар охири ду сол, таваллуд, фавт, муҳоҷират ва масоҳат.

Скрипти намунаи 6-4-ро дар пойгоҳи додаҳои analysis иҷро кунед.

CREATE TABLE districts (  
    district_name text,  
    region_name text,  
    pop_2023 integer,  
    births_2024 integer,  
    deaths_2024 integer,  
    migration_2024 integer,  
    pop_2024 integer,  
    area_land integer,  
    area_water integer  
);

INSERT INTO districts  
VALUES  
    ('Боғистон',   'Шимол',  48210, 1302, 311, -540,  48661, 61200,  1100),  
    ('Кӯҳсор',     'Шимол',  21540,  560, 190, -310,  21600, 98400,   350),  
    ('Обшор',      'Шимол',  33870,  903, 240,  -95,  34438, 22100, 18900),  
    ('Дарёбод',    'Марказ', 76420, 2050, 498,  410,  78382, 45300,  6200),  
    ('Сабзазор',   'Марказ', 29905,  771, 205, -120,  30351, 38700,   900),  
    ('Шаҳри Нав',  'Марказ', 912300, 21400, 5100, 9800, 938400, 12500, 1300),  
    ('Ширинкӯл',   'Ҷануб',  18230,  560, 166,  -70,  18554, 27600, 11400),  
    ('Регзор',     'Ҷануб',  41150, 1190, 287, -460,  41539, 112300,  150),  
    ('Санглох',    'Ҷануб',  15680,  410, 139, -200,  15751, 76900,   280),  
    ('Гулдара',    'Ҷануб',  57340, 1525, 360, -390,  58115, 51800,  2700),  
    ('Тозаоб',     'Шимол',  24790,  638, 175,  -85,  25168, 33400,  4600),  
    ('Мевазор',    'Марказ', 38615, 1012, 251, -150,  39226, 29900,   700);  

Намунаи 6-4: Сохтан ва пур кардани ҷадвали districts

Сутунҳои ҷадвал чунин маъно доранд:

Пас аҳолии охири соли 2024 бояд ба аҳолии охири соли 2023 ҷамъи тағйироти соли 2024 — таваллуд, фавт ва муҳоҷират — баробар бошад. Ин қоидаро дар бахши оянда месанҷем.
| area_land, area_water | Масоҳати хушкӣ ва об бо гектар |

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

Ҷамъ ва тарҳи сутунҳо

Акнун бо ду сутун ҳисоби сода анҷом медиҳем. Намунаи 6-5 шумораи фавтҳоро аз шумораи таваллудҳо дар ҳар ноҳия тарҳ мекунад. Демографҳо ин нишондиҳандаро афзоиши табиӣ меноманд.

SELECT district_name AS district,  
       region_name AS region,  
       births_2024 AS births,  
       deaths_2024 AS deaths,  
       births_2024 - deaths_2024 AS natural_increase  
FROM districts  
ORDER BY region_name, district_name;  

Намунаи 6-5: Тарҳи ду сутун дар ҷадвали districts

Бо гузоштани ифодаи births_2024 - deaths_2024 ҳамчун яке аз сутунҳои дастури SELECT, ҳисоб иҷро мешавад.

Бо калимаи калидии AS ба ҳар сутуни натиҷа номи кӯтоҳтар ва фаҳмо медиҳам. Агар ном муайян накунед, PostgreSQL аз нишонаи ?column? истифода мебарад, ки чандон муфид нест.

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

Санҷиши зуд бо ҳисобкунак ё қаламу коғаз тасдиқ мекунад, ки natural_increase ба фарқи ду сутун баробар аст.

Дар ҳамаи ноҳияҳои ҷадвали мо таваллуд аз фавт зиёд аст. Дар додаҳои воқеӣ ин на ҳамеша чунин аст: дар минтақаҳое, ки аҳолии солхӯрда бештар аст, шумораи фавтҳо метавонад аз таваллудҳо зиёдтар бошад ва natural_increase манфӣ мешавад.

Акнун ҳамин гуна ҳисобро барои санҷидани додаҳо истифода мебарем. Шумораи аҳолии соли 2024 бояд ба аҳолии соли 2023 бо илова кардани таваллуд ва муҳоҷират ва тарҳ кардани фавт баробар бошад. Коди намунаи 6-6 инро месанҷад.

SELECT district_name AS district,  
       region_name AS region,  
       pop_2024 AS pop,  
       pop_2023 + births_2024 - deaths_2024 +  
           migration_2024 AS components_total,  
       pop_2024 - (pop_2023 + births_2024 - deaths_2024 +  
           migration_2024) AS difference  
FROM districts  
ORDER BY difference;  

Намунаи 6-6: Санҷиши ҷамъбасти додаҳои аҳолӣ

Ин дархост шумораи аҳолии соли 2024-ро нишон медиҳад. Сипас ҷузъҳои тағйироти аҳолиро ба шумораи соли 2023 илова ё аз он тарҳ карда, натиҷаро дар сутуни components_total ҷой медиҳад.

Барои он ки дастӣ муқоиса накунем, сутуни дигаре илова мекунем: аз шумораи соли 2024 маҷмӯи ҳисобшударо тарҳ мекунем. Агар ҳамаи додаҳо дуруст бошанд, сутуни difference дар ҳар сатр бояд сифр дошта бошад.

Дар ҷадвали дувоздаҳсатра ин корро бо чашм ҳам кардан мумкин аст, аммо дар ҷадвали дорои ҳазорҳо сатр не. Бинобар ин натиҷаро бо ORDER BY аз рӯйи ҳамин сутун тартиб медиҳем: сатрҳое, ки фарқ доранд, дар боло ё поёни натиҷа пайдо мешаванд.

Пас аз иҷрои дархост сатрҳои аввал чунинанд:

Санҷиш хато ёфт! Дар сатри Регзор ду рақами охир ҷой иваз кардаанд: ба ҷойи 41593 адади 41539 ворид шудааст. Ин хатои маъмули воридкунии дастӣ аст ва мо онро дар ҷадвали намунавӣ қасдан гузоштем. Хаторо бо дастури UPDATE ислоҳ мекунем:

UPDATE districts  
SET pop_2024 = 41593  
WHERE district_name = 'Регзор';  

Дастури UPDATE қимати сутуни дар SET номбаршударо дар ҳамаи сатрҳое иваз мекунад, ки ба шарти WHERE мувофиқанд. WHERE-ро ҳеҷ гоҳ фаромӯш накунед: бе он қимат дар ҳамаи сатрҳои ҷадвал иваз мешавад. Пас аз ислоҳ намунаи 6-6-ро боз иҷро кунед — ҳамаи қиматҳои difference бояд сифр шаванд.

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

Ёфтани фоизи як қисм аз маҷмӯъ

Яке аз роҳҳои ошкор кардани фарқиятҳои ҷузъҳои маҷмӯи додаҳо ҳисоб кардани ҳиссаи фоизии ҳар ҷузъ аз маҷмӯъ мебошад. Сипас бо муқоисаи ин фоизҳо метавонед хулосаҳои пурмаъно ва баъзан ғайричашмдошт ба даст оред.

Барои ёфтани ҳисса аз маҷмӯъ, қимати мавриди назарро ба маҷмӯъ тақсим мекунем.

Масалан, агар сабади дорои 12 себ дошта бошед ва 9-тои онро барои кулча истифода баред:

9 / 12 = 0.75 = 75%  

Инро бо додаҳои ҷадвали districts месанҷем. Ду сутун масоҳати ҳар ноҳияро нишон медиҳанд:

• area_land — масоҳати хушкӣ;
• area_water — масоҳати об.

Ҳар ду бо гектар чен шудаанд. Бо коди намунаи 6-7 метавонем муайян кунем, ки чанд фоизи масоҳати ҳар ноҳияро об ташкил медиҳад.

SELECT district_name AS district,  
       region_name AS region,  
       area_water::numeric / (area_land + area_water) * 100 AS pct_water  
FROM districts  
ORDER BY pct_water DESC;  

Намунаи 6-7: Ҳисоб кардани фоизи масоҳати обии ноҳия

Қисми асосии ин дархост area_water-ро ба ҷамъи area_land ва area_water тақсим мекунад. Ҷамъи ин ду масоҳати умумии ноҳия мебошад.

Агар додаҳоро бо навъи аслии бутунашон истифода барем, қисми касрии заруриро намегирем: натиҷаи тақсими бутун дар ҳар сатр 0 мешавад.

Барои ба даст овардани натиҷаи касрӣ яке аз ададҳои бутунро ба numeric табдил медиҳем. Барои кӯтоҳӣ пас аз area_water аз навишти махсуси PostgreSQL — ::numeric — истифода мебарем.

Шумо метавонед ба ҷойи он функсияи стандартии ANSI SQL — CAST-ро, ки дар боби 5 омӯхтем, истифода баред.

Дар охир натиҷаро ба 100 зарб мекунем, то онро ба шакли фоиз нишон диҳем.

Пас аз тартибдиҳӣ аз фоизи калон ба хурд, сатрҳои аввали натиҷа чунин мешаванд:

Дар ноҳияи Обшор қариб нисфи масоҳатро об — масалан, обанбор — ташкил медиҳад. Шумораи зиёди рақамҳо пас аз нуқтаи даҳӣ хонданро душвор мекунад. Дар бахши навбатӣ бо функсияи round() шинос мешавем, ки натиҷаро гирд мекунад.

Пайгирии тағйироти фоизӣ

Нишондиҳандаи дигари муҳим дар таҳлили додаҳо тағйироти фоизӣ мебошад: як адад нисбат ба адади дигар чанд фоиз калонтар ё хурдтар шудааст?

Ҳисоби тағйироти фоизӣ ҳангоми таҳлили тағйирот дар давоми вақт зиёд истифода мешавад. Он махсусан барои муқоисаи тағйироти ҷузъҳои монанд муфид аст.

Масалан:

• Тағйироти солонаи шумораи мошинҳои фурӯхтаи ҳар истеҳсолкунанда.
• Тағйироти моҳонаи шумораи обуначиёни ҳар рӯйхати почтаи электронии ширкати маркетингӣ.
• Афзоиш ё коҳиши солонаи шумораи хонандагони мактабҳои кишвар.

Таносуби тағйирот чунин ҳисоб мешавад:

(қимати нав − қимати пешина) / қимати пешина  

Барои ифодаи он бо фоиз натиҷаро ба 100 зарб мекунем:

Тағйироти фоизӣ =  
(қимати нав − қимати пешина) / қимати пешина × 100  

Масалан, агар дӯкони лимонад дошта бошед ва имрӯз 73 истакону дирӯз 59 истакон фурӯхта бошед:

(73 − 59) / 59 ≈ 0.237 = 23.7%  

Акнун инро бо маҷмӯи хурди додаҳои санҷишӣ оид ба хароҷоти шуъбаҳои як мақомоти маҳаллии фарзӣ месанҷем. Намунаи 6-8 нишон медиҳад, ки хароҷоти кадом шуъбаҳо бештар афзоиш ё коҳиш ёфтааст.

CREATE TABLE percent_change (  
    department text,  
    spend_2019 numeric(10,2),  
    spend_2022 numeric(10,2)  
);

INSERT INTO percent_change  
VALUES  
    ('Assessor', 178556, 179500),  
    ('Building', 250000, 289000),  
    ('Clerk', 451980, 650000),  
    ('Library', 87777, 90001),  
    ('Parks', 250000, 223000),  
    ('Water', 199000, 195000);

SELECT department,  
       spend_2019,  
       spend_2022,  
       round(  
           (spend_2022 - spend_2019) / spend_2019 * 100,  
           1  
       ) AS pct_change  
FROM percent_change;  

Намунаи 6-8: Ҳисоб кардани тағйироти фоизӣ

Ҷадвали хурде бо номи percent_change месозем ва шаш сатрро бо додаҳои хароҷоти шуъбаҳо барои солҳои 2019 ва 2022 ворид мекунем.

Формулаи тағйироти фоизӣ spend_2019-ро аз spend_2022 тарҳ карда, натиҷаро ба spend_2019 тақсим мекунад. Сипас ба 100 зарб мекунем, то натиҷа бо фоиз ифода шавад.

Барои сода кардани намоиш ин дафъа функсияи round()-ро илова кардам, то танҳо як рақам пас аз нуқтаи даҳӣ боқӣ монад.

Функсия ду аргумент қабул мекунад:

1. Сутун ё ифодае, ки бояд гирд карда шавад.
2. Шумораи рақамҳои пас аз нуқтаи даҳӣ.

Азбаски ҳар ду адад навъи numeric доранд, натиҷа низ numeric мешавад.

Скрипт натиҷаи зеринро медиҳад:

Акнун танҳо бояд муайян кунем, ки чаро хароҷоти шуъбаи Clerk нисбат ба шуъбаҳои дигари шаҳр бештар афзоиш ёфтааст.

Истифодаи функсияҳои ҷамъбастӣ барои миёна ва ҷамъ

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

Рӯйхати пурраи функсияҳои ҷамъбастии PostgreSQL-ро, ки аз якчанд қимати воридшуда як натиҷа ҳосил мекунанд, дар ҳуҷҷатҳои PostgreSQL мебинед.

Ду функсияи бештар истифодашаванда дар таҳлили додаҳо инҳоянд:

• avg() — миёнаи арифметикӣ;
• sum() — ҷамъ.

Ба ҷадвали districts бармегардем. Шояд хоҳем ҳам шумораи умумии аҳолии ҳамаи ноҳияҳо ва ҳам миёнаи аҳолии як ноҳияро ҳисоб кунем.

Истифодаи avg() ва sum() барои сутуни pop_2024 ин корро осон мекунад. Намунаи 6-9 онро нишон медиҳад. Боз ҳам бо round() натиҷаи миёнаро то адади бутун гирд мекунем.

SELECT sum(pop_2024) AS district_sum,  
       round(avg(pop_2024), 0) AS district_average  
FROM districts;  

Намунаи 6-9: Истифодаи функсияҳои ҷамъбастии sum() ва avg()

Натиҷа чунин мешавад:

Дар 12 ноҳия дар маҷмӯъ 1 350 239 нафар зиндагӣ мекунанд ва миёнаи аҳолии як ноҳия 112 520 нафар аст.

Ёфтани медиана

Медиана дар маҷмӯи ададҳо нишондиҳандаест, ки аҳаммияташ аз миёна кам нест ва баъзан бештар ҳам ҳаст.

Фарқи онҳо:

• Миёнаи арифметикӣ: ҷамъи ҳамаи қиматҳо тақсим бар шумораи онҳо.
• Медиана: қимати мобайнӣ дар маҷмӯи тартибдодашуда.

Медиана дар таҳлили додаҳо муҳим аст, зеро таъсири қиматҳои ғайримаъмул ё дур аз дигар қиматҳоро кам мекунад.

Мисоле меорем. Фарз кунем шаш кӯдаки 10, 11, 10, 9, 13 ва 12-сола ба саёҳати таълимӣ мераванд. Барои ёфтани синни миёна синну солҳоро ҷамъ карда, ба шаш тақсим мекунем:

(10 + 11 + 10 + 9 + 13 + 12) / 6 ≈ 10.8  

Азбаски синну солҳо дар доираи танг қарор доранд, миёнаи 10,8-сола гурӯҳро хуб тавсиф мекунад.

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

Масалан, фарз кунем ба саёҳат як ҳамроҳи калонсол ҳамроҳ мешавад. Акнун синну солҳо чунинанд:

10, 11, 10, 9, 13, 12, 46  

Синни миёна хеле зиёд мешавад:

(10 + 11 + 10 + 9 + 13 + 12 + 46) / 7 ≈ 15.9  

Ҳоло миёна гурӯҳро хуб тавсиф намекунад, зеро қимати аз дигарон дур — 46 — онро ба боло кашидааст.

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

Синну соли иштирокчиёнро аз хурд ба калон тартиб медиҳем:

9, 10, 10, 11, 12, 13, 46  

Қимати мобайнӣ, яъне медиана, 11 аст. Барои ин гурӯҳ медианаи 11-сола синни маъмулии иштирокчиёнро аз миёнаи 15,9-сола беҳтар нишон медиҳад.

Агар шумораи қиматҳо ҷуфт бошад, медиана аз ҳисоби миёнаи ду қимати мобайнӣ ёфта мешавад.

Ба саёҳат боз як хонандаи 12-соларо илова мекунем:

9, 10, 10, 11, 12, 12, 13, 46  

Акнун ду қимати мобайнӣ 11 ва 12 мебошанд. Миёнаи онҳоро мегирем:

(11 + 12) / 2 = 11.5  

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

Дар маоши варзишгарон низ ҳамин ҳолат вуҷуд дорад: маоши як ё ду ситораи машҳур метавонад миёнаи маоши тамоми дастаро хеле баланд кунад.

Як санҷиши муфид ҳисоб кардани ҳам миёна ва ҳам медиана мебошад. Наздик будани онҳо метавонад ба симметрӣ будани тақсимот ишора кунад. Агар фарқашон калон бошад, медиана аксар вақт қимати маъмулиро беҳтар тавсиф мекунад.

Ёфтани медиана бо функсияҳои персентилӣ

PostgreSQL, мисли аксари пойгоҳҳои додаҳои релятсионӣ, функсияи дарунсохти median() надорад, ки дар Excel ё дигар барномаҳои ҷадвалҳои электронӣ мебинед. Чунин функсия ба стандарти ANSI SQL ҳам дохил намешавад.

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

Дар омор персентил нуқтаеро дар маҷмӯи тартибдодашуда нишон медиҳад, ки фоизи муайяни додаҳо дар поёни он қарор дорад.

Масалан, духтур метавонад бигӯяд, ки қади шумо дар гурӯҳи синну солатон ба персентили 60-ум мувофиқ аст. Яъне тақрибан 60 фоизи одамони он гурӯҳ аз шумо кӯтоҳтаранд.

Медиана ба персентили 50-ум баробар аст: нисфи қиматҳо поёнтар ва нисфи дигар болотар ҷой мегиранд.

Ду навъи функсияи персентилӣ вуҷуд дорад:

percentile_cont(n)  
percentile_disc(n)  

Ҳар ду ба стандарти ANSI SQL дохил мешаванд ва дар PostgreSQL, Microsoft SQL Server ва дигар пойгоҳҳои додаҳо мавҷуданд.

Функсияи percentile_cont(n) персентилҳоро ҳамчун қиматҳои пайваста ҳисоб мекунад. Яъне натиҷа ҳатман яке аз ададҳои маҷмӯи додаҳо нест: он метавонад қимати касрие байни ду адад бошад.

Ин ба тарзи ҳисоб кардани медиана барои шумораи ҷуфти қиматҳо мувофиқ аст, ки дар он миёнаи ду адади мобайнӣ гирифта мешавад.

Функсияи percentile_disc(n) танҳо қиматҳои дискретӣ бармегардонад. Яъне натиҷа яке аз қиматҳои мавҷудаи худи маҷмӯъ мебошад.

Дар намунаи 6-10 ҷадвали санҷишӣ бо шаш адад месозем ва персентилҳоро меёбем.

CREATE TABLE percentile_test (  
    numbers integer  
);

INSERT INTO percentile_test (numbers)  
VALUES  
    (1), (2), (3), (4), (5), (6);

SELECT  
    percentile_cont(.5)  
        WITHIN GROUP (ORDER BY numbers),  
    percentile_disc(.5)  
        WITHIN GROUP (ORDER BY numbers)  
FROM percentile_test;  

Намунаи 6-10: Санҷидани функсияҳои персентилии SQL

Ба ҳар ду функсия — ҳам пайваста ва ҳам дискретӣ — қимати .5 медиҳем. Он персентили 50-ум, яъне медианаро ифода мекунад.

Натиҷа:

Функсияи percentile_cont() медианаи интизорӣ — 3.5-ро баргардонд.

Аммо percentile_disc() танҳо қиматҳои дискретиро интихоб мекунад ва аз ин рӯ 3 медиҳад. Ин охирин қимати нисфи аввали ададҳои тартибдодашуда мебошад.

Азбаски усули маъмули ҳисоб кардани медиана барои маҷмӯи дорои шумораи ҷуфти қиматҳо гирифтани миёнаи ду қимати мобайнӣ аст, барои ёфтани медиана аз percentile_cont(.5) истифода баред.

Ёфтани медиана ва персентилҳо дар додаҳои аҳолӣ

Додаҳои аҳолии мо метавонанд нишон диҳанд, ки медиана воқеиятро аз миёна ба тарзи дигар тасвир мекунад.

Дар намунаи 6-11 ба функсияҳои ҷамъбастии sum() ва avg(), ки аллакай истифода кардем, percentile_cont()-ро илова мекунем. Бо ин роҳ ҷамъ, миёна ва медианаи аҳолии ноҳияҳоро меёбем.

SELECT sum(pop_2024) AS district_sum,  
       round(avg(pop_2024), 0) AS district_average,  
       percentile_cont(.5)  
           WITHIN GROUP (ORDER BY pop_2024) AS district_median  
FROM districts;  

Намунаи 6-11: Истифодаи sum(), avg() ва percentile_cont()

Натиҷаи шумо бояд чунин бошад:

Медиана ва миёна аз ҳам хеле фарқ мекунанд. Ин нишон медиҳад, ки миёнаҳо метавонанд тасаввуроти нодуруст диҳанд.

Нисфи ноҳияҳо камтар аз 36 832 нафар аҳолӣ ва нисфи дигар бештар аз ин шумора доранд. Агар дар гузориш бигӯед: «Аҳолии миёнаи як ноҳия 112 520 нафар аст», хонандагон метавонанд дар бораи ноҳияи маъмулӣ тасаввуроти нодуруст пайдо кунанд: аз 12 ноҳия танҳо дар яке аҳолӣ аз ин миёна зиёд аст.

Сабаб Шаҳри Нав аст: дар он беш аз 900 ҳазор нафар зиндагӣ мекунанд ва ҳамин як қимати калон миёнаро ба боло кашидааст. Медиана бошад, ба ин қимат қариб вокуниш нишон намедиҳад.

Ёфтани квантилҳои дигар бо функсияҳои персентилӣ

Барои таҳлил метавонед додаҳоро ба гурӯҳҳои хурдтари ҳамандоза низ ҷудо кунед. Маъмултарини онҳо инҳоянд:

• Квартилҳо: чор гурӯҳи ҳамандоза.
• Квинтилҳо: панҷ гурӯҳи ҳамандоза.
• Десилҳо: даҳ гурӯҳи ҳамандоза.

Барои ёфтани нуқтаи алоҳида қимати мувофиқро ба функсияи персентилӣ медиҳед. Масалан, барои ёфтани сарҳади квартили аввал, яъне 25 фоизи поёнии додаҳо, аз .25 истифода мебаред:

percentile_cont(.25)  

Аммо агар хоҳед якчанд нуқтаи буришро гиред, ворид кардани қиматҳо якто-якто заҳматталаб аст. Ба ҷойи ин метавонед онҳоро ҳамчун массив, яъне рӯйхати унсурҳо, ба percentile_cont() диҳед.

Намунаи 6-12 нишон медиҳад, ки чӣ гуна се нуқтаи ҷудокунандаи чор гурӯҳи квартилӣ якбора ҳисоб мешаванд.

SELECT percentile_cont(ARRAY[.25,.5,.75])  
           WITHIN GROUP (ORDER BY pop_2024) AS quartiles  
FROM districts;  

Намунаи 6-12: Додани массиви қиматҳо ба percentile_cont()

Дар ин мисол нуқтаҳои буришро бо ҷойгир кардани қиматҳо дар созандаи массиви ARRAY[] муайян мекунем.

Созандаи массив ифодаест, ки аз унсурҳои байни қавсҳои чоркунҷа массив месозад. Дар дохили қавсҳо қиматҳои бо вергул ҷудошударо менависем. Онҳо се нуқтаи буришро нишон медиҳанд, ки додаҳоро ба чор гурӯҳ ҷудо мекунанд.

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

       quartiles  
-----------------------  
 {24276,36832,51024.5}  

Азбаски массив дода будем, PostgreSQL низ массив бармегардонад. Дар натиҷа он бо қавсҳои фигурӣ — {} — нишон дода шудааст ва қиматҳо бо вергул ҷудо шудаанд.

• Квартили аввал: 24276. Тақрибан 25 фоизи ноҳияҳо аҳолии баробар ё камтар аз ин қимат доранд.
• Квартили дуюм: 36832. Ин ҳамон медиана аст.
• Квартили сеюм: 51024.5. Тақрибан 25 фоизи ноҳияҳо аҳолии бештар аз ин сарҳад доранд.

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

Массивҳо дар стандарти ANSI SQL муайян шудаанд. Истифодаи мо дар ин ҷо танҳо яке аз чанд роҳи кор бо массивҳо дар PostgreSQL мебошад.

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

Барои мисолҳои муайян кардан, ҷустуҷӯ ва тағйир додани массивҳо ба ҳуҷҷатҳои PostgreSQL оид ба массивҳо нигаред.

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

Як функсияи муфид барои кор бо натиҷаи намунаи 6-12 — unnest() мебошад. Он массивро ба сатрҳо табдил дода, хондани натиҷаро осон мекунад.

Намунаи 6-13 кодро нишон медиҳад:

SELECT unnest(  
           percentile_cont(ARRAY[.25,.5,.75])  
               WITHIN GROUP (ORDER BY pop_2024)  
       ) AS quartiles  
FROM districts;  

Намунаи 6-13: Табдил додани массив ба сатрҳо бо unnest()

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

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

Ёфтани мода

Мода қиматест, ки бештар аз ҳама такрор мешавад. Онро бо функсияи mode()-и PostgreSQL ёфтан мумкин аст.

Ин функсия ба стандарти SQL дохил намешавад ва синтаксисаш ба функсияҳои персентилӣ монанд аст.

Намунаи 6-14 модаро барои сутуни births_2024 ҳисоб мекунад. Ин сутун шумораи кӯдакони таваллудшударо нишон медиҳад.

SELECT mode() WITHIN GROUP (ORDER BY births_2024)  
FROM districts;  

Намунаи 6-14: Ёфтани қимати бештар такроршаванда бо mode()

Натиҷа 560 мебошад: дар ду ноҳия — Кӯҳсор ва Ширинкӯл — шумораи таваллудҳо ба ҳамин адад баробар аст. Дигар қиматҳои ин сутун ҳар кадом танҳо як бор вомехӯранд.

Хулоса

Кор бо ададҳо қадами муҳимест барои дарёфти маъно аз додаҳо. Бо малакаҳои математикии дар ин боб омӯхтаатон акнун омодаед асосҳои таҳлили ададиро бо SQL иҷро кунед.

Шумо асосҳои ҷамъ, миёна ва персентилҳоро аз худ кардед. Барои омори амиқтар — коррелятсия ва регрессия — PostgreSQL функсияҳои corr(), regr_slope() ва дигарҳоро дорад, ки дар ҳуҷҷатҳои функсияҳои ҷамъбастӣ оварда шудаанд.

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

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

1. Барои ҳар ноҳия афзоиши табииро (births_2024 - deaths_2024) ҳамчун фоизи аҳолии охири соли 2023 ҳисоб кунед, то ду рақам пас аз вергул гирд кунед ва се ноҳияи аввалро нишон диҳед.
2. Миёна ва медианаи сутуни births_2024-ро ёбед. Чаро онҳо ин қадар фарқ мекунанд?
3. Чанд фоизи тамоми аҳолии соли 2024 дар Шаҳри Нав зиндагӣ мекунад?

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