ein Tupel "zufällig" auswählen, die Wahrscheinlichkeit wird über Attribut bestimmt

[Boron]

Hallo,

angenommen ich habe folgende Tabelle:

id | name  | wk1
---------------
1  | Peter | 30
2  | Paul  | 50
3  | Simone| 3
4  | Heidi | 66
5  | Caro  | 33
6  | Klaus | 70
usw.

(Die Summe von wk1 ergibt in diesem Fall 30+...+70 = 252).

Wie kann ich jetzt mit einer Wahrscheinlichkeit von 30/252 die Zeile 1 auswählen lassen? bzw. mit einer Wahrscheinlichkeit von 50/252 die Zeile 2 usw.?

Also ich will mithilfe einer SQL-Abfrage nur ein Tupel als Ergebnis haben, aber die Auswahl dieses Tupels soll mit entsprechender Wahrscheinlichkeit erfolgen, welche implizit über das wk1-Attribut festgelegt wird (wobei wk1 selbst keine Wahrscheinlichkeit repräsentiert, sondern erstmal nur ein Anteilswert ist).

Bin über jede Hilfe dankbar!

[AmicaNoctis]

Hallo,

es ist zwar mit SQL machbar, auch mit einem einzigen Statement, aber dieses ist sehr komplex und benötigt Subselects. Besser ist es, alle Rows abzuholen und mit PHP eine auszuwählen. Aber wenn du auf SQL bestehst:

select *, @a:=0
from (select *, ((@b:=@a:=@a+wk1)-wk1) as sum from testsum) as testsum
where sum < rand()*@b order by sum desc limit 1;

Gruß,

Anja

[Boron]

Hallo Anja,

danke erstmal für die Antwort.

Besser ist es, alle Rows abzuholen und mit PHP eine auszuwählen.

Ja naja. Momentan sind es im Durchschnitt vielleicht 300 betroffene Zeilen (kommen ein paar WHERE-Bedingungen dazu), später sind es vielleicht mal 3000. Das alles mit PHP durchzugehen, scheint mir nicht besonders optimal.

Dein Code funktioniert leider nicht.

Mit @-kann man in SQL eine Variable deklarieren oder wie? Das war mir neu... kommt mir jedoch merkwürdig vor, insbesondere im subselect, wo es zwei mal hintereinander steht. Dein sum ist bei mir immer NULL. Ich würde mich freuen, wenn du nochmal drüber schauen könntest.

[AmicaNoctis]

Bei mir funktioniert's. Geht es um MySQL oder was verwendest du? Welche Version hast du im Einsatz. Kommt eine Fehlermeldung oder nur falsche Werte?

[Boron]

Ich verwende MySQL -> InnoDB.

Wenn ich es ganz ausführe, kommen überhaupt keine Werte (RAND() ist rot markiert):





Wenn ich das WHERE weglasse, kommt das:



Tabellenstruktur:

CREATE TABLE IF NOT EXISTS `testsum` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `wk1` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Daten für Tabelle `testsum`
--

INSERT INTO `testsum` (`id`, `name`, `wk1`) VALUES
(1, 'Peter', 30),
(2, 'Paul', 50),
(3, 'Simon', 3),
(4, 'Heidi', 66),
(5, 'Caro', 33),
(6, 'Klaus', 70);

[AmicaNoctis]

In PHPMyAdmin geht es bei mir auch nicht. Ich hab es die ganze Zeit auf der Kommandozeile getestet. Scheint an PMA zu liegen. Hast du's mal direkt von PHP aus probiert?

[wahsaga]

Wie oft werden denn Datensätze hinzugefügt/entfernt, oder der Wert wk1 geändert?

Wenn das nicht allzu oft passiert, dann würde ich die aufsummierten wk1-Werte gleich mit in der Tabelle vorhalten.

SELECT @a :=0;
UPDATE testsum SET swk1 = ( @a := @a + wk1 ) ORDER BY id ASC;


id 	name 	wk1 	swk1 
1	Peter	30	30
2	Paul	50	80
3	Simon	3	83
4	Heidi	66	149
5	Caro	33	182
6	Klaus	70	252

Das könnte man dann auch gleich noch auf das Interval 0 bis 1 normalisieren - dann braucht man auch beim Selektieren nicht mehr die Gesamtsumme kennen, sondern kann gleich mit RAND() arbeiten, was einen Wert zwischen 0 (einschl.) und 1 liefert.

[AmicaNoctis]

Das klingt zwar erstmal praktisch, aber man muss bei jedem UPDATE und DELETE höllisch aufpassen, dass man das nachher wieder angleicht. Davon abgesehen ist es halt einfach eine Redundanz und daher nicht zu empfehlen.

[wahsaga]

Das klingt zwar erstmal praktisch, aber man muss bei jedem UPDATE und DELETE höllisch aufpassen, dass man das nachher wieder angleicht.

Das versteht sich von selber.
Trigger bspw. können beim "höllisch aufpassen" helfen.

Davon abgesehen ist es halt einfach eine Redundanz und daher nicht zu empfehlen.

Das ist mir zu pauschal.

Redundanz kann in Ausnahmefällen durchaus bewusst in Kauf genommen werden - wenn ihre Vorteile (bspw. der, dass aufwendigere Berechnungen nicht jedes mal on-the-fly vorgenommen werden müssen) die Nachteile überwiegen .

[Boron]

wk1 ist leider kein reales Attribut ist, sondern hier vereinfacht dargestellt als ein Produkt zweier Attribute (wk_haeufigkeit und wk_mod) aus noch einer anderen Tabelle.

Relation1: zbs(zb_id,zb_name,...,wk_haeufigkeit)
Relation2: zb_auswahl(zb_vork_id,zb_id,ort_id,vegetation_id,...,wk_mod)

(Fett markiert Primärschlüssel. Kursiv markiert sind Fremdschlüssel.)


also aus zb_auswahl soll eine zb_id selektiert werden. Mit einer Wahrscheinlichkeit, die sich von wk_haeufigkeit*wk_mod anteilig an der gesammten Summe von wk_haeufigkeit*wk_mod ergibt...

[wahsaga]

wk1 ist leider kein reales Attribut ist, sondern hier vereinfacht dargestellt als ein Produkt zweier Attribute (wk_haeufigkeit und wk_mod) aus noch einer anderen Tabelle.

Gut, aber das macht für das prinzipielle Vorgehen ja keinen wesentlichen Unterschied.

[Boron]

Stimmt, nur in der Relation tritt eine Besonderheit auf: Die ganzen Fremdschlüssel - abgesehen von zb_id - dürfen auch NULL sein (steinigt mich ). Semantisch bedeutet das, dass in der WHERE-Abfrage dieser Fremdschlüssel keine Rolle spielt.

Dann stellt sich aber das Problem für die Summenbildung... es scheint mir nicht möglich, dein swk1 sinnvoll zu bilden bzw. zu normalisieren.

Anjas Code funktioniert erstmal für meine Beispieltabelle, wie folgt:

SELECT @a :=0, @b :=0;
SELECT  `testsum`.`id` ,  `testsum`.`name` ,  `testsum`.`wk1` ,  `summe` 
FROM 
	(SELECT *, ((@b := @a := @a + `wk1` ) - `wk1`) as `summe` FROM `testsum`) as `testsum`
WHERE (`summe` < RAND()*@b)
ORDER BY  `summe` DESC 
LIMIT 1

5000 zufällige Auswahlen aus den o.g. 6 Zeilen brachten:

Array
(
    [Caro] => 1500
    [Heidi] => 1409
    [Peter] => 33
    [Simone] => 468
    [Klaus] => 1397
    [Paul] => 193
)

Das Laufzeitverhalten mit 4 Sekunden finde ich gut, nur die Zahlen sind nicht so, wie sie sein sollten. Peter dürfte nie und nimmer so wenig Treffer haben - Simone müsste die wenigsten haben.
Ich muss mir das morgen nochmal in Ruhe anschauen.

[Boron]

AmicaNoctis, wofür war die Subtraktion von wk1 im Subselect gedacht?

Wenn ich "nur"

SELECT @a := 0;
SELECT `name`, ((@a := @a + `wk1`)) as `summe` FROM `testsum`

ausführe, bekomme ich die Tabelle mit dem kumulierten Anteilen:

name	summe
-------------
Peter	30
Paul	80
Simone	83
Heidi	149
Caro	182
Klaus	252

Wie muss ich darauf jetzt das RAND() anwenden? Wie kann ich mit einer Wahrscheinlichkeit von 30/252 den Peter auswählen bzw. mit 50/252 den Paul usw.?


Nachtrag: Ah ich glaube, ich verstehe langsam, warum -wk1.

Also

SELECT @a := 0;
SELECT `name`, ((@a := @a + `wk1`) - `wk1`) as `summe`, @a FROM `testsum`

liefert:

name	summe	@a
Peter	0	30
Paul	30	80
Simone	80	83
Heidi	83	149
Caro	149	182
Klaus	182	252

Wenn ich

SELECT @a := 0;
SELECT `t1`.`name`
FROM 
	(SELECT `name`, ((@a := @a + `wk1`) - `wk1`) as `summe`, @a FROM `testsum`) as `t1`
WHERE `t1`.`summe` < (RAND()*@a)
ORDER BY `t1`.`summe` DESC 
LIMIT 1

ausführe, stimmen die Häufigkeiten aber nicht.
Ich kann den Fehler einfach nicht entdecken.

[AmicaNoctis]

Wenn ich

SELECT @a := 0;
SELECT `t1`.`name`
FROM 
    (SELECT `name`, ((@a := @a + `wk1`) - `wk1`) as `summe`, @a FROM `testsum`) as `t1`
WHERE `t1`.`summe` < (RAND()*@a)
ORDER BY `t1`.`summe` DESC 
LIMIT 1

ausführe, stimmen die Häufigkeiten aber nicht.
Ich kann den Fehler einfach nicht entdecken.

Wie sieht @a denn nachher aus? Ich hatte das Subselect benutzt, damit ich außen die Variable @a auf 0 setzen kann und @b hab ich benutzt, weil @a außerhalb immer noch 0 ist. Wenn du die Initialisierung sowieso in einem weiteren SELECT (besser SET) machst, kannst du auch das Subselect sein lassen. Ich hatte das nur so geschrieben, damit es eine einzelne atomare Anweisung ist.

Gruß,

Anja

[Boron]

Ok, bin mir nicht ganz sicher, was du meinst... aber mit

SET @a := 0;
SELECT  `name` , ((@a := @a +  `wk1`) -  `wk1` ) AS  `summe`, @a 
FROM  `testsum`
HAVING `summe` < RAND()*@a
ORDER BY `summe` DESC
LIMIT 1

bekomme ich leider immer noch dieselben falschen Ergebnisse. Verstehe nicht ganz, was das @b ändern soll.