Lücken fehlender Monate füllen per SQL

Vasquez155 schrieb:
Andere Frage noch zu:
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())

Könnte ich mir das auch aus meiner DatumsTabelle generieren lassen? Dort ist alles lückenhaft angelegt auch mit Monat-Jahr in einer Spalte gepflegt, oder geht das gar nicht?
Zum Vergrößern anklicken....
Du kannst das auf viele Arten generieren, nur schneller oder einfacher wird es nicht unbedingt. Wenn du eine Lückenhafte Tabelle nimmst und nur den Inhalt der Lücken "auffüllst" ist das eher komplizierter als einfach eine Reihe zu generieren.

Als Basis für meine Reihe habe ich einfach alle Werte von Heute -2 Jahre bis Heute genommen. Da könnte man natürlich min() und max() der realen Werte nehmen.

Du musst aber auch irgendwo die Menge der Daten sinnvoll eingrenzen - Tage x Kunden x Länder.
 
Werbung:
Ah ok, verstehe.

Würde dann ein Select distinct auf den Monat_Jahr in der Datumstabelle reichen? Irgendwie hab ich das Gefühl, das würde nicht gehen. Das Problem ist, ich müsste das später in ein ETL einbauen bzw. benutzen. Daher würde ich halt gerne auf die vorhandene Datumstabelle gehen wollen.

----------------------------------------------------------------------------------------------
Eingrenzung:
Über die where condition kann ich es ja nicht, leider .. Hast du einen Tipp hierzu?

Vielleicht in der Kunden-Tabelle eine Spalte mit "Top-Kunden" generieren und hier eingrenzen? Oder wie würdest du hier vorgehen?
 
Es ist schwer das aus dem Zusammenhang gerissen zu beurteilen. Diese Abfrage kann ganz schön unperformant werden und ich kenne deine Datenmengen oder Systeme etc. alles nicht. Wieviel Laufzeit hast du derzeit? Wie groß ist das Resultat (im Sinne von Zeilen)? Welchem Zweck dient das ganze? Du tapezierst dir das ja nicht auf die Wand.

Grundsätzlich könntest du einfach am Ende zum GROUP BY ein HAVING packen oder das Ergebnis von diesem Query nochmal mit einem WHERE um Kunden ohne Umsatz filtern. Besser ist es natürlich immer schon früh zu filtern und möglichst gar nicht alle Daten zu verarbeiten aber das wird in diesem Fall den ganzen Join deutlich erweitern.

Vielleicht kannst du mal ein paar fiktive Beispieldatensätze für den Fall mit dem Kunden und dem Unterbrechnungszeitraum bauen und zeigen was raus kommen soll.
 
Also jetzt klappt es, allerdings wie oben beschrieben ... benötige ich Einschränkungen über eine Where - Condition oder ähnliches..

Ich beschreibe kurz, was das Ziel sein soll:

Betrachtungsweise soll am Schluss eine Prüfung über die Monate, wie lukrativ das Geschäft mit einem Kunde über die Jahre war, ob es Optimierungspotential gibt oder der Kunde so wenig Umsatz macht, ob es sich lohnt in weiter zu führen. So im groben mal ausgedrückt. Deswegen kann ich auch die Umsätze nicht rausfiltern mit 0 - Umsatz. Also die Abfrage dient dazu, dass ich jegliches miteinander vergleichen kann: Kunde, Land, Umsatz pro Monat ohne lücken.

Jetzt habe ich überlegt, wie ich eine Eingrenzung vornehmen könnte:
- Alle Kunden in Tabelle Kunde, deren Feld AKTIV mit 1 befüllt ist.
- Nur bestimmte Standorte, die einen Namen in der Auftragstabelle haben
- Alle Länder in der Tabelle Land, die aus Europa kommen..Feld EUR befüllt.

Kann ich das irgendwie umsetzen? Aktuell bekomme ich , über 3 Mio DS bei einer Laufzeit von 17 Min, als Ergebnis für jede Kombination, allerdings nur auf ein Jahr gesehen.
Wenn ich die Kunden und die Länder, wie oben einschränke, denke ich, kann ich auf 1 Mio DS kommen, mit Sicherheit, wenn nicht noch weniger.
 
Mal eine ganz andere Frage: Warum ist dir das Land wichtig und warum willst du die Umsätze nach Ländern aufteilen? Ich meine diese Dimension ist doch am Ende banane, wenn ein Kunde in einem Land Umsatz liefert aber in 29 anderen nicht ist er doch ein Kunde kategorisiert nach Umsatz, nicht nach Umsatz pro Land.

Das Problem ist hier schlicht die schiere Masse an Datenpunkten und die muss man möglichst am Anfang auf das wesentliche reduzieren. 17 Minuten Laufzeit sind eine Ewigkeit. Eventuell ist SQL gar kein optimales Mittel sondern eher sowas wie MDX.
plus-it.de

MDX, DAX, SQL - Die richtige Sprache für jedes Datenmodell - Professionelle Datenanalyse & Datenmanagement - plus-IT

Nachdem ich letztes Mal die technischen Grundlagen von multidimensionalen und tabellarischen Datenmodellen vorgestellt habe, möchte ich heute stärker auf die
plus-it.de plus-it.de

Auch weiß ich nicht wie du das visualisieren willst. Eventuell ist dein klassisches ERP dem gar nicht gewachsen. Auch hier gibt es Anbieter für ETL Tools die schöne Dinge gegen schön viel Geld bieten.

PS: Ich würde da auch weiter dran rum schrauben allerdings arbeite ich blind ohne Testdatensätze. Das Beste wäre es du gibst mal ein konstruiertes Beispiel vor und sagst dann genau wie du dir das Ergebnis wünscht.
 
Zuletzt bearbeitet:
@ukulele
Danke für deine Nachricht! :)

Genau, die Datenmenge ist aktuell mein Problem. Aber das richtige Ergebnis erhalte ich, dass freut mich schon sehr.

Bezüglich der Eingrenzung:
Ist es möglich, dass ich vorhinein, innerhalb des SQL schon eine Eingrenzung vornehmen kann, die folgendermaßen aussieht:

Natürlich sind in der Land-Tabelle alle Länder drinnen, was, wie du oben beschreibst, keinen Sinn macht. Da kommen am Schluss DS als Ergebnis, die keinen Umsatz vorweisen, weil es die Konstellation aus Kunde und Land nicht gibt. Allerdings hab ich in der Auftrags-Tabelle alle möglichen Konstellationen inkludiert. Und jetzt die Frage: Kann ich das eingrenzen, dass er mir nur die Konstellationen bildet, die es auch in der Auftragstabelle aus Kunde+Land gibt und die eben weglässt, wo es keine Kombi in der Auftragstabelle gibt, unabängig vom Umsatz?

Falls du mehr Infos benötigst, gib Bescheid, ich weiß nicht, wie ich das besser beschreiben oder darstellen soll.

Danke und Grüße
 
Das sollte sich leicht umsetzen lassen, wir machen einfach keinen CROSS JOIN mit land (spart sicher ne Menge Performance) sondern gruppieren im Anschluss nach Land - im idealfall nach dem Fremdschlüssel auf Land aus der Auftragstabelle.
Code: 
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, a.land_id,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
CROSS JOIN kunde k
left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
group by
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6)), kunde, a.land_id
Ansonsten macht man noch einen LEFT JOIN am Ende um Spalten aus der Tabelle Land zu holen.
 
Das habe ich jetzt so versucht, allerdings werden mir jetzt nicht mehr für die vorhandenen Kombinationen aus Kunde und Land ein DS pro Monat gebildet.

Da gibt es wieder die Lücken, weil wenn es die Kombination gibt, nur die angedruckt werden, die einen Umsatz in dem Monat haben. Wenn er 0 ist, gibt es diesen DS nicht, sondern dann wird in Land ein NULL eingetragen. Somit kann ich später keinen Vergleich auf den Vormonat machen, wenn ich darüber nachdenke.

Davor habe ich für jede Kombi, obwohl es keinen Umsatz gab, einen DS für jeden Monat erhalten und konnte später sagen:
Kunde + Land + Aktueller Monat > Kunde + Land + Aktueller Vormonat = i.O
....

Das kann ich jetzt nicht mehr, leider.

Kann ich das trotzdem irgendwie umsetzen? Weißt du wie ich meine? Die Performance ist natürlich jetzt schon wesentlich besser, aber das Ergebnis sollte anders aussehen.
 
@ukulele
Ich habe jetzt in den Cross joins filter eingebaut, die mir die Sachlage erheblich eingrenzen. Das ist super.

Könnte ich denn irgendwie an einer Stelle folgendes einbauen?:

Bilde mir nur die Konstellationen lückenlos, bei denen es mindestens einmal in den zwei Jahren einen Umsatz in irgendeinem Monat gab?

Dann würden doch keine DS gebildet, bei denen es in dem Zeitraum keinen Umsatz innerhalb der zwei Jahre gab, sehe ich das richtig?

Würde das irgendwie funktionieren? :)
 
Zum Thema Land wenn ein Umsatz existiert:
Das ist dann wieder Geschmackssache. Willst du eine Summe aller Umsätze und dann einen Drill down aus welchen Ländern sich das zusammen setzt oder willst du alle Umsätze auf alle Länder aufteilen die jemals einen Umsatz hatten:
Code: 
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, a.land_id,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
CROSS JOIN kunde k
CROSS JOIN ( SELECT DISTINCT auftrag.kunde_id,auftrag.land_id FROM auftrag ) l
left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
and a.land_id = l.land_id
group by
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6)), kunde, a.land_id

Zum Thema Filter nur Kunden, die mindestens einen Umsatz haben:
Geht natürlich, würde ich genauso möglichst früh filtern wollen. Das mit der Datumstabelle ist allerdings grütze, steht auch ein echtes Datum direkt in der Auftragstabelle?
Code: 
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, a.land_id,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
CROSS JOIN ( SELECT DISTINCT kunde.id,kunde.kunde FROM kunde INNER JOIN auftrag ON kunde.id = auftrag.kunde_id WHERE auftrag.datum >= dateadd(year,-2,getdate()) ) k
CROSS JOIN ( SELECT DISTINCT auftrag.kunde_id,auftrag.land_id FROM auftrag ) l
left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
and a.land_id = l.land_id
group by
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6)), kunde, a.land_id
Ansonsten muss in dem Select der gejoint wird auch noch ein Join auf die Datumstabelle mit rein.
 
Ok :) Danke. Ich probiere das mal aus.

Also mein Ziel ist es ..
--- Alle Kunden, aus Kunde, die es in der Auftragstabelle gibt,
--- Alle Länder, aus Länder, die es in der Auftragstabelle gibt,
--- Konstellation aus Kunde und Land, die es in der Auftragstabelle gibt
--- Lückenfrei pro Monat über die letzten zwei Jahre darzustellen
--- bei denen es mindestens in einem Monat in den zwei Jahren einen Umsatz gab in einem Monat gab

Das heißt, ich müsste im vorhinein folgende Filter, wie du gezeigt hast einbauen:

Hole mir alle Kunden und Länder aus der Kunden und Ländertabelle, die in der Auftragstabelle vorkommen!
Und erstelle mir dann eine lückenhafte Ansicht pro Monat über die letzten zwei Jahre, bei denen es in der Konstellation aus Kunde und Land mindestens einmal einen Umsatz in einem Monat gab.

Ohje, ich hoffe, ich hab es richtig formuliert.

In der Auftragstabelle gibt es einen Bezug zur Datums-Tabelle über eine ID, allerdings auf den Tag gesehen. Als Zusatz-Attribut ist der Monat und Jahr natürlich auch in der Datumstabelle.
 
@ukulele

Ich habe es tatsächlich hinbekommen. Wow, danke für euren Support!

Jetzt habe ich noch eine kurze Frage:
Ich habe ja jetzt eine Lückenlose Darstellung der Monate. Wie könnte ich denn nun sagen, im SQL, Vergleiche mir anhand der Konstellation aus Kunde und Land, wenn ich die letzten 6 Monate Umsatz habe, bist du ein guter Kunde, als Beispiel.
 
Du könntest in das Endergebnis noch eine Spalte einbauen die eine laufende Summe bildet, a la
Code: 
sum(umsatz) OVER (PARTITION BY kunde,land ORDER BY datum) AS laufende_summe
Soll irgendein Kundenstatus gewechselt werden verlgeichst du die laufende Summe mit deinem Schwellenwert per CASE.
Code: 
(CASE WHEN sum(umsatz) OVER (PARTITION BY kunde,land ORDER BY datum) > schwellenwert THEN 'toller Kunde' ELSE 'Kunde' END) AS kundenart
 
@ukulele,@dabadepdu

danke für deine Antwort.

Ich muss das wegen der Kundenart ein wenig differenzieren:

Ich brauch einen Vergleich zu Neu - und Bestandskunde.
- Ein Neukunde hat die letzten 24 Monate keinen Umsatz generiert, aber bsp. im aktuellen und die 24 folgenden Monate, jeden Monat einen Umsatz.
- Ein Bestandskunde besteht dann, wenn er nach dem er Neukunde geworden ist, 24 Monate lang Umsatz generiert hat.

Kann ich das mit der Partition By umsetzen? Oder habt ihr eine andere Idee?

Danke für eure Hilfe.
 
Werbung:
Ja das geht. Du kannst eine oder mehrere laufende Summen bilden und innerhalb von sum() mit CASE filtern. Etwa so:
Code: 
sum(CASE WHEN datum < dateadd(year,-2,getdate()) THEN NULL ELSE umsatz) OVER (PARTITION BY... ORDER BY...) AS umsatz_aelter_2jahre
 
Zurück
Oben