handling duplicates on Temporary Tables PostgreSQL

PALALP 0
Viewed 23

I want to print the first 40 rows but I get many duplicated rows in the results. How to make sure this doesn't happens?

WITH film AS (SELECT 
       m.rental_rate AS rental_price,
       m.length as dur,
       m.rating AS age_rating
    FROM movie AS m      
    WHERE m.rental_rate >2  
    ORDER BY dur DESC
    ),
duration AS (SELECT
             f.rating as age_rating,
       MIN(f.length) AS min_length,
       MAX(f.length) AS max_length,
       AVG(f.length) AS avg_length,
       Min(f.rental_rate) AS min_rental_rate,
       Max(f.rental_rate) AS max_rental_rate,
       AVG(f.rental_rate) AS avg_rental_rate
       FROM movie AS f
       GROUP BY age_rating  
       ORDER BY avg_length ASC)
SELECT 
       film.age_rating,
       duration.min_length,
       duration.max_length,
       duration.avg_length,
       duration.min_rental_rate,
       duration.max_rental_rate,
       duration.avg_rental_rate
FROM film INNER JOIN duration ON film.age_rating = duration.age_rating
LIMIT 40 ;
temp-tables sql postgresql
1 Answers

I don't think you meant to, but your query is effectively doing this:

SELECT
   m.rating as age_rating,
   MIN(f.length) AS min_length,
   MAX(f.length) AS max_length,
   AVG(f.length) AS avg_length,
   Min(f.rental_rate) AS min_rental_rate,
   Max(f.rental_rate) AS max_rental_rate,
   AVG(f.rental_rate) AS avg_rental_rate
 FROM
   movie AS f
   join movie AS m on   
     f.rating = m.rating
 where
   m.rental_rate > 2
 GROUP BY age_rating  
 limit 40

Just with more code than is necessary. So what's happening is you are summarizing by rating but then re-joining to all movies with that same rating.

For example, if your data looked like this:

film            rating        length     rental rate
Willy Wonka     R             1:30       4
Gremlins        R             2:00       5
My Little Pony  R             2:30       6

You would get all three rows (with summary data by rating), only without the film name.

I believe what you want is much simpler. I'm reading into this a little bit, but I think your first CTE was trying to filter films by rental rate. If this is the case, you should have returned (and joined on) some form of key for the movie table.

Try this and let me know if this is the expected result.

SELECT
   f.rating as age_rating,
   MIN(f.length) AS min_length,
   MAX(f.length) AS max_length,
   AVG(f.length) AS avg_length,
   Min(f.rental_rate) AS min_rental_rate,
   Max(f.rental_rate) AS max_rental_rate,
   AVG(f.rental_rate) AS avg_rental_rate
 FROM
   movie AS f
 where
   f.rental_rate > 2
 GROUP BY f.rating
 limit 40

One final note -- the order by clauses inside of the CTE are unnecessary, inefficient and misleading. There is no guarantee they will propagate to the final query and in many cases will be clobbered. Leave your sorting for the very last step.

Hambone0
Related