我有下表电影:
id | title | year
315 Harry Potter and the Deathly Hallows: Part 2 2011
407 Cinderella 2015
826 The Shape of Water 2017
799 Enchanted 2007
523 How to Train Your Dragon 2010
618 Crazy Rich Asians 2018
和表格 流派:
movie_id | genre
315 adventure
315 fantasy
315 mystery
315 drama
407 drama
407 fantasy
826 drama
826 thriller
826 adventure
826 horror
799 fantasy
799 comedy
799 romance
523 drama
523 fantasy
618 romance
618 comedy
和表关键字:
movie_id | keyword
315 magic
315 wizards
315 witch
315 friendship
315 abuse
407 prince
407 fairy tale
407 magic
407 poor girl
407 abuse
826 scientist
826 mute
826 friendship
799 musical
799 magic
799 witch
799 friendship
523 viking
523 boy
523 fire
618 singapore
618 wedding
618 money
我正在尝试构造一个查询,该查询输出与给定电影具有共同类型的所有电影。如果有些电影具有相同数量的常见类型,那么我想按照最大常见关键字的顺序对这些电影进行排名。
例如,如果电影是"哈利波特与死亡圣器:第 2 部分",则查询的输出将是:
title | genre_frequency | keyword_frequency
Cinderella 2 2
The Shape of Water 2 1
How to Train Your Dragon 2 0
Enchanted 1 3
没有任何与指定电影相同的流派的电影不包括在输出中(例如,疯狂的亚洲富人(。
我有两个查询可以给我genre_frequency和keyword_frequency。
select m.*, genre_frequency from movie m
join (
select m.id, count(*) as genre_frequency
from movie m
join genre g on m.id=g.movie_id
where g.genre in (select g1.genre
from genre g1
where g1.movie_id=315)
group by m.id
) f
on m.id=f.id
where m <> 315
order by f.genre_frequency desc;
select m.*, keyword_frequency from movie m
join (
select m.id, count(*) as keyword_frequency
from movie m
join keyword k on m.id=k.movie_id
where k.keyword in (select k1.keyword
from keyword k1
where k1.movie_id=315)
group by m.id
) f
on m.id=f.id
where m <> 315
order by f.keyword_frequency desc;
问题是我想将上面的两个查询合并到一个查询中,以便我可以如上所示输出表。我不确定我该怎么做。任何见解都值得赞赏。
您可以尝试使用UNION ALL
组合Genres
表和keyword
表,并添加grp
列以拆分结果集的两个部分。 然后使用条件聚合函数。
查询 #1
select m.title,
count(CASE WHEN t1.grp = 'g' THEN 1 END) as genre_frequency,
count(CASE WHEN t1.grp = 'k' THEN 1 END) as keyword_frequency
from Movies m
join (
SELECT movie_id,genre name,'g' grp
FROM Genres
UNION ALL
SELECT movie_id,keyword,'k' grp
FROM keyword
) t1 on m.id=t1.movie_id
where (t1.name in (select g1.genre
from Genres g1
where g1.movie_id=315) or
t1.name in (select k1.keyword
from keyword k1
where k1.movie_id=315))
AND m.id <> 315
group by m.title;
| title | genre_frequency | keyword_frequency |
| ------------------------ | --------------- | ----------------- |
| Cinderella | 2 | 2 |
| Enchanted | 1 | 3 |
| How to Train Your Dragon | 2 | 0 |
| The Shape of Water | 2 | 1 |
在DB Fiddle上查看
下面的查询首先获取所有电影,并与与您正在寻找的电影具有共同流派的电影进行内部联接。 这将允许删除与要搜索的电影没有任何共同流派的任何电影.
在这种情况下,我使用您对流派频率的查询作为派生表。我还删除了 where 语句中的 IN 子句,并使用了另一个内部连接以获得更好的性能.
第二个派生表,即使用 LEFT JOIN 连接的表是用于获取关键字频率的查询。 与类型频率表相同的逻辑适用,唯一的区别是 LEFT JOIN,因为两部电影可以具有共同的类型,但不能具有关键字><。
请注意 select 子句中的 IFNULL 语句,以便在未找到 common.
的关键字时返回 0
最后,我们只是首先按流派频率排序,然后按关键字频率降序排序。
select m.title, IFNULL(g_fq.genre_frequency,0),
IFNULL(k_fq.keyword_frequency,0)
FROM movie m
INNER JOIN
(select m.id as movie_id, genre_frequency from movie m
join (
select m.id, count(*) as genre_frequency
from movie m
join genre g on m.id=g.movie_id
INNER JOIN
(select g1.genre
from genre g1
where g1.movie_id=315) as a on a.genre=g.genre
group by m.id
) f
on m.id=f.id
where m.id <> 315
) as g_fq ON m.id=g_fq.movie_id
LEFT JOIN
(
select m.id as movie_id, keyword_frequency from movie m
join (
select m.id, count(*) as keyword_frequency
from movie m
join keyword k on m.id=k.movie_id
INNER JOIN
(select k1.keyword
from keyword k1
where k1.movie_id=315) as b on b.keyword=k.keyword
group by m.id
) f
on m.id=f.id
where m.id <> 315
) as k_fq on m.id=k_fq.movie_id
order by IFNULL(g_fq.genre_frequency,0) DESC,IFNULL(k_fq.keyword_frequency,0) DESC