Боби 9. Гурӯҳбандӣ ва ҷамъбасти додаҳо

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

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

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

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

Дар бисёр кишварҳо идораҳои омор ҳар сол аз китобхонаҳои оммавӣ пурсиш мегузаронанд: шумораи ташрифҳо, китобҳо, кормандон, соатҳои корӣ ва нишондиҳандаҳои дигарро ҷамъ мекунанд. Масалан, дар ИМА ин корро Институти хадамоти осорхонаҳо ва китобхонаҳо — IMLS дар доираи Пурсиши солонаи китобхонаҳои оммавӣ анҷом медиҳад, ки тақрибан 9 000 муассисаро фаро мегирад.

Дар ин боб бо пурсиши хурди фарзӣ кор мекунем: китобхонаҳои ҳамон 12 ноҳияе, ки дар бобҳои 6 ва 7 омӯхтем. Номҳо ва ададҳо сохтаанд, вале мушкилоте, ки дар онҳо пинҳон шудаанд — китобхонаҳои нав ва басташуда, кодҳои махсус ба ҷойи қимат, номҳои такрорӣ — дар додаҳои воқеӣ ҳам вомехӯранд.

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

Барои нигоҳ доштани додаҳои пурсишҳои солҳои 2024, 2023 ва 2022 се ҷадвал месозем. Барои ошкор кардани тамоюлҳо бисёр вақт таҳлили додаҳои чанд сол муфид аст.

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

Сохтани ҷадвалҳои пурсиши китобхонаҳо

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

Сохтани ҷадвали додаҳои китобхонаҳо барои соли 2024

Аввал ҷадвали додаҳои соли 2024-ро месозем. Намунаи 9-1 онро бо фармони CREATE TABLE месозад ва бо INSERT пур мекунад.

CREATE TABLE libraries_2024 (  
    lib_code text CONSTRAINT lib_code_2024_key PRIMARY KEY,  
    region text NOT NULL,  
    lib_name text NOT NULL,  
    city text NOT NULL,  
    phone text,  
    stataddr text NOT NULL,  
    visits integer NOT NULL,  
    wifi_sessions integer NOT NULL  
);

INSERT INTO libraries_2024  
VALUES  
    ('L01', 'Марказ', 'Китобхонаи миллӣ',    'Шаҳри Нав', '372-21-10-01', '00', 412300, 198400),  
    ('L02', 'Марказ', 'Китобхонаи марказӣ',  'Шаҳри Нав', '372-21-10-02', '00', 186200,  90310),  
    ('L03', 'Марказ', 'Китобхонаи кӯдакон',  'Шаҳри Нав', '372-21-10-03', '07',  74150,  21800),  
    ('L04', 'Марказ', 'Китобхонаи марказӣ',  'Дарёбод',   '372-31-20-11', '00',  58900,  24100),  
    ('L05', 'Марказ', 'Китобхонаи Мевазор',  'Мевазор',   '372-32-21-05', '00',  21450,   8800),  
    ('L06', 'Марказ', 'Китобхонаи Сабзазор', 'Сабзазор',  NULL,           '15',  18700,   6150),  
    ('L07', 'Шимол',  'Китобхонаи марказӣ',  'Боғистон',  '372-41-30-07', '00',  30210,  11200),  
    ('L08', 'Шимол',  'Китобхонаи Кӯҳсор',   'Кӯҳсор',    NULL,           '00',   9800,   2100),  
    ('L09', 'Шимол',  'Китобхонаи Обшор',    'Обшор',     '372-43-32-09', '07',  16400,   5900),  
    ('L10', 'Шимол',  'Китобхонаи Тозаоб',   'Тозаоб',    '372-44-33-10', '00',     -3,     -3),  
    ('L11', 'Ҷануб',  'Китобхонаи марказӣ',  'Регзор',    '372-51-40-11', '00',  28900,   9400),  
    ('L12', 'Ҷануб',  'Китобхонаи Гулдара',  'Гулдара',   '372-52-41-12', '00',  33100,  12700),  
    ('L13', 'Ҷануб',  'Китобхонаи Санглох',  'Санглох',   '372-53-42-13', '15',   7300,   1500),  
    ('L14', 'Ҷануб',  'Китобхонаи Ширинкӯл', 'Ширинкӯл',  '372-54-43-14', '00',     -1,   4100),  
    ('L15', 'Ҷануб',  'Китобхонаи наврасон', 'Гулдара',   '372-52-41-15', '00',  12900,   6800),  
    ('L16', 'Марказ', 'Китобхонаи донишҷӯён', 'Шаҳри Нав', '372-21-10-16', '00',  98700,  61200);

CREATE INDEX lib_name_2024_idx  
ON libraries_2024 (lib_name);  

Намунаи 9-1: Сохтан ва пур кардани ҷадвали пурсиши китобхонаҳо барои соли 2024.

Кодро дар пойгоҳи додаҳои analysis иҷро кунед.

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

Ба сутуни lib_code маҳдудияти калиди асосӣ медиҳем. Ин код ба ҳар муассисаи китобхона як бор дода мешавад ва дар солҳои гуногун тағйир намеёбад. Азбаски беназир аст, дар ҳар сатр мавҷуд аст ва эҳтимоли тағйир ёфтанаш кам мебошад, метавонад калиди асосии табиӣ шавад.

Дар таърифи ҳар сутун навъи мувофиқи додаҳо нишон дода шудааст. Барои сутунҳое, ки бояд ҳамеша қимат дошта бошанд, NOT NULL илова шудааст. Сутуни phone ин маҳдудиятро надорад: на ҳар китобхона телефон дорад.

Барои тезонидани ҷустуҷӯи китобхонаи мушаххас ба сутуни lib_name индекс илова мекунем. Дар ҷадвали шонздаҳсатра индекс суръатро тағйир намедиҳад, вале дар пурсиши воқеии ҳазорсатра фоида меорад — инро дар боби 8 дидем.

Сохтани ҷадвалҳои додаҳои китобхонаҳо барои солҳои 2023 ва 2022

Сохтани ҷадвалҳои пурсишҳои солҳои 2023 ва 2022 низ қадамҳои монанд дорад. Коди намунаи 9-2-ро иҷро кунед:

CREATE TABLE libraries_2023 (  
    lib_code text CONSTRAINT lib_code_2023_key PRIMARY KEY,  
    region text NOT NULL,  
    lib_name text NOT NULL,  
    city text NOT NULL,  
    phone text,  
    stataddr text NOT NULL,  
    visits integer NOT NULL,  
    wifi_sessions integer NOT NULL  
);

INSERT INTO libraries_2023  
VALUES  
    ('L01', 'Марказ', 'Китобхонаи миллӣ',    'Шаҳри Нав', '372-21-10-01', '00', 425800, 171200),  
    ('L02', 'Марказ', 'Китобхонаи марказӣ',  'Шаҳри Нав', '372-21-10-02', '00', 191400,  78600),  
    ('L03', 'Марказ', 'Китобхонаи кӯдакон',  'Шаҳри Нав', '372-21-10-03', '00',  70200,  18300),  
    ('L04', 'Марказ', 'Китобхонаи марказӣ',  'Дарёбод',   '372-31-20-11', '00',  57300,  20800),  
    ('L05', 'Марказ', 'Китобхонаи Мевазор',  'Мевазор',   '372-32-21-05', '00',  22900,   7600),  
    ('L06', 'Марказ', 'Китобхонаи Сабзазор', 'Сабзазор',  NULL,           '00',  19800,   5100),  
    ('L07', 'Шимол',  'Китобхонаи марказӣ',  'Боғистон',  '372-41-30-07', '00',  32400,   9700),  
    ('L08', 'Шимол',  'Китобхонаи Кӯҳсор',   'Кӯҳсор',    NULL,           '00',  11200,   1700),  
    ('L09', 'Шимол',  'Китобхонаи Обшор',    'Обшор',     '372-43-32-09', '00',  17100,   4800),  
    ('L10', 'Шимол',  'Китобхонаи Тозаоб',   'Тозаоб',    '372-44-33-10', '00',  14600,   3900),  
    ('L11', 'Ҷануб',  'Китобхонаи марказӣ',  'Регзор',    '372-51-40-11', '00',  27900,   8100),  
    ('L12', 'Ҷануб',  'Китобхонаи Гулдара',  'Гулдара',   '372-52-41-12', '00',  31800,  10600),  
    ('L13', 'Ҷануб',  'Китобхонаи Санглох',  'Санглох',   '372-53-42-13', '00',   8100,   1200),  
    ('L14', 'Ҷануб',  'Китобхонаи Ширинкӯл', 'Ширинкӯл',  '372-54-43-14', '00',  10400,   3300),  
    ('L16', 'Марказ', 'Китобхонаи донишҷӯён', 'Шаҳри Нав', '372-21-10-16', '00',  96100,  50900),  
    ('L17', 'Ҷануб',  'Китобхонаи сайёр',    'Регзор',    NULL,           '00',   4200,      0);

CREATE TABLE libraries_2022 (  
    lib_code text CONSTRAINT lib_code_2022_key PRIMARY KEY,  
    region text NOT NULL,  
    lib_name text NOT NULL,  
    city text NOT NULL,  
    phone text,  
    stataddr text NOT NULL,  
    visits integer NOT NULL,  
    wifi_sessions integer NOT NULL  
);

INSERT INTO libraries_2022  
VALUES  
    ('L01', 'Марказ', 'Китобхонаи миллӣ',    'Шаҳри Нав', '372-21-10-01', '00', 448100, 142500),  
    ('L02', 'Марказ', 'Китобхонаи марказӣ',  'Шаҳри Нав', '372-21-10-02', '00', 199700,  64200),  
    ('L03', 'Марказ', 'Китобхонаи кӯдакон',  'Шаҳри Нав', '372-21-10-03', '00',  71900,  14100),  
    ('L04', 'Марказ', 'Китобхонаи марказӣ',  'Дарёбод',   '372-31-20-11', '00',  55100,  16900),  
    ('L05', 'Марказ', 'Китобхонаи Мевазор',  'Мевазор',   '372-32-21-05', '00',  23800,   6100),  
    ('L06', 'Марказ', 'Китобхонаи Сабзазор', 'Сабзазор',  NULL,           '00',  20600,   4000),  
    ('L07', 'Шимол',  'Китобхонаи марказӣ',  'Боғистон',  '372-41-30-07', '00',  33900,   7800),  
    ('L08', 'Шимол',  'Китобхонаи Кӯҳсор',   'Кӯҳсор',    NULL,           '00',  12100,   1300),  
    ('L09', 'Шимол',  'Китобхонаи Обшор',    'Обшор',     '372-43-32-09', '00',  18300,   3700),  
    ('L10', 'Шимол',  'Китобхонаи Тозаоб',   'Тозаоб',    '372-44-33-10', '00',  15200,   3100),  
    ('L11', 'Ҷануб',  'Китобхонаи марказӣ',  'Регзор',    '372-51-40-11', '00',  29300,   6600),  
    ('L12', 'Ҷануб',  'Китобхонаи Гулдара',  'Гулдара',   '372-52-41-12', '00',  30200,   8400),  
    ('L13', 'Ҷануб',  'Китобхонаи Санглох',  'Санглох',   '372-53-42-13', '00',   8800,    900),  
    ('L14', 'Ҷануб',  'Китобхонаи Ширинкӯл', 'Ширинкӯл',  '372-54-43-14', '00',  10900,   2500),  
    ('L17', 'Ҷануб',  'Китобхонаи сайёр',    'Регзор',    NULL,           '00',   5100,      0);

CREATE INDEX lib_name_2023_idx  
ON libraries_2023 (lib_name);

CREATE INDEX lib_name_2022_idx  
ON libraries_2022 (lib_name);  

Намунаи 9-2: Сохтан ва пур кардани ҷадвалҳои пурсиши китобхонаҳо барои солҳои 2023 ва 2022.

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

Аксари пурсишҳои давомдор аз як сол ба соли дигар каме тағйир меёбанд: таҳиягарон саволҳои нав илова мекунанд ё саволҳои мавҷударо дигар мекунанд. Пеш аз муқоисаи солҳо ҳамеша санҷед, ки сутунҳо ҳамон як чизро чен мекунанд.

Акнун додаҳоро таҳқиқ мекунем, то ҳикояи онҳоро ёбем.

Таҳқиқи додаҳои китобхонаҳо бо функсияҳои ҷамъбастӣ

Функсияҳои ҷамъбастӣ — aggregate functions қиматҳои чанд сатрро гирифта, дар болои онҳо амалеро иҷро мекунанд ва як натиҷа бармегардонанд.

Масалан, бо функсияи ҷамъбастии avg() метавон миёнаи қиматҳоро гирифт. Инро дар боби 6 омӯхта будед.

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

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

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

Ҳисоб кардани сатрҳо ва қиматҳо бо count()

Пас аз ворид кардани маҷмӯи додаҳо, қадами аввалини оқилона санҷидани шумораи сатрҳои ҷадвал аст.

Фарз кунем, ки дар ҳуҷҷатҳои пурсиш шумораи муассисаҳо чунин навишта шудааст:

Фарқият ба кушодашавӣ ё баста шудани муассисаҳои китобхона вобаста аст.

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

Функсияи ҷамъбастии count(), ки қисми стандарти ANSI SQL аст, ҳисоб кардани сатрҳо ва дигар амалҳои шуморишро осон мекунад.

Агар ба он ситорача диҳем:

count(*)  

функсия ҳамаи сатрҳоро новобаста аз доштани қиматҳои NULL ҳисоб мекунад.

Дар ҳар се дархости намунаи 9-3 ҳамин корро мекунем:

SELECT count(*)  
FROM libraries_2024;

SELECT count(*)  
FROM libraries_2023;

SELECT count(*)  
FROM libraries_2022;  

Намунаи 9-3: Истифодаи count() барои ҳисоб кардани сатрҳои ҷадвал.

Фармонҳоро як-як иҷро кунед. Натиҷаҳо 16, 16 ва 15 мебошанд — ҳар се ба шумораи интизоршуда мувофиқанд.

Ин қадами хуби аввал аст, зеро метавонад аз мушкилоте ба монанди сатрҳои намерасида ё ворид кардани файли нодуруст хабар диҳад. Дар ҷадвали шонздаҳсатра хаторо бо чашм ҳам пайдо кардан мумкин аст, аммо дар ҷадвали дорои 9 000 сатр — не.

Ҳисоб кардани қиматҳои мавҷудаи сутун

Агар ба count() ба ҷойи ситорача номи сутунро диҳем, он танҳо сатрҳоеро ҳисоб мекунад, ки қимати он сутун NULL нест.

Масалан, шумораи қиматҳои ғайри-NULL-и сутуни phone-ро дар ҷадвали соли 2024 чунин ҳисоб мекунем:

SELECT count(phone)  
FROM libraries_2024;  

Намунаи 9-4: Истифодаи count() барои ҳисоб кардани қиматҳои сутун.

Натиҷа:

Аз 16 сатр танҳо 14-то рақами телефон доранд. Яъне дар ду сатр сутуни phone NULL аст.

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

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

Ҳисоб кардани қиматҳои беназири сутун

Дар боби 4 калимаи калидии DISTINCT-ро омӯхтем. Он қисми стандарти SQL буда, бо SELECT рӯйхати қиматҳои беназирро бармегардонад.

Онро ҳам барои қиматҳои беназири як сутун ва ҳам барои таркибҳои беназири қиматҳои чанд сутун истифода бурдан мумкин аст.

Ҳамчунин метавон DISTINCT-ро ба count() илова кард, то шумораи қиматҳои беназири сутунро гирем.

Намунаи 9-5 ду дархост дорад. Якум ҳамаи қиматҳои сутуни lib_name-и ҷадвали соли 2024-ро ҳисоб мекунад. Дуюм ҳамин корро бо DISTINCT анҷом медиҳад.

Ҳар дуро алоҳида иҷро кунед:

SELECT count(lib_name)  
FROM libraries_2024;

SELECT count(DISTINCT lib_name)  
FROM libraries_2024;  

Намунаи 9-5: Истифодаи count() барои ҳисоб кардани қиматҳои беназири сутун.

Дархости аввал ҳамон шумораи сатрҳоеро медиҳад, ки дар намунаи 9-3 ёфта будем — 16. Ин хуб аст: интизорем дар ҳар сатр номи муассисаи китобхона бошад.

Аммо дархости дуюм рақами хурдтар медиҳад:

Истифодаи DISTINCT номҳои такрориро бартараф карда, шумораи номҳои беназирро ба 13 мерасонад.

Санҷиши муфассалтар нишон медиҳад, ки чор муассиса номи зеринро доранд:

Китобхонаи марказӣ  

Ҳар кадом дар шаҳр ё ноҳияи дигар ҷойгир аст: Шаҳри Нав, Дарёбод, Боғистон ва Регзор. Ин мисол нишон медиҳад, ки чаро номи китобхона калиди асосии хуб шуда наметавонад ва чаро пурсиш ба ҳар муассиса коди алоҳида медиҳад.

Дар бахши «Ҷамъбасти додаҳо бо GROUP BY» барои дидани таркибҳои беназири қиматҳо дархост менависем.

Ёфтани қиматҳои калонтарин ва хурдтарин бо max() ва min()

Функсияҳои max() ва min() мутаносибан қиматҳои калонтарин ва хурдтарини сутунро медиҳанд. Онҳо аз чанд ҷиҳат муфиданд.

Аввал, доираи қиматҳои сабтшударо нишон медиҳанд.

Дуюм, метавонанд мушкилоти ғайричашмдошти додаҳоро ошкор кунанд. Ҳоло инро мебинем.

Ҳар ду функсия номи сутунро ҳамчун аргумент мегиранд.

Дар намунаи 9-6 онҳоро барои сутуни visits-и ҷадвали соли 2024 истифода мебарем.

SELECT max(visits), min(visits)  
FROM libraries_2024;  

Намунаи 9-6: Ёфтани шумораи зиёдтарин ва камтарини ташрифҳо бо max() ва min().

Натиҷа:

Ҷолиб аст. Қимати калонтарини беш аз 412 ҳазор барои китобхонаи миллии шаҳри калон қобили қабул аст. Аммо қимати хурдтарини -3 чӣ маъно дорад? Шумораи ташрифҳо манфӣ буда наметавонад.

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

Дар ин маҷмӯи додаҳо қиматҳои манфии сутунҳои ададӣ чунин маъно доранд:

Дар ҷадвали соли 2024 китобхонаи Тозаоб барои таъмир баста буд (-3) ва китобхонаи Ширинкӯл ба саволи ташрифҳо ҷавоб надодааст (-1).

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

Бо банди WHERE онҳоро полоиш кардан мумкин аст.

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

Эзоҳ: Роҳи беҳтар ин аст, ки дар сутуни visits барои додаҳои мавҷуднабуда NULL гузошта, сутуни алоҳидаи visits_flag созем. Дар он кодҳо метавонанд сабаби набудани маълумотро шарҳ диҳанд.

Ҷамъбасти додаҳо бо GROUP BY

Бо истифодаи банди GROUP BY ҳамроҳи функсияҳои ҷамъбастӣ, натиҷаҳоро аз рӯйи қиматҳои як ё чанд сутун гурӯҳбандӣ мекунем.

Ин имкон медиҳад амалҳое ба монанди sum() ё count()-ро барои ҳар минтақа ё ҳар шаҳр алоҳида иҷро кунем.

Биёед тарзи кори онро омӯзем.

GROUP BY, ки он низ қисми стандарти ANSI SQL аст, агар бе функсияи ҷамъбастӣ истифода шавад, метавонад мисли DISTINCT натиҷаи бидуни такрор диҳад.

Намунаи 9-7:

SELECT region  
FROM libraries_2024  
GROUP BY region  
ORDER BY region;  

Намунаи 9-7: Истифодаи GROUP BY барои сутуни region.

Банди GROUP BY-ро пас аз FROM навишта, номи сутуни гурӯҳбандиро нишон медиҳем.

Дар ин ҷо region-ро интихоб мекунем ва аз рӯйи ҳамон сутун гурӯҳбандӣ менамоем. Бо ORDER BY region натиҷаҳоро аз рӯйи алифбо ҷобаҷо мекунем.

Натиҷа номҳои беназири минтақаҳо аз ҷадвали соли 2024 мебошад:

 region  
--------  
 Марказ  
 Ҷануб  
 Шимол  

Дар се сатри баргардондашуда такрор вуҷуд надорад.

Эзоҳ: Тартиби ҳарфҳо аз танзими collation-и пойгоҳи додаҳо вобаста аст. Дар боби 2 барои пойгоҳҳои мо тартиби алифбои тоҷикӣ муқаррар кардем, бинобар ин Ҷануб пеш аз Шимол меояд — мисли луғати тоҷикӣ.

Гурӯҳбандӣ бо як сутун маҳдуд нест. Дар намунаи 9-8 додаҳои соли 2024-ро аз рӯйи ду сутун — city ва region — гурӯҳбандӣ мекунем:

SELECT city, region  
FROM libraries_2024  
GROUP BY city, region  
ORDER BY city, region;  

Намунаи 9-8: Истифодаи GROUP BY барои сутунҳои city ва region.

Натиҷа аввал аз рӯйи шаҳр ва баъд аз рӯйи минтақа ҷобаҷо шуда, таркибҳои беназирро нишон медиҳад:

Ин гурӯҳбандӣ 12 сатр медиҳад, ки аз шумораи умумии сатрҳои ҷадвал 4-то камтар аст.

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

Якҷо кардани GROUP BY бо count()

Агар GROUP BY-ро бо функсияи ҷамъбастие ба монанди count() якҷо кунем, маълумоти тавсифии бештар мегирем.

Масалан, медонем, ки ҷадвали соли 2024 дорои 16 муассисаи китобхона аст. Метавонем шумораи муассисаҳои ҳар минтақаро ҳисоб карда, натиҷаҳоро ҷобаҷо кунем, то минтақаҳои дорои муассисаҳои бештарро бинем.

SELECT region, count(*)  
FROM libraries_2024  
GROUP BY region  
ORDER BY count(*) DESC;  

Намунаи 9-9: Истифодаи GROUP BY бо count() барои сутуни region.

Акнун қиматҳои region ва шумораи сатрҳои дорои ҳар қимати онро талаб мекунем.

Дар рӯйхати SELECT сутуни region ва функсияи count(*)-ро нишон медиҳем. Ситорача боис мешавад ҳамаи сатрҳои гурӯҳ, новобаста аз қиматҳои NULL дар сутунҳо, ҳисоб шаванд.

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

Барои он ки минтақаи дорои муассисаҳои бештар дар боло бошад, аз ин истифода мебарем:

ORDER BY count(*) DESC  

Натиҷа:

Дар минтақаи Марказ, ки Шаҳри Нав дар он ҷойгир аст, муассисаҳо бештаранд. Ин тааҷҷубовар нест: дар боби 6 дидем, ки аксари аҳолӣ дар ҳамин шаҳр зиндагӣ мекунад.

Истифодаи GROUP BY барои чанд сутун бо count()

Бо якҷо кардани GROUP BY, count() ва чанд сутун метавон маълумоти боз ҳам бештар гирифт.

Масалан, дар ҳар се ҷадвал сутуни stataddr коде дорад, ки тағйир ёфтани суроғаи муассисаро дар соли гузашта нишон медиҳад:

Намунаи 9-10 шумораи муассисаҳои ҳар минтақаро аз рӯйи ҳамин ҳолатҳо ҳисоб мекунад:

SELECT region, stataddr, count(*)  
FROM libraries_2024  
GROUP BY region, stataddr  
ORDER BY region, stataddr;  

Намунаи 9-10: Истифодаи GROUP BY ва count() барои сутунҳои region ва stataddr.

Қисмҳои муҳими дархост инҳоянд:

• Номи ду сутун ва count(*) пас аз SELECT.
• Ҳузури ҳар ду сутун дар GROUP BY, то count() шумораи сатрҳои ҳар таркиби region ва stataddr-ро нишон диҳад.

Барои осон хондани натиҷа онро аввал аз рӯйи минтақа ва баъд аз рӯйи коди ҳолати суроға ҷобаҷо мекунем:

Натиҷа нишон медиҳад, ки коди 00 — тағйир наёфтани суроға — дар ҳар минтақа маъмултарин ҳолат аст.

Инро интизор ҳам мешудем: эҳтимол шумораи муассисаҳое, ки суроғаашонро иваз накардаанд, аз муассисаҳои кӯчида бештар аст.

Натиҷа ба мо итминон медиҳад, ки додаҳоро ба таври мантиқӣ таҳлил мекунем.

Агар коди 07 — кӯчидан ба ҷойи нав — дар ҳар минтақа маъмултарин мебуд, бояд мепурсидем: оё дархостро дуруст навиштаем ё дар додаҳо мушкиле ҳаст?

Бозгашт ба sum() барои омӯзиши фаъолияти китобхонаҳо

Акнун усулҳоямонро васеъ карда, гурӯҳбандӣ ва ҷамъбастро дар ҷадвалҳои пайвастшудаи солҳои 2024, 2023 ва 2022 истифода мебарем.

Ҳадаф муайян кардани тамоюли ташриф ба китобхонаҳо дар ҳамин се сол аст. Барои ин бояд бо функсияи sum() шумораи умумиро ҳисоб кунем.

Пеш аз навиштани дархостҳо, қиматҳои -3 ва -1-ро ба назар мегирем. Барои он ки ин ададҳои манфӣ ба таҳлил таъсир нарасонанд, бо WHERE танҳо сатрҳоеро мегирем, ки visits-и онҳо сифр ё аз сифр калонтар аст.

Аввал шумораи умумии солонаи ташрифҳоро аз ҳар ҷадвал алоҳида ҳисоб мекунем. Фармонҳои SELECT-и намунаи 9-11-ро ҷудогона иҷро кунед:

SELECT sum(visits) AS visits_2024  
FROM libraries_2024  
WHERE visits >= 0;

SELECT sum(visits) AS visits_2023  
FROM libraries_2023  
WHERE visits >= 0;

SELECT sum(visits) AS visits_2022  
FROM libraries_2022  
WHERE visits >= 0;  

Намунаи 9-11: Истифодаи sum() барои ҳисоб кардани шумораи умумии ташрифҳо дар солҳои 2022, 2023 ва 2024.

Натиҷаҳо:

Аз рӯйи ин ададҳо ташрифҳо соли 2023 зиёд шуда, соли 2024 кам шудаанд. Аммо шитоб накунед.

Дархостҳои боло ҳамаи ташрифҳои сабтшудаи ҳар ҷадвалро ҷамъ мекунанд. Аз ҳисобҳои аввали боб медонем, ки таркиби муассисаҳо дар ҷадвалҳо гуногун аст: соли 2023 китобхонаи калони донишҷӯён (L16) кушода шуд, соли 2024 китобхонаи наврасон (L15) пайдо шуд ва китобхонаи сайёр (L17) аз байн рафт.

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

Пас мебинем, ки агар таҳлилро танҳо бо муассисаҳое маҳдуд кунем, ки дар ҳар се ҷадвал вуҷуд доранд ва қимати visits-и онҳо манфӣ нест, натиҷа чӣ гуна тағйир меёбад.

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

SELECT sum(l24.visits) AS visits_2024,  
       sum(l23.visits) AS visits_2023,  
       sum(l22.visits) AS visits_2022  
FROM libraries_2024 l24  
JOIN libraries_2023 l23  
    ON l24.lib_code = l23.lib_code  
JOIN libraries_2022 l22  
    ON l24.lib_code = l22.lib_code  
WHERE l24.visits >= 0  
  AND l23.visits >= 0  
  AND l22.visits >= 0;  

Намунаи 9-12: Истифодаи sum() барои ҷамъ кардани ташрифҳо дар ҷадвалҳои пайвастшудаи солҳои 2024, 2023 ва 2022.

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

Дар боло барои сутуни visits-и ҳар се ҷадвал sum() истифода мебарем.

Ҷадвалҳоро аз рӯйи калидҳои асосиашон пайваст карда, номҳои мустаор муайян мекунем. Инро дар боби 7 омӯхта будем. Дар ин ҷо калимаи ихтиёрии AS-ро нанавиштаем.

Масалан, барои ҷадвали соли 2024 номи мустаори l24-ро истифода мебарем, то номи пурраи онро дар тамоми дархост такрор накунем.

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

Мисли намунаи 9-11, бо WHERE талаб мекунем, ки visits дар ҳар се ҷадвал сифр ё аз сифр калонтар бошад. Ин кодҳои манфиро аз ҷамъ хориҷ мекунад — ва ҳамроҳи онҳо китобхонаҳои Тозаоб ва Ширинкӯлро низ, зеро барои соли 2024 шумораи ташрифҳои онҳо маълум нест. Дар натиҷа 12 муассиса боқӣ мемонад.

Натиҷа:

Акнун тасвир тамоман дигар аст: дар муассисаҳои якхела ташрифҳо ҳар сол кам шудаанд. «Афзоиши» соли 2023, ки дар намунаи 9-11 дидем, танҳо аз кушода шудани китобхонаи нав буд.

Барои тасвири пурраи тағйироти истифодаи китобхонаҳо бояд барои ҳамаи сутунҳои нишондиҳандаҳои фаъолият дархостҳои монанд иҷро кунем ва тамоюли ҳар кадомро бинем.

Масалан, сутуни wifi_sessions шумораи пайвастшавиҳои корбарон ба интернети бесими китобхонаро нишон медиҳад.

Агар дар намунаи 9-12 ба ҷойи visits аз wifi_sessions истифода барем, чунин натиҷа мегирем:

Пас, гарчанде ташрифҳо кам шудаанд, истифодаи шабакаи Wi-Fi хеле афзудааст.

Ин ба мо дар бораи тағйир ёфтани нақши китобхонаҳо маълумоти муҳим медиҳад.

Эзоҳ: Гарчанде ҷадвалҳоро аз рӯйи lib_code пайваст кардем, дар додаҳои воқеӣ мумкин аст баъзе муассисаҳои дар ҳар се ҷадвал мавҷудбуда дар ин се сол якҷоя ё тақсим шуда бошанд. Хуб аст бо ташкилоти пурсишкунанда тамос гирифта, нуктаҳои эҳтиётии кор бо додаҳоро пурсед.

Гурӯҳбандии шумораи умумии ташрифҳо аз рӯйи минтақа

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

Шояд бипурсед: «Оё ин коҳиш дар ҳамаи қисмҳои кишвар рух додааст ё шиддати тамоюл аз минтақа вобаста буд?»

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

SELECT l24.region,  
       sum(l24.visits) AS visits_2024,  
       sum(l23.visits) AS visits_2023,  
       sum(l22.visits) AS visits_2022,  
       round(  
           (sum(l24.visits::numeric) - sum(l23.visits))  
           / sum(l23.visits) * 100,  
           1  
       ) AS chg_2024_23,  
       round(  
           (sum(l23.visits::numeric) - sum(l22.visits))  
           / sum(l22.visits) * 100,  
           1  
       ) AS chg_2023_22  
FROM libraries_2024 l24  
JOIN libraries_2023 l23  
    ON l24.lib_code = l23.lib_code  
JOIN libraries_2022 l22  
    ON l24.lib_code = l22.lib_code  
WHERE l24.visits >= 0  
  AND l23.visits >= 0  
  AND l22.visits >= 0  
GROUP BY l24.region  
ORDER BY chg_2024_23 DESC;  

Намунаи 9-13: Истифодаи GROUP BY барои пайгирии тағйироти фоизии ташрифҳо аз рӯйи минтақа.

Пас аз SELECT сутуни region-и ҷадвали соли 2024 меояд. Ҳамин сутун дар GROUP BY низ нишон дода шудааст.

Пас аз сутунҳои ташрифҳо, формулаи шиноси тағйироти фоизиро аз боби 6 меорем. Онро ду бор истифода карда, барои фаҳмо будани натиҷа чунин номҳо медиҳем:

• chg_2024_23 — тағйироти соли 2024 нисбат ба 2023.
• chg_2023_22 — тағйироти соли 2023 нисбат ба 2022.

Дар охир бо ORDER BY натиҷаро аз рӯйи chg_2024_23 ҷобаҷо мекунем.

Тамоюл дар ҳама ҷо якхела нест. Дар Ҷануб соли 2024 ташрифҳо 2,2 фоиз афзудаанд, дар ҳоле ки дар Шимол коҳиш ду сол пайдарпай идома дорад ва суръат гирифтааст.

Дар ҳамин марҳала бояд сабабҳои тағйиротро таҳқиқ кунем. Масалан, оё дар Ҷануб китобхонае таъмир шуд ё барномаи наве оғоз ёфт? Дар Шимол бошад, дар боби 7 дидем, ки аҳолии ноҳияи Кӯҳсор коҳиш ёфтааст.

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

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

Баъзан ӯ шарҳи хубе медиҳад. Дар ҳолатҳои дигар мутахассис мегӯяд: «Ин натиҷа дуруст наменамояд».

Чунин ҷавоб метавонад шуморо водор кунад, ки ба масъули додаҳо ё ҳуҷҷатҳо баргардед ва санҷед: оё код ё нозукии муҳимеро аз назар дур мондаед?

Полоиши дархости ҷамъбастӣ бо HAVING

Барои дақиқтар кардани таҳлил метавонем танҳо гурӯҳе аз минтақаҳои дорои хусусиятҳои монандро омӯзем.

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

Барои полоиши натиҷаи функсияҳои ҷамъбастӣ бояд аз банди HAVING, ки қисми стандарти ANSI SQL аст, истифода барем.

Шумо аллакай бо WHERE шиносед. Аммо функсияҳои ҷамъбастие ба монанди sum()-ро барои полоиши гурӯҳҳо мустақиман дар WHERE истифода бурда наметавонем.

Сабаб ин аст, ки WHERE сатрҳои алоҳидаро пеш аз гурӯҳбандӣ полоиш мекунад, дар ҳоле ки функсияҳои ҷамъбастӣ дар болои чанд сатр кор мекунанд.

HAVING ба гурӯҳҳои ҳосилшуда шарт мегузорад.

Намунаи 9-14 дархости қаблиро бо илова кардани HAVING пас аз GROUP BY тағйир медиҳад:

SELECT l24.region,  
       sum(l24.visits) AS visits_2024,  
       sum(l23.visits) AS visits_2023,  
       sum(l22.visits) AS visits_2022,  
       round(  
           (sum(l24.visits::numeric) - sum(l23.visits))  
           / sum(l23.visits) * 100,  
           1  
       ) AS chg_2024_23,  
       round(  
           (sum(l23.visits::numeric) - sum(l22.visits))  
           / sum(l22.visits) * 100,  
           1  
       ) AS chg_2023_22  
FROM libraries_2024 l24  
JOIN libraries_2023 l23  
    ON l24.lib_code = l23.lib_code  
JOIN libraries_2022 l22  
    ON l24.lib_code = l22.lib_code  
WHERE l24.visits >= 0  
  AND l23.visits >= 0  
  AND l22.visits >= 0  
GROUP BY l24.region  
HAVING sum(l24.visits) > 60000  
ORDER BY chg_2024_23 DESC;  

Намунаи 9-14: Истифодаи HAVING барои полоиши натиҷаи дархости ҷамъбастӣ.

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

Илова кардани HAVING шумораи сатрҳои натиҷаро ба ду мерасонад. Дар амал метавонед қиматҳои гуногунро бисанҷед.

Дар пурсиши воқеӣ бо садҳо гурӯҳ HAVING воситаи пурқувват аст: масалан, метавонистед танҳо иёлатҳо ё вилоятҳои калонро ҷудо карда, чунин ҷумла нависед:

«Дар байни минтақаҳои дорои шумораи бештари ташриф ба китобхонаҳо, Ҷануб ягона минтақае буд, ки аз соли 2023 то 2024 афзоиш дошт».

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

Хулоса

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

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

Бо пайваст кардани маҷмӯаҳои додаҳо тавонистед баъзе тамоюлҳои ҷолибро муайян кунед.

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

Мутаассифона, чунин мушкилот қисми кори ҳаррӯзаи таҳлилгари додаҳост.

Дар боби оянда бо имкониятҳои SQL-и муосир — CTE, дархостҳои рекурсивӣ ва функсияҳои равзанавӣ — шинос мешавед, ки дархостҳои мураккабро кӯтоҳтар ва хонданбобтар мекунанд.

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

1. Дар соли 2024 дар кадом шаҳрҳо беш аз як китобхона ҳаст? Аз GROUP BY ва HAVING истифода баред.
2. Шумораи умумии пайвастшавиҳо ба Wi-Fi-ро дар соли 2024 барои ҳар минтақа ҳисоб кунед. Кодҳои манфиро хориҷ кунед.
3. Миёнаи ташрифҳоро ба як китобхона дар ҳар минтақа барои соли 2024 ёбед. Чаро бояд WHERE visits >= 0-ро нависед?

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