Na ja, da war was mit den doofen Datum(s)
T-SQL
Ein Kunde hat eine Auftragstabelle, diese hat eine Spalte O_CREATED, er möchte nun alle Datum(s) selektiert haben, bei denen ein Abstand von mindestes 7 Tagen zwischen der Erstellung existiert....
Kategorie Ein Kunde hat eine Auftragstabelle, diese hat eine Spalte O_CREATED, er möchte nun alle Datum(s) selektiert haben, bei denen ein Abstand von mindestes 7 Tagen zwischen der Erstellung existiert....
Hier meine Lösung ->
select r1.o_created,min(r2.o_created) as next_date into #tmp from PG_ORDER r1, PG_ORDER r2 where r1.o_created < r2.o_created groupby r1.o_created
select* from #tmp where o_created + 7 < next_date
droptable #tmp
Gruß JJR
P.S.: Andere Lösungen willkommen!
select r1.o_created,min(r2.o_created) as next_date into #tmp from PG_ORDER r1, PG_ORDER r2 where r1.o_created < r2.o_created groupby r1.o_created
select* from #tmp where o_created + 7 < next_date
droptable #tmp
Gruß JJR
P.S.: Andere Lösungen willkommen!
Kommentare
Dein Kunde möchte aus der Auftragstabelle alle "Totzeiten" mit Start und Ende haben wo in einem Zeitraum von mind. 7 Tagen keine einzige Order erstellt wurde, ohne Rücksicht auf irgendwelche anderen Eigenschaften der Orders - nur der Erstellungszeitpunkt zählt. Korrekt?
Gruß,
Sebastian
P.S.: wenn ich das nämlich nicht verstanden habe, sollte ich ja lieber auch nicht weiter versuchen schlaue Kommentare zu dem Thema abzugeben
Erstellt von Sebastian Stricker um 09:44:45 PM am 05/27/2009 | - Website - |
richtig, kann man ja auch auf Totzeiten bei Hotelzimmern, Autos und ähnlichem anwenden ist ja nur ein Group by ein weiterer denke ich.
Gruß JJR
Erstellt von JakeJBlues um 09:58:03 PM am 05/27/2009 | - Website - |
o.k, was hältst du dann davon...
select o1.o_created as 'start', o2.o_created as 'end'
from pg_order o1, pg_order o2
where o1.o_created+7 < o2.o_created
and not exists (select 1 from pg_order o where o.o_created > o1.o_created and o.o_created < o2.o_created)
group by o1.o_created, o2.o_created
...das sollte alle "Totzeiten" mit Start und Ende ermitteln, wo innerhalb von 7 Tagen keine Order angelegt wurde und kommt ohne temp. Tabelle aus. Ob es tatsächlich performanter ist habe ich jetzt nicht geprüft - aber i.d.R. ist so ein Subselect mit exists ganz schön schnell
Grüße,
Sebastian
Erstellt von Sebastian Stricker um 10:37:40 PM am 05/27/2009 | - Website - |
die Sache mit dem Exists hat was, habe ich nicht bedacht nur eine Frage habe ich noch.....
group by wozu?
Gruß JJR
Erstellt von JakeJBlues um 11:12:24 PM am 05/27/2009 | - Website - |
habe es auf einer Tabelle mit 12000 Datum(s) laufen lassen meine Version 10 Sek. Deine 1:47 Min.
Gruß JJR
P.S.: Wäre Cool, wenn Du das mal auf Oracle probieren könntest!
Erstellt von Joerg Reck um 10:34:19 AM am 05/28/2009 | - Website - |
dass das exists hier so deutlich langsamer ist hätte ich tatsächlich nicht vermutet - probiere das dann mal auf Oracle aus, da nimmt man das eigentlich ganz gerne mal.
Aber die eigentlichen Performance-Einbußen kommen hier anscheinend vielmehr durch die doppelte Nutzung von PG_ORDER in der FROM-Klausel, wahrscheinlich kann da der Index auf O_CREATED nicht so gut genutzt werden wie gewünscht.
Deshalb hab ich mal folgendes probiert um die doppelte Nutzung von PG_ORDER im FROM zu eliminieren:
select o1.o_created as 'start', (select min(o2.o_created) from PG_ORDER o2 where o2.o_created > o1.o_created) as 'end'
from pg_order o1
where o1.o_created+7 < (select min(o2.o_created) from PG_ORDER o2 where o2.o_created > o1.o_created)
group by o1.o_created
...auf meinem Laptop mit 12.000 Datensätzen keine messbare Ausführungszeit
Ich vermute das Subselect erkennt, dass kein neuer Index-Scan notwendig ist, da ja das minimale Datum ausgehend vom aktuellen gesucht wird und man dazu nur vom aktuellen Indexstand weiter schauen muss ohne nochmal mit der Suche von vorne zu beginnen (auf O_CREATED: nonclustered Index, aufsteigend sortiert).
Grüße,
Sebastian
P.S.: das group by wird nur benötigt falls exakt gleiche Startzeistempel in PG_ORDER existieren und man diese nicht mehrfach im Resultset haben möchte
Erstellt von Sebastian Stricker um 07:43:00 PM am 05/28/2009 | - Website - |
jetzt ist aber die #tmp deutlich lesbarer
Werde es mal versuchen....
Gruß JJR
P.S.: Auf das Ergebnis von Oracle bin ich gespannt!
P.P.S.: Hast Du es schon mit einem inner join versucht, dort müßte die Bedingung o1.created < o2.created doch auch funktionieren....!
P.P.S.: Wieso sollten dann da doppelte drinnen sein?!
Erstellt von Joerg Reck um 09:22:38 PM am 05/28/2009 | - Website - |
last solution is the fastest solution auch bei 60000 Datensätzen
Gruß JJR
Erstellt von Joerg Reck um 09:32:58 PM am 05/28/2009 | - Website - |
die Oracle Ergebnisse standen ja noch aus. Leider ist meine Umgebung nicht so geeignet dafür (VM zu schwach), ich konnte die Tests nicht so 1:1 fahren wie auf MSSQL. Aber folgendes ist mir an einem kleineren Beispiel aufgefallen: deine Lösung mit der temporären Tabelle scheint unter Oracle im ersten Anlauf die beste Variante zu sein, deswegen hier mal die Oracle-konforme Variante 1 mit temp. Tabelle:
-- einmalig zum Anlegen der temp. Tabelle:
create global temporary table pg_order_temp on commit delete rows
as select r1.o_created, r1.o_created as next_date from pg_order r1
where 0=1;
-- die eigentliche Abfrage:
insert into pg_order_temp select r1.o_created,min(r2.o_created) as next_date from PG_ORDER r1, PG_ORDER r2
where r1.o_created < r2.o_created group by r1.o_created;
select * from pg_order_temp where o_created + 7 < next_date;
-- falls die temp. Tabelle irgendwann doch mal nicht mehr benötigt wird:
drop table pg_order_temp;
Richtige Vergleichszahlen hätten mich auch brennend interessiert, ich kann mir z.B. gut vorstellen dass wenn die Tabellenstatistiken einmal eingefahren sind, auch unter Oracle die Variante mit dem Subselect ganz gut abschneidet. Und das exists habe ich auch noch nicht ganz abgeschrieben... Aber da brauch ich erstmal einen potenteren Host für meine VM... muss gleich mal beim Hardwareversand vorbeisurfen
Viele Grüße und noch ein schönes Pfingstfest,
Sebastian
Erstellt von Sebastian Stricker um 03:01:39 PM am 05/31/2009 | - Website - |
Beim Ausprobieren auf Oracle hab ich im Explain-Plan gesehen, dass das Group-By die meiste Zeit der bislang besten Variante mit Subselect verschlingt und dies durch die Auswertung des WHERE-Ausdruckes zustandekommt --> ist aber dort gar nicht für jeden Datensatz notwendig weil anschließend eh nach O_CREATED gruppiert wird --> also aus dem WHERE ein HAVING gemacht - besser geht's jetzt glaub ich wirklich nicht mehr:
select o1.o_created as 'start', (select min(o2.o_created) from PG_ORDER o2 where o2.o_created > o1.o_created) as 'end'
from pg_order o1
group by o1.o_created
having o1.o_created+7 < (select min(o2.o_created) from PG_ORDER o2 where o2.o_created > o1.o_created)
Das läuft sogar auf meiner kleinen VM in Oracle richtig schnell
Viele Grüße,
Sebastian
Erstellt von Sebastian Stricker um 03:45:02 PM am 05/31/2009 | - Website - |
Gruß JJR
So sah der doch beim letzten Mal aus
{ Link }
Erstellt von Joerg Reck um 07:46:34 PM am 05/31/2009 | - Website - |