wiki:Напредни извештаи од базата (SQL и складирани процедури)

Context Navigation

Напредни извештаи од базата (SQL и складирани процедури)

Извештај за членови со најмногу позајмувања по месец

SELECT 
    users.FirstName,
    users.LastName,
    EXTRACT(MONTH FROM loan.LoanDate) AS month,
    COUNT(loan.LoanID) AS loan_count
FROM users
INNER JOIN loan ON users.UserID = loan.MemberID
WHERE EXTRACT(MONTH FROM loan.LoanDate) = 4  -- Change this number for different months (1-12)
GROUP BY 
    users.UserID, 
    users.FirstName, 
    users.LastName,
    EXTRACT(MONTH FROM loan.LoanDate)
ORDER BY loan_count DESC
LIMIT 5;

π_{FirstName, LastName, month, loan_count} (σ_{EXTRACT(MONTH FROM LoanDate) = 4} (Users ⨝{Users.UserID = Loan.MemberID} Loan) γ{Users.UserID, Users.FirstName, Users.LastName, EXTRACT(MONTH FROM Loan.LoanDate)} COUNT(Loan.LoanID) → loan_count τ_{loan_count DESC} ρ_{LIMIT 5})

Извештај за најпозајмени книги по тримесечие

SELECT 
    book.CoverImage,
    book.Title,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
    COUNT(loan.LoanID) AS loan_count
FROM book
INNER JOIN book_copies ON book.BookID = book_copies.BookID
LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
INNER JOIN book_author ON book.BookID = book_author.BookID
INNER JOIN author ON book_author.AuthorID = author.AuthorID
WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
GROUP BY 
    book.BookID,
    book.Title,
    book.CoverImage,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate)
ORDER BY loan_count DESC
LIMIT 5;

π_{CoverImage, Title, Genre, FirstName, LastName, quarter, loan_count} (σ_{EXTRACT(QUARTER FROM LoanDate) = 4} ((Book ⨝{Book.BookID = Book_Copies.BookID} Book_Copies) ⟕{Book_Copies.CopyID = Loan.BookCopyID} Loan ⨝{Book.BookID = Book_Author.BookID} Book_Author ⨝{Book_Author.AuthorID = Author.AuthorID} Author) γ_{Book.BookID, Book.Title, Book.CoverImage, Book.Genre, Author.FirstName, Author.LastName, EXTRACT(QUARTER FROM Loan.LoanDate)} COUNT(Loan.LoanID) → loan_count τ_{loan_count DESC} ρ_{LIMIT 5})

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

SELECT 
    book.CoverImage,
    book.Title,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
    COUNT(loan.LoanID) AS loan_count
FROM book
INNER JOIN book_copies ON book.BookID = book_copies.BookID
LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
INNER JOIN book_author ON book.BookID = book_author.BookID
INNER JOIN author ON book_author.AuthorID = author.AuthorID
WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
GROUP BY 
    book.BookID,
    book.Title,
    book.CoverImage,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate)
ORDER BY loan_count ASC
LIMIT 5;

π_{CoverImage, Title, Genre, FirstName, LastName, quarter, loan_count}
(σ_{EXTRACT(QUARTER FROM LoanDate) = 4}
((Book ⨝{Book.BookID = Book_Copies.BookID} Book_Copies)
⟕{Book_Copies.CopyID = Loan.BookCopyID} Loan
⨝{Book.BookID = Book_Author.BookID} Book_Author
⨝{Book_Author.AuthorID = Author.AuthorID} Author)
γ_{Book.BookID, Book.Title, Book.CoverImage, Book.Genre, Author.FirstName, Author.LastName, EXTRACT(QUARTER FROM Loan.LoanDate)} COUNT(Loan.LoanID) → loan_count
τ_{loan_count ASC} ρ_{LIMIT 5})'

                     π (CoverImage, Title, Genre, FirstName, LastName, quarter, loan_count)
                                                |
                                    τ (ORDER BY loan_count ASC, LIMIT 5)
                                                |
                      γ (GROUP BY BookID, Title, CoverImage, Genre, FirstName, LastName, quarter, COUNT(LoanID) → loan_count)
                                                |
                           σ (EXTRACT(QUARTER FROM LoanDate) = 4)
                                                |
         ┌───────────────────────────────────────────⨝──────────────────────────────────────────────┐
         |                                                                                          |
    (Book ⨝ Book_Copies)                                                              (Loan ⟕ Book_Copies.CopyID = Loan.BookCopyID)
         |                                                                                          |
  (Book ⨝ Book_Author)                                                                  (Loan Table)
         |
  (Book_Author ⨝ Author)
         |
      (Author Table)

Извештај за сума на платени и неплатени казни во текот на годината

SELECT 
    EXTRACT(YEAR FROM finedate) AS fine_year,
    SUM(CASE WHEN status = 'Paid' THEN fineamount ELSE 0 END) AS total_paid,
    SUM(CASE WHEN status = 'Unpaid' THEN fineamount ELSE 0 END) AS total_unpaid,
    SUM(fineamount) AS total_fines
FROM fine
WHERE EXTRACT(YEAR FROM finedate) = 2024  -- Change year as needed
GROUP BY EXTRACT(YEAR FROM finedate)
ORDER BY fine_year;

Извештај за задоцнети враќања и просечно доцнење по месец

SELECT 
    EXTRACT(YEAR FROM LoanDate) as year,
    EXTRACT(MONTH FROM LoanDate) as month,
    COUNT(*) as total_late_returns,
    AVG(ReturnDate - LoanDate) as avg_days_late
FROM Loan
WHERE ReturnDate > LoanDate + INTERVAL '14 days'
GROUP BY 
    EXTRACT(YEAR FROM LoanDate),
    EXTRACT(MONTH FROM LoanDate)
ORDER BY year, month;

Извештај за кондиција на книга

SELECT 
    b.Title,
    bc.Condition,
    COUNT(*) as copy_count
FROM Book b
JOIN Book_Copies bc ON b.BookID = bc.BookID
GROUP BY b.Title, bc.Condition
ORDER BY b.Title;

Извештај за членовите

SELECT 
    u.FirstName,
    u.LastName,
    COUNT(l.LoanID) as total_loans,
    COUNT(f.FineID) as total_fines,
    SUM(f.FineAmount) as total_fine_amount
FROM Users u
JOIN Member m ON u.UserID = m.UserID
LEFT JOIN Loan l ON m.MemberID = l.MemberID
LEFT JOIN Fine f ON l.LoanID = f.LoanID
GROUP BY u.UserID, u.FirstName, u.LastName
ORDER BY total_loans DESC;

Извештај за анализа на времето за враќање

SELECT 
    b.Title,
    AVG(l.ReturnDate - l.LoanDate) as AvgDaysToReturn,
    MIN(l.ReturnDate - l.LoanDate) as MinDaysToReturn,
    MAX(l.ReturnDate - l.LoanDate) as MaxDaysToReturn,
    COUNT(*) as TotalLoans,
    COUNT(CASE WHEN f.FineID IS NOT NULL THEN 1 END) as LoansWithFines
FROM 
    Loan l
    JOIN Book_Copies bc ON l.BookCopyID = bc.CopyID
    JOIN Book b ON bc.BookID = b.BookID
    LEFT JOIN Fine f ON l.LoanID = f.LoanID
WHERE 
    l.ReturnDate IS NOT NULL
GROUP BY 
    b.BookID, b.Title
HAVING 
    COUNT(*) >= 5  -- Only show books with at least 5 loans
ORDER BY 
    AVG(l.ReturnDate - l.LoanDate) DESC;

π_{Title, AvgDaysToReturn, MinDaysToReturn, MaxDaysToReturn, TotalLoans, LoansWithFines}
(σ_{TotalLoans ≥ 5}
(γ_{Book.BookID, Book.Title}
(Loan ⨝{Loan.BookCopyID = Book_Copies.CopyID} Book_Copies
⨝{Book_Copies.BookID = Book.BookID} Book
⟕{Loan.LoanID = Fine.LoanID} Fine)
AVG(Loan.ReturnDate - Loan.LoanDate) → AvgDaysToReturn,
MIN(Loan.ReturnDate - Loan.LoanDate) → MinDaysToReturn,
MAX(Loan.ReturnDate - Loan.LoanDate) → MaxDaysToReturn,
COUNT(*) → TotalLoans,
COUNT(σ{Fine.FineID IS NOT NULL} (*)) → LoansWithFines)
τ_{AvgDaysToReturn DESC})

Извештај за активност на членови

WITH DateRanges AS (
    SELECT
        generate_series(
            date_trunc('year', CURRENT_DATE) - interval '5 year',
            date_trunc('year', CURRENT_DATE),
            interval '1 year'
        ) as start_date,
        generate_series(
            date_trunc('year', CURRENT_DATE) - interval '4 year',
            date_trunc('year', CURRENT_DATE),
            interval '1 year'
        ) as end_date
)
SELECT 
    u.FirstName || ' ' || u.LastName as MemberName,
    m.Membership_Status,
    d.start_date::date as PeriodStart,
    d.end_date::date as PeriodEnd,
    COUNT(DISTINCT l.LoanID) as TotalLoans,
    COUNT(DISTINCT CASE WHEN l.ReturnDate IS NULL THEN l.LoanID END) as ActiveLoans,
    COUNT(DISTINCT f.FineID) as TotalFines,
    COALESCE(SUM(f.FineAmount), 0) as TotalFineAmount,
    COALESCE(SUM(fp.PaymentAmount), 0) as TotalPaidAmount
FROM 
    DateRanges d
    CROSS JOIN Member m
    JOIN Users u ON m.UserID = u.UserID
    LEFT JOIN Loan l ON m.MemberID = l.MemberID 
        AND l.LoanDate >= d.start_date 
        AND l.LoanDate < d.end_date
    LEFT JOIN Fine f ON l.LoanID = f.LoanID
    LEFT JOIN FinePayment fp ON f.FineID = fp.FineID
GROUP BY 
    u.FirstName, u.LastName, m.Membership_Status, d.start_date, d.end_date
ORDER BY 
    d.start_date DESC, TotalLoans DESC;

π_{MemberName, Membership_Status, PeriodStart, PeriodEnd, TotalLoans, ActiveLoans, TotalFines, TotalFineAmount, TotalPaidAmount} (τ_{PeriodStart DESC, TotalLoans DESC}

(γ_{FirstName, LastName, Membership_Status, start_date, end_date}

(σ_{Loan.LoanDate ≥ start_date ∧ Loan.LoanDate < end_date}

(DateRanges × Member ⨝_{Member.UserID = Users.UserID} Users

⟕_{Member.MemberID = Loan.MemberID} Loan ⟕_{Loan.LoanID = Fine.LoanID} Fine ⟕_{Fine.FineID = FinePayment.FineID} FinePayment

)

) COUNT(DISTINCT Loan.LoanID) → TotalLoans, COUNT(DISTINCT σ_{Loan.ReturnDate IS NULL} (Loan.LoanID)) → ActiveLoans, COUNT(DISTINCT Fine.FineID) → TotalFines, COALESCE(SUM(Fine.FineAmount), 0) → TotalFineAmount, COALESCE(SUM(FinePayment.PaymentAmount), 0) → TotalPaidAmount

)

)

                                      π (MemberName, Membership_Status, PeriodStart, PeriodEnd, TotalLoans, ActiveLoans, TotalFines, TotalFineAmount, TotalPaidAmount)
                                                                                   |
                                                           τ (ORDER BY PeriodStart DESC, TotalLoans DESC)
                                                                                   |
                                   γ (GROUP BY FirstName, LastName, Membership_Status, start_date, end_date, Aggregate Functions)
                                                                                   |
                             σ (Loan.LoanDate ≥ start_date AND Loan.LoanDate < end_date)
                                                                                   |
    ┌───────────────────────────×──────────────────────────────┐
    |                                                           |
 (DateRanges)                                          (Member ⨝ Users)
                                                                   |
                                                                   ⨝
                                                                   |
                 ┌───────────────────────────────────────────────────────────────────⨝─────────────────────────────────────────────────────────────┐
                 |                                                                                                                             |
          (Member ⟕ Loan)                                                                                                             (Loan ⟕ Fine)
                                                                                                                                            |
                                                                                                                                            ⨝
                                                                                                                                            |
                                                                                                                                    (Fine ⟕ FinePayment)

Извештај за најпозајмени жанри на временски периоди

WITH TimePeriodLoans AS (
    SELECT 
        b.Genre,
        l.LoanDate,
        DATE_TRUNC('month', l.LoanDate) as MonthPeriod,
        DATE_TRUNC('quarter', l.LoanDate) as QuarterPeriod,
        DATE_TRUNC('year', l.LoanDate) as YearPeriod
    FROM 
        Book b
        JOIN Book_Copies bc ON b.BookID = bc.BookID
        JOIN Loan l ON bc.CopyID = l.BookCopyID
)
SELECT 
    Genre,
    Period,
    PeriodType,
    LoanCount,
    ROUND(AVG(LoanCount) OVER (
        PARTITION BY Genre, PeriodType 
        ORDER BY Period 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) as MovingAverage
FROM (
    SELECT 
        Genre,
        MonthPeriod as Period,
        'Monthly' as PeriodType,
        COUNT(*) as LoanCount
    FROM TimePeriodLoans
    GROUP BY Genre, MonthPeriod
    UNION ALL
    SELECT 
        Genre,
        QuarterPeriod,
        'Quarterly',
        COUNT(*)
    FROM TimePeriodLoans
    GROUP BY Genre, QuarterPeriod
    UNION ALL
    SELECT 
        Genre,
        YearPeriod,
        'Yearly',
        COUNT(*)
    FROM TimePeriodLoans
    GROUP BY Genre, YearPeriod
) as CombinedPeriods
ORDER BY 
    PeriodType, Period DESC, LoanCount DESC;

π_{Genre, Period, PeriodType, LoanCount, MovingAverage} (τ_{PeriodType ASC, Period DESC, LoanCount DESC}

(γ_{Genre, PeriodType, Period}

(σ_{Loan.LoanDate IS NOT NULL}

(

(Book ⨝_{Book.BookID = Book_Copies.BookID} Book_Copies ⨝_{Book_Copies.CopyID = Loan.BookCopyID} Loan)

)

) COUNT(*) → LoanCount

) ∪ γ_{Genre, PeriodType, Period}

(σ_{Loan.LoanDate IS NOT NULL}

(

(Book ⨝_{Book.BookID = Book_Copies.BookID} Book_Copies ⨝_{Book_Copies.CopyID = Loan.BookCopyID} Loan)

)

) COUNT(*) → LoanCount

) ∪ γ_{Genre, PeriodType, Period}

(σ_{Loan.LoanDate IS NOT NULL}

(

(Book ⨝_{Book.BookID = Book_Copies.BookID} Book_Copies ⨝_{Book_Copies.CopyID = Loan.BookCopyID} Loan)

)

) COUNT(*) → LoanCount

)

) ⨝ (

γ_{Genre, PeriodType} (

WINDOW_{Genre, PeriodType, ORDER BY Period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW} (

AVG(LoanCount) → MovingAverage

)

)

)

                                          π (Genre, Period, PeriodType, LoanCount, MovingAverage)
                                                                 |
                                                    τ (ORDER BY PeriodType, Period DESC, LoanCount DESC)
                                                                 |
                                          ⨝ (Join for Moving Average Calculation)
                                    ┌────────────────────────────────────────┐
                                    |                                        |
         γ (GROUP BY Genre, PeriodType, Period, Aggregate Functions)        γ (Moving Average Calculation)
                                    |                                        |
                              ∪ (UNION ALL)                                  γ (OVER PARTITION Genre, PeriodType ORDER BY Period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
          ┌──────────────────────────┴──────────────────────────┐
          |                          |                           |
    γ (Monthly Loans)          γ (Quarterly Loans)        γ (Yearly Loans)
          |                          |                           |
    σ (LoanDate IS NOT NULL)    σ (LoanDate IS NOT NULL)  σ (LoanDate IS NOT NULL)
          |                          |                           |
    (Book ⨝ Book_Copies ⨝ Loan) (Book ⨝ Book_Copies ⨝ Loan) (Book ⨝ Book_Copies ⨝ Loan)

Last modified 8 months ago Last modified on 02/24/25 16:20:22

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text