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

Timestamp:

02/24/25 16:20:22 (8 months ago)

Author:

222039

Comment:

--

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

@@ -334 +334 @@
     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  
+    )  
+  )  
+)
+
+{{{#!sql
+                                          π (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)
+
+}}}