Mysql Benutzerlimit in Abfrage

[SteiniKeule]

Hallo

Ich suche eine Möglichkeit die neusten 5 Werte zu bekommen, wobei pro User nur ein Treffer sein darf.

Beispiel:
ID | UserID | Wert | Datum

SELECT Wert FORM tabelle ORDER BY Datum DESC LIMIT 5

Das würde mir jetzt die neuesten 5 ausgeben. Wenn die ersten 3 Treffer von UserID 109 sind, könnte ich 2 von den 3 nicht verwenden. Ich habe leider auch keinen Ansatz, wonach ich Google befragen könnte.

[ChristianK]

Ich habe leider keine Lösung zur Hand, das jedoch könnte ein Ansatz sein: http://stackoverflow.com/questions/8...-once-per-user

[akretschmer]

Hallo

Ich suche eine Möglichkeit die neusten 5 Werte zu bekommen, wobei pro User nur ein Treffer sein darf.

Beispiel:
ID | UserID | Wert | Datum

SELECT Wert FORM tabelle ORDER BY Datum DESC LIMIT 5

Das würde mir jetzt die neuesten 5 ausgeben. Wenn die ersten 3 Treffer von UserID 109 sind, könnte ich 2 von den 3 nicht verwenden. Ich habe leider auch keinen Ansatz, wonach ich Google befragen könnte.

Was hast Du denn versucht? Es gibt viele Wege dafür.

Wir erzeugen uns mal Beispieldaten:

test=# create table steinikeule as (select u, random() * 1000 as wert, '2014-01-01'::date + d * '1day'::interval as datum from generate_series(1,4) u cross join generate_series(1,4) d);
SELECT 16
Time: 5,665 ms
test=*# select * from steinikeule ;
 u |       wert       |        datum
---+------------------+---------------------
 1 | 767.284286674112 | 2014-01-02 00:00:00
 1 | 856.550519354641 | 2014-01-03 00:00:00
 1 | 487.047519534826 | 2014-01-04 00:00:00
 1 | 828.791714739054 | 2014-01-05 00:00:00
 2 | 362.077887635678 | 2014-01-02 00:00:00
 2 | 451.759324874729 | 2014-01-03 00:00:00
 2 | 120.529083069414 | 2014-01-04 00:00:00
 2 | 65.3791120275855 | 2014-01-05 00:00:00
 3 | 35.8766815625131 | 2014-01-02 00:00:00
 3 |  194.53845359385 | 2014-01-03 00:00:00
 3 | 307.075649965554 | 2014-01-04 00:00:00
 3 | 211.576706729829 | 2014-01-05 00:00:00
 4 | 717.916613910347 | 2014-01-02 00:00:00
 4 | 785.597726237029 | 2014-01-03 00:00:00
 4 |  190.51311025396 | 2014-01-04 00:00:00
 4 |  52.352393977344 | 2014-01-05 00:00:00
(16 rows)

Du suchst nun also die TOP N je u. Dazu zählen wir, für jedes u und sortiert nach datum absteigend:

test=*# select *, row_number() over (partition by u order by datum desc) from steinikeule ;
 u |       wert       |        datum        | row_number
---+------------------+---------------------+------------
 1 | 828.791714739054 | 2014-01-05 00:00:00 |          1
 1 | 487.047519534826 | 2014-01-04 00:00:00 |          2
 1 | 856.550519354641 | 2014-01-03 00:00:00 |          3
 1 | 767.284286674112 | 2014-01-02 00:00:00 |          4
 2 | 65.3791120275855 | 2014-01-05 00:00:00 |          1
 2 | 120.529083069414 | 2014-01-04 00:00:00 |          2
 2 | 451.759324874729 | 2014-01-03 00:00:00 |          3
 2 | 362.077887635678 | 2014-01-02 00:00:00 |          4
 3 | 211.576706729829 | 2014-01-05 00:00:00 |          1
 3 | 307.075649965554 | 2014-01-04 00:00:00 |          2
 3 |  194.53845359385 | 2014-01-03 00:00:00 |          3
 3 | 35.8766815625131 | 2014-01-02 00:00:00 |          4
 4 |  52.352393977344 | 2014-01-05 00:00:00 |          1
 4 |  190.51311025396 | 2014-01-04 00:00:00 |          2
 4 | 785.597726237029 | 2014-01-03 00:00:00 |          3
 4 | 717.916613910347 | 2014-01-02 00:00:00 |          4
(16 rows)

Da Du nur den jeweils 1. willst kannst Du das nun als WHERE nutzen:

test=*# select * from (select *, row_number() over (partition by u order by datum desc) from steinikeule ) foo where row_number = 1;
 u |       wert       |        datum        | row_number
---+------------------+---------------------+------------
 1 | 828.791714739054 | 2014-01-05 00:00:00 |          1
 2 | 65.3791120275855 | 2014-01-05 00:00:00 |          1
 3 | 211.576706729829 | 2014-01-05 00:00:00 |          1
 4 |  52.352393977344 | 2014-01-05 00:00:00 |          1
(4 rows)

Das ist doch mal flexibel, oder? Du kannst das für beliebige N verwenden.
Dumm für Dich: kann MySQL nicht.

Aber hey, nicht aufgeben. Du hast hier den Sonderfall mit N=1. Da gibt es noch was, DISTINCT ON(). Wir schauen mal:

test=*# select distinct on(u) u, wert, datum from steinikeule order by u, datum desc;
 u |       wert       |        datum
---+------------------+---------------------
 1 | 828.791714739054 | 2014-01-05 00:00:00
 2 | 65.3791120275855 | 2014-01-05 00:00:00
 3 | 211.576706729829 | 2014-01-05 00:00:00
 4 |  52.352393977344 | 2014-01-05 00:00:00
(4 rows)

Das ist doch mal kurz und elegant, oder? Dumm nur, Du ahnst es, kann MySQL nicht.

Aber schauen wir mal weiter.

Du suchst für jedes u das max(datum), also diese Paare. Und für diese Paare den Wert aus der Tabelle.

test=*# select u, max(datum) from steinikeule group by u order by u;
 u |         max
---+---------------------
 1 | 2014-01-05 00:00:00
 2 | 2014-01-05 00:00:00
 3 | 2014-01-05 00:00:00
 4 | 2014-01-05 00:00:00
(4 rows)
test=*# select * from steinikeule where (u, datum) in (select u, max(datum) from steinikeule group by u) order by u;
 u |       wert       |        datum
---+------------------+---------------------
 1 | 828.791714739054 | 2014-01-05 00:00:00
 2 | 65.3791120275855 | 2014-01-05 00:00:00
 3 | 211.576706729829 | 2014-01-05 00:00:00
 4 |  52.352393977344 | 2014-01-05 00:00:00
(4 rows)

Das könnte man auch als JOIN schreiben:

test=*# select s.* from steinikeule s inner join (select u, max(datum) as datum from steinikeule group by u) foo on((s.u, s.datum)=(foo.u,foo.datum)) order by u;
 u |       wert       |        datum
---+------------------+---------------------
 1 | 828.791714739054 | 2014-01-05 00:00:00
 2 | 65.3791120275855 | 2014-01-05 00:00:00
 3 | 211.576706729829 | 2014-01-05 00:00:00
 4 |  52.352393977344 | 2014-01-05 00:00:00
(4 rows)

Reicht das als Auswahl möglicher Lösungen? So früh fällt mir grad mehr nicht ein.