Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

09/30/26 17:14:46 (112 minutes ago)

Author:

223306

Comment:

--

AdvancedReports

@@ -1 @@
-= Напредни извештаи =
-
-Извештаите се извршуваат над податоците од базата на податоци на !BiblioPremium. Извештаите користат податоци од повеќе релации, спојувања, агрегатни функции и пресметани вредности за добивање аналитички информации за продажбата и залихите.
-
-Во релационата алгебра се користи проширена нотација: σ (селекција), π (проекција), ⋈ (спојување), γ (групирање и агрегатни функции), τ (подредување) и ← (доделување на привремена релација).
-
-== Најуспешни книги според продажба и приход ==
+= Advanced Reports =
+
+Во оваа фаза се изработени два комплексни аналитички извештаи над податоците од базата на !BiblioPremium.
+
+Извештаите се наменети за долгорочна анализа на работењето на системот и користат податоци од повеќе релации, повеќестепено агрегирање, временска анализа, CTE (Common Table Expressions), прозорски функции, рангирање и пресметани аналитички показатели.
+
+Во двата извештаи се земаат предвид само реализираните нарачки со статус ''ZAVRSENA''.
+
+Во релационата алгебра се користи проширена нотација:
+
+* σ – селекција
+* π – генерализирана проекција со пресметани атрибути
+* ⋈ – спојување
+* ⟕ – лево надворешно спојување
+* ρ – преименување
+* γ – групирање и агрегатни функции
+* τ – подредување
+* ← – доделување на привремена релација
+
+За аналитичките операции како RANK, DENSE_RANK, LAG, ROW_NUMBER и подвижни агрегати се користи проширена аналитичка нотација.
+
+
+== Квартална анализа и рангирање на продажните перформанси на книгите по категории ==
 
 === Data requirements description ===
 
-Потребно е да се добие аналитички извештај кој ќе покаже кои книги се најуспешни според бројот на продадени примероци и остварениот приход.
-
-За секоја книга се прикажуваат:
-
-- вкупниот број на продадени примероци;
-- вкупниот остварен приход;
-- бројот на различни реализирани нарачки во кои се појавува книгата;
-- просечниот приход од една ставка во нарачка.
-
-Во извештајот се земаат предвид само нарачките со статус ''ZAVRSENA'', бидејќи тие претставуваат реализирани купувања.
-
-Извештајот може да се користи за анализа на продажбата и за идентификување на книгите кои генерираат најголем приход.
-
-=== Решение во SQL ===
-
-{{{
+Целта на извештајот е да се анализира како се менува продажбата на книгите и категориите низ кварталите и да се идентификуваат книгите кои имаат најголемо влијание врз приходот.
+
+За секој квартал, категорија и книга потребно е да се прикажат:
+
+* бројот на различни реализирани нарачки;
+* вкупниот број на продадени примероци;
+* вкупниот остварен приход;
+* просечната продажна цена;
+* вкупниот приход на категоријата во кварталот;
+* просечниот приход по продавана книга во категоријата;
+* рангот на книгата во рамки на категоријата за конкретниот квартал;
+* процентуалното учество на книгата во приходот на категоријата;
+* приходот на истата книга во претходниот достапен квартал;
+* процентуалната промена на приходот во однос на претходниот достапен квартал;
+* кумулативниот приход на книгата низ кварталите;
+* класификација на трендот на продажба.
+
+Извештајот овозможува квартално, полугодишно, годишно и повеќегодишно следење на продажбата.
+
+Со него може да се утврди кои книги носат најголем приход во својата категорија, како нивната продажба се менува низ времето и дали одредена книга покажува растечки или опаѓачки тренд.
+
+
+=== Solution SQL ===
+
+{{{
+WITH quarterly_book_sales AS (
+    SELECT
+        DATE_TRUNC('quarter', n.datum) AS kvartal,
+        k.kniga_id,
+        k.naslov,
+        k.isbn,
+        kat.kategorija_id,
+        kat.naziv AS kategorija,
+
+        COUNT(DISTINCT n.naracka_id) AS broj_naracki,
+
+        SUM(s.kolicina) AS vkupno_prodadeni,
+
+        SUM(
+            s.kolicina * s.edinechna_cena
+        ) AS vkupen_prihod,
+
+        AVG(
+            s.edinechna_cena
+        ) AS prosecna_prodazna_cena
+
+    FROM project.knigi k
+
+    JOIN project.ima_kategorija ik
+        ON k.kniga_id = ik.kniga_id
+
+    JOIN project.kategorii kat
+        ON ik.kategorija_id = kat.kategorija_id
+
+    JOIN project.sodrzi s
+        ON k.kniga_id = s.kniga_id
+
+    JOIN project.naracki n
+        ON s.naracka_id = n.naracka_id
+
+    WHERE n.status = 'ZAVRSENA'
+
+    GROUP BY
+        DATE_TRUNC('quarter', n.datum),
+        k.kniga_id,
+        k.naslov,
+        k.isbn,
+        kat.kategorija_id,
+        kat.naziv
+),
+
+category_statistics AS (
+    SELECT
+        kvartal,
+        kategorija_id,
+        kategorija,
+
+        COUNT(DISTINCT kniga_id)
+            AS broj_prodavani_knigi,
+
+        SUM(vkupno_prodadeni)
+            AS vkupno_prodadeni_kategorija,
+
+        SUM(vkupen_prihod)
+            AS vkupen_prihod_kategorija,
+
+        AVG(vkupen_prihod)
+            AS prosechen_prihod_po_kniga
+
+    FROM quarterly_book_sales
+
+    GROUP BY
+        kvartal,
+        kategorija_id,
+        kategorija
+),
+
+quarterly_analysis AS (
+    SELECT
+        qbs.kvartal,
+        qbs.kniga_id,
+        qbs.naslov,
+        qbs.isbn,
+        qbs.kategorija_id,
+        qbs.kategorija,
+        qbs.broj_naracki,
+        qbs.vkupno_prodadeni,
+        qbs.vkupen_prihod,
+        qbs.prosecna_prodazna_cena,
+
+        cs.broj_prodavani_knigi,
+        cs.vkupno_prodadeni_kategorija,
+        cs.vkupen_prihod_kategorija,
+        cs.prosechen_prihod_po_kniga,
+
+        RANK() OVER (
+            PARTITION BY
+                qbs.kvartal,
+                qbs.kategorija_id
+            ORDER BY
+                qbs.vkupen_prihod DESC
+        ) AS rang_vo_kategorija,
+
+        LAG(qbs.vkupen_prihod) OVER (
+            PARTITION BY
+                qbs.kniga_id,
+                qbs.kategorija_id
+            ORDER BY
+                qbs.kvartal
+        ) AS prihod_prethoden_kvartal,
+
+        SUM(qbs.vkupen_prihod) OVER (
+            PARTITION BY
+                qbs.kniga_id,
+                qbs.kategorija_id
+            ORDER BY
+                qbs.kvartal
+            ROWS BETWEEN
+                UNBOUNDED PRECEDING
+                AND CURRENT ROW
+        ) AS kumulativen_prihod
+
+    FROM quarterly_book_sales qbs
+
+    JOIN category_statistics cs
+        ON qbs.kvartal = cs.kvartal
+        AND qbs.kategorija_id = cs.kategorija_id
+),
+
+final_analysis AS (
+    SELECT
+        *,
+
+        ROUND(
+            (
+                vkupen_prihod
+                /
+                NULLIF(
+                    vkupen_prihod_kategorija,
+                    0
+                )
+            ) * 100,
+            2
+        ) AS procent_od_prihod_na_kategorija,
+
+        ROUND(
+            (
+                (
+                    vkupen_prihod -
+                    prihod_prethoden_kvartal
+                )
+                /
+                NULLIF(
+                    prihod_prethoden_kvartal,
+                    0
+                )
+            ) * 100,
+            2
+        ) AS promena_prihod_procent,
+
+        CASE
+            WHEN prihod_prethoden_kvartal IS NULL
+                THEN 'NEMA PRETHODEN PERIOD'
+
+            WHEN vkupen_prihod >
+                 prihod_prethoden_kvartal * 1.10
+                THEN 'RAST'
+
+            WHEN vkupen_prihod <
+                 prihod_prethoden_kvartal * 0.90
+                THEN 'PAD'
+
+            ELSE 'STABILNO'
+        END AS trend
+
+    FROM quarterly_analysis
+)
+
 SELECT
-    k.kniga_id,
-    k.naslov,
-    k.isbn,
-    SUM(s.kolicina) AS vkupno_prodadeni,
-    SUM(s.kolicina * s.edinechna_cena) AS vkupen_prihod,
-    COUNT(DISTINCT n.naracka_id) AS broj_naracki,
+    TO_CHAR(
+        kvartal,
+        'YYYY-"Q"Q'
+    ) AS kvartal,
+
+    kategorija,
+    kniga_id,
+    naslov,
+    isbn,
+
+    broj_naracki,
+    vkupno_prodadeni,
+
     ROUND(
-        AVG(s.kolicina * s.edinechna_cena),
+        vkupen_prihod,
         2
-    ) AS prosechen_prihod_od_stavka
-FROM project.knigi k
-JOIN project.sodrzi s
-    ON k.kniga_id = s.kniga_id
-JOIN project.naracki n
-    ON s.naracka_id = n.naracka_id
-WHERE n.status = 'ZAVRSENA'
-GROUP BY
-    k.kniga_id,
-    k.naslov,
-    k.isbn
-HAVING SUM(s.kolicina) > 0
-ORDER BY
-    vkupen_prihod DESC;
-}}}
-
-=== Решение во релациона алгебра ===
-
-{{{
-R1 ← KNIGI ⋈ KNIGI.kniga_id = SODRZI.kniga_id SODRZI
-
-R2 ← R1 ⋈ SODRZI.naracka_id = NARACKI.naracka_id
-      σ(status = 'ZAVRSENA')(NARACKI)
-
-R3 ← γ
-      kniga_id, naslov, isbn;
-      SUM(kolicina) → vkupno_prodadeni,
-      SUM(kolicina × edinechna_cena) → vkupen_prihod,
-      COUNT-DISTINCT(naracka_id) → broj_naracki,
-      AVG(kolicina × edinechna_cena) → prosechen_prihod_od_stavka
-      (R2)
-
-R4 ← σ(vkupno_prodadeni > 0)(R3)
-
-Result ← τ vkupen_prihod DESC
-         (R4)
-}}}
-
-=== Објаснување ===
-
-Прво се спојуваат книгите со ставките од нарачките преку ''kniga_id''. Потоа резултатот се спојува со нарачките преку ''naracka_id'', при што се задржуваат само нарачките со статус ''ZAVRSENA''.
-
-Со γ се врши групирање по книга и се пресметуваат вкупниот број на продадени примероци, вкупниот приход, бројот на различни нарачки и просечниот приход од ставка.
-
-Со σ се задржуваат само книгите за кои има продадени примероци.
-
-На крај, со τ резултатите се подредуваат според вкупниот приход во опаѓачки редослед.
-
-== Проценка на ризик од исцрпување на залихата ==
-
-=== Data requirements description ===
-
-Потребно е да се добие аналитички извештај кој ќе идентификува книги кај кои постои потенцијален ризик од исцрпување на моменталната залиха.
-
-За секоја книга се прикажуваат:
-
-- моменталната залиха;
-- вкупниот број на продадени примероци;
-- бројот на реализирани нарачки;
-- просечната продажба по нарачка;
-- проценетиот број на нарачки до исцрпување на моменталната залиха.
-
-Извештајот ги зема предвид само реализираните нарачки со статус ''ZAVRSENA''.
-
-Просечната продажба по нарачка се пресметува врз основа на вкупниот број на продадени примероци и бројот на различни реализирани нарачки.
-
-Проценетиот број на нарачки до исцрпување на залихата се добива со споредување на моменталната залиха со просечната продажба по нарачка.
-
-Помал број на проценети нарачки до исцрпување укажува дека моменталната залиха би се потрошила со помал број на просечни нарачки.
-
-Овој извештај може да се користи за следење на залихите и навремено планирање на дополнување на книгите.
-
-=== Решение во SQL ===
-
-{{{
-SELECT
-    k.kniga_id,
-    k.naslov,
-    k.kolicina_na_zaliha AS momentalna_zaliha,
-
-    SUM(s.kolicina) AS vkupno_prodadeni,
-
-    COUNT(DISTINCT n.naracka_id) AS broj_naracki,
+    ) AS vkupen_prihod,
 
     ROUND(
-        SUM(s.kolicina)::numeric /
-        NULLIF(COUNT(DISTINCT n.naracka_id), 0),
+        prosecna_prodazna_cena,
         2
-    ) AS prosecna_prodazba_po_naracka,
+    ) AS prosecna_prodazna_cena,
+
+    broj_prodavani_knigi,
+
+    vkupno_prodadeni_kategorija,
 
     ROUND(
-        k.kolicina_na_zaliha::numeric /
-        NULLIF(
-            SUM(s.kolicina)::numeric /
-            NULLIF(COUNT(DISTINCT n.naracka_id), 0),
+        vkupen_prihod_kategorija,
+        2
+    ) AS vkupen_prihod_kategorija,
+
+    ROUND(
+        prosechen_prihod_po_kniga,
+        2
+    ) AS prosechen_prihod_po_kniga,
+
+    rang_vo_kategorija,
+
+    procent_od_prihod_na_kategorija,
+
+    ROUND(
+        COALESCE(
+            prihod_prethoden_kvartal,
             0
         ),
         2
-    ) AS proceneti_naracki_do_istekuvanje
-
-FROM project.knigi k
-
-JOIN project.sodrzi s
-    ON k.kniga_id = s.kniga_id
-
-JOIN project.naracki n
-    ON s.naracka_id = n.naracka_id
-    AND n.status = 'ZAVRSENA'
-
-GROUP BY
-    k.kniga_id,
-    k.naslov,
-    k.kolicina_na_zaliha
-
-HAVING SUM(s.kolicina) > 0
+    ) AS prihod_prethoden_kvartal,
+
+    promena_prihod_procent,
+
+    ROUND(
+        kumulativen_prihod,
+        2
+    ) AS kumulativen_prihod,
+
+    trend
+
+FROM final_analysis
 
 ORDER BY
-    proceneti_naracki_do_istekuvanje ASC;
-}}}
-
-=== Решение во релациона алгебра ===
-
-{{{
-R1 ← KNIGI ⋈ KNIGI.kniga_id = SODRZI.kniga_id SODRZI
-
-R2 ← R1 ⋈ SODRZI.naracka_id = NARACKI.naracka_id
-      σ(status = 'ZAVRSENA')(NARACKI)
-
-R3 ← γ
-      kniga_id, naslov, kolicina_na_zaliha;
-      SUM(kolicina) → vkupno_prodadeni,
-      COUNT-DISTINCT(naracka_id) → broj_naracki
-      (R2)
-
-R4 ← σ(vkupno_prodadeni > 0)(R3)
-
-R5 ← π
-      kniga_id,
-      naslov,
-      kolicina_na_zaliha,
-      vkupno_prodadeni,
-      broj_naracki,
-      (
-          vkupno_prodadeni / broj_naracki
-      ) → prosecna_prodazba_po_naracka,
-      (
-          kolicina_na_zaliha /
-          (vkupno_prodadeni / broj_naracki)
-      ) → proceneti_naracki_do_istekuvanje
-      (R4)
-
-Result ← τ proceneti_naracki_do_istekuvanje ASC
-         (R5)
-}}}
+    kvartal,
+    kategorija,
+    rang_vo_kategorija,
+    naslov;
+}}}
+
+
+=== Solution Relational Algebra ===
+
+Најпрво се издвојуваат само реализираните нарачки:
+
+{{{
+N ← σ status='ZAVRSENA' (NARACKI)
+}}}
+
+Книгите се поврзуваат со категориите:
+
+{{{
+KC ←
+KNIGI
+⋈ KNIGI.kniga_id = IMA_KATEGORIJA.kniga_id
+IMA_KATEGORIJA
+⋈ IMA_KATEGORIJA.kategorija_id = KATEGORII.kategorija_id
+KATEGORII
+}}}
+
+Потоа се додаваат ставките и реализираните нарачки:
+
+{{{
+R1 ←
+KC
+⋈ KNIGI.kniga_id = SODRZI.kniga_id
+SODRZI
+
+R2 ←
+R1
+⋈ SODRZI.naracka_id = N.naracka_id
+N
+}}}
+
+Датумот се трансформира во квартал и се врши првото ниво на агрегација:
+
+{{{
+QBS ←
+quarter(datum),
+kniga_id,
+naslov,
+isbn,
+kategorija_id,
+kategorija
+
+γ
+
+COUNT-DISTINCT(naracka_id)
+    → broj_naracki,
+
+SUM(kolicina)
+    → vkupno_prodadeni,
+
+SUM(kolicina × edinechna_cena)
+    → vkupen_prihod,
+
+AVG(edinechna_cena)
+    → prosecna_prodazna_cena
+
+(R2)
+}}}
+
+Второто ниво на агрегација се врши на ниво квартал–категорија:
+
+{{{
+CS ←
+kvartal,
+kategorija_id,
+kategorija
+
+γ
+
+COUNT-DISTINCT(kniga_id)
+    → broj_prodavani_knigi,
+
+SUM(vkupno_prodadeni)
+    → vkupno_prodadeni_kategorija,
+
+SUM(vkupen_prihod)
+    → vkupen_prihod_kategorija,
+
+AVG(vkupen_prihod)
+    → prosechen_prihod_po_kniga
+
+(QBS)
+}}}
+
+Двете нивоа на анализа се поврзуваат:
+
+{{{
+QA ←
+QBS
+⋈ QBS.kvartal = CS.kvartal
+  ∧ QBS.kategorija_id = CS.kategorija_id
+CS
+}}}
+
+Рангирањето во рамки на квартал и категорија се изразува со проширена аналитичка операција:
+
+{{{
+QA1 ←
+RANK()
+OVER (
+    PARTITION BY kvartal, kategorija_id
+    ORDER BY vkupen_prihod DESC
+)
+(QA)
+}}}
+
+Приходот од претходниот достапен квартален запис се добива со:
+
+{{{
+QA2 ←
+LAG(vkupen_prihod)
+OVER (
+    PARTITION BY kniga_id, kategorija_id
+    ORDER BY kvartal
+)
+(QA1)
+}}}
+
+Кумулативниот приход се добива со:
+
+{{{
+QA3 ←
+CUMULATIVE_SUM(vkupen_prihod)
+OVER (
+    PARTITION BY kniga_id, kategorija_id
+    ORDER BY kvartal
+)
+(QA2)
+}}}
+
+Се пресметуваат процентуалното учество и промената на приходот:
+
+{{{
+FA ← π
+    kvartal,
+    kategorija,
+    kniga_id,
+    naslov,
+    isbn,
+    broj_naracki,
+    vkupno_prodadeni,
+    vkupen_prihod,
+    prosecna_prodazna_cena,
+    broj_prodavani_knigi,
+    vkupno_prodadeni_kategorija,
+    vkupen_prihod_kategorija,
+    prosechen_prihod_po_kniga,
+    rang_vo_kategorija,
+
+    (
+        100 ×
+        vkupen_prihod /
+        vkupen_prihod_kategorija
+    )
+        → procent_od_prihod_na_kategorija,
+
+    prihod_prethoden_kvartal,
+
+    (
+        100 ×
+        (
+            vkupen_prihod -
+            prihod_prethoden_kvartal
+        )
+        /
+        prihod_prethoden_kvartal
+    )
+        → promena_prihod_procent,
+
+    kumulativen_prihod
+
+(QA3)
+}}}
+
+Конечниот резултат се подредува според квартал, категорија и ранг:
+
+{{{
+RESULT ←
+τ kvartal ASC,
+  kategorija ASC,
+  rang_vo_kategorija ASC
+(FA)
+}}}
+
 
 === Објаснување ===
 
-Прво се спојуваат книгите со ставките од нарачките, а потоа со нарачките. Се задржуваат само реализираните нарачки со статус ''ZAVRSENA''.
-
-Со γ се групираат податоците според книга и се пресметуваат вкупниот број на продадени примероци и бројот на различни реализирани нарачки.
-
-Просечната продажба по нарачка се добива со делење на вкупниот број на продадени примероци со бројот на реализирани нарачки.
-
-Проценетиот број на нарачки до исцрпување на залихата се добива со делење на моменталната залиха со просечната продажба по нарачка.
-
-Со τ резултатите се подредуваат растечки според проценетиот број на нарачки до исцрпување на залихата.
+Извештајот користи повеќе нивоа на обработка.
+
+Во ''quarterly_book_sales'' податоците од пет релации се поврзуваат и се агрегираат на ниво квартал–книга–категорија.
+
+Со:
+
+{{{
+DATE_TRUNC('quarter', n.datum)
+}}}
+
+датумите на нарачките се претвораат во квартални периоди.
+
+За секоја книга се пресметуваат бројот на реализирани нарачки, бројот на продадени примероци, приходот и просечната продажна цена.
+
+Во ''category_statistics'' се врши второ ниво на агрегација. Податоците кои претходно биле пресметани по книга повторно се агрегираат на ниво квартал–категорија.
+
+На овој начин може да се спореди индивидуалниот резултат на книгата со резултатите на целата категорија.
+
+Со:
+
+{{{
+RANK() OVER (
+    PARTITION BY qbs.kvartal, qbs.kategorija_id
+    ORDER BY qbs.vkupen_prihod DESC
+)
+}}}
+
+секоја книга добива ранг во својата категорија за конкретниот квартал.
+
+Со ''LAG()'' се добива приходот од претходниот достапен квартален запис за истата книга и категорија.
+
+Ако книгата нема продажба во некој календарски квартал, тој квартал не создава посебен ред со вредност 0. Поради тоа ''LAG()'' се однесува на претходниот достапен квартален запис.
+
+Со прозорска ''SUM()'' се пресметува кумулативниот приход на книгата низ кварталите.
+
+Потоа се пресметуваат процентуалното учество во приходот на категоријата и процентуалната промена во однос на претходниот достапен период.
+
+Со ''CASE'' трендот се класифицира како ''RAST'', ''PAD'', ''STABILNO'' или ''NEMA PRETHODEN PERIOD''.
+
+
+== Временска анализа на продажбата и прогноза на ризик од исцрпување на залихата ==
+
+=== Data requirements description ===
+
+Целта на вториот извештај е да се анализира долгорочната продажна динамика на книгите и врз основа на историската продажба да се направи проценка кои книги имаат најголем ризик од исцрпување на залихата.
+
+За да не се користи само вкупната историска продажба, реализираните продажби најпрво се агрегираат на месечно ниво.
+
+За секоја книга се анализираат:
+
+* продажбата во последниот достапен месечен период;
+* продажбата во претходниот достапен месечен период;
+* приходот во последниот период;
+* приходот во претходниот период;
+* бројот на различни нарачки;
+* подвижниот просек на продажбата над тековниот и најмногу двата претходни достапни месечни записи;
+* кумулативниот приход;
+* процентуалната промена на продажбата;
+* моменталната залиха;
+* проценетиот број на месеци до исцрпување на залихата;
+* ниво на ризик;
+* глобален ранг според ризикот.
+
+Подвижниот просек се користи за прогнозата да не зависи само од продажбата во еден период.
+
+Извештајот може да се користи за планирање на дополнување на залихите и за идентификување на книги чија моментална залиха може брзо да се потроши.
+
+
+=== Solution SQL ===
+
+{{{
+WITH monthly_book_sales AS (
+    SELECT
+        k.kniga_id,
+        k.naslov,
+
+        DATE_TRUNC(
+            'month',
+            n.datum
+        ) AS mesec,
+
+        SUM(
+            s.kolicina
+        ) AS prodadeni,
+
+        SUM(
+            s.kolicina *
+            s.edinechna_cena
+        ) AS prihod,
+
+        COUNT(
+            DISTINCT n.naracka_id
+        ) AS broj_naracki
+
+    FROM project.knigi k
+
+    JOIN project.sodrzi s
+        ON k.kniga_id = s.kniga_id
+
+    JOIN project.naracki n
+        ON s.naracka_id = n.naracka_id
+
+    WHERE n.status = 'ZAVRSENA'
+
+    GROUP BY
+        k.kniga_id,
+        k.naslov,
+        DATE_TRUNC(
+            'month',
+            n.datum
+        )
+),
+
+sales_with_history AS (
+    SELECT
+        kniga_id,
+        naslov,
+        mesec,
+        prodadeni,
+        prihod,
+        broj_naracki,
+
+        LAG(prodadeni) OVER (
+            PARTITION BY kniga_id
+            ORDER BY mesec
+        ) AS prodadeni_prethoden_mesec,
+
+        LAG(prihod) OVER (
+            PARTITION BY kniga_id
+            ORDER BY mesec
+        ) AS prihod_prethoden_mesec,
+
+        AVG(prodadeni) OVER (
+            PARTITION BY kniga_id
+            ORDER BY mesec
+            ROWS BETWEEN
+                2 PRECEDING
+                AND CURRENT ROW
+        ) AS podvizen_prosek_prodazba,
+
+        SUM(prihod) OVER (
+            PARTITION BY kniga_id
+            ORDER BY mesec
+            ROWS BETWEEN
+                UNBOUNDED PRECEDING
+                AND CURRENT ROW
+        ) AS kumulativen_prihod
+
+    FROM monthly_book_sales
+),
+
+latest_period AS (
+    SELECT
+        *,
+
+        ROW_NUMBER() OVER (
+            PARTITION BY kniga_id
+            ORDER BY mesec DESC
+        ) AS rn
+
+    FROM sales_with_history
+),
+
+risk_analysis AS (
+    SELECT
+        lp.kniga_id,
+        lp.naslov,
+        lp.mesec,
+
+        k.kolicina_na_zaliha,
+
+        lp.prodadeni
+            AS prodadeni_posleden_mesec,
+
+        lp.prodadeni_prethoden_mesec,
+
+        lp.prihod
+            AS prihod_posleden_mesec,
+
+        lp.prihod_prethoden_mesec,
+
+        lp.broj_naracki,
+
+        lp.podvizen_prosek_prodazba,
+
+        lp.kumulativen_prihod,
+
+        ROUND(
+            (
+                (
+                    lp.prodadeni -
+                    lp.prodadeni_prethoden_mesec
+                )::numeric
+                /
+                NULLIF(
+                    lp.prodadeni_prethoden_mesec,
+                    0
+                )
+            ) * 100,
+            2
+        ) AS rast_prodazba_procent,
+
+        ROUND(
+            k.kolicina_na_zaliha::numeric
+            /
+            NULLIF(
+                lp.podvizen_prosek_prodazba,
+                0
+            ),
+            2
+        ) AS proceneti_meseci_do_istekuvanje
+
+    FROM latest_period lp
+
+    JOIN project.knigi k
+        ON lp.kniga_id = k.kniga_id
+
+    WHERE lp.rn = 1
+),
+
+final_analysis AS (
+    SELECT
+        *,
+
+        CASE
+            WHEN proceneti_meseci_do_istekuvanje <= 1
+                 AND COALESCE(
+                     rast_prodazba_procent,
+                     0
+                 ) > 0
+                THEN 'KRITICEN RIZIK'
+
+            WHEN proceneti_meseci_do_istekuvanje <= 2
+                THEN 'VISOK RIZIK'
+
+            WHEN proceneti_meseci_do_istekuvanje <= 4
+                THEN 'SREDEN RIZIK'
+
+            ELSE 'NIZOK RIZIK'
+        END AS nivo_na_rizik,
+
+        DENSE_RANK() OVER (
+            ORDER BY
+                proceneti_meseci_do_istekuvanje
+                ASC NULLS LAST
+        ) AS rang_rizik
+
+    FROM risk_analysis
+)
+
+SELECT
+    kniga_id,
+    naslov,
+
+    TO_CHAR(
+        mesec,
+        'YYYY-MM'
+    ) AS posleden_period,
+
+    kolicina_na_zaliha,
+
+    prodadeni_posleden_mesec,
+
+    prodadeni_prethoden_mesec,
+
+    broj_naracki,
+
+    ROUND(
+        prihod_posleden_mesec,
+        2
+    ) AS prihod_posleden_mesec,
+
+    ROUND(
+        COALESCE(
+            prihod_prethoden_mesec,
+            0
+        ),
+        2
+    ) AS prihod_prethoden_mesec,
+
+    ROUND(
+        podvizen_prosek_prodazba,
+        2
+    ) AS podvizen_prosek_prodazba,
+
+    ROUND(
+        kumulativen_prihod,
+        2
+    ) AS kumulativen_prihod,
+
+    rast_prodazba_procent,
+
+    proceneti_meseci_do_istekuvanje,
+
+    nivo_na_rizik,
+
+    rang_rizik
+
+FROM final_analysis
+
+ORDER BY
+    rang_rizik,
+    naslov;
+}}}
+
+
+=== Solution Relational Algebra ===
+
+Најпрво се издвојуваат реализираните нарачки:
+
+{{{
+N ← σ status='ZAVRSENA' (NARACKI)
+}}}
+
+Се поврзуваат книгите, ставките и реализираните нарачки:
+
+{{{
+R1 ←
+KNIGI
+⋈ KNIGI.kniga_id = SODRZI.kniga_id
+SODRZI
+
+R2 ←
+R1
+⋈ SODRZI.naracka_id = N.naracka_id
+N
+}}}
+
+Податоците се агрегираат на ниво книга–месец:
+
+{{{
+MBS ←
+kniga_id,
+naslov,
+month(datum)→mesec
+
+γ
+
+SUM(kolicina)
+    → prodadeni,
+
+SUM(kolicina × edinechna_cena)
+    → prihod,
+
+COUNT-DISTINCT(naracka_id)
+    → broj_naracki
+
+(R2)
+}}}
+
+Продажбата од претходниот достапен период се добива со:
+
+{{{
+H1 ←
+LAG(prodadeni)
+OVER (
+    PARTITION BY kniga_id
+    ORDER BY mesec
+)
+(MBS)
+}}}
+
+Приходот од претходниот достапен период се добива со:
+
+{{{
+H2 ←
+LAG(prihod)
+OVER (
+    PARTITION BY kniga_id
+    ORDER BY mesec
+)
+(H1)
+}}}
+
+Подвижниот просек се пресметува над тековниот и најмногу двата претходни достапни месечни записи:
+
+{{{
+H3 ←
+MOVING_AVG(prodadeni)
+OVER (
+    PARTITION BY kniga_id
+    ORDER BY mesec
+    ROWS BETWEEN
+        2 PRECEDING
+        AND CURRENT ROW
+)
+(H2)
+}}}
+
+Кумулативниот приход се пресметува од првиот достапен период до тековниот:
+
+{{{
+H4 ←
+CUMULATIVE_SUM(prihod)
+OVER (
+    PARTITION BY kniga_id
+    ORDER BY mesec
+)
+(H3)
+}}}
+
+Записите се рангираат обратно хронолошки:
+
+{{{
+H5 ←
+ROW_NUMBER()
+OVER (
+    PARTITION BY kniga_id
+    ORDER BY mesec DESC
+)
+(H4)
+}}}
+
+Се задржува најновиот достапен период за секоја книга:
+
+{{{
+LATEST ← σ rn=1 (H5)
+}}}
+
+Се додава моменталната залиха:
+
+{{{
+RB ←
+LATEST
+⋈ LATEST.kniga_id = KNIGI.kniga_id
+KNIGI
+}}}
+
+Се пресметуваат промената на продажбата и проценетото време до исцрпување:
+
+{{{
+RA ← π
+    kniga_id,
+    naslov,
+    mesec,
+    kolicina_na_zaliha,
+    prodadeni,
+    prodadeni_prethoden_mesec,
+    prihod,
+    prihod_prethoden_mesec,
+    broj_naracki,
+    podvizen_prosek_prodazba,
+    kumulativen_prihod,
+
+    (
+        100 ×
+        (
+            prodadeni -
+            prodadeni_prethoden_mesec
+        )
+        /
+        prodadeni_prethoden_mesec
+    )
+        → rast_prodazba_procent,
+
+    (
+        kolicina_na_zaliha /
+        podvizen_prosek_prodazba
+    )
+        → proceneti_meseci_do_istekuvanje
+
+(RB)
+}}}
+
+Со условна проширена проекција се определува нивото на ризик.
+
+Потоа книгите се рангираат според проценетото време до исцрпување:
+
+{{{
+RR ←
+DENSE_RANK()
+OVER (
+    ORDER BY
+    proceneti_meseci_do_istekuvanje ASC
+)
+(RA)
+}}}
+
+Конечниот резултат е:
+
+{{{
+RESULT ←
+τ rang_rizik ASC,
+  naslov ASC
+(RR)
+}}}
+
+
+=== Објаснување ===
+
+Во ''monthly_book_sales'' реализираните продажби се агрегираат на месечно ниво.
+
+Со:
+
+{{{
+DATE_TRUNC('month', n.datum)
+}}}
+
+се формира временска серија за секоја книга.
+
+За секој достапен месечен период се пресметуваат бројот на продадени примероци, приходот и бројот на различни реализирани нарачки.
+
+Во ''sales_with_history'' се користи ''LAG()'' за добивање на продажбата и приходот од претходниот достапен период.
+
+Дополнително се пресметува подвижен просек:
+
+{{{
+AVG(prodadeni) OVER (
+    PARTITION BY kniga_id
+    ORDER BY mesec
+    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
+)
+}}}
+
+Овој показател ја зема предвид продажбата од тековниот и најмногу двата претходни достапни месечни записи.
+
+Поради тоа проценката на залихата е помалку зависна од евентуално невообичаено висока или ниска продажба во само еден период.
+
+Со:
+
+{{{
+SUM(prihod) OVER (
+    PARTITION BY kniga_id
+    ORDER BY mesec
+    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+)
+}}}
+
+се добива кумулативниот приход.
+
+Во ''latest_period'' со ''ROW_NUMBER()'' се идентификува последниот достапен период за секоја книга.
+
+Потоа резултатот се поврзува со ''knigi'' за да се добие моменталната залиха.
+
+Проценетото време до исцрпување се пресметува како однос меѓу моменталната залиха и подвижниот просек на продажбата.
+
+''NULLIF'' спречува делење со нула.
+
+Со ''CASE'' книгите се класифицираат во различни нивоа на ризик, а со ''DENSE_RANK()'' се рангираат според проценетата итност за дополнување на залихата.
+
+Важно е дека ''LAG()'' го враќа претходниот достапен месечен запис. Ако во одреден календарски месец нема реализирана продажба за книгата, тој месец не се појавува како посебен ред со продажба 0.
+
 
 == Финална дискусија ==
 
-Двата извештаи користат податоци од повеќе релации и агрегатни функции за добивање аналитички информации за работењето на !BiblioPremium.
-
-Првиот извештај се фокусира на продажбата и приходите и овозможува анализа на успешноста на различните книги според бројот на продадени примероци и остварениот приход.
-
-Вториот извештај се фокусира на залихите и продажната динамика и овозможува идентификување на книги кај кои постои потенцијален ризик од исцрпување на залихата.
-
-И двата извештаи ги користат само нарачките со статус ''ZAVRSENA'', со што незавршените нарачки не влијаат врз аналитичките пресметки.
-
-Извештаите можат да се користат за периодична анализа на продажбата, приходите и залихите во !BiblioPremium.
+Двата извештаи се наменети за долгорочна аналитичка обработка на податоците во !BiblioPremium.
+
+Првиот извештај се фокусира на кварталните продажни перформанси. Тој комбинира анализа на книга, категорија и временски период и овозможува споредување на тековниот резултат со претходните периоди.
+
+Со него може да се идентификуваат книгите кои генерираат најголем дел од приходот на категоријата, да се следи нивниот ранг и да се анализира дали приходот расте, опаѓа или останува релативно стабилен.
+
+Вториот извештај има прогнозна цел. Историската продажба се анализира како временска серија и се користи за проценка на ризикот од исцрпување на моменталната залиха.
+
+Во двата извештаи се користат повеќе напредни SQL механизми:
+
+* CTE (Common Table Expressions);
+* повеќекратни JOIN операции;
+* повеќестепено агрегирање;
+* COUNT(DISTINCT ...);
+* SUM и AVG;
+* DATE_TRUNC;
+* RANK();
+* DENSE_RANK();
+* LAG();
+* ROW_NUMBER();
+* PARTITION BY;
+* прозорски функции;
+* window frame со ROWS BETWEEN;
+* кумулативни пресметки;
+* подвижен просек;
+* CASE;
+* NULLIF;
+* COALESCE;
+* процентуални показатели;
+* квартална анализа;
+* месечна анализа;
+* споредување со претходни периоди;
+* рангирање;
+* проценка на идно исцрпување на залиха.
+
+Со тоа извештаите не прикажуваат само податоци кои директно се наоѓаат во базата, туку од постојните трансакциски податоци изведуваат дополнителни информации за долгорочните продажни перформанси и идните потреби за залиха.