Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

SELECT (tbl_a): For Each Row - Multi-INSERT (tbl_b)

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von Froschkoenig84, 22 Juli 2015.

  1. Froschkoenig84

    Froschkoenig84 Aktiver Benutzer

    Hallo, vermutlich hattet ihr das alle schon mal, ich bilde mir ein, ich hätte auf MySQL mal einen Trigger gebastelt, ist aber schon ein paar Jahre her und ich weiß gar ned, ob das auf MSSQL/TransSQL überhaupt laufen würde...

    Ich habe eine Tabelle A mit 250'000 Orten weltweit. Innerhalb dieser Tabelle gibt es die Felder GEO_LAT und GEO_LON. Nun möchte ich eine Tabelle-B mit den DISTANCES erzeugen.

    TBL_A:
    • ID
    • GEO_LAT
    • GEO_LON
    TBL_B:
    • ID_A
    • ID_B
    • DISTANCE
    Für die Einzelabfrage habe ich nun also:

    Code:
    SELECT TOP 100
    [id],
    ROUND(6378388 * ACOS(SIN(@geo_lat) * SIN([geo_lat]) + COS(@geo_lat) * COS([geo_lat]) * COS([geo_lon] - @geo_lon)),0) AS 'distance'
    FROM
    [dbo].[destinations]
    WHERE
    [is_country] != 1 AND
    [geo_lat] IS NOT NULL AND [geo_lat] != 0 AND
    [geo_lon] IS NOT NULL AND [geo_lon] != 0
    ORDER BY distance

    Wie ihr sehen könnt, will ich auch nur die ersten 100 DESTINATIONs. Das funktioniert soweit ganz gut, allerdings nur für Einzelabfragen. Wäre schick, wenn ich nun...
    1. einen SUBSELECT oder INNERJOIN verwenden könnte um alle Datensätze mit allen anderen Datensätzen gegen zu fragen. Also Beispiel...
      • München->Berlin
      • München->Köln
      • München->Hamburg
      • Berlin->...
    2. und außerdem gleich noch für jedes Ergebnis einen INSERT (oder noch besser einen UPDATE, falls bereits vorhanden) in die TBL_B. :) Ich denke das ist klar oder?
    Wer kann mir helfen, ich bin anscheinend zu doof auch nur die Multi-SELECTs hinzubekommen, ganz zu schweigen von INSERT. :/
     
    Zuletzt von einem Moderator bearbeitet: 23 Juli 2015
  2. Distrilec

    Distrilec Datenbank-Guru

    Code:
    Select * From destinations Cross Join destinations
    Karthesisches Produkt bilden und für jede Zeile berechnen lassen...
    Vllt. als kleiner Hinweis: Das Ergibt ist dann (Anzahl Zeilen in "destinations" - 1)² - nach deiner Aussage also 249.999² = ca. 62.500.000.000
    Das dauert etwas :)
     
    akretschmer gefällt das.
  3. akretschmer

    akretschmer Datenbank-Guru

    Alternative: PostgreSQL / PostGIS nutzen und dort die KNN-Indexe. Das macht Umkreissuche Indexbasiert - und damit RICHTIG schnell. Kann aber derzeit wohl wirklich nur PostgreSQL / PostGIS.
     
  4. Distrilec

    Distrilec Datenbank-Guru

    Nope... Oracle kann das schon seit mehr als 10 Jahren.... :)
     
    akretschmer gefällt das.
  5. akretschmer

    akretschmer Datenbank-Guru

  6. Distrilec

    Distrilec Datenbank-Guru

    Oracle Spatial (was sich generell um räumliche Informationen kümmert). Eingeführt mit Oracle 9i (glaube ich...)
     
  7. Froschkoenig84

    Froschkoenig84 Aktiver Benutzer

    Ja, Oracle kann das, auch MSSQL kann das, aber es funktioniert oft nicht, wenn der DB-Server irgendwelche Sonder-Settings genießt und da ich die auf dem Server meines Kunden ablege, ... ich rechne das lieber einmal pro Monat via Cron ab. :)

    Wegen dem CROSS JOIN... ich beschränke die Abfrage ja auf die 100 nahesten Destinationen. :) //TOP 100
     
  8. Froschkoenig84

    Froschkoenig84 Aktiver Benutzer

    Kurze Frage, ist es möglich innerhalb eines CROSS JOINs zu limitieren? - Vermutlich nicht, da dies ja dem Karthesisches Produkt widerspricht.

    Aber es muss doch eine Möglichkeit geben, jede Zeile durchzulaufen und im Vergleich zu sich selbst dann nur die 100 niedrigsten (via ORDER BY) Datensätze auszugeben. :/ Oder nicht?

    Alternativ brauche ich ein SQL-Skript, das quasi für jede Zeile/Datensatz eine eigene Prozedur anspricht, die wiederum mit den übergebenen Parameter eine Abfrage auf 100 niedrigsten (via ORDER BY) Datensätze begrenzt. :) Oder sehe ich das falsch? - Ich habe auch daran das in .NET zu lösen, indem ich einfach die Datensätze in ein Array ziehe und dann pro Zeile der ersten Instanz (also max. 100) INSERTs erzeuge. Aber vermutlich bekäme ich einen ServerTimeOut, in PHP würde das übrigens so aussehen:

    PHP:
    <?php
    $destinations 
    = [
    118527=>[-6.282452,106.798069],
    94595=>[43.9674095,12.574701],
    66954=>[47.106601,15.709999],
    86299=>[40.8394155,9.405953],
    113231=>[-6.260548,106.8135175],
    118504=>[-6.2894905,106.7913565],
    113332=>[12.5859325,-81.6992395],
    83986=>[12.583109,-81.701914],
    63502=>[40.81316,-91.103134],
    123515=>[-40.811577,172.787469]
    ];
    global 
    $destinations;
    $dissies = array();
    global 
    $dissies;


    function 
    getDestinations($id,$destination_a) {
    global 
    $destinations;
    global 
    $dissies;
    $i 0;
    foreach(
    $destinations as $key => $destination_b) {
      if(
    $id != $key) {
       
    $d['ids_a'][] = $id;
       
    $d['ids_b'][] = $key;
       
    $d['dists'][] = round(6378388 acos(sin($destination_a[0]) * sin($destination_b[0]) + cos($destination_a[0]) * cos($destination_b[0]) * cos($destination_b[1] - $destination_a[1])));
      }
    }
    if(isset(
    $d) && is_array($d['dists'])) {
      
    array_multisort($d['dists'],$d['ids_a'],$d['ids_b']);
      foreach(
    $d['dists'] as $key => $dist)
      {
       
    /*
       $dissies[] = [
        'id_a' => $d['ids_a'][$key],
        'id_b' => $d['ids_b'][$key],
        'distance' => $dist
       ];
       */
       
    echo '['.$d['ids_a'][$key].'|'.$d['ids_b'][$key].'] : '.$dist.'<br />';
       if(++
    $i >= 100) break;
      }
      echo 
    '<br />';
    }
    unset(
    $destinations[$id],$d);
    }

    $count 0;
    foreach(
    $destinations as $key => $destination_a) {
    echo ++
    $count.'<br />';
    getDestinations($key,$destination_a);
    }
    ?>
    Aber das muss ja auch irgendwie direkt in SQL gehen, ... ich bin leider nicht so fit, wenn es darum geht, dynamische SQL-Skripte zu fertigen. :/ Kann mir jemand ein paar Tipps geben?
     
  9. akretschmer

    akretschmer Datenbank-Guru

    Code:
    test=*# select * from foo;
     i
    ---
     1
     2
     3
     4
     5
     6
    (6 rows)
    
    test=*# select f1.*, f2.* from foo f1 cross join foo f2 where f1.i < f2.i;
     i | i
    ---+---
     1 | 2
     1 | 3
     1 | 4
     1 | 5
     1 | 6
     2 | 3
     2 | 4
     2 | 5
     2 | 6
     3 | 4
     3 | 5
     3 | 6
     4 | 5
     4 | 6
     5 | 6
    (15 rows)
    
     
  10. Froschkoenig84

    Froschkoenig84 Aktiver Benutzer

    Okay, öhmmm... da steig ich jetz grad aus. :/ Das Limit begrenzt aber nur die unsortierten Ergebnisse. :/

    Egal, ... ich hab jetzt mal meine PROCEDURE erstellt...
    Code:
    CREATE PROCEDURE GetDistancesBetweenDestinations
        @id INT,
        @geo_lat FLOAT,
        @geo_lon FLOAT
    AS
    BEGIN
        INSERT INTO
            [database].[dbo].[distances_destinations] (
                [id_a],
                [id_b],
                [distance]
            )
            SELECT TOP 100
                @id AS 'id_a',
                [id] AS 'id_b',
                ROUND(6378388 * ACOS(SIN(@geo_lat) * SIN([geo_lat]) + COS(@geo_lat) * COS([geo_lat]) * COS([geo_lon] - @geo_lon)),0) AS 'distance'
            FROM
                [database].[dbo].[destinations]
            WHERE
                [is_country] <> 1 AND
                [market] = 'de' AND
                [geo_lat] IS NOT NULL AND [geo_lat] != 0 AND
                [geo_lon] IS NOT NULL AND [geo_lon] != 0
            ORDER BY
                'distance'
    END
    Jetzt kommt die große Frage... Kann ich diese Prozedur nun innerhalb eines SELECTs (notfalls muss ich noch ein OUTPUT-Parameter "XX neue Datensätze hinzugefügt" definieren), für jede gefundene Zeile verwenden? Aus diesem SELECT würde ich mir gerne die Werte holen, die ich dann 250'000 mal in die entsprechende Prozedur feuere. Aktuell wirft er mir einen Fehler....

    Code:
    SELECT TOP 100
                [id],
                EXEC dbo.GetDistancesBetweenDestinations [id],[geo_lat],[geo_lon] AS 'test'
            FROM ...

    Der Prozeduraufruf soll gleich mit den SELECT-Daten gefüttert werden, sollte doch eigentlich klappen oder nicht? :/
     
  11. Distrilec

    Distrilec Datenbank-Guru

    Code:
    Select *  from (
    Select t1.*,  (entfernungsberechnung), row_number() over(partition by t1.city order by (entfernungsberechnung) asc) as rn
    From  destinations t1
    
    Cross Join destinations t2
    )
    Where rn <= 100
    Jetzt mal als Pseudo-Pseudocode :)

    EDIT: Du musst trotzdem alle 62 Milliarden Zeilen (durch das karthesische Produkt) berechnen... es weden danach einfach nur die ersten 100 angezeigt und der rest wird weggeworfen...
     
  12. Froschkoenig84

    Froschkoenig84 Aktiver Benutzer

    Okay, ... hab ich ausprobiert, irgendwie mach ich beim unteren WHERE etwas falsch. :/
    Code:
    SELECT *
    FROM (
        SELECT
            t1.*,
            ROUND(6378388 * ACOS(SIN(t1.geo_lat) * SIN(t2.[geo_lat]) + COS(t1.geo_lat) * COS(t2.[geo_lat]) * COS(t2.[geo_lon] - t1.geo_lon)),0),
            row_number() OVER(PARTITION BY t1.city ORDER BY ROUND(6378388 * ACOS(SIN(t1.geo_lat) * SIN(t2.[geo_lat]) + COS(t1.geo_lat) * COS(t2.[geo_lat]) * COS(t2.[geo_lon] - t1.geo_lon)),0) ASC) AS 'rn'
        FROM
            [database].[dbo].[destinations] t1
    
        CROSS JOIN
            [database].[dbo].[destinations] t2
    )
    WHERE 'rn' <= 100
    Aber die Berechnung wäre vermutlich ziemlich übel. :/ Das geht in den GByte bereich, um diese Tabelle nur darstellen zu können.
    Vermutlich ist der Ansatz mit einer PROCEDURE sinnvoller. Sobald ich herausgefunden habe, wie man eine Prozdur innerhalb eines SELECTs verwendet.
    (Vielleicht muss ich mit einer dazwischengeschaltenen FUNCTION tricksen.) :/
     
  13. Distrilec

    Distrilec Datenbank-Guru

    Grundsätzlich kann man nur Funktionen verwenden :)
     
  14. Froschkoenig84

    Froschkoenig84 Aktiver Benutzer

    Schade. :/ Aber ich scheitere heute sowieso an allem :(

    Allein schon in meiner PROCEDURE in der ich ein INSERT-INTO-SELECT ausführe, in welchem aber das ORDER BY nicht berücksichtigt wird.

    Am Besten löse ich das jetzt ganz anders...
    Ich zieh mir die Tabelle in C#.NET in ein LIST und verfahre ähnlich wie oben mit PHP angedeutet, dann kombiniere ich immer 1'000 Primär-Datensätze zu einem (maximal 100'000) INSERT und hoffe, dass ein CRON mir nachts einfach das C#.NET-Skript abarbietet.

    Ich wollte eigentlich nur die Übergabe-Latenzen aussparen, aber das wird mir alles zu blöd. Mit MongoDb wäre ich bereits fertig. ;)


    EDIT: Danke an alle, für eure Hilfe. :)
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden