Geek Answers Handbook

Find movies with the most awards in a given year - code duplication

I am trying to write a query (PostgreSQL) to get "movies with the most awards in 2012."

I have the following tables:

CREATE TABLE Award(
    ID_AWARD bigserial CONSTRAINT Award_pk PRIMARY KEY,
    award_name VARCHAR(90),
    category VARCHAR(90),
    award_year integer,
    CONSTRAINT award_unique UNIQUE (award_name, category, award_year));

CREATE TABLE AwardWinner(
    ID_AWARD integer,
    ID_ACTOR integer,
    ID_MOVIE integer,
    CONSTRAINT AwardWinner_pk PRIMARY KEY (ID_AWARD));

And I wrote the following query that gives the correct results, but I think there is a lot of code duplication.

select * from 
(select id_movie, count(id_movie) as awards 
from Award natural join awardwinner 
where award_year = 2012 group by id_movie) as SUB
where awards = (select max(count) from 
(select id_movie, count(id_movie) 
from Award natural join awardwinner 
where award_year = 2012 group by id_movie) as SUB2);

So SUB, and SUB2- this is exactly the same subquery. Is there a better way to do this?

+4
source share
3 answers

Get all winning movies

SELECT id_movie, awards
FROM  (
   SELECT aw.id_movie, count(*) AS awards
         ,rank() OVER (ORDER BY count(aw.id_movie) DESC) AS rnk
   FROM   award       a
   JOIN   awardwinner aw USING (id_award)
   WHERE  a.award_year = 2012
   GROUP  BY aw.id_movie
   ) sub
WHERE  rnk = 1;

Basic moments

  • It should be simpler and faster than the offers so far. Test c EXPLAIN ANALYZE.

  • , CTE . : .

  • OVER . :

    rank() OVER (ORDER BY count(aw.id_movie) DESC) AS rnk

  • JOIN NATURAL JOIN, , / .
    JOIN USING , .

  • id_movie NULL ( JOIN, pk), count(*). .

, :

SELECT aw.id_movie, count(*) AS awards
FROM   award       a
JOIN   awardwinner aw USING (id_award)
WHERE  a.award_year = 2012
GROUP  BY 1
ORDER  BY 2 DESC, 1 -- as tie breaker
LIMIT  1

(1, 2) .
id_movie ORDER BY , .

+2

, , :

with cte_s as (
   select id_movie, count(id_movie) as awards
   from Award natural join awardwinner 
   where award_year = 2012
   group by id_movie
)
select
    sub.id_movie, sub.awards
from cte_s as sub
where sub.awards = (select max(sub2.awards) from cte_s as sub2)

- (, , PostgreSQL ):

with cte_s as (
    select
        id_movie,
        count(id_movie) as awards,
        max(count(id_movie)) over() as max_awards
    from Award natural join awardwinner 
    where award_year = 2012
    group by id_movie
)
select id_movie
from cte_s
where max_awards = awards

rank() (, , cte ):

with cte_s as (
    select
        id_movie,
        count(id_movie) as awards,
        rank() over(order by count(id_movie) desc) as rnk
    from Award natural join awardwinner 
    where award_year = 2012
    group by id_movie
)
select id_movie
from cte_s
where rnk = 1

update. , , , cte, . genearal cte , - 2 ( ), , , . , @Erwin . :

  • natural join - . , RDBMS - SQL Server, , outer/inner join.
  • , .
  • , , - ( , awards 2012 awardwinner), join, exists in, .
, :
with cte_s as (
    select
        aw.id_movie,
        count(*) as awards,
        rank() over(order by count(*) desc) as rnk
    from awardwinner as aw
    where
        exists (
            select *
            from award as a
            where a.id_award = aw.id_award and a.award_year = 2012
        )
    group by aw.id_movie
)
select id_movie
from cte_s
where rnk = 1
+7

- ?

SELECT ID_MOVIE, COUNT(*)
FROM AwardWinner
JOIN Award ON Award.ID_AWARD = AwardWinner.ID_AWARD
WHERE award_year = 2012
GROUP BY ID_MOVIE
ORDER BY COUNT(*) DESC

, ( , ):

SELECT ID_MOVIE, COUNT(DISTINCT AwardWinner.ID_AWARD)
FROM AwardWinner
JOIN Award ON Award.ID_AWARD = AwardWinner.ID_AWARD
WHERE award_year = 2012
GROUP BY ID_MOVIE
ORDER BY COUNT(*) DESC
0

More articles:


All Articles