Not logged in. · Lost password · Register

SpeedyGonzalez
Member since Jul 2017
103 posts
Subject: Wie könnte folgende SQL-Query gelöst werden?
Hallo zusammen,

wie würdet ihr diese Anfrage formulieren? (Entstammt dem Stanford Online-Kurs)

Movie ( mID, title, year, director )
Reviewer ( rID, name )
Rating ( rID, mID, stars, ratingDate ) 

"For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie."

Viele Grüsse,
Speedy
Marcel[Inf]
#faui2k15, GTI-Tutor a. D.
Member since Nov 2015
507 posts
+2 Ford Prefect, Tenma
Wenn du "twice" als "at least twice" liest, könnte es so gehen:

  1. WITH double_ratings AS (
  2.  SELECT
  3.     r1.rID, r1.mID
  4.  FROM Rating r1
  5.  JOIN Rating r2 ON r2.rID = r1.rID AND r2.mID = r1.mID AND r2.ratingDate < r1.ratingDate AND r1.stars > r2.stars
  6.  GROUP BY r1.rID, r1.mID
  7. ) SELECT
  8.     Movie.title, Reviewer.name
  9.  FROM double_ratings
  10.  JOIN Movie ON Movie.mID = double_ratings.mID
  11.  JOIN Reviewer ON Reviewer.rID = double_ratings.rID
  12. )

Insbesondere kann das Ergebnis Duplikate enthalten.
This post was edited on 2018-07-05, 08:27 by Marcel[Inf].
Ford Prefect
Pangalaktischer- Donnergurgler-Trinker
(Administrator)
Avatar
Member since Oct 2002
3275 posts
Alternativ könnte man erst mittels GROUP BY rID, mID und HAVING COUNT Bedingung alle eindeutig doppelten Reviews zusammensuchen, dann unter jenen weiter filtern.
Quote: <mute> mit Miranda macht irc kein spass :P <Loki|muh> dann geh doch wieder :)
<mute> ich benutze kein miranda <Loki|muh> na und? :)
SpeedyGonzalez
Member since Jul 2017
103 posts
In reply to post #2
Das "with" wird leider nicht erkannt.

Mein Vorschlag wäre das hier gewesen, allerdings habe ich dann statt dem Ergebnistupel zweimal "Sarah Martinez" "Gone With The Wind", was ich einfach durch ein DISTINCT eliminiert habe:

SELECT DISTINCT re.name, mo.title
FROM Rating ra
Join Movie mo ON mo.mID=ra.mID
Join Reviewer re ON ra.rID=re.rID

WHERE ra.rID=(SELECT r1.rID
     FROM Rating r1
     JOIN Rating r2 ON r2.rID = r1.rID
     AND r2.mID = r1.mID
     AND r2.ratingDate < r1.ratingDate
     AND r1.stars > r2.stars
     GROUP BY r1.rID, r1.mID)
 AND ra.mID=(SELECT r1.mID
     FROM Rating r1
     JOIN Rating r2 ON r2.rID = r1.rID
     AND r2.mID = r1.mID
     AND r2.ratingDate < r1.ratingDate
     AND r1.stars > r2.stars
     GROUP BY r1.rID, r1.mID);
Close Smaller – Larger + Reply to this post:
Verification code: VeriCode Please enter the word from the image into the text field below. (Type the letters only, lower case is okay.)
Smileys: :-) ;-) :-D :-p :blush: :cool: :rolleyes: :huh: :-/ <_< :-( :'( :#: :scared: 8-( :nuts: :-O
Special characters:
Go to forum
Datenschutz | Kontakt
Powered by the Unclassified NewsBoard software, 20150713-dev, © 2003-2011 by Yves Goergen