Changes between Version 6 and Version 7 of Напредни извештаи од базата (SQL и складирани процедури)
- Timestamp:
- 08/15/25 23:40:48 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Напредни извештаи од базата (SQL и складирани процедури)
v6 v7 7 7 CREATE VIEW MostPopularFlightsReport AS 8 8 SELECT 9 f. flightid,10 f. flight_number,11 f. departure_airport,12 f. arrival_airport,13 COUNT(b .BookingID) AS NumberOfBookings9 f.FlightID, 10 f.Flight_Number, 11 f.Departure_Airport, 12 f.Arrival_Airport, 13 COUNT(bf.BookingID) AS NumberOfBookings 14 14 FROM 15 15 Flight f 16 JOIN 17 Booking b ON f.flightid = b.flightid 18 GROUP BY 19 f.flightid, f.flight_number, f.departure_airport, f.arrival_airport 16 JOIN BookingFlight bf ON f.FlightID = bf.FlightID 17 GROUP BY 18 f.FlightID, f.Flight_Number, f.Departure_Airport, f.Arrival_Airport 20 19 ORDER BY 21 20 NumberOfBookings DESC; … … 27 26 {{{ 28 27 29 30 28 CREATE VIEW AverageDestinationRatingReport AS 31 29 SELECT 32 d. destinationid,33 d. name AS DestinationName,34 AVG(r. rating) AS AverageRating30 d.DestinationID, 31 d.Name AS DestinationName, 32 AVG(r.Rating) AS AverageRating 35 33 FROM 36 34 Review r 37 JOIN 38 Destination d ON r.targetid = d.DestinationID 39 GROUP BY 40 d.destinationid, d.name 35 JOIN Booking b ON r.BookingID = b.BookingID 36 JOIN BookingFlight bf ON b.BookingID = bf.BookingID 37 JOIN Flight f ON bf.FlightID = f.FlightID 38 JOIN Airport a ON f.Arrival_Airport = a.AirportID 39 JOIN Destination d ON a.DestinationID = d.DestinationID 40 GROUP BY 41 d.DestinationID, d.Name 41 42 ORDER BY 42 43 AverageRating DESC; … … 50 51 CREATE VIEW UserBookingExpenditureReport AS 51 52 SELECT 52 u.userid, 53 u.name, 54 u.surname, 55 COUNT(b.bookingid) AS NumberOfBookings, 56 SUM(b.total_cost) AS TotalExpenditure 57 FROM 58 application_user u 59 JOIN 60 Booking b ON u.userid = b.userid 61 GROUP BY 62 u.userid, u.name, u.surname 53 u.UserID, 54 u.Name, 55 u.Surname, 56 COUNT(DISTINCT b.BookingID) AS NumberOfBookings, 57 SUM(b.Total_Cost) AS TotalExpenditure 58 FROM 59 ApplicationUser u 60 JOIN Booking b ON u.UserID = b.UserID 61 GROUP BY 62 u.UserID, u.Name, u.Surname 63 63 ORDER BY 64 64 TotalExpenditure DESC; 65 65 66 66 67 }}} 67 68 … … 72 73 CREATE VIEW PendingPaymentBookingsReport AS 73 74 SELECT 74 b.bookingid, 75 u.name AS UserName, 76 u.surname AS UserSurname, 77 f.flight_number, 78 f.departure_airport, 79 f.arrival_airport, 80 b.booking_date, 81 b.total_cost 75 b.BookingID, 76 u.Name AS UserName, 77 u.Surname AS UserSurname, 78 STRING_AGG(f.Flight_Number, ', ') AS Flights, 79 b.Booking_Date, 80 b.Total_Cost 82 81 FROM 83 82 Booking b 84 JOIN 85 application_user u ON b.userid = u.userid 86 JOIN 87 Flight f ON b.flightid = f.flightid 83 JOIN ApplicationUser u ON b.UserID = u.UserID 84 JOIN BookingFlight bf ON b.BookingID = bf.BookingID 85 JOIN Flight f ON bf.FlightID = f.FlightID 88 86 WHERE 89 b.payment_status = 'PENDING'; 87 b.Payment_Status = 'PENDING' 88 GROUP BY 89 b.BookingID, u.Name, u.Surname, b.Booking_Date, b.Total_Cost; 90 90 91 91 }}} … … 97 97 CREATE VIEW TopDestinationsWishlistReport AS 98 98 SELECT 99 d. destinationid,100 d. name AS DestinationName,101 d. country,102 COUNT(w. wishlistid) AS NumberOfWishlistEntries99 d.DestinationID, 100 d.Name AS DestinationName, 101 d.Country, 102 COUNT(w.WishlistID) AS NumberOfWishlistEntries 103 103 FROM 104 104 Wishlist w 105 JOIN 106 Destination d ON w.targetid = d.destinationid 107 GROUP BY 108 d.destinationid, d.name, d.country 105 JOIN Destination d ON w.TargetID = d.DestinationID 106 GROUP BY 107 d.DestinationID, d.Name, d.Country 109 108 ORDER BY 110 109 NumberOfWishlistEntries DESC … … 119 118 CREATE VIEW TopUsersByBookings AS 120 119 SELECT 121 u.userid, 122 u.name, 123 u.surname, 124 COUNT(b.bookingid) AS NumberOfBookings 125 FROM 126 application_user u 127 JOIN 128 Booking b ON u.userid = b.userid 129 GROUP BY 130 u.userid, u.name, u.surname 120 u.UserID, 121 u.Name, 122 u.Surname, 123 COUNT(DISTINCT b.BookingID) AS NumberOfBookings 124 FROM 125 ApplicationUser u 126 JOIN Booking b ON u.UserID = b.UserID 127 GROUP BY 128 u.UserID, u.Name, u.Surname 131 129 ORDER BY 132 130 NumberOfBookings DESC; 133 131 132 134 133 }}} 135 134 … … 140 139 CREATE VIEW FrequentFlightReviewers AS 141 140 SELECT 142 u. userid,143 u. name,144 u. surname,145 COUNT(r. reviewid) AS NumberOfReviews141 u.UserID, 142 u.Name, 143 u.Surname, 144 COUNT(r.ReviewID) AS NumberOfReviews 146 145 FROM 147 146 Review r 148 JOIN 149 application_user u ON r.userid = u.userid 150 WHERE 151 r.targetid IN (SELECT FlightID FROM Flight) 152 GROUP BY 153 u. userid, u.name, u.surname147 JOIN Booking b ON r.BookingID = b.BookingID 148 JOIN ApplicationUser u ON b.UserID = u.UserID 149 JOIN BookingFlight bf ON b.BookingID = bf.BookingID 150 JOIN Flight f ON bf.FlightID = f.FlightID 151 GROUP BY 152 u.UserID, u.Name, u.Surname 154 153 ORDER BY 155 154 NumberOfReviews DESC; 156 155 156 157 157 }}} 158 158 … … 164 164 CREATE VIEW PaymentMethodSpendReport AS 165 165 SELECT 166 p. payment_method,166 p.Payment_Method, 167 167 SUM(p.Amount) AS TotalAmountSpent 168 168 FROM 169 169 Payment p 170 170 GROUP BY 171 p. payment_method171 p.Payment_Method 172 172 ORDER BY 173 173 TotalAmountSpent DESC; … … 179 179 {{{ 180 180 181 CREATE VIEW FlightBookingBy CityAS182 SELECT 183 a.airportid,184 c.name AS CityName,185 COUNT(b. bookingid) AS NumberOfBookings181 CREATE VIEW FlightBookingByDepartureDestination AS 182 SELECT 183 depDest.DestinationID, 184 depDest.Name AS DepartureDestinationName, 185 COUNT(b.BookingID) AS NumberOfBookings 186 186 FROM 187 187 Booking b 188 JOIN 189 Flight f ON b.FlightID = f.FlightID 190 JOIN 191 Airport a ON f.departure_airport = a.airportid 192 JOIN 193 Destination c ON a.city = c.destinationid 194 GROUP BY 195 a.airportid, c.name 188 JOIN BookingFlight bf ON b.BookingID = bf.BookingID 189 JOIN Flight f ON bf.FlightID = f.FlightID 190 JOIN Airport depA ON f.Departure_Airport = depA.AirportID 191 JOIN Destination depDest ON depA.DestinationID = depDest.DestinationID 192 GROUP BY 193 depDest.DestinationID, depDest.Name 196 194 ORDER BY 197 195 NumberOfBookings DESC; … … 203 201 {{{ 204 202 205 CREATE VIEW TopMon htlyReport AS203 CREATE VIEW TopMonthlyReport AS 206 204 WITH MonthlyBookings AS ( 207 205 SELECT 208 DATE_TRUNC('month', b.booking_date) AS month, 209 d.name AS destination_name, 210 COUNT(b.bookingid) AS bookings_count, 211 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank 206 DATE_TRUNC('month', b.Booking_Date) AS Month, 207 d.Name AS DestinationName, 208 COUNT(b.BookingID) AS BookingsCount, 209 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date) 210 ORDER BY COUNT(b.BookingID) DESC) AS Rank 212 211 FROM Booking b 213 JOIN Flight f ON b.flightid = f.flightid 214 JOIN Airport a ON f.arrival_airport = a.airportid 215 JOIN Destination d ON a.city = d.destinationid 216 GROUP BY month, destination_name 212 JOIN BookingFlight bf ON b.BookingID = bf.BookingID 213 JOIN Flight f ON bf.FlightID = f.FlightID 214 JOIN Airport a ON f.Arrival_Airport = a.AirportID 215 JOIN Destination d ON a.DestinationID = d.DestinationID 216 GROUP BY Month, d.Name 217 217 ), 218 219 218 MonthlyFlights AS ( 220 219 SELECT 221 DATE_TRUNC('month', b.booking_date) AS month, 222 f.flight_number, 223 COUNT(b.bookingid) AS bookings_count, 224 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank 220 DATE_TRUNC('month', b.Booking_Date) AS Month, 221 f.Flight_Number, 222 COUNT(b.BookingID) AS BookingsCount, 223 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date) 224 ORDER BY COUNT(b.BookingID) DESC) AS Rank 225 225 FROM Booking b 226 JOIN Flight f ON b.flightid = f.flightid 227 GROUP BY month, f.flight_number 226 JOIN BookingFlight bf ON b.BookingID = bf.BookingID 227 JOIN Flight f ON bf.FlightID = f.FlightID 228 GROUP BY Month, f.Flight_Number 228 229 ), 229 230 230 MonthlyAirports AS ( 231 231 SELECT 232 DATE_TRUNC('month', b.booking_date) AS month, 233 a.name AS airport_name, 234 COUNT(b.bookingid) AS departures_count, 235 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.booking_date) ORDER BY COUNT(b.bookingid) DESC) AS rank 232 DATE_TRUNC('month', b.Booking_Date) AS Month, 233 a.Name AS AirportName, 234 COUNT(b.BookingID) AS DeparturesCount, 235 RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date) 236 ORDER BY COUNT(b.BookingID) DESC) AS Rank 236 237 FROM Booking b 237 JOIN Flight f ON b.flightid = f.flightid 238 JOIN Airport a ON f.departure_airport = a.airportid 239 GROUP BY month, a.name 238 JOIN BookingFlight bf ON b.BookingID = bf.BookingID 239 JOIN Flight f ON bf.FlightID = f.FlightID 240 JOIN Airport a ON f.Departure_Airport = a.AirportID 241 GROUP BY Month, a.Name 240 242 ) 241 242 SELECT month, 'Destination' AS category, destination_name AS name, bookings_count AS count FROM MonthlyBookings WHERE rank <= 3243 SELECT Month, 'Destination' AS Category, DestinationName AS Name, BookingsCount AS Count 244 FROM MonthlyBookings WHERE Rank <= 3 243 245 UNION ALL 244 SELECT month, 'Flight' AS category, flight_number AS name, bookings_count AS count FROM MonthlyFlights WHERE rank <= 3 246 SELECT Month, 'Flight' AS Category, Flight_Number AS Name, BookingsCount AS Count 247 FROM MonthlyFlights WHERE Rank <= 3 245 248 UNION ALL 246 SELECT month, 'Airport' AS category, airport_name AS name, departures_count AS count FROM MonthlyAirports WHERE rank <= 3 247 ORDER BY month, category, count DESC; 248 249 }}} 249 SELECT Month, 'Airport' AS Category, AirportName AS Name, DeparturesCount AS Count 250 FROM MonthlyAirports WHERE Rank <= 3 251 ORDER BY Month, Category, Count DESC; 252 253 }}}
