Замима

Фармонҳои асосии psql

Фармонҳое, ки бо хатти моил ба чап — \ — оғоз мешаванд, фармонҳои худи psql мебошанд, на SQL. Онҳо бо нуқтаверг анҷом намеёбанд.

Ба psql-и дохили контейнер аз ҷузвдони postgres-learning пайваст мешавед:

docker compose exec db psql -U postgres -d analysis  

Файли SQL-ро аз компютери худ иҷро кардан мумкин аст: аввал онро ба контейнер нусха кунед, баъд бо -f иҷро намоед. Ин роҳ ҳам дар Terminal ва ҳам дар PowerShell кор мекунад:

docker compose cp script.sql db:/tmp/script.sql  
docker compose exec db psql -U postgres -d analysis -f /tmp/script.sql  

Тартиби қисмҳои SELECT

Қисмҳои дархост бояд бо ҳамин тартиб навишта шаванд. Ҳар қисм, ба ҷуз SELECT, ихтиёрӣ аст:

WITH ...              -- CTE, боби 10  
SELECT ...            -- сутунҳо ва ифодаҳо, боби 4  
FROM ...              -- ҷадвалҳо ва JOIN, боби 7  
WHERE ...             -- полоиши сатрҳо, боби 4  
GROUP BY ...          -- гурӯҳбандӣ, боби 9  
HAVING ...            -- полоиши гурӯҳҳо, боби 9  
ORDER BY ...          -- тартиб, боби 4  
LIMIT ...;            -- шумораи сатрҳо  

PostgreSQL онҳоро бо тартиби дигар иҷро мекунад: аввал FROM ва JOIN, баъд WHERE, GROUP BY, HAVING, SELECT ва дар охир ORDER BY ва LIMIT. Аз ин рӯ, номи мустаоре, ки дар SELECT бо AS медиҳед, дар ORDER BY кор мекунад, вале дар WHERE не.

Фармонҳои асосии SQL

Дар хотир доред: UPDATE ва DELETE бе WHERE ҳамаи сатрҳои ҷадвалро тағйир медиҳанд ё нест мекунанд. Агар боварӣ надошта бошед, фармонро дар дохили транзаксия иҷро кунед: натиҷаро бо SELECT санҷед ва баъд COMMIT ё ROLLBACK нависед.

Вақте ки дармондед

• Паёми хаторо бодиққат хонед. Он одатан ҷойи хаторо нишон медиҳад: LINE 3 ва нишонаи ^ зери ҷойи мушкил.
• Дархостро қисм-қисм санҷед. Аз SELECT * FROM table LIMIT 5 оғоз кунед ва қисмҳоро якто-якто илова намоед.
• Ҳуҷҷатҳои расмиро истифода баред. Ҳуҷҷатҳои PostgreSQL пурра ва дақиқанд. Барои ҳар фармон дар psql фармони \h кумак мекунад.
• Матни хаторо ҷустуҷӯ кунед. Матни паёмро айнан ҳамроҳи калимаи PostgreSQL ба низоми ҷустуҷӯ ворид кунед — эҳтимол касе бо ҳамин мушкил аллакай рӯ ба рӯ шудааст.
• Мушкилро ба шакли хурд оред. Ҷадвали хурде бо се-чор сатр созед, ки ҳамон хаторо такрор мекунад. Аксар вақт ҳангоми сохтани чунин мисол ҷавоб худ аз худ пайдо мешавад.

Барои омӯзиши минбаъда

• Ҳуҷҷатҳои PostgreSQL — махсусан бахши «Tutorial» ва «The SQL Language».
• Modern SQL — сомонаи Маркус Винанд дар бораи имкониятҳои нави стандарти SQL.
• Use The Index, Luke — китоби ройгони Маркус Винанд дар бораи индексҳо ва суръати дархостҳо.

Ҷавобҳо ба машқҳо

Дар SQL як масъаларо бо чанд роҳ ҳал кардан мумкин аст. Агар дархости шумо аз ҷавоби зерин фарқ кунад, вале ҳамон натиҷаро диҳад, он ҳам дуруст аст. Натиҷаҳо дар PostgreSQL 18 бо додаҳои бобҳо санҷида шудаанд.

Боби 3

1.

CREATE TABLE subjects (  
    id bigserial,  
    subject_name varchar(50),  
    hours_per_week integer  
);

INSERT INTO subjects (subject_name, hours_per_week)  
VALUES ('Математика', 5),  
       ('Забони тоҷикӣ', 4),  
       ('Информатика', 2);

SELECT * FROM subjects;  

2. PostgreSQL сатрро қабул намекунад:

ERROR:  invalid input syntax for type date: "дирӯз"  

Сутуни hire_date навъи date дорад ва танҳо санаро қабул мекунад. Навъи дода ҳамин тавр аз воридшавии додаҳои нодуруст муҳофизат мекунад.

3.

INSERT INTO subjects (subject_name)  
VALUES ('Расмкашӣ');  

Дар сутуни hours_per_week қимати NULL пайдо мешавад — қимат номаълум аст. Дар psql он ҳамчун ҷойи холӣ намоён мешавад. Сутуни id бошад, худкор пур мешавад.

Боби 4

1.

SELECT first_name, last_name, salary  
FROM teachers  
WHERE school = 'Myers Middle School'  
ORDER BY last_name;  

Натиҷа: Bush, Cole, Diaz.

2.

SELECT first_name, last_name, salary  
FROM teachers  
WHERE first_name LIKE 'S%'  
  AND salary > 40000;  

Танҳо Samuel Cole (43 500). Samantha Bush бо S оғоз мешавад, вале маошаш 36 200 аст.

3.

SELECT last_name, hire_date, salary  
FROM teachers  
WHERE hire_date >= '2010-01-01'  
ORDER BY salary DESC;  

Натиҷа: Roush (38 500), Smith ва Bush (36 200).

Боби 5

1. numeric(5,1): панҷ рақам дар маҷмӯъ, яке пас аз вергул — то 9999,9. Навъҳои real ва double precision ададҳоро тақрибан нигоҳ медоранд, бинобар ин ҳангоми ҷамъ кардани масофаҳо хатоҳои гирдкунӣ ҷамъ мешаванд.

2.

SELECT CAST('2024-03-08' AS date) + 10;  

Натиҷа 2024-03-18 аст. Ба date адади бутунро илова кунед, рӯз илова мешавад. Шакли CAST('2024-03-08' AS date) + interval '10 days' низ кор мекунад, вале timestamp бармегардонад: 2024-03-18 00:00:00.

3. Натиҷаи double precision 0.30000000000000004 ва натиҷаи numeric маҳз 0.3 аст. CAST('12.5' AS integer) хато медиҳад — invalid input syntax for type integer: "12.5" — зеро сатр адади бутун нест. Аввал ба numeric табдил диҳед: CAST('12.5' AS numeric).

Боби 6

1.

SELECT district_name,  
       round(  
           (births_2024 - deaths_2024)::numeric / pop_2023 * 100,  
           2  
       ) AS natural_pct  
FROM districts  
ORDER BY natural_pct DESC  
LIMIT 3;  

Натиҷа: Регзор (2.19), Ширинкӯл (2.16), Боғистон (2.06). Калимаи LIMIT шумораи сатрҳои натиҷаро маҳдуд мекунад.

2.

SELECT round(avg(births_2024), 0) AS avg_births,  
       percentile_cont(.5)  
           WITHIN GROUP (ORDER BY births_2024) AS median_births  
FROM districts;  

Миёна 2693 ва медиана 957.5 аст. Шаҳри Нав бо 21 400 таваллуд миёнаро ба боло мекашад — ҳамон ҳолате, ки бо аҳолӣ дидем.

3.

SELECT round(  
           (SELECT pop_2024 FROM districts  
            WHERE district_name = 'Шаҳри Нав')::numeric  
           / sum(pop_2024) * 100,  
           1  
       ) AS pct  
FROM districts;  

Тақрибан 69.5 фоиз. Дар қавсҳо зердархост омадааст — дархосте дар дохили дархости дигар, ки як қимат бармегардонад.

Боби 7

1.

SELECT d.district_name, p.pop_2020  
FROM districts AS d  
LEFT JOIN districts_pop_2020 AS p  
    ON d.district_name = p.district_name  
   AND d.region_name = p.region_name  
WHERE p.district_name IS NULL;  

Натиҷа холӣ аст: ҳар 12 ноҳия дар ҳар ду ҷадвал мувофиқ доранд.

2.

SELECT d.district_name,  
       d.pop_2024 - p.pop_2020 AS raw_change  
FROM districts AS d  
JOIN districts_pop_2020 AS p  
    ON d.district_name = p.district_name  
   AND d.region_name = p.region_name  
WHERE d.pop_2024 < p.pop_2020;  

Натиҷа: Кӯҳсор (−710) ва Санглох (−669).

3.

SELECT region_name FROM districts  
UNION  
SELECT region_name FROM districts_pop_2020;  

UNION такрорҳоро хориҷ мекунад ва 3 сатр медиҳад. Бо UNION ALL ҳамаи 24 сатр — 12 аз ҳар ҷадвал — боқӣ мемонанд.

Боби 8

1. Яке аз ҷавобҳои имконпазир. Агар ҷадвалҳои машқро аллакай сохта бошед, аввал онҳоро нест кунед — songs-ро пеш аз albums:

DROP TABLE IF EXISTS songs, albums;

CREATE TABLE albums (  
    album_id bigint GENERATED ALWAYS AS IDENTITY,  
    catalog_code text NOT NULL,  
    title text NOT NULL,  
    artist text NOT NULL,  
    release_date date,  
    genre text,  
    description text,  
    CONSTRAINT album_id_key PRIMARY KEY (album_id),  
    CONSTRAINT catalog_code_unique UNIQUE (catalog_code),  
    CONSTRAINT release_date_check CHECK (release_date > '1925-01-01')  
);

CREATE TABLE songs (  
    song_id bigint GENERATED ALWAYS AS IDENTITY,  
    title text NOT NULL,  
    composers text NOT NULL,  
    album_id bigint NOT NULL REFERENCES albums (album_id),  
    CONSTRAINT song_id_key PRIMARY KEY (song_id)  
);  

Ҳар ду ҷадвал калиди сунъии асосӣ доранд. songs.album_id калиди хориҷӣ аст, то суруд ба албоми мавҷуднабуда ишора накунад. Ном, иҷрокунанда ва оҳангсозон ҳатмӣ (NOT NULL) мебошанд. CHECK санаҳои бемантиқро рад мекунад: пластинкаҳои LP пеш аз асри XX вуҷуд надоштанд.

2. catalog_code — коди каталогии пластинка — метавонад калиди табиӣ бошад. Аммо бояд санҷед, ки он воқеан беназир аст: ширкатҳои гуногуни сабт метавонанд кодҳои якхела истифода баранд ва нашри дубораи як албом метавонад коди нав гирад. Агар шубҳа бошад, калиди сунъиро нигоҳ доред ва ба catalog_code маҳдудияти UNIQUE гузоред.

3. songs.album_id — калидҳои хориҷӣ худкор индекс намегиранд, вале дар JOIN ва несткунии каскадӣ истифода мешаванд. Инчунин albums.title, albums.artist ва songs.title, агар аз рӯйи онҳо зуд-зуд ҷустуҷӯ кунед. Калидҳои асосӣ ва сутунҳои UNIQUE аллакай индекс доранд.

CREATE INDEX songs_album_id_idx ON songs (album_id);  

Боби 9

1.

SELECT city, count(*)  
FROM libraries_2024  
GROUP BY city  
HAVING count(*) > 1  
ORDER BY count(*) DESC;  

Натиҷа: Шаҳри Нав (4) ва Гулдара (2).

2.

SELECT region, sum(wifi_sessions) AS wifi_2024  
FROM libraries_2024  
WHERE wifi_sessions >= 0  
GROUP BY region  
ORDER BY wifi_2024 DESC;  

Натиҷа: Марказ 410 760, Ҷануб 34 500, Шимол 19 200.

3.

SELECT region,  
       count(*) AS libraries,  
       round(avg(visits), 0) AS avg_visits  
FROM libraries_2024  
WHERE visits >= 0  
GROUP BY region  
ORDER BY avg_visits DESC;  

Натиҷа: Марказ 124 343, Ҷануб 20 550, Шимол 18 803. Бе WHERE кодҳои -1 ва -3 ҳамчун ташрифҳои воқеӣ ба ҳисоб дохил мешуданд ва миёнаро паст мекарданд.

Боби 10

1.

SELECT user_id,  
       sum(play_duration) AS total,  
       RANK() OVER (ORDER BY sum(play_duration) DESC) AS user_rank  
FROM streaming.plays  
GROUP BY user_id  
ORDER BY user_rank;  

Натиҷа: корбари 3 (2967 сония), 2 (1796), 1 (1157). Функсияи равзанавӣ пас аз GROUP BY иҷро мешавад, бинобар ин метавонад бо натиҷаи sum() кор кунад.

2.

WITH full_plays AS (  
    SELECT p.song_id  
    FROM streaming.plays p  
    JOIN streaming.songs s ON p.song_id = s.id  
    WHERE p.play_duration = s.duration  
)  
SELECT title  
FROM streaming.songs  
WHERE id NOT IN (SELECT song_id FROM full_plays)  
ORDER BY id;  

Ҳафт суруд: Song B, C, E, F, H, I ва J.

3.

WITH RECURSIVE play_sequence (parent_id, total_duration) AS (  
    SELECT id, play_duration  
    FROM streaming.plays  
    WHERE id = 13

    UNION ALL

    SELECT p.id, ps.total_duration + p.play_duration  
    FROM streaming.plays p  
    JOIN play_sequence ps ON p.played_after = ps.parent_id  
)  
SELECT count(*) AS songs,  
       max(total_duration) AS total_duration  
FROM play_sequence;  

Пайдарпайӣ 5 суруд ва 920 сония дорад.

Боби 11

1. Аввал бо SELECT * FROM accounts; бинед, ки Alice ва Bob кадом id доранд. Агар ҳамаи мисолҳои бобро як бор иҷро карда бошед, Bob id = 1 ва Alice id = 2 дорад:

BEGIN;

UPDATE accounts  
SET balance = balance - 500  
WHERE id = 2;

UPDATE accounts  
SET balance = balance + 500  
WHERE id = 1;

COMMIT;  

Пас аз ҳар UPDATE бояд UPDATE 1 бинед. Агар UPDATE 0 бошад — масалан, id нодуруст аст — ба ҷойи COMMIT ROLLBACK нависед. Пас аз интиқол Alice 10 500 ва Bob 9 500 дорад.

2. Дар дохили транзаксия SELECT count(*) FROM accounts; 0 нишон медиҳад. Пас аз ROLLBACK ҳамаи сатрҳо бармегарданд — DELETE ҳеҷ гоҳ тасдиқ нашуда буд.

Боби 12

1.

CREATE INDEX libraries_2024_region_idx  
ON libraries_2024 (region);

EXPLAIN SELECT * FROM libraries_2024 WHERE region = 'Шимол';  

Нақша Seq Scan on libraries_2024 аст — индекс истифода намешавад. Ҷадвал ҳамагӣ 16 сатр дорад ва дар як саҳифа ҷой мегирад; хондани он аз хондани индекс ва баъд ҷадвал арзонтар аст.

2.

CREATE TABLE posts (  
    id integer PRIMARY KEY,  
    title text,  
    tags text[]  
);

INSERT INTO posts  
VALUES (1, 'SQL барои навомӯзон', ARRAY['sql', 'postgres']),  
       (2, 'Индексҳо', ARRAY['postgres', 'index']),  
       (3, 'Python', ARRAY['python']);

CREATE INDEX posts_tags_idx  
ON posts USING gin (tags);

SELECT title  
FROM posts  
WHERE tags @> ARRAY['postgres'];  

Натиҷа: «SQL барои навомӯзон» ва «Индексҳо». Барои массивҳо индекси GIN мувофиқ аст.

3. Не. Дар боби 8 индекс танҳо барои street сохта шуд. EXPLAIN барои house_number = 42 Parallel Seq Scan on addresses-ро нишон медиҳад.