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

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

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

    Hallo,

    angenommen ich habe folgende Tabelle:

    Code:
    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!

  • #2
    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:

    Code:
    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
    Zuletzt geändert von AmicaNoctis; 21.08.2009, 20:39. Grund: Code optimiert und nochmal korrigiert
    [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
    Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
    Super, danke!
    [/COLOR]

    Kommentar


    • #3
      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.

      Kommentar


      • #4
        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?
        [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
        Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
        Super, danke!
        [/COLOR]

        Kommentar


        • #5
          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:
          Code:
          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);
          Zuletzt geändert von Boron; 21.08.2009, 21:22.

          Kommentar


          • #6
            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?
            [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
            Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
            Super, danke!
            [/COLOR]

            Kommentar


            • #7
              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.
              Code:
              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.
              I don't believe in rebirth. Actually, I never did in my whole lives.

              Kommentar


              • #8
                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.
                [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
                Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
                Super, danke!
                [/COLOR]

                Kommentar


                • #9
                  Zitat von AmicaNoctis Beitrag anzeigen
                  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 .
                  I don't believe in rebirth. Actually, I never did in my whole lives.

                  Kommentar


                  • #10
                    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...
                    Zuletzt geändert von Boron; 22.08.2009, 13:18.

                    Kommentar


                    • #11
                      Zitat von Boron Beitrag anzeigen
                      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.
                      I don't believe in rebirth. Actually, I never did in my whole lives.

                      Kommentar


                      • #12
                        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:
                        Code:
                        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:
                        Code:
                        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.

                        Kommentar


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

                          Wenn ich "nur"
                          Code:
                          SELECT @a := 0;
                          SELECT `name`, ((@a := @a + `wk1`)) as `summe` FROM `testsum`
                          ausführe, bekomme ich die Tabelle mit dem kumulierten Anteilen:

                          Code:
                          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
                          Code:
                          SELECT @a := 0;
                          SELECT `name`, ((@a := @a + `wk1`) - `wk1`) as `summe`, @a FROM `testsum`
                          liefert:
                          Code:
                          name	summe	@a
                          Peter	0	30
                          Paul	30	80
                          Simone	80	83
                          Heidi	83	149
                          Caro	149	182
                          Klaus	182	252
                          Wenn ich
                          Code:
                          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.
                          Zuletzt geändert von Boron; 22.08.2009, 11:15.

                          Kommentar


                          • #14
                            Zitat von Boron Beitrag anzeigen
                            Wenn ich
                            Code:
                            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
                            Zuletzt geändert von AmicaNoctis; 22.08.2009, 12:00.
                            [COLOR="DarkSlateGray"]Hast du die [COLOR="DarkSlateGray"]Grundlagen zur Fehlersuche[/color] gelesen? Hast du Code-Tags benutzt?
                            Hast du als URL oder Domain-Beispiele example.com, example.net oder example.org benutzt?
                            Super, danke!
                            [/COLOR]

                            Kommentar


                            • #15
                              Ok, bin mir nicht ganz sicher, was du meinst... aber mit
                              Code:
                              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.
                              Zuletzt geändert von Boron; 22.08.2009, 12:13.

                              Kommentar

                              Lädt...
                              X