关于WHERE子句OR运算符及COUNT(DISTINCT)计数逻辑的技术问询

一、WHERE子句的运行逻辑 #

首先澄清你关于WHERE子句的疑问：

• 它并不会生成两个独立的结果集R1和R2再合并，而是逐行检查每个元组是否满足以下任一条件：
  1. 该元组的genre存在于《哈利·波特与死亡圣器》的流派列表中
  2. 该元组的keyword存在于《哈利·波特与死亡圣器》的关键词列表中
• 只要满足其中一个条件，这个元组就会被纳入后续的分组统计流程。

二、COUNT(DISTINCT)的统计误区 #

你预期的是统计「与哈利波特重叠的流派/关键词数量」，但当前查询的统计逻辑完全不同：

• 一旦某部电影的任意一个元组通过了WHERE筛选（不管是因为流派匹配还是关键词匹配），该电影所有符合WHERE条件的元组都会被聚合到同一个分组（按title和year）。
• COUNT(DISTINCT genre)统计的是该分组内所有不同的genre值（不管这个genre是否和哈利波特重叠），同理COUNT(DISTINCT keyword)统计的是该分组内所有不同的keyword值。
• 举个例子：如果《Cinderella》有5个不同的关键词，其中只要有1个和哈利波特重叠，那么它的keyword_freq就会被统计为5，而不是你预期的2——这就是你的结果和预期不符的核心原因。

三、修正查询以得到预期结果 #

要实现你的需求（统计与哈利波特共享的流派/关键词数量），需要用CASE语句精准筛选出重叠的部分再统计：

SELECT 
  title, 
  year, 
  -- 统计与哈利波特共享的流派数量
  COUNT(DISTINCT CASE 
    WHEN genre IN (SELECT genre FROM genkeyword WHERE title = 'Harry Potter and the Deathly Hallows') 
    THEN genre 
  END) AS shared_genre_freq,
  -- 统计与哈利波特共享的关键词数量
  COUNT(DISTINCT CASE 
    WHEN keyword IN (SELECT keyword FROM genkeyword WHERE title = 'Harry Potter and the Deathly Hallows') 
    THEN keyword 
  END) AS shared_keyword_freq
FROM genkeyword 
WHERE ( 
  genre IN (SELECT genre FROM genkeyword WHERE title = 'Harry Potter and the Deathly Hallows') 
  OR keyword IN (SELECT keyword FROM genkeyword WHERE title = 'Harry Potter and the Deathly Hallows') 
) 
AND title <> 'Harry Potter and the Deathly Hallows' 
GROUP BY title, year 
ORDER BY shared_genre_freq DESC, shared_keyword_freq DESC;

这个查询会：

1. 先筛选出所有和哈利波特有至少一个流派或关键词重叠的电影元组
2. 分组后，只统计每个分组中确实和哈利波特重叠的流派/关键词的数量
3. 最终得到你预期的输出结果

内容的提问来源于stack exchange，提问作者ceno980