[MySQL] Bedingtes löschen

[DFYX]

Ich bastel grad eine Hörerchartsseite für Radio Nightlife und bin an einem Punkt angelangt, wo meine MySQL Kenntnisse versagen. Wenn ein Admin ein neues Lied einträgt und schon 100 Lieder eingetragen sind, soll das Lied, das im Schnitt am wenigsten Punkte pro User bekommen hat, wo aber mindestens schon ein User abgestimmt hat (Weil neue Lieder ja erst mal 0 Votes und damit auch im Schnitt 0 Punkte haben) gelöscht werden. Natürlich könnte ich das bequem mit zwei Queries und mysql_num_rows machen und werde das auch, wenn niemand ne Lösung weiß, aber irgendwie find ich das eklig. Mir wärs lieber, wenn ich die Abfrage, ob schon 100 Lieder eingetragen sind, direkt im MySQL Query hätte. Es scheint ja was in Richtung IF zu geben, aber momentan scheint die MySQL Seite nicht richtig zu funktionieren und mit den paar Seiten, die mir Google ausgespuckt hat, komm ich grad nich klar.

[drunken monkey]

Du könntest auf die eher russische Methode zurückgreifen, und am Ende der Punkteschnitt-Abfrage zum Löschen noch

Code:

[...]
HAVING ID NOT IN (SELECT ID
     FROM Wertungen
     ORDER BY punkte DESC
     LIMIT 100);

anhängen, das würde quasi die ersten 100 nicht löschen. Glaub ich.

Keine Zeit, das noch zu überprüfen, muss weg. o_O

[DFYX]

Hm, nich so ganz was ich will. Ich will ja nicht das hundertste bewertete löschen, sondern das schlechteste bewertete. Also mal zwei Beispielszenarien:

Szenario 1:
62 bewertete
17 unbewertete
=> Zusammen nur 79, also wird nichts gelöscht

Szenario 2:
83 bewertete
17 unbewertete
=> Zusammen 100, also wird von den 83 das, das im Schnitt die wenigsten Punkte hat, gelöscht. Das kann aber sowohl die niedrigste als auch die höchste ID haben oder irgendwas dazwischen.

Der Anfang wäre DELETE FROM charts WHERE votes>0 ORDER BY pointspervote ASC LIMIT 1 und da muss jetzt noch die Abfrage rein, ob die Tabelle 100 Datensätze enthält oder nicht.

[drunken monkey]

Hm, nich so ganz was ich will. Ich will ja nicht das hundertste bewertete löschen, sondern das schlechteste bewertete. Also mal zwei Beispielszenarien:

...

Habe ich schon verstanden und dafür ist das kleine Schnippselchen auch da. Habe jetzt mehr Zeit, also ausführlich.

Der Anfang wäre DELETE FROM charts WHERE votes>0 ORDER BY pointspervote ASC LIMIT 1 und da muss jetzt noch die Abfrage rein, ob die Tabelle 100 Datensätze enthält oder nicht.

...

OK, wenn das so aufgebaut ist, würde eine so weit ich erkennen kann passende Query so aussehen (ID ist der Primärschlüssel, weiß ja nicht, wie der bei dir heißt):

DELETE FROM charts WHERE votes>0 HAVING ID NOT IN (SELECT ID FROM charts ORDER BY pointspervote DESC LIMIT 100) ORDER BY pointspervote ASC LIMIT 1

Eigentlich könntest du dann sogar alles hinter meinem Eingefügten weglassen ("ORDER BY pointspervote ASC LIMIT 1"), dann würden alle gelöscht werden, die über das hundertste hinausgehen, also wenn wegen irgendwas einmal 105 drin wären würden gleich die schlechtesten 5 gelöscht.

Funktion: Meine Unterabfrage gibt die hundert besten aus, dann wird bei allen zu löschenden überprüft, ob sie darin enthalten sind. Sprich: die hundert besten bleiben drin, alle anderen (bzw mit deinem "Limit 1" eben nur das allerschlechteste) werden gelöscht. Falls weniger als hundert in der Tabelle drin sind, dann wird nichts gelöscht.

Gäbe sicher noch eine elegantere Lösung mit IF-Statement, aber das ist in SQL so merkwürdig, ich weiß auf jeden Fall nicht, wie's ginge, und das müsste auch funktionieren.
Ich hoffe, das tut es auch. ^^'

[DFYX]

Dein Ansatz geht so nicht auf. Alle neu eingefügten, also noch nicht gevoteten haben ja noch pointspervote=0 und würden deshalb rausfliegen.

[drunken monkey]

Ups, übersehen, warte kurz...

Also dein Argument ist zwar grundsätzlich falsch - schließlich habe ich dein "WHERE votes > 0" drin gelassen - aber dadurch ist mir was anderes aufgefallen: falls du bei pointspervote standardmäßig (also, wenn's noch keine Stimmen gibt) 0 einträgst, würden die ohne Stimmen bei den 100 aus der Unterabfrage nicht drin stehen, also würden nur welche gelöscht werden, wenn's mehr als 100 gibt, bei denen schon abgestimmt wurde, nicht mehr als 100 insgesamt.

Nächster Versuch, noch komplizierter (wo sind denn die richtigen Experten, wenn man sie mal braucht? ó_Ò):

Code:

DELETE 
    FROM charts
    WHERE votes>0 
    HAVING ID NOT IN (SELECT ID, (CASE WHEN votes = 0 THEN 1000
                        ELSE pointspervote END) AS x
        FROM charts
        ORDER BY pointspervote DESC
        LIMIT 100)
    ORDER BY x ASC
    LIMIT 1;

So!
Vielleicht...o_O'' Also das würde fürs Sortieren allen, bei denen noch nicht abgestimmt wurde einen sehr hohen Wert zuweisen (sollte über oder beim Maximum liegen, weiß ja nicht, was das bei dir ist, 1000 dürfte aber reichen, oder?), so dass die sicher in den 100 drin liegen, die nicht gelöscht werden. Glaube ich. Langsam habe ich selbst kein allzu großes Vertrauen mehr.
Außerdem weiß ich nicht, ob das "ELSE pointspervote" gültig ist.

Versuch's halt mal...o_O''

@ Manni unter mir: Erst recht falsch, hat den gleichen Fehler wie meins, nur sogar explizit angeschrieben. o_O'

[Manni]

Probiers mal damit:

Code:

DELETE FROM `charts`
WHERE `votes` > 0
HAVING `id` NOT IN (
  SELECT `id` FROM `charts`
  WHERE `votes` > 0
  ORDER BY `pointspervote` DESC
  LIMIT 100
);

EDIT: Jetzt hab ich so lange dran rumeditiert, dass der Affe tatsächlich schneller war als ich XD

[DFYX]

Also Mannis Ansatz sieht mir irgendwie identisch aus wie der erste vom Affen und der andere is irgendwie etwas unübersichtlich. Mal schaun, ob ich da noch durchblick. Ich hätte irgendwie einfach was in Richtung IF(count(charts.*) >= 100) oder so getippt, nur weiß ich nich, wie das IF bei MySQL funktioniert.

[drunken monkey]

OK, habe noch eine deutlich einfachere, aber auch deutlich unelegantere Lösung gefunden: eine einfache WHERE-Bedingung, die bei allen gleich ist.

Code:

DELETE FROM charts
    WHERE votes > 0 AND (SELECT COUNT (*)
                            FROM charts) > 100
    ORDER BY pointspervote ASC
    LIMIT 1

Das dürfte jetzt echt funktionieren, ist sogar überprüft, auch wenn's irgendiwe grauslich ist, dass die WHERE-Bedingung für jeden Eintrag überprüft wird. :-/ Wie IF-Klauseln in SQL funktionieren weiß ich nämlich auch nicht, wie man ja merkt. Habe auch nirgends was gefunden und bezweifle langsam sogar, dass es die überhaupt gibt! o_O''

[DFYX]

Stimmt noch nicht so ganz, was du geschrieben hast. Folgendes sollte aber funktionieren:

Code:

DELETE FROM charts WHERE votes > 0 AND COUNT(*) > 99 ORDER BY pointspervote  ASC

Nicht elegant, aber es geht und immerhin löschts alles, was über 100 is. Kommt ja nicht soooo sehr auf die Performance an, weil das nur gelegentlich von den Admins ausgeführt wird. Ein anderer Ansatz, der mir grad noch eingefallen ist, wäre das Ändern des Werts bei LIMIT. Leider hat weder das Setzen per CASE noch COUNT(*)-99 geklappt. Weiß jemand, obs ne Möglichkeit gibt, den Wert anzupassen?

Und IF gibts in MySQL auf jeden Fall, zumindest in Form von SELECT IF oder IF TABLE EXISTS. Mehr hab ich aber leider auch noch nicht gefunden.

Edit:
Scheint doch nicht zu gehen. Kommt zwar kein Fehler, aber es wird nix gelöscht.

[drunken monkey]

Stimmt noch nicht so ganz, was du geschrieben hast.

...

Doch.


Wohingegen deins komplett fehlerhaft ist. Erstens macht das "WHERE COUNT(*) > 99" was total anderes, als du willst, zweitens würde es ohne den Fehler wegen dem fehlenden "LIMIT 1" alle Einträge löschen, sobald mehr als 99 vorhanden sind.
Also bitte, anstatt da irgendwelche "Verbesserungen" anbringen zu wollen, versuch doch einfach meine Lösung und werde glücklich. Oder sag zumindest nachher, was daran nicht geklappt hat.

[DFYX]

Doch.


Wohingegen deins komplett fehlerhaft ist. Erstens macht das "WHERE COUNT(*) > 99" was total anderes, als du willst, zweitens würde es ohne den Fehler wegen dem fehlenden "LIMIT 1" alle Einträge löschen, sobald mehr als 99 vorhanden sind.
Also bitte, anstatt da irgendwelche "Verbesserungen" anbringen zu wollen, versuch doch einfach meine Lösung und werde glücklich. Oder sag zumindest nachher, was daran nicht geklappt hat.

...

Ganz einfach. Dein Code hat nur nen Syntax Error gebracht. Der mag das SELECT COUNT(x) und so weiter an der Stelle nicht. Also sag bitte nicht einfach mal aus Prinzip doch, wenns nicht stimmt. Ich hab keine Lust, als Stammuser noch geflamet zu werden.

@Jeez:
Dein Code hat den gleichen Fehler wie die bisherigen auch. Gezählt werden sollen alle Einträge, gelöscht aber nur welche, die schon bewertet wurden.

[drunken monkey]

Ganz einfach. Dein Code hat nur nen Syntax Error gebracht. Der mag das SELECT COUNT(x) und so weiter an der Stelle nicht. Also sag bitte nicht einfach mal aus Prinzip doch, wenns nicht stimmt. Ich hab keine Lust, als Stammuser noch geflamet zu werden.

...

Sorry, hätte ich nicht gedacht. ó_Ò War nicht aus Prinzip "Doch" gesagt, sondern weil ich's getestet habe und keinen Fehler entdecken konnte. Allerdings auf PostgreSQL, hätte nicht gedacht, dass da so große Unterschiede sind. Aber wenn's echt nicht klappt, weiß ich nicht mehr weiter.

Da ja anscheinend auch sonst niemand eine funktionierende Lösung anzubieten hat (was ich so gesehen habe o_O) musst du wohl wirklich 2 Queries verwenden. :-/

[Jesus_666]

Sorry, hätte ich nicht gedacht. ó_Ò War nicht aus Prinzip "Doch" gesagt, sondern weil ich's getestet habe und keinen Fehler entdecken konnte. Allerdings auf PostgreSQL, hätte nicht gedacht, dass da so große Unterschiede sind. Aber wenn's echt nicht klappt, weiß ich nicht mehr weiter.

Da ja anscheinend auch sonst niemand eine funktionierende Lösung anzubieten hat (was ich so gesehen habe o_O) musst du wohl wirklich 2 Queries verwenden. :-/

...

Zwischen den einzelnen SQL-Dialekten gibt es starke Unterschiede. AFAIK hält sich niemand so wirklich an den aktuellen SQL-Standard.

BTW, ich habe rein theoretisch etwas. Das Konzept ist folgendes:

Code:

select * from DFYX where id in
  (select id from DFYX where num_votes > 0 order by mean_vote asc)
limit count(*) - $limit

...wobei $limit die Anzahl der zu behaltenden Zeilen ist, in diesem Fall 100.
Dummerweise kann man bei limit unter keinen Umständen nichtskalare Werte verwenden, also braucht man zwei Queries:

Code:

$cl = mysql_result(mysql_query('select count(*) from DFYX'), 0, 0) - $limit;

select * from DFYX where id in
  (select id from DFYX where num_votes > 0 order by mean_vote asc)
limit $cl

Dummerweise läßt MySQL (und vermutlich SQL allgemein) nicht zu, daß man aus einer Tabelle löscht, während man gleichzeitig mit einem Subquery liest. Also bin ich mittlerweile schon bei drei Queries angekommen...

MySQL kann IN übrigens doch; der Subquery darf nur kein LIMIT beinhalten. Die aktuelle Version meines Ansatzes ist im Wesentlichen die letzte, nur daß das LIMIT jetzt am Ende steht und der Wert invertiert ist.


BTW, @DFYX' Kommentar zu meinem letzten Post: Du hast das "where num_votes > 0" schon bemerkt, oder?

[Jesus_666]

Ich hätte das hier anzubieten:

Code:

delete from DFYX where id in
    (select id from DFYX where num_votes > 0
     order by mean_vote asc limit 10)

Dummerweise unterstützt mein MySQL (4.1.7) das IN-Schlüsselwort noch nicht. (Irgendwie kann MySQL allgemein komplexe Queries nicht gut. Ich bin schon mal darüber gestolpert, daß es UNION nicht gibt.)

Ich bastel' weiter dran rum...

[Manni]

Code:

DELETE FROM `charts` AS charts
WHERE charts.`votes` > 0
   AND NOT EXISTS (
  SELECT * FROM `charts` AS charts2
  WHERE charts2.`votes` > 0
  ORDER BY charts2.`pointspervote` DESC
  LIMIT 100;
);

KA, ob das geht *Schultern zuck*

Ansonsten mach es halt einfach in zwei Queries...

[DFYX]

Ja, hab ich bemerkt und genau das ist der Fehler. dein Code löscht die 10 schlechtesten bewerteten Songs, unabhängig davon, wie viele Songs eingetragen sind oder seh ich das falsch?

Ich probiers jetzt jedenfalls mit zwei Queries. Kostet zwar Performance und sieht nicht schön aus, aber wenigstens funktionierts.

[Jesus_666]

Ja, hab ich bemerkt und genau das ist der Fehler. dein Code löscht die 10 schlechtesten bewerteten Songs, unabhängig davon, wie viele Songs eingetragen sind oder seh ich das falsch?

...

Yup. Beim zweiten habe ich das korrigiert. Dummerweise ist der zweite aber noch unökonomischer.