Advanced Reports

Во оваа фаза се изработени два комплексни аналитички извештаи над податоците од базата на BiblioPremium.

Извештаите се наменети за долгорочна анализа на работењето на системот и користат податоци од повеќе релации, повеќестепено агрегирање, временска анализа, CTE (Common Table Expressions), прозорски функции, рангирање и пресметани аналитички показатели.

Во двата извештаи се земаат предвид само реализираните нарачки со статус ZAVRSENA.

Во релационата алгебра се користи проширена нотација:

• σ – селекција
• π – генерализирана проекција со пресметани атрибути
• ⋈ – спојување
• ⟕ – лево надворешно спојување
• ρ – преименување
• γ – групирање и агрегатни функции
• τ – подредување
• ← – доделување на привремена релација

За аналитичките операции како RANK, DENSE_RANK, LAG, ROW_NUMBER и подвижни агрегати се користи проширена аналитичка нотација.

Квартална анализа и рангирање на продажните перформанси на книгите по категории

Data requirements description

Целта на извештајот е да се анализира како се менува продажбата на книгите и категориите низ кварталите и да се идентификуваат книгите кои имаат најголемо влијание врз приходот.

За секој квартал, категорија и книга потребно е да се прикажат:

• бројот на различни реализирани нарачки;
• вкупниот број на продадени примероци;
• вкупниот остварен приход;
• просечната продажна цена;
• вкупниот приход на категоријата во кварталот;
• просечниот приход по продавана книга во категоријата;
• рангот на книгата во рамки на категоријата за конкретниот квартал;
• процентуалното учество на книгата во приходот на категоријата;
• приходот на истата книга во претходниот достапен квартал;
• процентуалната промена на приходот во однос на претходниот достапен квартал;
• кумулативниот приход на книгата низ кварталите;
• класификација на трендот на продажба.

Извештајот овозможува квартално, полугодишно, годишно и повеќегодишно следење на продажбата.

Со него може да се утврди кои книги носат најголем приход во својата категорија, како нивната продажба се менува низ времето и дали одредена книга покажува растечки или опаѓачки тренд.

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
    TO_CHAR(
        kvartal,
        'YYYY-"Q"Q'
    ) AS kvartal,

    kategorija,
    kniga_id,
    naslov,
    isbn,

    broj_naracki,
    vkupno_prodadeni,

    ROUND(
        vkupen_prihod,
        2
    ) AS vkupen_prihod,

    ROUND(
        prosecna_prodazna_cena,
        2
    ) AS prosecna_prodazna_cena,

    broj_prodavani_knigi,

    vkupno_prodadeni_kategorija,

    ROUND(
        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 prihod_prethoden_kvartal,

    promena_prihod_procent,

    ROUND(
        kumulativen_prihod,
        2
    ) AS kumulativen_prihod,

    trend

FROM final_analysis

ORDER BY
    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)

Објаснување

Извештајот користи повеќе нивоа на обработка.

Во 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.

Првиот извештај се фокусира на кварталните продажни перформанси. Тој комбинира анализа на книга, категорија и временски период и овозможува споредување на тековниот резултат со претходните периоди.

Со него може да се идентификуваат книгите кои генерираат најголем дел од приходот на категоријата, да се следи нивниот ранг и да се анализира дали приходот расте, опаѓа или останува релативно стабилен.

Вториот извештај има прогнозна цел. Историската продажба се анализира како временска серија и се користи за проценка на ризикот од исцрпување на моменталната залиха.

Во двата извештаи се користат повеќе напредни 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;
• процентуални показатели;
• квартална анализа;
• месечна анализа;
• споредување со претходни периоди;
• рангирање;
• проценка на идно исцрпување на залиха.

Со тоа извештаите не прикажуваат само податоци кои директно се наоѓаат во базата, туку од постојните трансакциски податоци изведуваат дополнителни информации за долгорочните продажни перформанси и идните потреби за залиха.