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


Ignore:
Timestamp:
09/06/25 14:40:23 (41 hours ago)
Author:
173067
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

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

    v7 v8  
    11== Напредни извештаи од базата
    22
    3 * Извештај за најпопуларни летови по резервација
     3* Извештај за букинзи на корисник
    44
    55{{{
    66
    7 CREATE VIEW MostPopularFlightsReport AS
     7CREATE OR REPLACE VIEW v_user_bookings AS
    88SELECT
     9    u.UserID,
     10    u.Name || ' ' || u.Surname AS FullName,
     11    b.BookingID,
     12    b.BookingDate,
     13    b.PaymentStatus,
     14    b.TotalCost,
    915    f.FlightID,
    10     f.Flight_Number,
    11     f.Departure_Airport,
    12     f.Arrival_Airport,
    13     COUNT(bf.BookingID) AS NumberOfBookings
    14 FROM
    15     Flight f
    16 JOIN BookingFlight bf ON f.FlightID = bf.FlightID
    17 GROUP BY
    18     f.FlightID, f.Flight_Number, f.Departure_Airport, f.Arrival_Airport
    19 ORDER BY
    20     NumberOfBookings DESC;
    21 
    22 }}}
    23 
    24 * Извештај за просечен рејтинг по дестинација
    25 
    26 {{{
    27 
    28 CREATE VIEW AverageDestinationRatingReport AS
    29 SELECT
    30     d.DestinationID,
    31     d.Name AS DestinationName,
    32     AVG(r.Rating) AS AverageRating
    33 FROM
    34     Review r
    35 JOIN Booking b ON r.BookingID = b.BookingID
     16    f.FlightNumber,
     17    f.DepartureDate,
     18    f.ReturnDate,
     19    dep.Name AS DepartureAirport,
     20    arr.Name AS ArrivalAirport,
     21    f.Price
     22FROM Booking b
     23JOIN ApplicationUser u ON b.UserID = u.UserID
    3624JOIN BookingFlight bf ON b.BookingID = bf.BookingID
    3725JOIN 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
    42 ORDER BY
    43     AverageRating DESC;
    44 
    45 }}}
    46 
    47 * Извештај за историја на резервации на корисници со вкупни трошоци
    48 
    49 {{{
    50 
    51 CREATE VIEW UserBookingExpenditureReport AS
    52 SELECT
    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 ORDER BY
    64     TotalExpenditure DESC;
     26JOIN Airport dep ON f.DepartureAirportID = dep.AirportID
     27JOIN Airport arr ON f.ArrivalAirportID = arr.AirportID;
    6528
    6629
    6730}}}
    6831
    69 * Извештај за резервации со статус 'Pending'
     32* Извештај за летови
    7033
    7134{{{
    7235
    73 CREATE VIEW PendingPaymentBookingsReport AS
     36CREATE OR REPLACE VIEW v_flight_summary AS
    7437SELECT
    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
    81 FROM
    82     Booking b
    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
    86 WHERE
    87     b.Payment_Status = 'PENDING'
    88 GROUP BY
    89     b.BookingID, u.Name, u.Surname, b.Booking_Date, b.Total_Cost;
     38    f.FlightID,
     39    f.FlightNumber,
     40    dep.Name AS DepartureAirport,
     41    arr.Name AS ArrivalAirport,
     42    f.DepartureDate,
     43    f.ReturnDate,
     44    f.Price,
     45    f.AvailableSeats,
     46    COUNT(bf.BookingID) AS TotalBookings
     47FROM Flight f
     48JOIN Airport dep ON f.DepartureAirportID = dep.AirportID
     49JOIN Airport arr ON f.ArrivalAirportID = arr.AirportID
     50LEFT JOIN BookingFlight bf ON f.FlightID = bf.FlightID
     51GROUP BY f.FlightID, f.FlightNumber, dep.Name, arr.Name, f.DepartureDate, f.ReturnDate, f.Price, f.AvailableSeats;
    9052
    9153}}}
    9254
    93 * Извештај за топ 5 дестинации според Wishlist
     55* Извештај за рецензии
    9456
    9557{{{
    9658
    97 CREATE VIEW TopDestinationsWishlistReport AS
     59CREATE OR REPLACE VIEW v_reviews AS
    9860SELECT
    99     d.DestinationID,
    100     d.Name AS DestinationName,
    101     d.Country,
    102     COUNT(w.WishlistID) AS NumberOfWishlistEntries
    103 FROM
    104     Wishlist w
    105 JOIN Destination d ON w.TargetID = d.DestinationID
    106 GROUP BY
    107     d.DestinationID, d.Name, d.Country
    108 ORDER BY
    109     NumberOfWishlistEntries DESC
    110 LIMIT 5;
     61    r.ReviewID,
     62    u.Name || ' ' || u.Surname AS Reviewer,
     63    r.TargetID,
     64    r.TargetType,
     65    r.ReviewComment,
     66    r.Rating,
     67    r.Date
     68FROM Review r
     69JOIN ApplicationUser u ON r.UserID = u.UserID;
    11170
    11271}}}
    11372
    114 * Извештај за корисници кои имаат највеќе резервации
     73* Извештај за извршени плаќања по корисник
    11574
    11675{{{
    11776
    118 CREATE VIEW TopUsersByBookings AS
     77CREATE OR REPLACE VIEW v_user_payments AS
    11978SELECT
    12079    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
    129 ORDER BY
    130     NumberOfBookings DESC;
    131 
     80    u.Name || ' ' || u.Surname AS FullName,
     81    COUNT(p.PaymentID) AS NumPayments,
     82    SUM(p.Amount) AS TotalPaid,
     83    AVG(p.Amount) AS AvgPayment
     84FROM Payment p
     85JOIN ApplicationUser u ON p.UserID = u.UserID
     86GROUP BY u.UserID, u.Name, u.Surname;
    13287
    13388}}}
    13489
    135 * Извештај за корисници со највеќе рецензии
     90* Извештај за Wishlist
    13691
    13792{{{
    13893
    139 CREATE VIEW FrequentFlightReviewers AS
     94CREATE OR REPLACE VIEW v_user_wishlist AS
    14095SELECT
    14196    u.UserID,
    142     u.Name,
    143     u.Surname,
    144     COUNT(r.ReviewID) AS NumberOfReviews
    145 FROM
    146     Review r
    147 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
    153 ORDER BY
    154     NumberOfReviews DESC;
    155 
     97    u.Name || ' ' || u.Surname AS FullName,
     98    w.WishlistID,
     99    w.TargetID,
     100    w.TargetType,
     101    w.DateAdded
     102FROM Wishlist w
     103JOIN ApplicationUser u ON w.UserID = u.UserID;
    156104
    157105}}}
    158106
    159 
    160 * Извештај за најдобри начини на плаќање според вкупната потрошувачка
     107* Извештај за најдобро рангирани летови
    161108
    162109{{{
    163110
    164 CREATE VIEW PaymentMethodSpendReport AS
     111CREATE OR REPLACE VIEW v_best_value_flights AS
    165112SELECT
    166     p.Payment_Method,
    167     SUM(p.Amount) AS TotalAmountSpent
    168 FROM
    169     Payment p
    170 GROUP BY
    171     p.Payment_Method
    172 ORDER BY
    173     TotalAmountSpent DESC;
     113    f.FlightID,
     114    f.FlightNumber,
     115    f.Price,
     116    COALESCE(AVG(r.Rating), 0) AS AvgRating
     117FROM Flight f
     118LEFT JOIN Review r ON r.TargetType = 'Flight' AND r.TargetID = f.FlightID
     119GROUP BY f.FlightID, f.FlightNumber, f.Price
     120HAVING COALESCE(AVG(r.Rating),0) > 4 AND f.Price < 200;
    174121
    175122}}}
    176123
    177 * Извештај за резервација на летови по град на поаѓање
    178 
    179 {{{
    180 
    181 CREATE VIEW FlightBookingByDepartureDestination AS
    182 SELECT
    183     depDest.DestinationID,
    184     depDest.Name AS DepartureDestinationName,
    185     COUNT(b.BookingID) AS NumberOfBookings
    186 FROM
    187     Booking b
    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
    194 ORDER BY
    195     NumberOfBookings DESC;
    196 
    197 }}}
    198124
    199125* Извештај за топ 3 најпопуларни дестинации, аеродроми и летови за секој месец во годината
    … …  
    201127{{{
    202128
    203 CREATE VIEW TopMonthlyReport AS
     129CREATE OR REPLACE VIEW TopMonthlyReport AS
    204130WITH MonthlyBookings AS (
    205131    SELECT
    206         DATE_TRUNC('month', b.Booking_Date) AS Month,
     132        DATE_TRUNC('month', b.BookingDate) AS Month,
    207133        d.Name AS DestinationName,
    208134        COUNT(b.BookingID) AS BookingsCount,
    209         RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date)
     135        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
    210136                     ORDER BY COUNT(b.BookingID) DESC) AS Rank
    211137    FROM Booking b
    212138    JOIN BookingFlight bf ON b.BookingID = bf.BookingID
    213139    JOIN Flight f ON bf.FlightID = f.FlightID
    214     JOIN Airport a ON f.Arrival_Airport = a.AirportID
     140    JOIN Airport a ON f.ArrivalAirportID = a.AirportID
    215141    JOIN Destination d ON a.DestinationID = d.DestinationID
    216142    GROUP BY Month, d.Name
    … …  
    218144MonthlyFlights AS (
    219145    SELECT
    220         DATE_TRUNC('month', b.Booking_Date) AS Month,
    221         f.Flight_Number,
     146        DATE_TRUNC('month', b.BookingDate) AS Month,
     147        f.FlightNumber,
    222148        COUNT(b.BookingID) AS BookingsCount,
    223         RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date)
     149        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
    224150                     ORDER BY COUNT(b.BookingID) DESC) AS Rank
    225151    FROM Booking b
    226152    JOIN BookingFlight bf ON b.BookingID = bf.BookingID
    227153    JOIN Flight f ON bf.FlightID = f.FlightID
    228     GROUP BY Month, f.Flight_Number
     154    GROUP BY Month, f.FlightNumber
    229155),
    230156MonthlyAirports AS (
    231157    SELECT
    232         DATE_TRUNC('month', b.Booking_Date) AS Month,
     158        DATE_TRUNC('month', b.BookingDate) AS Month,
    233159        a.Name AS AirportName,
    234160        COUNT(b.BookingID) AS DeparturesCount,
    235         RANK() OVER (PARTITION BY DATE_TRUNC('month', b.Booking_Date)
     161        RANK() OVER (PARTITION BY DATE_TRUNC('month', b.BookingDate)
    236162                     ORDER BY COUNT(b.BookingID) DESC) AS Rank
    237163    FROM Booking b
    238164    JOIN BookingFlight bf ON b.BookingID = bf.BookingID
    239165    JOIN Flight f ON bf.FlightID = f.FlightID
    240     JOIN Airport a ON f.Departure_Airport = a.AirportID
     166    JOIN Airport a ON f.DepartureAirportID = a.AirportID
    241167    GROUP BY Month, a.Name
    242168)
    … …  
    244170FROM MonthlyBookings WHERE Rank <= 3
    245171UNION ALL
    246 SELECT Month, 'Flight' AS Category, Flight_Number AS Name, BookingsCount AS Count
     172SELECT Month, 'Flight' AS Category, FlightNumber AS Name, BookingsCount AS Count
    247173FROM MonthlyFlights WHERE Rank <= 3
    248174UNION ALL