Advanced Reports

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

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

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

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

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

Квартална анализа на продажбата на книги по категории

Data requirements description

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

За секој квартал и книга се пресметуваат:

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

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

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

Solution SQL

WITH quarterly_sales AS (
    SELECT
        DATE_TRUNC('quarter', n.datum) AS kvartal,
        k.kniga_id,
        k.naslov,
        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_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,
        kat.kategorija_id,
        kat.naziv
),

sales_analysis AS (
    SELECT
        *,

        RANK() OVER (
            PARTITION BY kvartal, kategorija_id
            ORDER BY vkupen_prihod DESC
        ) AS rang_vo_kategorija,

        LAG(vkupen_prihod) OVER (
            PARTITION BY kniga_id, kategorija_id
            ORDER BY kvartal
        ) AS prihod_prethoden_kvartal

    FROM quarterly_sales
)

SELECT
    TO_CHAR(kvartal, 'YYYY-"Q"Q') AS kvartal,

    kategorija,
    kniga_id,
    naslov,

    broj_naracki,
    vkupno_prodadeni,

    ROUND(vkupen_prihod, 2) AS vkupen_prihod,

    ROUND(prosecna_cena, 2) AS prosecna_cena,

    rang_vo_kategorija,

    ROUND(
        COALESCE(prihod_prethoden_kvartal, 0),
        2
    ) AS prihod_prethoden_kvartal,

    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 sales_analysis

ORDER BY
    kvartal,
    kategorija,
    rang_vo_kategorija;

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

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

QS ←
quarter(datum),
kniga_id,
naslov,
kategorija_id,
kategorija

γ

COUNT-DISTINCT(naracka_id) → broj_naracki,
SUM(kolicina) → vkupno_prodadeni,
SUM(kolicina × edinechna_cena) → vkupen_prihod,
AVG(edinechna_cena) → prosecna_cena

(R2)

Во проширена аналитичка нотација се пресметува рангот на книгата во рамки на категоријата:

QS1 ←
RANK()
OVER (
    PARTITION BY kvartal, kategorija_id
    ORDER BY vkupen_prihod DESC
)
(QS)

Приходот од претходниот достапен квартал се добива со:

QS2 ←
LAG(vkupen_prihod)
OVER (
    PARTITION BY kniga_id, kategorija_id
    ORDER BY kvartal
)
(QS1)

Потоа се пресметува процентуалната промена:

A ← π
    kvartal,
    kategorija,
    kniga_id,
    naslov,
    broj_naracki,
    vkupno_prodadeni,
    vkupen_prihod,
    prosecna_cena,
    rang_vo_kategorija,
    prihod_prethoden_kvartal,

    100 ×
    (vkupen_prihod - prihod_prethoden_kvartal)
    /
    prihod_prethoden_kvartal
        → promena_prihod_procent

(QS2)

Конечниот резултат се подредува според квартал, категорија и ранг:

RESULT ←
τ kvartal ASC,
  kategorija ASC,
  rang_vo_kategorija ASC
(A)

Објаснување

Во првиот дел од прашалникот се поврзуваат пет релации: knigi, ima_kategorija, kategorii, sodrzi и naracki.

Со:

DATE_TRUNC('quarter', n.datum)

нарачките се групираат по квартали.

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

Со:

RANK() OVER (
    PARTITION BY kvartal, kategorija_id
    ORDER BY vkupen_prihod DESC
)

книгите се рангираат според приходот во рамки на нивната категорија.

Со LAG() се добива приходот на истата книга од претходниот достапен квартален период.

Потоа се пресметува процентуалната промена на приходот.

Со CASE трендот се класифицира како:

• RAST – приходот е зголемен за повеќе од 10%;
• PAD – приходот е намален за повеќе од 10%;
• STABILNO – промената е во интервал од ±10%;
• NEMA PRETHODEN PERIOD – нема претходен запис за споредба.

Со овој извештај може да се следат продажните перформанси на книгите низ повеќе квартали.

Анализа на продажбата и проценка на ризик од исцрпување на залихата

Data requirements description

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

Продажбите прво се групираат по книга и месец.

За секоја книга се анализираат:

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

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

Solution SQL

WITH monthly_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

    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_analysis AS (
    SELECT
        *,

        LAG(prodadeni) OVER (
            PARTITION BY kniga_id
            ORDER BY mesec
        ) AS prodadeni_prethoden_mesec,

        AVG(prodadeni) OVER (
            PARTITION BY kniga_id
            ORDER BY mesec
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS prosecna_prodazba

    FROM monthly_sales
),

latest_sales AS (
    SELECT DISTINCT ON (kniga_id)
        kniga_id,
        naslov,
        mesec,
        prodadeni,
        prihod,
        prodadeni_prethoden_mesec,
        prosecna_prodazba

    FROM sales_analysis

    ORDER BY
        kniga_id,
        mesec DESC
)

SELECT
    ls.kniga_id,
    ls.naslov,

    TO_CHAR(ls.mesec, 'YYYY-MM') AS posleden_period,

    k.kolicina_na_zaliha,

    ls.prodadeni AS prodadeni_posleden_period,

    ls.prodadeni_prethoden_mesec,

    ROUND(
        ls.prosecna_prodazba,
        2
    ) AS prosecna_mesecna_prodazba,

    ROUND(
        (
            (ls.prodadeni - ls.prodadeni_prethoden_mesec)::numeric
            /
            NULLIF(ls.prodadeni_prethoden_mesec, 0)
        ) * 100,
        2
    ) AS promena_prodazba_procent,

    ROUND(
        k.kolicina_na_zaliha::numeric
        /
        NULLIF(ls.prosecna_prodazba, 0),
        2
    ) AS proceneti_meseci_do_istekuvanje,

    CASE
        WHEN
            k.kolicina_na_zaliha::numeric
            /
            NULLIF(ls.prosecna_prodazba, 0) <= 2
            THEN 'VISOK RIZIK'

        WHEN
            k.kolicina_na_zaliha::numeric
            /
            NULLIF(ls.prosecna_prodazba, 0) <= 4
            THEN 'SREDEN RIZIK'

        ELSE 'NIZOK RIZIK'
    END AS nivo_na_rizik

FROM latest_sales ls

JOIN project.knigi k
    ON ls.kniga_id = k.kniga_id

ORDER BY
    proceneti_meseci_do_istekuvanje ASC NULLS LAST,
    ls.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

Продажбите се групираат по книга и месец:

MS ←
kniga_id,
naslov,
month(datum) → mesec

γ

SUM(kolicina) → prodadeni,
SUM(kolicina × edinechna_cena) → prihod

(R2)

Продажбата од претходниот достапен месечен период се добива со:

MS1 ←
LAG(prodadeni)
OVER (
    PARTITION BY kniga_id
    ORDER BY mesec
)
(MS)

Просечната продажба од тековниот и најмногу двата претходни достапни периоди се пресметува со:

MS2 ←
MOVING_AVG(prodadeni)
OVER (
    PARTITION BY kniga_id
    ORDER BY mesec
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
(MS1)

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

LATEST ←
LATEST_BY(mesec)
GROUP BY kniga_id
(MS2)

Резултатот се поврзува со книгите за да се добие моменталната залиха:

RISK ←
LATEST
⋈ LATEST.kniga_id = KNIGI.kniga_id
KNIGI

Се пресметуваат процентуалната промена и проценетото време до исцрпување:

A ← π
    kniga_id,
    naslov,
    mesec,
    kolicina_na_zaliha,
    prodadeni,
    prodadeni_prethoden_mesec,
    prosecna_prodazba,

    100 ×
    (prodadeni - prodadeni_prethoden_mesec)
    /
    prodadeni_prethoden_mesec
        → promena_prodazba_procent,

    kolicina_na_zaliha
    /
    prosecna_prodazba
        → proceneti_meseci_do_istekuvanje

(RISK)

Конечниот резултат се подредува од книгите со најголем ризик кон книгите со помал ризик:

RESULT ←
τ proceneti_meseci_do_istekuvanje ASC
(A)

Објаснување

Во monthly_sales продажбите се групираат по книга и месец.

Со:

DATE_TRUNC('month', n.datum)

се добива месечна временска анализа.

За секој месец се пресметуваат бројот на продадени примероци и остварениот приход.

Со LAG() се добива продажбата од претходниот достапен месечен период.

Со:

AVG(prodadeni) OVER (
    PARTITION BY kniga_id
    ORDER BY mesec
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

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

Овој просек се користи како основа за проценка на идната потрошувачка на залихата.

Во latest_sales се задржува последниот достапен период за секоја книга.

Потоа податоците се поврзуваат со knigi за да се добие моменталната залиха.

Проценетото време до исцрпување се пресметува како:

моментална залиха / просечна месечна продажба

Со CASE книгите се делат во три групи:

• VISOK RIZIK – залихата се проценува дека ќе трае најмногу 2 месеци;
• SREDEN RIZIK – залихата се проценува дека ќе трае најмногу 4 месеци;
• NIZOK RIZIK – залихата се проценува дека ќе трае повеќе од 4 месеци.

NULLIF се користи за да се избегне делење со нула.

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

Заклучок

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

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

Во извештаите се користат:

• повеќекратни JOIN операции;
• CTE изрази;
• COUNT(DISTINCT ...);
• SUM и AVG;
• GROUP BY;
• DATE_TRUNC;
• RANK();
• LAG();
• PARTITION BY;
• прозорски функции;
• подвижен просек;
• CASE;
• NULLIF;
• COALESCE;
• процентуални пресметки;
• квартална и месечна анализа.

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