Wie muss ich abfragen?

[gma]

Hallo,

ich habe zwei Tabellen:

Tabelle1:

start ratio
155 0.91
252 0.82
613 0.91
710 0.95
1071 40178,00
1168 0.89
1529 38230,00
1626 0.93
1987 40178,00
2084 0.87
2445 0.87
2542 0.97
2903 38077,00
3000 0.82
3361 0.96
3458 0.74
3819 0.85
3916 0.69
4277 0.94
.....

Tabelle 2:

start end
155 4832
5376 6889
7859 8247
8732 9023
...


Beide Tabellen sind aufsteigend sortiert. Ich möchte jetzt eine weitere Tabelle erstellen, die nur die Reihen von Tabelle1 enthält, für die Tabelle2.start > Tabelle1.start > Tabelle2.end ist.

Geht das innerhalb von MySQL?


gma

[Heinrich]

Ich "oute" mich mal: Ich habe keinen Plan, was du willst.....

[feuervogel]

Zitat von Heinrich

Ich "oute" mich mal: Ich habe keinen Plan, was du willst.....

Zunächst möchte er einen JOIN über beide Tabellen mit einer trivialen JOIN bedingung und mehreren WHERE-bedingungen. desweitern möchte er das (unerfindlicherweise) in einer extra neuen tabelle. ich denke, hier wäre ein VIEW besser geeignet.

er möchte allerdings nicht wissen, wie man das macht oder hat irgendwelche konkreten probleme (geschweige denn vorarbeit geleistet), bei denen man ihm helfen könnte, sondern will einfach nur wissen, ob das "innerhalb von mysql" geht. meine antwort: ja.

[gma]

Zitat von feuervogel

Zunächst möchte er einen JOIN über beide Tabellen mit einer trivialen JOIN bedingung und mehreren WHERE-bedingungen. desweitern möchte er das (unerfindlicherweise) in einer extra neuen tabelle. ich denke, hier wäre ein VIEW besser geeignet.

er möchte allerdings nicht wissen, wie man das macht oder hat irgendwelche konkreten probleme (geschweige denn vorarbeit geleistet), bei denen man ihm helfen könnte, sondern will einfach nur wissen, ob das "innerhalb von mysql" geht. meine antwort: ja.

Bitte um Entschuldigung für meine kurz angebundene Anfrage.

Einfach nur ein paar views und selects mit ein bischen where ist es aber leider nicht.

Die Tabellen sind mehrere Millionen Einträge lang (Tabelle 1). Tabelle 2 ist ca 15000 Einträge lang.



Zum Thema nichts getan: Das Problem habe ich (leider nur sehr umständlich) bereits ausserhalb von MySQL gelöst, und zwar so:

Die Tabellen habe ich sortiert, aus MySQL exportiert, den Inhalt der resultierenden Textdateien Zeile für Zeile mit einem Perl-Skript in Arrays eingelesen, gesplitted (2 Spalten) und dann in einem simplen Loop die entsprechenden Abfragen gemacht, die gemacht werden müssen:

finde die Reihen aus Tabelle 1, die innerhalb von START und ENDE aller Reihen aus Tabelle 2 liegen. Das bedeutet: 15000 mal Bereiche festlegen, und die innerhalb dieser BEreiche liegenden Werte von TAbelle 1 ausgeben (egal ob in neue Tabelle oder als select-Ergebnis).

# retrieve only enriched fragments from array
# uses sorted list of 1 chromosome array results

$pair_file = @ARGV[0];
$regions_file = @ARGV[1]; 
$output_file = @ARGV[2];

open ( PAIR , $pair_file );
open ( REGION , $regions_file );

open (OUTPUT , ">$output_file" );

#load Pair-file into arrays @pos and @ratio
$c = 0;
foreach ( <PAIR> )

{
    $line = $_;
    
    # now split $_ into to variables separated by tab $pos and $ratio!!!!
    
    @splitted = split(/\t/);
    @pos[$c] = @splitted[1];
    @ratio[$c] = @splitted[2];
    
    print "@pos[$c]\t" , "@ratio[$c] \n";
    $c++;
}
$arraynumb = $c;

print "\n\n\n";

$c = 0;
foreach ( <REGION> )

{
    $line = $_;
    
    # now split $_ into to variables separated by tab $pos and $ratio!!!!
    
    @splittedb = split(/\t/);
    @start[$c] = @splittedb[1];
    @end[$c] = @splittedb[2];
    
    print "@start[$c]\t" , "@end[$c] \n";
    $c++;
}
$regions_count = $c;

$i = 0; #$i is array counter
$c = 0; #$c is regions counter
#print OUTPUT "$arraynumb\n";

while  ($i < $arraynumb)
    
    {
    
    if (@pos[$i] >= @start[$c])
    
        { 

        if (@pos[$i] <= @end[$c])
        
            {
            @pos[$i]=~ s/\s+//g ;
            @ratio[$i]=~ s/\s+//g ;
            print OUTPUT "@pos[$i]\t" , "@ratio[$i]\n ";
            
            }
            
        else {$c++;}
        $i++;
        
    
        }

    else {$i++;}    

    }

Das ist ganz schönes Kauderwelsch, aber ich habe leider noch nicht viel Zeit gefunden, mich mit dem Skripten zu beschäftigen (leider sind hier auch noch die Variablen-Namen nicht mit den Beispiel-Tabellen übereinstimmend).

Egal wie, was ich brauche ist so etwas wie die while-Schleife, nur innerhalb von MySQL. Oder braucht man eine externe Skriptsprache, um z.B. über Perls DBD::MySQL die Abfrage durchzuführen?

gma

[gma]

irgendwie fällt es mir schwer (weil ich einfach nicht besonders auskenne) mir korrekt auszudrücken, wie ich gerade beim Durchlesen meiner eigenen Antwort feststellen musste.

Ich möchte wissen, wie ich innerhalb MySQL (5.0, bevorzugt über MySQL-Query-Browser angesteuert) eine Schleife programmiere, die durch Tabelle 2 Zeile für Zeile durchgeht, START und END feststellt, und jeweils alle Zeilen aus Tabelle 1 ausgibt, für die Tabelle1.START zwischen Tabelle2.START und Tabelle2.END aus der jeweiligen Zeile aus Tabelle 2 liegt.

Ich habe hierzu auch schon das MySQL-Handbuch zu Rate gezogen, bin aber damit überfordert (ich verstehe Kapitel 3, das war es aber auch).

gma

[feuervogel]

vielleicht meinst du das:

SELECT
Tabelle1.start, Tabelle1.ratio, Tabelle2.start, Tabelle2.end
FROM
Tabelle1, Tabelle2
WHERE Tabelle2.start > Tabelle1.start AND Tabelle1.start > Tabelle2.end

?

[gma]

Zitat von feuervogel

vielleicht meinst du das:

SELECT
Tabelle1.start, Tabelle1.ratio, Tabelle2.start, Tabelle2.end
FROM
Tabelle1, Tabelle2
WHERE Tabelle2.start > Tabelle1.start AND Tabelle1.start > Tabelle2.end

?

vielen Dank, das tut es tatsächlich, auch wenn die Abfrage im Vergleich zu meinem Perl-Skript ziemlich langsam ist (Skript: G4-iBook: 40 sek; Linux auf 2X2,4Ghz : 10 sek; MySQL: für 7400 results etwas mehr als 10 mInuten auf dem Mac), aber es geht.

Ich verstehe nur nicht wieso, denn wo erkläre ich denn MySQL welches Tabelle2.start und welches Tabelle2.end verwendet werden soll? Es könnte ja auch Tabelle2.start aus Zeile 1 und Tabelle2.end aus Zeile sonstwo sein. Oder wird automatisch immer nur die Kombination start und end aus einer Zeile eingelesen? Oder werden alle Kombinationen durchgetestet (das würde dann die Geschwindigkeit erklären)? Dann wäre doch eine Schleife, die immer nur die richtigen Paare start und end ausliest und als Suchkriterium verwendet einfach schneller, oder?

gma

[feuervogel]

Zitat von gma

vielen Dank, das tut es tatsächlich, auch wenn die Abfrage im Vergleich zu meinem Perl-Skript ziemlich langsam ist (Skript: G4-iBook: 40 sek; Linux auf 2X2,4Ghz : 10 sek; MySQL: für 7400 results etwas mehr als 10 mInuten auf dem Mac), aber es geht.

die vorliegende datenstruktur ist ja auch nicht für so eine abfrage optimiert.

Ich verstehe nur nicht wieso, denn wo erkläre ich denn MySQL welches Tabelle2.start und welches Tabelle2.end verwendet werden soll? Es könnte ja auch Tabelle2.start aus Zeile 1 und Tabelle2.end aus Zeile sonstwo sein. Oder wird automatisch immer nur die Kombination start und end aus einer Zeile eingelesen? Oder werden alle Kombinationen durchgetestet (das würde dann die Geschwindigkeit erklären)? Dann wäre doch eine Schleife, die immer nur die richtigen Paare start und end ausliest und als Suchkriterium verwendet einfach schneller, oder?

gma

es werden alle kombinationen getestet. du schreibst nirgends, dass die zeile 1 aus tabelle1 und zeile 1 aus tabelle2 im zusammenhang stehen (auch sehe ich nirgends ein fremd- und primärschlüsselattribut).

im gegenteil:

Die Tabellen sind mehrere Millionen Einträge lang (Tabelle 1). Tabelle 2 ist ca 15000 Einträge lang.

das hieße ja, dass nur die ersten 15.000 zeilen aus tabelle1 eine entsprechung in tabelle2 haben (wenn zeile x aus tabelle1 ausschließlich mit zeile x aus tabelle2 zusammen betrachtet werden soll). dann hast du mit reinem sql allerdings ein problem (und du solltest dein tabellendesign überdenken).

[gma]

es werden alle kombinationen getestet. du schreibst nirgends, dass die zeile 1 aus tabelle1 und zeile 1 aus tabelle2 im zusammenhang stehen (auch sehe ich nirgends ein fremd- und primärschlüsselattribut).

hmmh, stimmt natürlich. Aber genau das möchte ich ja: für eine Abfrage in Tabelle1 soll immer das Paar START, END benutzt werden, wobei dieses Paar verschiedene Einträge in Tabelle 1 identifizieren kann (deswegen macht ein Primärschlüssel meiner Meinung auch keinen Sinn).

Ich sehe schon, das das in Datenbanksystemen irgendwie anders läuft. Aber nochmal, wäre es nicht am sinnvollsten, so zu arbeiten, wie ich es in meinem Perl-Skript mache. Da gehe ich einfach Zeile für Zeile durch Tabelle 2, stelle START und END fest, gehe linear durch Tabelle1 , stelle fest ob die Bedingung erfüllt ist (Ausgabe), noch nicht (zu klein - dann nächste Zeile von Tabelle1, oder grösser ist (dann weiter zu nächster Zeile in Tabelle2). Das geht natürlich nur aufgrund der Sortierung beider Tabellen.

Oder macht man das in Datenbanksystemen einfach nicht? Wie müsste ich dann das Design verändern, um schneller zu suchen?

gma

[feuervogel]

wenn du genau das machen willst, was ich nun glaube zu verstehen (sorry, aber die zeiten, dass ich mir undokumentiertes perl-gewusel zu gemüte führe, sind vorbei), dann ist es tatsächlich besser nicht auf dem datenbanksystem zu arbeiten und das alles einmal in den RAM zu lesen und dort darauf zu arbeiten.