SQL查询优化 - 如果A>B，仅选择X，其中A和B的值在变量的范围内。

Question

我正在练习我的SQL技能并尝试解决一些练习题。

问题: 在所有同一评审人两次评价同一部电影 (存储在rating表中) 且第二次给出更高的评分 (rating.stars) 的情况下，返回评审人的姓名 (存储在reviewer表中) 和电影的标题 (存储在movie表中)。

SELECT 
    r.NAME AS reviewer
    ,m.Title AS movietitle  
FROM rating ra
LEFT JOIN movie m ON m.mID = ra.mID
LEFT JOIN reviewer r ON r.rID = ra.rID
LEFT JOIN 
(
    SELECT 
        ra.rID
        ,ra.mID
        ,MAX(ra.RatingDate) AS MaxDate
        ,MIN(ra.RatingDate) AS MinDate
        ,MAX(ra.stars) AS MaxStars
        ,MIN(ra.stars) AS MinStars
    FROM Rating ra
    GROUP BY ra.rID, ra.mID
    HAVING MAX(ra.stars) <> MIN(ra.stars) and COUNT(*) = 2
) rs ON ra.rID = rs.rID AND ra.mID = rs.mID
WHERE 
    ra.Ratingdate = rs.MaxDate 
    AND ra.stars = rs.MaxStars

对于上述查询解决方案，我尝试并且认为是正确的。

1. 这是解决问题最清晰的方式吗？
2. 有没有解决相同问题的快捷方法？
3. 这些问题的实际名称是什么？我在标题上列出了一个名称，“只有当A>B时才选择X，在此范围内的A和B值是变量”，是否有更好的分类这些类型的问题的方式或者它们已经在某些书籍或社区中分类了？

参考文献

http://sqlfiddle.com/#!2/8031b/1509

http://i42.photobucket.com/albums/e311/indiecoding/data_zpsd505fa8a.png

- stacktogo

---

+1 是为了表扬您准备问题得当，包括 SQL fiddle。 - Szymon

我认为你的查询不正确。问题说后面的评分比前面的评分高。你只是在测试他是否给出了不同的评分，这可能是第二个评分比第一个评分低。要获得问题中的具体结果，您需要使用“评分”表的自连接。 - Barmar

我收回之前的话。你的最终WHERE子句解决了那个问题。虽然它实际上应该在ON子句中，因为它比较的是两个表/子查询。 - Barmar

我仍然建议使用自连接，因为它更具普适性。您的查询对于对某些事物进行了3次评级的人将无法工作。 - Barmar

如果您有可用的代码，并正在寻求关于样式的评论和改进建议，codereview.stackexchange.com是更好的选择。 - Barmar

感谢提到codereview.stackexchange。我在想Barmar如何让答案更简短，因为这只是一本书中的简单练习，所以我在想。自连接就可以解决问题：）。超过3次，我可以想象使用带有分区和排名的行号以及派生表。养成使用派生表的习惯，因为它更容易可视化，但是根据我得到的答案，看起来并不难。感谢你们所有人的帮助。 - stacktogo

Answer 1

我倾向于使用exists子句来完成此操作：

select rv.name, m.title
from rating r join
     movie m
     on r.mid = m.mid join
     reviewer rv
     on r.rid = rv.rid
where exists (select 1
              from rating r2
              where r2.rid = r.rid and
                    r2.mid = r.mid and
                    r2.ratingdate < r.ratingdate and
                    r2.stars < r.stars
             );

join只是将必要的表格合并在一起，以获取评论者姓名和电影名称。关键在于相关子查询。它直接实现了您所寻找的逻辑。

- Gordon Linoff

---

只返回翻译后的文本：这样更清晰。 - zinking

+1 并被选为接受的答案，因为使用了 1. "exists"（这对我来说是新的）2. 在 where 子句中加入使其更清晰 3. 使用 lesser 而不是 greater 运算符，这将为此情况带来相同的结果，我没有考虑到这一点。问题是：如果我将 where exists... 替换为 "inner join rating sj ON r.rid = sj.rid and r.mid = sj.mid and r.ratingdate < sj.ratingdate and r.stars < sj.stars"，那么它会成为性能瓶颈吗？ - stacktogo

@stacktogo . . . 从您的数据来看，切换到“inner join”在性能方面应该非常相似。 “exists”的优点是可以在找到第一个匹配值时停止搜索。当评审员多次评论同一部电影时，这将产生更大的差异。 - Gordon Linoff

感谢您的解释。停止在第一个匹配值，非常有趣。感谢您的帮助。 - stacktogo

Answer 2

这将生成符合条件的每对 pod 评分的一行。例如，如果同一位评论者对同一部电影进行了三次评分，并且评分逐渐增加，则您将在输出中为每个对（1,2）;（2,3）和（1,3）获得一行。当然，无论如何，所有输出行的电影名称和评分者名称都将相同，因此如果您只想要这些信息，那么只需在选择子句中添加一个 distinct，您将仅获得一行...

Select r.Name reviewer, m.Title movietitle  
From rating r1
  join rating r2 
     On r2.mID = r1.mID
       And rID = r1.rID
       And r2.RatingDate > r1.RatingDate
       And r2.stars > r1.stars
  left join movie m ON m.mID = r1.mID
  left join reviewer r ON r.rID = 1.rID

如果您不想看到(1,3)的输出[两者之间有一个介于之间的评级]，并且如果您希望生成其他数据，例如评级日期，则需要使用子查询来限制成对相邻(连续)的评级...

Select r.Name reviewer, m.Title movietitle  
From rating r1
  join rating r2 
     On r2.mID = r1.mID
       And rID = r1.rID
       And r2.stars > r1.stars
       And r2.RatingDate =
           (Select min(ratingDate)
            From rating 
            Where mID = r1.mId
              and rId = r1.rId
              And ratingDate > r1.ratingDate)
  left join movie m ON m.mID = r1.mID
  left join reviewer r ON r.rID = 1.rID

- Charles Bretana

---

让我来解释一下：r2.RatingDate > r1.RatingDate 可以带来多个结果（即 Date2 > Date1 & Date3 > Date1），因此会有来自连续迭代的记录。因此，已经有了 r1 作为“下一个迭代”日期，我们可以确定 r2.RatingDate=r1.NextIterativeDate 只会带来一个结果，并且所有后续的迭代都将按照 r2.stars > r1.stars 进行排序。哇，这真的很好！+1 鼓励你的额外努力并提到了我没有想到的自连接。不幸的是，必须给与星星更相关的问题。非常棒的答案，即使对于我的短视也能理解。 - stacktogo