postgresql 查找电影与最高数量的奖项在某些年份-代码重复

hts6caw3  于 12个月前  发布在  PostgreSQL
关注(0)|答案(3)|浏览(114)

我试图写一个查询(PostgreSQL),以获得“2012年获奖最多的电影”。
我有以下表格:

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));

字符串
我写了下面的查询,它给出了正确的结果,但我认为有相当多的代码重复。

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);


所以SUBSUB2是完全相同的子查询。有更好的方法吗?

qzlgjiam

qzlgjiam1#

你可以使用common table expression来避免代码重复:

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)

字符串
或者你可以用window function做类似的事情(未经测试,但我认为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来避免代码重复。一般来说,如果可能的话,最好避免在查询中多次使用cte-第一个查询使用2个表扫描(或索引查找),第二个和第三个只使用一个,所以我应该指定最好使用这些查询。无论如何,@靳泽在回答中做了这样的测试,只是为了补充他的一大优点:

  • 我也反对natural join,因为它容易出错。实际上,我的主要RDBMS是SQL Server,它不支持它,所以我更习惯于显式的outer/inner join
  • 在查询中始终使用别名是一个好习惯,这样可以避免奇怪的结果。
  • 这可能是完全主观的事情,但通常如果我只使用一些表来过滤查询的主表中的行(比如在这个查询中,我们只想获得2012年的awards,只过滤awardwinner中的行),我宁愿不使用join,而是使用existsin,这对我来说似乎更合乎逻辑。

因此,最终查询可能是:

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
xqkwcwgp

xqkwcwgp2#

获取所有获奖影片

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;

字符串

要点

  • 这应该比到目前为止的建议更简单和更快。用EXPLAIN ANALYZE测试。
  • 在某些情况下,CTE有助于避免代码重复,但这次不是:子查询可以很好地完成这项工作,而且通常更快。
  • 你可以在同一个查询级别上运行一个窗口函数OVER一个聚合函数。这就是为什么这样做的原因:
rank() OVER (ORDER BY count(aw.id_movie) DESC) AS rnk

  • 我建议在JOIN条件中使用显式的列名,而不是NATURAL JOIN,如果您稍后更改/向底层表添加列,则很容易损坏。

使用USING的JOIN条件几乎一样短,但不容易中断。

  • 由于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


这里使用位置参考(12)作为简写。
我在ORDER BY中添加了id_movie作为决胜局,以防多部电影都有资格获胜。

b4qexyjb

b4qexyjb3#

你不需要这样的东西吗?

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

相关问题