Changes between Version 19 and Version 20 of Напредни извештаи од базата (SQL и складирани процедури)

Timestamp:

02/13/25 11:29:45 (9 months ago)

Author:

222039

Comment:

--

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

@@ -195 +195 @@
     d.start_date DESC, TotalLoans DESC;
 }}}
+
+=== Извештај за најпозајмени жанри на временски периоди ===
+{{{#!sql
+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;
+}}}