« Just dropped in my inbox :-) | Main| Wenn ihr mal nach Hause kommt und die Tochter weint :-( »

Na ja, da war was mit den doofen Datum(s)

10
Category
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!

Comments

Gravatar Image1 - Mal sehen ob ich die Aufgabe verstanden habe... Emoticon

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 Emoticon


Gravatar Image2 - Hallo Sebastian,

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

Gravatar Image3 - Hallo Jörg,

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 Emoticon

Grüße,
Sebastian

Gravatar Image4 - Hallo Sebastian,

die Sache mit dem Exists hat was, habe ich nicht bedacht nur eine Frage habe ich noch.....

group by wozu?

Gruß JJR

Gravatar Image5 - Hallo Sebastian,

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!

Gravatar Image6 - Hallo Jörg,

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 Emoticon

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

Gravatar Image7 - Hallo Sebastian,

jetzt ist aber die #tmp deutlich lesbarer Emoticon

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?!

Gravatar Image8 - Hallo Sebastian,

last solution is the fastest solution auch bei 60000 Datensätzen Emoticon

Gruß JJR

Gravatar Image9 - Hallo Jörg,

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 Emoticon

Viele Grüße und noch ein schönes Pfingstfest,
Sebastian

Gravatar Image10 - Einen hab ich doch noch Emoticon

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 Emoticon

Viele Grüße,
Sebastian

Gravatar Image11 - Hallo Sebsatian, jetzt sach bloss, das Teil was Du im Keller stehen hast ist zu klein?
Gruß JJR
So sah der doch beim letzten Mal aus Emoticon
{ Link }

Post A Comment

:-D:-o:-p:-x:-(:-):-\:angry::cool::cry::emb::grin::huh::laugh::lips::rolleyes:;-)

Amazon


Impressum

Firmenname: Peanuts-Soft
Straße Nummer: Biinger Strasse 8
PLZ Ort: 55263 Wackernheim
Telefon: +491772134526
E-Mail: joerg.reck @ peanuts-soft.de
Disclaimer: Peanuts-Soft übernimmt keine Garantie dafür, dass die auf dieser Website bereitgestellten Informationen vollständig, richtig und stets aktuell sind. Dies gilt auch für alle Links, auf die verwiesen wird. Peanuts-Soft ist für die Inhalte, auf die per Link verwiesen wird, nicht verantwortlich. Peanuts-Soft haftet nicht für konkrete, mittelbare und unmittelbare Schäden oder Schäden, die durch fehlende Nutzungsmöglichkeiten, Datenverluste oder entgangene Gewinne – sei es aufgrund der Nichteinhaltung vertraglicher Verpflichtungen, durch Fahrlässigkeit oder eine andere unerlaubte Handlung – im Zusammenhang mit der Nutzung von Dokumenten oder Informationen bzw. der Erbringung von Dienstleistungen entstehen, die auf dieser Web Site zugänglich sind.
Datenschutz: Inhalt und Gestaltung der Internetseiten sind urheberrechtlich geschützt. Eine Vervielfältigung der Seiten oder deren Inhalte bedarf der vorherigen schriftlichen Zustimmung von Peanuts-Soft.


Locations of visitors to this page

Powered By

Domino BlogSphere
Version 3.0.2