Боби 10. SQL-и муосир

SQL дар аввали солҳои 1970 ихтироъ шудааст, яъне пеш аз пайдоиши Altair — яке аз аввалин компютерҳои шахсӣ. Бо вуҷуди он ки аз пайдоишаш даҳсолаҳо гузаштаанд, SQL мисли бисёр забонҳои инсонӣ барои ҷавоб додан ба талаботи ҷаҳони муосир рушд карданро идома медиҳад.

Аммо аксари одамон ҳанӯз бо SQL дар шакле шиносанд, ки стандарти SQL-92 муайян кардааст. Ин стандарт модели ибтидоии релятсионӣ ва ғояҳои онро пурра шакл дод. Вале пас аз ҷорӣ шудани он тағйироти зиёде ба амал омаданд.

Доираи истифодаи пойгоҳҳои додаҳои релятсионӣ аз модели классикии релятсионӣ хеле фаротар рафтааст. Ин тағйирот дар версияҳои баъдии забон низ инъикос ёфтаанд.

SQL-и муосир имкониятҳои нав медиҳад, ки на танҳо забонро хонданбобтар ва фаҳмотар мекунанд, балки ҳисобҳои мураккабро осон сохта, кор бо додаҳои бесохтор ва нимсохторёфтаро низ имконпазир мегардонанд.

Биёед имкониятҳои SQL-и муосирро дар Postgres ҳангоми кор бо хидмати пахши мусиқӣ омӯзем. Ҳазорон нафар метавонанд тавассути чунин хидмат сурудҳо ва ҳунармандони дӯстдоштаашонро гӯш кунанд.

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

SQL-и муосир чист?

Истилоҳи SQL-и муосир — modern SQL-ро Маркус Винанд нисбатан ба наздикӣ маъмул кардааст.

Маркус мутахассиси маъруфи пойгоҳҳои додаҳост. Ӯ барои шинос кардани таҳиягарон бо имкониятҳое, ки пас аз SQL-92 ба стандарт илова шудаанд, талош мекунад.

Ӯ аҳаммияти ин тағйиротро дар сомонаи худ, ки ба SQL-и муосир бахшида шудааст, чунин шарҳ медиҳад:

Аз соли 1999 SQL дигар танҳо бо модели релятсионӣ маҳдуд нест. Он вақт стандарти ISO/IEC 9075 — «стандарти SQL» — массивҳо, объектҳо ва дархостҳои рекурсивиро илова кард. Аз он вақт ҳаҷми стандарти SQL нисбат ба SQL-92 панҷ баробар калон шудааст. Ба ибораи дигар, SQL-и релятсионӣ танҳо тақрибан 20 фоизи SQL-и муосирро ташкил медиҳад.

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

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

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

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

Бо ин ҳама бартарӣ, чаро SQL-и муосир ҳанӯз дар маҷмӯи абзорҳои ҳар таҳиягар нест? Ҳадди ақал чанд сабаб вуҷуд дорад.

Одат ба дониши пешина. Баъзе таҳиягарон SQL-ро солҳои зиёд пеш омӯхта, версияи SQL-92-ро барои вазифаҳои гуногуни коркарди додаҳо хуб аз худ кардаанд. Ҳатто агар дархостҳояшон дароз ё камсамар бошанд ҳам, вазифа ҳал мешавад. Аз ин рӯ, онҳо корро бо ҳамон усули пешина идома медиҳанд.

Чаҳорчӯбаҳои ORM. Баъзе таҳиягарон пурра ба ORM — воситаҳои харитасозии объектӣ-релятсионӣ — ҳамчун қабати байни барнома ва пойгоҳи додаҳо такя мекунанд. Онҳо тавлиди дархостҳои SQL-ро ба ORM месупоранд ва бовар мекунанд, ки он роҳи беҳтаринро медонад. Вале ORM-ҳо барои вазифаҳои умумӣ тарҳрезӣ шудаанд. Онҳо на ҳамеша мефаҳманд, ки дар ҳолати мушаххаси шумо ба ҷойи пайвасткунии мураккаби ҷадвал бо худаш функсияи равзанавӣ самараноктар аст.

Ҳадафи ин боб ворид кардани SQL-и муосир ба маҷмӯи абзорҳои шумост.

Мо истифодаи CTE, дархостҳои рекурсивӣ ва функсияҳои равзанавиро дар Postgres меомӯзем.

Барои омӯзиши бештар ба сомонаи Modern SQL-и Маркус Винанд муроҷиат кунед.

Бор кардани маҷмӯи додаҳои хидмати мусиқӣ

Имкониятҳои SQL-и муосирро бо мисоли хидмати пахши мусиқӣ меомӯзем, ки дар он корбарон сурудҳо ва ҳунармандони дӯстдоштаи худро гӯш мекунанд.

Схемаи пойгоҳи додаҳо хеле сода аст: се ҷадвал дар схемаи streaming.

streaming.users          streaming.plays               streaming.songs  
---------------          ----------------------        ---------------  
id        (PK) ◄──────── user_id                ┌────► id        (PK)  
name                     song_id ───────────────┘      title  
location                 id              (PK)          duration  
                         play_start_time  
                         play_duration  
                         played_after ──> plays.id  

Ҷадвалҳои songs ва users вазифаи фаҳмо доранд: онҳо маълумоти сурудҳои дастрас ва корбарони хидматро нигоҳ медоранд.

Ҷадвали plays сабт мекунад, ки корбарон кадом сурудҳоро гӯш кардаанд.

Ҳар боре корбар суруди навро пахш мекунад, ба plays сабти наве бо маълумоти зерин илова мешавад:

Эзоҳи тарҷумон: Дар муқаддимаи матни аслӣ сутуни охир previous_play номида шудааст, вале дар сохтори ҷадвал ва ҳамаи мисолҳои минбаъда номи он played_after аст. Барои иҷрои мисолҳо аз played_after истифода кунед.

Додаҳо дар анбори коди китоби Денис Магда Just Use Postgres! дар GitHub ҷойгиранд (иҷозатномаи Apache 2.0). Барои бор кардани онҳо ба PostgreSQL-и дар боби 2 оғозкардаатон ин қадамҳоро иҷро кунед.

1. Ба ҷузвдони postgres-learning гузаред ва анбори кодро нусхабардорӣ кунед:

cd postgres-learning  
git clone https://github.com/dmagda/just-use-postgres-book  

Агар git насб накарда бошед, ду файли streaming_ddl.sql ва streaming_data.sql-ро аз ҷузвдони data/streaming-и анбори код дастӣ боргирӣ кунед.

2. Файлҳои SQL-ро дар пойгоҳи додаҳои analysis иҷро кунед:

Аввал файлҳоро ба дохили контейнер нусха мекунем ва баъд бо параметри -f-и psql иҷро менамоем:

docker compose cp just-use-postgres-book/data/streaming/streaming_ddl.sql db:/tmp/  
docker compose cp just-use-postgres-book/data/streaming/streaming_data.sql db:/tmp/

docker compose exec db psql -U postgres -d analysis -f /tmp/streaming_ddl.sql  
docker compose exec db psql -U postgres -d analysis -f /tmp/streaming_data.sql  

Ин фармонҳо дар Terminal-и macOS ва Linux ва дар PowerShell-и Windows якхела кор мекунанд.

Файли аввал схемаи streaming ва се ҷадвалро месозад, дуюм онҳоро бо додаҳо пур мекунад.

3. Ба PostgreSQL пайваст шуда, ҷадвалҳои сохташударо санҷед:

docker compose exec db psql -U postgres -d analysis  

Сипас дар дохили psql:

\dt streaming.*  

Натиҷа:

Акнун, ки додаҳо омодаанд, омӯзиши SQL-и муосирро аз CTE оғоз мекунем.

Ифодаҳои умумии ҷадвалӣ — CTE

CTE — Common Table Expression имкон медиҳад дархости калон ё мураккабро ба қисмҳои хурдтар ва идорашаванда ҷудо кунем.

Он бо банди WITH чунин муайян мешавад:

WITH cte_name AS (  
    auxiliary_statement  
)  
primary_statement;  

Банди WITH CTE-и номдор месозад, ки фармони асосӣ баъдтар ба он муроҷиат карда метавонад.

Дар дохили CTE фармони ёрирасон иҷро мешавад. Он метавонад дархости одии SELECT барои хондани додаҳо ё фармони тағйирдиҳандаи додаҳо, ба монанди INSERT, UPDATE, DELETE ё MERGE бошад.

Натиҷаи он барои фармони асосӣ дастрас мешавад.

Фармони асосӣ низ метавонад SELECT ё фармони тағйирдиҳандаи додаҳо бошад, ки натиҷаи CTE-ро истифода мебарад.

Умуман, метавонед CTE-ро ҳамчун ҷадвал ё намои муваққатие тасаввур кунед, ки танҳо дар доираи ҳамин дархост истифода мешавад.

Биёед ба хидмати мусиқии худ баргашта, CTE-ро дар амал омӯзем.

Интихоби додаҳо бо CTE

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

Намунаи зерин нишон медиҳад, ки чунин гузоришро бо CTE чӣ гуна месозем.

Намунаи 10-1: Пайгирии сурудҳои маъмултарин

WITH plays_cte AS (  
    SELECT s.title, s.duration  
    FROM streaming.plays p  
    JOIN streaming.songs s ON p.song_id = s.id  
    WHERE p.play_start_time::DATE  
          BETWEEN '2024-09-15' AND '2024-09-16'  
      AND p.play_duration = s.duration  
)  
SELECT title, COUNT(*) AS play_count  
FROM plays_cte  
GROUP BY title  
ORDER BY play_count DESC;  

Дархост CTE-и plays_cte-ро муайян мекунад. Он ҳамаи сурудҳоеро мегирад, ки корбарон аз 15 то 16 сентябри соли 2024 то охир гӯш кардаанд:

p.play_duration = s.duration  

Ҷадвали songs маълумоти сурудҳо ва plays муддати гӯш кардани онҳоро нигоҳ медорад.

Фармони асосӣ аз натиҷаи CTE истифода мебарад:

SELECT ...  
FROM plays_cte  

Сипас бо COUNT(*) шумораи пахши ҳар сурудро ҳисоб карда, натиҷаҳоро ҷобаҷо мекунад.

Натиҷа:

Эзоҳ: Вобаста ба мураккабии дархост, Postgres метавонад CTE-ро ба фармони асосӣ ҳамроҳ кунад ё дархостро ба шакли самараноктар табдил диҳад.

Масалан, дар намунаи 10-1 CTE барои омӯзиш истифода шудааст. Ин дархостро бо як SELECT ҳам навиштан мумкин аст. Postgres метавонад инро шинохта, нақшаи мувофиқи иҷро созад. Барои санҷиш аз EXPLAIN ANALYZE истифода баред.

Ҳамон тавре ки мантиқи равшани барномаро осон мехонед ва тағйир медиҳед, дархостҳои CTE-дорро низ осон мутобиқ карда метавонед.

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

Дархости навбатӣ бо чанд тағйир ба дархости пешина ин рейтингро месозад.

Намунаи 10-2: Ёфтани сурудҳои каммаъруфтарин

WITH plays_cte AS (  
    SELECT s.title, s.duration, p.play_duration  
    FROM streaming.plays p  
    JOIN streaming.songs s ON p.song_id = s.id  
    WHERE p.play_start_time::DATE  
          BETWEEN '2024-09-15' AND '2024-09-16'  
      AND p.play_duration < (s.duration / 2)  
)  
SELECT title, MIN(play_duration) AS min_play_duration  
FROM plays_cte  
GROUP BY title  
ORDER BY min_play_duration ASC  
LIMIT 3;  

Мисли пештара, plays_cte рӯйхати сурудҳо ва муддати пахши онҳоро дар фосилаи муайяншуда месозад.

Аммо ин дафъа танҳо сурудҳое интихоб мешаванд, ки камтар аз нисфи давомнокиашон гӯш карда шудаанд:

p.play_duration < (s.duration / 2)  

Дар ин мисол онҳоро ҳамчун сурудҳои каммаъруф ҳисоб мекунем.

Фармони асосӣ аз plays_cte се сурудеро интихоб мекунад, ки кӯтоҳтарин муддати гӯшкуниро доранд:

MIN(play_duration)  

Натиҷа, бо муддат дар сония:

Истифодаи чанд CTE дар як дархост

То ин ҷо дархостҳо танҳо як CTE доштанд. Вале як дархост метавонад чанд CTE дошта бошад ва ҳар CTE-и баъдӣ метавонад ба CTE-ҳои пеш аз худ муроҷиат кунад.

Фарз мекунем меъёри намунаи 10-2 нисбат ба сурудҳои навнашр чандон одилона нест.

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

Инро бо шакли зерин иҷро кардан мумкин аст.

Намунаи 10-3: Ёфтани сурудҳои каммаъруф аз рӯйи рафтори се ё зиёда корбар

WITH plays_cte AS (  
    SELECT s.title, s.duration, p.play_duration, p.user_id  
    FROM streaming.plays p  
    JOIN streaming.songs s ON p.song_id = s.id  
    WHERE p.play_start_time::DATE  
          BETWEEN '2024-09-15' AND '2024-09-16'  
      AND p.play_duration < (s.duration / 2)  
),  
user_play_counts AS (  
    SELECT title,  
           duration,  
           COUNT(DISTINCT user_id) AS user_count,  
           MIN(play_duration) AS min_play_duration,  
           COUNT(*) AS total_play_count  
    FROM plays_cte  
    GROUP BY title, duration  
)  
SELECT title, duration, min_play_duration, total_play_count  
FROM user_play_counts  
WHERE user_count >= 3  
ORDER BY min_play_duration ASC  
LIMIT 3;  

CTE-и plays_cte ҳамон мантиқи пешинаро дорад: сурудҳоеро мегирад, ки камтар аз нисфи давомнокиашон гӯш карда шудаанд. Барои ҳисоб кардани корбарон ин дафъа user_id низ интихоб шудааст.

CTE-и user_play_counts натиҷаи plays_cte-ро хонда, барои ҳар суруд инҳоро ҳисоб мекунад:

• Шумораи корбарони беназир.
• Кӯтоҳтарин муддати гӯшкунӣ.
• Шумораи умумии чунин пахшҳо.

Фармони асосӣ танҳо сурудҳоеро мегирад, ки на камтар аз се корбари беназир доранд:

WHERE user_count >= 3  

Натиҷа:

Дархост ба ҳадаф мерасад ва мантиқи он хонданбоб аст.

Аммо аз рӯйи намуди код чунин менамояд, ки Postgres барои натиҷаи ниҳоӣ бояд се SELECT-и ҷудогона иҷро кунад. Оё зердархостҳо ё як SELECT самараноктар нестанд?

Ҷавоб аз вазъият вобаста аст. То нақшаҳои иҷроро муқоиса накунем, қатъӣ гуфта наметавонем.

Аммо як чиз равшан аст: Postgres на ҳамеша дархостро айнан бо тартиби навишташуда иҷро мекунад.

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

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

Онро дар боби 8 бо индексҳо истифода бурдем. Ҳоло ба аввали дархости намунаи 10-3 инро илова кунед:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)  

Нақшаи иҷро чунин мешавад:

Limit (actual rows=1 loops=1)  
  -> Sort (actual rows=1 loops=1)  
       Sort Key: user_play_counts.min_play_duration  
       Sort Method: quicksort  Memory: 25kB  
       -> Subquery Scan on user_play_counts (actual rows=1 loops=1)  
            -> GroupAggregate (actual rows=1 loops=1)  
                 Group Key: s.title, s.duration  
                 Filter: (count(DISTINCT p.user_id) >= 3)  
                 Rows Removed by Filter: 5  
                 -> Sort (actual rows=9 loops=1)  
                      Sort Key: s.title, s.duration, p.user_id  
                      Sort Method: quicksort  Memory: 25kB  
                      -> Hash Join (actual rows=9 loops=1)  
                           Hash Cond: (s.id = p.song_id)  
                           Join Filter:  
                             (p.play_duration < (s.duration / 2))  
                           Rows Removed by Join Filter: 27  
                           -> Seq Scan on songs s  
                                (actual rows=10 loops=1)  
                           -> Hash (actual rows=36 loops=1)  
                                Buckets: 1024  Batches: 1  
                                Memory Usage: 10kB  
                                -> Seq Scan on plays p  
                                     (actual rows=36 loops=1)  
                                     Filter:  
                                       (  
                                         play_start_time::date  
                                           >= '2024-09-15'::date  
                                         AND  
                                         play_start_time::date  
                                           <= '2024-09-16'::date  
                                       )  

Гарчанде plays_cte дар аввали дархост муайян шудааст, он ҳамчун амали алоҳида дар нақша нест. Postgres мантиқи онро ба қисми дигари дархост ҳамроҳ кардааст.

Марҳалаҳои асосӣ чунинанд:

Hash Join. Пойгоҳи додаҳо ҷадвалҳои plays ва songs-ро пайваст карда, танҳо сатрҳои мувофиқ ба шартҳоро мегирад.

GroupAggregate. Натиҷаи пайвасткунӣ ҷобаҷо ва аз рӯйи номи суруд ва давомнокии он гурӯҳбандӣ мешавад. Шарти на камтар аз се корбар низ дар ҳамин марҳала санҷида мешавад:

COUNT(DISTINCT p.user_id) >= 3  

Subquery Scan, Sort ва Limit. Натиҷаи гурӯҳбандӣ барои қисми берунӣ дастрас шуда, аз рӯйи кӯтоҳтарин муддати гӯшкунӣ ҷобаҷо ва маҳдуд карда мешавад.

Ҳамин тавр, дарозтар будани нусхаи CTE-дори дархост маънои камсамартар будани онро надорад.

Дар намунаи 10-3 Postgres ҷадвалҳои plays ва songs-ро як бор аз назар гузаронда, додаҳоро мувофиқи шартҳои ду CTE ва фармони асосӣ полоиш, ҷобаҷо ва гурӯҳбандӣ мекунад.

Ҳангоми интихоби зердархост ё CTE усулеро интихоб кунед, ки мантиқи SQL-ро барои шумо ва дастаатон равшантар мекунад.

Дар бисёр ҳолатҳо Postgres метавонад CTE-ҳоро бо ҳам ва бо фармони асосӣ самаранок якҷо кунад. Бо ин роҳ хонданбобӣ беҳтар мешавад ва суръат низ нигоҳ дошта мешавад.

Эзоҳ: Агар ба CTE аз чанд ҷой муроҷиат шавад, Postgres одатан метавонад онро як бор ҳисоб карда, натиҷаро барои истифодаи минбаъда нигоҳ дорад. Инро моддӣ гардондан — materialization меноманд.

Барои талаб кардани чунин рафтор:

WITH cte_name AS MATERIALIZED (  
    ...  
)  

Барои иҷозаи ҳамроҳ кардани CTE ба дархости берунӣ:

WITH cte_name AS NOT MATERIALIZED (  
    ...  
)  

Эзоҳи тарҷумон: Дар матни аслӣ NON MATERIALIZED омадааст. Синтаксиси дурусти PostgreSQL — NOT MATERIALIZED аст. Ҳамчунин дар нақшаи боло Subquery Scan худ аз худ маънои моддӣ гардондани CTE-ро надорад.

Тағйир додани додаҳо бо CTE

Ғайр аз хондани додаҳо, CTE метавонад онҳоро тағйир диҳад.

Чунин CTE дар дохили WITH фармонҳои INSERT, UPDATE, DELETE ё MERGE-ро истифода мебарад ва метавонад сабтҳои иловашуда, навшуда ё нестшударо ба фармони асосӣ диҳад.

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

Намунаи 10-4: CTE-и тағйирдиҳандаи додаҳо

WITH updated_play AS (  
    UPDATE streaming.plays  
    SET play_duration = 200  
    WHERE id = 30  
    RETURNING song_id, play_duration  
)  
SELECT s.title,  
       s.duration,  
       CASE  
           WHEN up.play_duration = s.duration  
               THEN 'Moved Up the Rank'  
           ELSE 'Rank Not Changed'  
       END AS rank_change_status  
FROM updated_play up  
JOIN streaming.songs s ON s.id = up.song_id;  

Дархост чунин амал мекунад:

1. CTE дар ҷадвали plays барои ҷаласаи id = 30 қимати play_duration-ро ба 200 сония тағйир медиҳад. Агар давомнокии пурраи суруд 200 сония бошад, яъне корбар онро то охир гӯш кардааст.
2. Бо RETURNING қиматҳои song_id ва play_duration-и навро ба фармони асосӣ медиҳад.
3. Фармони асосӣ месанҷад, ки муддати гӯшкунӣ ба давомнокии пурраи суруд баробар аст ё не.

Натиҷа:

Moved Up the Rank маънои «Дар рейтинг боло рафт»-ро дорад.

Эзоҳи тарҷумон: Ин дархост воқеан рейтингро аз нав ҳисоб намекунад. Он танҳо пурра гӯш шудани сурудро месанҷад ва мувофиқи ин шарт паёми намунавӣ медиҳад.

Маслиҳат: Агар ҳамаи сутунҳои сабтҳои тағйирёфтаро баргардондан хоҳед, аз ин истифода баред:

RETURNING *  

Агар фармони асосӣ ба натиҷаи CTE эҳтиёҷ надошта бошад, RETURNING-ро нанавиштан мумкин аст.

Хусусияти дигари муҳим ин аст, ки CTE-и тағйирдиҳандаи додаҳо ҳатто вақте иҷро мешавад, ки фармони асосӣ ба натиҷаи он муроҷиат намекунад.

Ин аз CTE-и одии SELECT фарқ мекунад: CTE-и хонданӣ, ки натиҷааш лозим нест, метавонад умуман иҷро нашавад.

Як дархост метавонад чанд CTE-и хонданӣ ва тағйирдиҳанда дошта бошад. Вале тартиби навиштани онҳо кафолат намедиҳад, ки яке тағйироти дигареро дар худи ҷадвал мебинад. Онҳо аз як нусхаи лаҳзавии додаҳо истифода мекунанд.

Биёед инро бо нав кардан ва хондани play_duration барои ҷаласаи id = 12 бинем.

Аввал қимати ҷориро мегирем:

SELECT song_id, play_duration  
FROM streaming.plays  
WHERE id = 12;  

Ҳоло ин ҷаласа ба суруди song_id = 3 тааллуқ дорад ва корбар 118 сония гӯш кардааст:

Акнун қиматро нав карда, месанҷем, ки CTE-и баъдӣ тағйиротро мебинад ё не.

Намунаи 10-5: CTE-ҳои хонданӣ ва тағйирдиҳанда бо як нусхаи лаҳзавии додаҳо

WITH updated_play AS (  
    UPDATE streaming.plays  
    SET play_duration = 150  
    WHERE id = 12  
    RETURNING song_id, play_duration  
),  
current_play_duration AS (  
    SELECT song_id,  
           (play_duration = 150) AS is_change_visible_to_cte  
    FROM streaming.plays  
    WHERE id = 12  
)  
SELECT is_change_visible_to_cte,  
       (play_duration = 150) AS is_change_visible_to_primary  
FROM updated_play up  
JOIN current_play_duration cp ON up.song_id = cp.song_id;  

updated_play муддати пахшро ба 150 сония тағйир медиҳад.

current_play_duration ҳамин сабтро аз ҷадвали streaming.plays мехонад ва месанҷад, ки қимат 150 шудааст ё не.

Фармони асосӣ натиҷаҳои ҳар ду CTE-ро пайваст карда, нишон медиҳад, ки тағйирот барои онҳо намоён буд ё не.

Натиҷа:

Дар psql:

• f маънои false — нодурустро дорад.
• t маънои true — дурустро дорад.

Яъне current_play_duration, ки ҷадвали аслиро мехонад, тағйироти ҳамин фармонро надидааст. Аммо фармони асосӣ қимати навро аз натиҷаи RETURNING гирифтааст.

Барои он ки CTE-и дуюм қимати навро бинад, бояд ба натиҷаи updated_play мустақиман муроҷиат кунад.

Намунаи 10-6: Хондани натиҷаи CTE-и тағйирдиҳанда

WITH updated_play AS (  
    UPDATE streaming.plays  
    SET play_duration = 160  
    WHERE id = 12  
    RETURNING id, song_id, play_duration  
),  
current_play_duration AS (  
    SELECT song_id,  
           (play_duration = 160) AS is_change_visible_to_cte  
    FROM updated_play  
    WHERE id = 12  
)  
SELECT is_change_visible_to_cte,  
       (play_duration = 160) AS is_change_visible_to_primary  
FROM updated_play up  
JOIN current_play_duration cp ON up.song_id = cp.song_id;  

Ин дафъа current_play_duration натиҷаи updated_play-ро мустақиман мехонад ва қимати навро мебинад:

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

Акнун меомӯзем, ки CTE чӣ гуна дархостҳои рекурсивиро барои коркарди сохторҳои иерархӣ ва дарахтмонанд имкон медиҳад.

Дархостҳои рекурсивӣ

Дархости рекурсивӣ навъи махсуси CTE аст, ки бо илова кардани RECURSIVE сохта мешавад.

Шакли умумии он:

WITH RECURSIVE cte_name (column1, column2, columnN) AS (  
    SELECT columns  
    FROM table1  
    WHERE condition

    UNION ALL

    SELECT columns  
    FROM cte_name  
    WHERE recursive_condition  
)  
SELECT columns  
FROM cte_name;  

Ин сохтор чор қисми асосӣ дорад:

1. Таърифи CTE-и рекурсивӣ.
2. Қисми ғайрирекурсивӣ.
3. Қисми рекурсивӣ.
4. Фармони асосӣ.

RECURSIVE ба Postgres мегӯяд, ки CTE дархости рекурсивӣ аст.

Таъриф метавонад рӯйхати ихтиёрии сутунҳоро дошта бошад:

(column1, column2, columnN)  

Ин сутунҳо дар қадамҳои рекурсия интиқол меёбанд ва ҳар ду қисми ғайрирекурсивӣ ва рекурсивӣ бояд қиматҳои мувофиқи онҳоро баргардонанд.

Қисми ғайрирекурсивӣ як бор иҷро мешавад ва ҷадвали муваққатии кориро бо додаҳои ибтидоӣ пур мекунад. Ин додаҳо ҳам ба натиҷаи умумӣ илова мешаванд ва ҳам барои қадами аввали рекурсия истифода мегарданд.

UNION ё UNION ALL натиҷаҳои қисми ибтидоӣ ва қадамҳои рекурсивиро якҷо мекунад:

• UNION сатрҳои такрориро хориҷ мекунад.
• UNION ALL ҳамаи сатрҳоро нигоҳ медорад.

Қисми рекурсивӣ дар ҳар қадам натиҷаи қадами пешинаро аз ҷадвали корӣ истифода мебарад.

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

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

Пас аз анҷоми рекурсия, фармони асосӣ метавонад натиҷаро бихонад.

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

Намунаи 10-7: Ҷараёни иҷрои дархости рекурсивӣ бо псевдокод

// Қадами 1: Қисми ғайрирекурсивиро як бор иҷро мекунем.  
non_recursive_result = execute(non_recursive_term);

// Қадами 2: Агар UNION истифода шавад, такрорҳоро мебардорем.  
// Барои UNION ALL ин қадам лозим нест.  
if (using UNION)  
    non_recursive_result = remove_duplicates(non_recursive_result);

// Қадами 3: Натиҷаи ибтидоиро ба натиҷаи умумӣ илова мекунем.  
final_result.add(non_recursive_result);

// Қадами 4: Ҷадвали кориро бо натиҷаи ибтидоӣ пур мекунем.  
working_table = non_recursive_result;

// Қадами 5: То вақте ҷадвали корӣ холӣ нест, идома медиҳем.  
while (working_table is not empty) {

    // Қадами 6: Қисми рекурсивиро бо додаҳои қадами пешина иҷро мекунем.  
    intermediate_table = execute(  
        recursive_term,  
        using = working_table  
    );

    // Қадами 7: Барои UNION такрорҳоро хориҷ мекунем:  
    // ҳам дар натиҷаи ҷорӣ ва ҳам нисбат ба натиҷаи умумӣ.  
    if (using UNION)  
        intermediate_table = remove_duplicates(  
            intermediate_table,  
            excluding = final_result  
        );

    // Қадами 8: Натиҷаи қадами ҷориро ба натиҷаи умумӣ илова мекунем.  
    final_result.add(intermediate_table);

    // Қадами 9: Ҷадвали кориро бо натиҷаи ҷорӣ иваз мекунем.  
    working_table = intermediate_table;  
}

// Қадами 10: Натиҷаи умумиро бармегардонем.  
return final_result;  

Акнун мебинем, ки дархостҳои рекурсивӣ барои хидмати мусиқии мо чӣ фоида доранд.

Дархост аз додаҳои иерархӣ

Хидмати мо метавонад пайдарпайии сурудҳоро пайгирӣ кунад: ҳолате, ки корбар сурудҳоро яке пас аз дигаре бе танаффус гӯш мекунад.

Маълумоти пахшҳо дар streaming.plays нигоҳ дошта мешавад:

\d streaming.plays  

Сохтори ҷадвал:

Ҳар боре корбар суруди навро оғоз мекунад, хидмат ба ҷадвал сабти нав илова мекунад:

• song_id шиносаи суруди ҷорӣ мешавад.
• id қимати навбатии худкор афзояндаро мегирад.
• Агар суруд давоми суруди пешина бошад, played_after ба id-и ҷаласаи пахши пешина баробар мешавад.
• Агар суруд оғози пайдарпайӣ бошад, played_after ба NULL баробар мешавад.

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

Дар додаҳои намунавӣ 13 ҷаласаи пахш оғози пайдарпайӣ мебошанд:

SELECT count(*)  
FROM streaming.plays  
WHERE played_after IS NULL;  

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

Намунаи 10-8: Ёфтани сурудҳои пайдарпай пас аз ҷаласаи мушаххас

WITH RECURSIVE play_sequence AS (  
    SELECT id,  
           user_id,  
           song_id,  
           play_start_time,  
           play_duration,  
           played_after  
    FROM streaming.plays  
    WHERE id = 5

    UNION ALL

    SELECT p.id,  
           p.user_id,  
           p.song_id,  
           p.play_start_time,  
           p.play_duration,  
           p.played_after  
    FROM streaming.plays p  
    JOIN play_sequence ps ON p.played_after = ps.id  
)  
SELECT user_id,  
       song_id,  
       play_start_time,  
       play_duration AS duration,  
       played_after  
FROM play_sequence  
ORDER BY play_start_time;  

Қисми ғайрирекурсивӣ маълумоти ҷаласаи id = 5-ро мегирад ва ба натиҷа илова мекунад.

Қисми рекурсивӣ ҷаласаи навбатиро меҷӯяд:

p.played_after = ps.id  

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

Раванд то ҷаласаи охирин меравад: ҷаласае, ки дигар ягон сабт дар played_after ба id-и он ишора намекунад.

Пас аз анҷоми рекурсия, фармони асосӣ натиҷаҳоро аз рӯйи play_start_time ҷобаҷо мекунад.

Пайдарпайӣ аз plays.id = 5 чунин аст:

Бо тағйири хурд метавон сатҳи ҳар сурудро дар пайдарпайӣ низ гирифт.

Барои ин сутуни ҳисобии level илова мекунем. Дар қисми ибтидоӣ он 1 аст ва дар ҳар қадами рекурсия як воҳид зиёд мешавад.

WITH RECURSIVE play_sequence AS (  
    SELECT id,  
           user_id,  
           song_id,  
           play_start_time,  
           play_duration,  
           played_after,  
           1 AS level  
    FROM streaming.plays  
    WHERE id = 5

    UNION ALL

    SELECT p.id,  
           p.user_id,  
           p.song_id,  
           p.play_start_time,  
           p.play_duration,  
           p.played_after,  
           ps.level + 1  
    FROM streaming.plays p  
    JOIN play_sequence ps ON p.played_after = ps.id  
)  
SELECT user_id,  
       song_id,  
       play_start_time,  
       play_duration AS duration,  
       played_after,  
       level  
FROM play_sequence  
ORDER BY play_start_time;  

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

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

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

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

WITH RECURSIVE cte_name (column1, column2, columnN)  

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

Фарз мекунем хидмати мусиқӣ бояд муддати умумии гӯшкунии як пайдарпайиро ҳисоб кунад.

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

Намунаи 10-9: Ҳисоб кардани муддати умумии пахши пайдарпайӣ

WITH RECURSIVE play_sequence (  
    parent_id,  
    sequence,  
    total_duration  
) AS (  
    SELECT id,  
           ARRAY[id],  
           play_duration  
    FROM streaming.plays  
    WHERE id = 5

    UNION ALL

    SELECT p.id,  
           ps.sequence || p.id,  
           ps.total_duration + p.play_duration  
    FROM streaming.plays p  
    JOIN play_sequence ps ON p.played_after = ps.parent_id  
)  
SELECT p.song_id,  
       p.play_start_time,  
       p.play_duration,  
       ps.sequence,  
       ps.total_duration  
FROM play_sequence ps  
JOIN streaming.plays p ON ps.parent_id = p.id  
ORDER BY ps.sequence, p.play_start_time;  

Дархост се сутунро байни қадамҳо нигоҳ медорад:

Қисми ғайрирекурсивӣ аввал иҷро мешавад:

• id ҳамчун parent_id гирифта мешавад.
• ARRAY[id] массиви ибтидоии sequence-ро месозад.
• play_duration қимати ибтидоии total_duration мешавад.

Қиматҳо мувофиқи тартиби сутунҳои таърифи CTE ҷойгир мешаванд.

Қисми рекурсивӣ муддати пахши ҷориро ба ҷамъ илова мекунад:

ps.total_duration + p.play_duration  

Ҳамчунин id-и ҷорӣ ба массиви пайдарпайӣ илова мешавад:

ps.sequence || p.id  

Пас аз анҷоми рекурсия, фармони асосӣ ба ин сутунҳо дастрасӣ дорад.

Натиҷа:

Сатри охирин нишон медиҳад, ки муддати умумии гӯшкунии тамоми пайдарпайӣ 403 сония аст.

Ҳамаи мисолҳои қаблӣ пайдарпайиро аз ҷаласаи id = 5 месохтанд.

Барои гирифтани ҳамаи пайдарпайиҳо кофист дар қисми ғайрирекурсивӣ ин шартро:

WHERE id = 5  

бо ин иваз кунем:

WHERE played_after IS NULL  

Дархости пурра:

WITH RECURSIVE play_sequence (  
    parent_id,  
    sequence,  
    total_duration  
) AS (  
    SELECT id,  
           ARRAY[id],  
           play_duration  
    FROM streaming.plays  
    WHERE played_after IS NULL

    UNION ALL

    SELECT p.id,  
           ps.sequence || p.id,  
           ps.total_duration + p.play_duration  
    FROM streaming.plays p  
    JOIN play_sequence ps ON p.played_after = ps.parent_id  
)  
SELECT p.song_id,  
       p.play_start_time,  
       p.play_duration,  
       ps.sequence,  
       ps.total_duration  
FROM play_sequence ps  
JOIN streaming.plays p ON ps.parent_id = p.id  
ORDER BY ps.sequence, p.play_start_time;  

Дархост рӯйхати ҳамаи пайдарпайиҳо ва муддатҳои ҷамъшудаи онҳоро медиҳад.

Қисми кӯтоҳшудаи натиҷа барои ду пайдарпайии аввал:

Ҳамин тавр, омӯхтем, ки чӣ гуна дархостҳои рекурсивии Postgres-ро барои коркарди сохторҳои иерархӣ ва дарахтмонанд истифода барем.

Функсияҳои равзанавӣ

Мисли дархостҳои рекурсивӣ, функсияҳои равзанавӣ — window functions яке аз имкониятҳои пурқуввати SQL-и муосиранд.

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

• Ҳисоб кардани ҷойгоҳ ва рейтинг.
• Ҷамъҳои пайдарпай ва ҷамъшаванда.
• Миёнаҳои ҳаракаткунанда.
• Муқоиса бо сатрҳои пешина ва баъдӣ — LAG ва LEAD.
• Ҳисобҳои дигари таҳлилӣ.

Функсияи равзанавӣ имкон медиҳад ҳисобро дар болои маҷмӯи сатрҳои ба ҳам алоқаманд иҷро кунем.

Дархост бо банди OVER додаҳоро ба зермаҷмӯаҳо — равзанаҳо — тақсим карда, функсияро ба ҳар кадом татбиқ мекунад.

Шакли содаи он:

SELECT function_name(arguments)  
       OVER (PARTITION BY columnA)  
FROM table_name;  

function_name(arguments) метавонад функсияи ҷамъбастӣ бошад:

SUM()  
AVG()  

ё функсияи махсуси равзанавӣ:

RANK()  
ROW_NUMBER()  

Банди PARTITION BY сатрҳои дорои қимати якхелаи columnA-ро ба як зермаҷмӯа ҷойгир мекунад.

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

Аммо фарқи асосӣ чунин аст:

• Ҷамъбасти одӣ бо GROUP BY барои ҳар гурӯҳ як сатр медиҳад.
• Функсияи равзанавӣ сатрҳои алоҳидаро нигоҳ дошта, натиҷаи ҳисобро ба онҳо илова мекунад.

Биёед ин фарқро дар хидмати мусиқӣ бинем.

Фарз мекунем бояд муддати умумии пахши ҳар сурудро ҳисоб кунем. Инро бо SUM() ва GROUP BY осон иҷро мекунем.

Намунаи 10-10: Ҳисоб кардани муддати умумии пахши ҳар суруд

SELECT song_id,  
       SUM(play_duration) AS total_duration  
FROM streaming.plays  
GROUP BY song_id  
ORDER BY total_duration DESC;  

Натиҷа аз суруди дорои муддати умумии бештар оғоз мешавад:

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

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

Агар танҳо user_id-ро ба GROUP BY илова кунем:

SELECT song_id,  
       user_id,  
       SUM(play_duration) AS total_duration  
FROM streaming.plays  
GROUP BY song_id, user_id  
ORDER BY song_id;  

он гоҳ SUM() ҷамъро барои ҳар корбар алоҳида ҳисоб мекунад:

Ин натиҷаи дилхоҳи мо нест.

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

Намунаи 10-11: Ҳисоб кардани муддати умумӣ ҳамроҳи корбарон бо пайвасткунии ҷадвал ба натиҷаи ҷамъбастии худаш

SELECT DISTINCT p.song_id,  
                p.user_id,  
                t.total_duration  
FROM streaming.plays p  
JOIN (  
    SELECT song_id,  
           SUM(play_duration) AS total_duration  
    FROM streaming.plays  
    GROUP BY song_id  
) t ON p.song_id = t.song_id  
ORDER BY p.song_id;  

Дархост муддати умумии ҳар сурудро ҳисоб карда, ҳамаи корбарони онро нишон медиҳад:

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

Бо функсияи равзанавӣ ҳамин вазифаро кӯтоҳтар ва хонданбобтар ҳал кардан мумкин аст.

Намунаи 10-12: Ҳисоб кардани муддати умумӣ ҳамроҳи корбарон бо функсияи равзанавӣ

WITH plays_with_total AS (  
    SELECT song_id,  
           user_id,  
           SUM(play_duration)  
               OVER (PARTITION BY song_id) AS total_duration  
    FROM streaming.plays  
)  
SELECT DISTINCT song_id, user_id, total_duration  
FROM plays_with_total  
ORDER BY song_id, user_id;  

CTE-и plays_with_total функсияи равзанавиро муайян мекунад.

Он сатрҳоро аз рӯйи song_id ба зермаҷмӯаҳо ҷудо карда, барои ҳар зермаҷмӯа муддати умумии пахшро ҳисоб мекунад.

Масалан, барои ду суруди аввал равзанаҳо чунинанд:

                  play_id  user_id  play_duration  
PARTITION         ───────  ───────  ─────────────  
song_id = 1   ┌      1        1          200   ┐  
              │     10        2          200   │  SUM = 800  
              │     20        3          200   │  
              └     30        3          200   ┘  
song_id = 2   ┌      2        1          144   ┐  
              │     11        2          206   │  SUM = 654  
              │     21        3          186   │  
              └     31        3          118   ┘  

Ҳар сатр ҷамъи равзанаи худро мегирад, вале ҳамчун сатри алоҳида боқӣ мемонад.

Пас аз ҳисоб кардани CTE, SELECT DISTINCT сатрҳои такрории як ҷуфти song_id ва user_id-ро, ки ҷамъашон яксон аст, хориҷ мекунад.

Натиҷа ба варианти пешина монанд аст:

Ҳисоб кардани ҷамъи пайдарпай

Бо илова кардани ORDER BY дар дохили OVER, тартиби сатрҳои ҳар равзанаро муайян карда метавонем.

Ин тартиб дар якҷоягӣ бо чорчӯбаи равзана — window frame имкон медиҳад ҷамъҳои пайдарпай ҳисоб кунем.

Масалан, намунаи 10-13 барои суруди song_id = 2 муддати пахшро ҷамъ мекунад ва сатрҳоро аз рӯйи user_id тартиб медиҳад.

Намунаи 10-13: Ҳисоб кардани ҷамъи пайдарпай ва муддати умумӣ

SELECT song_id,  
       user_id,  
       play_duration,  
       SUM(play_duration) OVER (  
           PARTITION BY song_id  
           ORDER BY user_id  
       ) AS total_play_duration  
FROM streaming.plays  
WHERE song_id = 2;  

Натиҷа:

Ҳисоб аз корбари user_id = 1 оғоз мешавад. Барои ӯ як сабт ҳаст, бинобар ин ҷамъ ба 144 сония баробар аст.

Дар сатҳи навбатӣ сабти корбари user_id = 2 низ дохил мешавад. Ӯ 206 сония гӯш кардааст:

144 + 206 = 350  

Барои user_id = 3 ду сабт ҳаст: 186 ва 118 сония. Ҷамъи онҳо 304 сония мебошад:

186 + 118 = 304  

Ин қимат ба ҷамъи қаблӣ илова мешавад:

350 + 304 = 654  

Азбаски ҳар ду сатри корбари сеюм дар ORDER BY user_id қимати яксон доранд, чорчӯбаи пешфарз ҳар дуи онҳоро якҷо фаро мегирад. Аз ин рӯ, дар ҳар ду сатр ҷамъ 654 нишон дода мешавад.

Чорчӯбаи ҳар сатр чунин аст:

user_id  play_duration   чорчӯба (сатрҳои ҷамъшаванда)      ҷамъ  
───────  ─────────────   ───────────────────────────────   ────  
   1          144        144                                144  
   2          206        144 + 206                          350  
   3          186        144 + 206 + 186 + 118              654  
   3          118        144 + 206 + 186 + 118              654  

Эзоҳ: Дар намунаи 10-13 PARTITION BY-ро метавон нанавишт, зеро WHERE song_id = 2 аллакай танҳо сатрҳои як сурудро интихоб мекунад. Дар ин ҳолат ҳамаи сатрҳои интихобшуда як равзанаро ташкил медиҳанд.

Истифодаи RANK() барои рейтинг

То ин ҷо SUM()-ро ҳамчун функсияи равзанавӣ истифода кардем.

Умуман, функсияҳои ҷамъбастии дарунсохт, ба монанди AVG(), MIN() ва COUNT()-ро низ дар ҳолати равзанавӣ истифода бурдан мумкин аст.

Postgres ҳамчунин функсияҳои махсусе дорад, ки танҳо бо OVER кор мекунанд. Онҳо барои рейтинг ва муқоисаи сатрҳои пешинаву баъдӣ истифода мешаванд.

Фарз мекунем хидмати мусиқӣ бояд сурудҳоро аз рӯйи муддати умумии пахш рейтингбандӣ кунад.

Намунаи 10-14: Рейтингбандии сурудҳо аз рӯйи муддати умумии пахш

SELECT song_id,  
       SUM(play_duration) AS total_play_duration,  
       RANK() OVER (  
           ORDER BY SUM(play_duration) DESC  
       ) AS song_rank  
FROM streaming.plays  
GROUP BY song_id  
ORDER BY song_rank;  

Дархост чунин кор мекунад.

GROUP BY song_id. Postgres сабтҳоро аз рӯйи суруд гурӯҳбандӣ карда, бо SUM(play_duration) муддати умумии ҳар сурудро ҳисоб мекунад. Барои ҳар суруд як сатр мемонад.

RANK() OVER (...). Функсияи равзанавӣ натиҷаи гурӯҳбандиро аз рӯйи муддати умумӣ бо тартиби камшавӣ рейтингбандӣ мекунад. Агар ду суруд муддати умумии якхела дошта бошанд, ҳар ду рейтинги яксон мегиранд.

ORDER BY song_rank. Натиҷаи ниҳоӣ аз рӯйи ҷойгоҳ ҷобаҷо мешавад. Суруди дорои муддати умумии бештар ҷойгоҳи 1 мегирад.

Натиҷа:

Хулоса

SQL дар 50 сол хеле рушд кардааст ва имкониятҳои пас аз SQL-92 иловашуда кори ҳаррӯзаро ба таври назаррас осон мекунанд.

• CTE дархости калонро ба қадамҳои номдор ҷудо мекунад. Postgres одатан онҳоро ба нақшаи ягона ҳамроҳ мекунад, бинобар ин хонданбобӣ ба суръат зарар намерасонад. CTE метавонад додаҳоро тағйир диҳад ва бо RETURNING натиҷаро ба қисми дигари дархост диҳад; ҳамаи қисмҳо як нусхаи лаҳзавии додаҳоро мебинанд.
• Дархостҳои рекурсивӣ бо WITH RECURSIVE сохторҳои иерархӣ ва занҷирҳоро — масалан, сурудҳои пайдарпай — бе донистани умқи онҳо пайгирӣ мекунанд.
• Функсияҳои равзанавӣ бо OVER ҳисобро дар болои гурӯҳи сатрҳо иҷро мекунанд, вале баръакси GROUP BY сатрҳои алоҳидаро нигоҳ медоранд. Бо онҳо ҷамъҳои пайдарпай ва рейтингҳо дар як дархост ҳисоб мешаванд.

Дар боби оянда мебинем, ки PostgreSQL чӣ гуна якчанд тағйиротро ба як воҳиди кор — транзаксия — муттаҳид мекунад.

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

1. Корбаронро аз рӯйи муддати умумии гӯшкунӣ бо RANK() рейтингбандӣ кунед.
2. Бо CTE сурудҳоеро ёбед, ки ҳеҷ кас онҳоро то охир гӯш накардааст.
3. Бо дархости рекурсивӣ ҳисоб кунед, ки пайдарпайии аз ҷаласаи id = 13 оғозшуда чанд суруд дорад ва муддати умумии он чанд сония аст.

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