select inside select and minimizing running time in POSTGRESQL

Viewed 15

I am trying to calculate the total booking number and the percentage for each hotel per year using POSTGRESQL. Here is my code:

        WITH distribution_per_year AS
        (
          SELECT hotel, arrival_date_year,
                 COUNT(*) AS booking_by_hotel, 
                (SELECT   COUNT(*) AS total_booking FROM "Full_Data" )
          FROM "Full_Data" 
          GROUP BY hotel, arrival_date_year
        )  

        SELECT hotel, arrival_date_year, booking_by_hotel, total_booking,
               round(booking_by_hotel *100.00 / total_booking , 2) as percent

        FROM distribution_per_year

and it worked, it gives me the results as I wanted

 |  hotel | arrival_date_year | booking_by_hotel | total_booking | percent |
 |:------ |:----------------- |:---------------- |:------------- |:--------|
 | Hotel1 |      2015         |      6526        |     100561    |   6.49  | 
 | Hotel1 |      2016         |     33210        |     100561    |  33.02  | 
 | Hotel1 |      2017         |     20064        |     100561    |  19.95  | 
 | Hotel2 |      2015         |     6758         |     100561    |  6.72   | 
 | Hotel2 |      2016         |     22434        |     100561    |  22.31  | 
 | Hotel2 |      2017         |     11569        |     100561    |  11.50  | 

My question is: I noticed it takes time to run this code. I think it's because it repeats the subquery every time it group by

        (SELECT   COUNT(*) AS total_booking FROM "Full_Data" )

Is there a way to enhance this code??

0 Answers
Related