✔ MySQL - Zufallsausgabe von Datensätze beeinflussen

[Yaslaw]

Bist du bereit für einen anderen Ansatz? Der relativ kompliziert aussieht, es aber nicht ist?

Also, meine Idee.
1) Sortieren der Daten nach dem Wert absteigend und die Reihenfolge (Zeilennummer) bestimmen. Dann hat der höchste Wert die Höchste rownum.

2) Pro Datensatz Anzahl rownum Datensätze erstellen. Also in deinem Beispeil 4 Datensätze für id3, 3 Datensätze für id4 etc.. Gibt in deinem Beispiel am Ende 10 Datensätze.

3) Zufälligen id aus diesen Daten nehmen

Und so könnte es aussehen (meine Tabele heisst test_rnd)

SELECT
	dat2.id
FROM	
	(
		SELECT
			@cnt := IF(@last_rownum = dat1.rownum, @cnt+1, 0) AS cnt,
			(@cnt < dat1.rownum) AS flag,
			@last_rownum := dat1.rownum AS rownum,
			dat1.id
		FROM
			test_rnd,
			(
				SELECT
					@rownum := @rownum + 1 AS rownum,
					id
				FROM
					(SELECT @rownum := 0, @last_rownum:=0, @cnt:= 0) AS vars0,
					(SELECT id FROM test_rnd ORDER BY wert ASC) AS dat0
			) AS dat1
	) AS dat2
WHERE
	dat2.flag = 1
ORDER BY RAND()
LIMIT 1

Alles mit @sind Variablen. Siehe dazu die MySQL-Doku

Beginne ich ganz innen mit der Erklärung. Ich starte mit 2 SELECT

STEP 1

SUBSELECT dat1:

SELECT
	@rownum := @rownum + 1 AS rownum,
	id
FROM
	(SELECT @rownum := 0, @last_rownum:=0, @cnt:= 0) AS vars0,
	(SELECT id FROM test_rnd ORDER BY wert ASC) AS dat0

Dies ist ein normales Konstrukt um mittels Variablen Zeilennummern zu vergeben. Ich habe vorgängig im zweiten Subselect die Daten nach dem Wert sortiert, damit der höchste Wert auch die höchste Zeilennummer kriegt

Ausgabe:

rownum | id
===========
     1 | 2
     2 | 1
     3 | 4
     4 | 3

STEP 2

SUBSELECT dat2:

SELECT
	@cnt := IF(@last_rownum = dat1.rownum, @cnt+1, 0) AS cnt,
	(@cnt < dat1.rownum) AS flag,
	@last_rownum := dat1.rownum AS rownum,
	dat1.id
FROM
	test_rnd,
	[dat1]

In dat2 kombiniere ich mein Resultat von dat1 mit meiner Tabelle. Dabei gebe ich keine Join-Bedinung an. Ergo gibt es COUNT(test_rnd)*COUNT(test_rnd) Zeilen. Mehr Zeilen als wir brauchen.
Darum zähle ich für jede ID, wieviel mal sie bereits vorgekommen ist (startend mit 0).
Für jeden Datensatz wird ein Feld ´Flag´ erstellt. Ist dier Counter (@cnt) gleich oder höher der rownum, so wird dieses Flag auf 0 gesetzt. Somit gibt es in unserem Beispiel für id3 4 Datensätze mit Flag 1, für die id4 sind es 3 etc.

Das Resultat sieht dann so aus

cnt | flag | rownum | id
========================
cnt | flag | rownum | id
========================
  0 |    1 |      1 | 2
  1 |    0 |      1 | 2
  2 |    0 |      1 | 2
  3 |    0 |      1 | 2
  0 |    1 |      2 | 1
  1 |    1 |      2 | 1
  2 |    0 |      2 | 1
  3 |    0 |      2 | 1
  0 |    1 |      3 | 4
  1 |    1 |      3 | 4
  2 |    1 |      3 | 4
  3 |    0 |      3 | 4
  0 |    1 |      4 | 3
  1 |    1 |      4 | 3
  2 |    1 |      4 | 3
  3 |    1 |      4 | 3

STEP 3
Zufallszahl und Endfilter

SELECT
	dat2.id
FROM	
	[dat2]
WHERE
	dat2.flag = 1
ORDER BY RAND()
LIMIT 1

in dat2 haben wir das Flag erstellt, welches uns sagt ob uns der Datensatz interessiert. Nach diesem filtern wir nun.
Das ergibt ein Resultat

id
==
2
1
1
4
4
4
3
3
3
3

Diese Daten nun noch zufällig sortieren und den ersten Wert auslesen....

 

[Yaslaw]

Nachtrag:
Die Liste mit den IDs kannst du auch nach dem Updaten mit dem folgenden Script als 'Materialized View' speichern und für den rnd nur noch auf diese zugreiffen. (Materialized Views gibts in MySQL nicht. Aber du kannst die Tabelle jedesmal kurz neu erstellen und abfüllen)

Dadurch dass dies nur beim ändern der Daten von deiner Tabelle passiert, muss der Perfomante Teil nicth so häufig ausgeführt werden und der Zufallszugriff der bei jedem Seitenaufruf gemacht wird greifft auf die vorbereiteten Daten zu.

(Der DROP-Befehl muss beim ersten mal nicht ausgeführt werden)

DROP TABLE mv_test_rnd;
CREATE TABLE mv_test_rnd (index idx_id (id)) SELECT
	dat2.id
FROM	
	(
		SELECT
			@cnt := IF(@last_rownum = dat1.rownum, @cnt+1, 0) AS cnt,
			(@cnt < dat1.rownum) AS flag,
			@last_rownum := dat1.rownum AS rownum,
			dat1.id
		FROM
			test_rnd,
			(
				SELECT
					@rownum := @rownum + 1 AS rownum,
					id
				FROM
					(SELECT @rownum := 0, @last_rownum:=0, @cnt:= 0) AS vars0,
					(SELECT id FROM test_rnd ORDER BY wert ASC) AS dat0
			) AS dat1
	) AS dat2
WHERE
	dat2.flag = 1;

Der Aufruf sieht nachher dann so aus

SELECT id
FROM mv_test_rnd
ORDER BY RAND()
LIMIT 1

 

[proloser]

Ich bin euch sehr dankbar für die Ansätze aber so gut sind meine MySQL Kenntnisse nicht um diesen Code zu verstehen. Ich werde es wohl oder übel mit der Variante mit hoher Performance machen.

SELECT * FROM tabelle
 ORDER BY wert*RAND() DESC 
LIMIT 1;

 

[tombe]

Irgendwie verstehe ich das noch nicht so ganz. Du hast ganz oben geschrieben das bei 100 Aufrufen die Verteilung so aussehen soll.

ID: 3 = 40 aufrufe
ID: 4 = 30 aufrufe
ID: 1 = 20 aufrufe
ID: 2 = 10 aufrufe

Ich verstehe das so, das ID 3 40 Mal angezeigt wird und dann wird ID 4 30 Mal angezeigt usw. Wo und wie prüfst du wie oft ein bestimmtes Banner bereits angezeigt wurde?

Kannst du die Verteilung damit nicht regeln:

SELECT id, (wert / (SELECT SUM(wert) FROM tabelle)) * 100 AS wert2 FROM tabelle ORDER BY wert2 DESC

Damit bekommst du den prozentualen Anteil der einzelnen IDs.

 

[proloser]

Ja ich hätte vielleicht dazu schreiben müssen damit es nicht genau auf den aufruf stimmen muss aber ich möchte einfach damit die Banner mit hohe Priorität (also höheren Zahl) öfter eingeblendet werden als die anderen.

So wie es aussieht muss ich mit dieser Variante ...

SELECT * FROM tabelle
 ORDER BY wert*RAND() DESC 
LIMIT 1;

... nicht überprüfen wie oft der Banner schon angezeigt wurde.