Напредни извештаи

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

Во релационата алгебра се користи проширена нотација: σ (селекција), π (проекција), ⋈ (спојување), γ (групирање и агрегатни функции), τ (подредување) и ← (доделување на привремена релација).

Најуспешни книги според продажба и приход

Data requirements description

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

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

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

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

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

Решение во SQL

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,
    ROUND(
        AVG(s.kolicina * s.edinechna_cena),
        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,

    ROUND(
        SUM(s.kolicina)::numeric /
        NULLIF(COUNT(DISTINCT n.naracka_id), 0),
        2
    ) AS prosecna_prodazba_po_naracka,

    ROUND(
        k.kolicina_na_zaliha::numeric /
        NULLIF(
            SUM(s.kolicina)::numeric /
            NULLIF(COUNT(DISTINCT n.naracka_id), 0),
            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

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)

Објаснување

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

Со γ се групираат податоците според книга и се пресметуваат вкупниот број на продадени примероци и бројот на различни реализирани нарачки.

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

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

Со τ резултатите се подредуваат растечки според проценетиот број на нарачки до исцрпување на залихата.

Финална дискусија

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

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

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

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

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