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

Froschkoenig84 · 22 Juli 2015

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...

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->...
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. :/

Distrilec · 22 Juli 2015

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 · 22 Juli 2015

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.

Distrilec · 22 Juli 2015

akretschmer schrieb:
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.

Nope... Oracle kann das schon seit mehr als 10 Jahren....

akretschmer · 22 Juli 2015

echt?

Distrilec · 23 Juli 2015

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

Froschkoenig84 · 23 Juli 2015

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

Froschkoenig84 · 23 Juli 2015

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?

akretschmer · 23 Juli 2015

Froschkoenig84 schrieb:
Kurze Frage, ist es möglich innerhalb eines CROSS JOINs zu limitieren? - Vermutlich nicht,

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)

Froschkoenig84 · 23 Juli 2015

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? :/

Distrilec · 23 Juli 2015

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...

Froschkoenig84 · 23 Juli 2015

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.) :/

Distrilec · 23 Juli 2015

Grundsätzlich kann man nur Funktionen verwenden

Froschkoenig84 · 23 Juli 2015

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.

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

Froschkoenig84

Aktiver Benutzer

Distrilec

Datenbank-Guru

akretschmer

Datenbank-Guru

Distrilec

Datenbank-Guru

akretschmer

Datenbank-Guru

Distrilec

Datenbank-Guru

Froschkoenig84

Aktiver Benutzer

Froschkoenig84

Aktiver Benutzer

akretschmer

Datenbank-Guru

Froschkoenig84

Aktiver Benutzer

Distrilec

Datenbank-Guru

Froschkoenig84

Aktiver Benutzer

Distrilec

Datenbank-Guru

Froschkoenig84

Aktiver Benutzer

Ähnliche Themen