Rückgabe wo es nichts gibt...

Nersgatt · Verfasst: Do 15.08.13 14:47

... oder so ähnlich.

Hier das Problem, das ich in der SB angedeutet habe.

Das ganze spielt sich ab unter Firebird 2.5.

Man stelle sich folgendes Konstrukt vor:

markieren

Quelltext

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Tabelle PERSONEN
ID | NACHNAME
-------------
1  | Meier
2  | Müller
3  | Schulze

Tabelle BUCHUNGEN
ID | PERSONID | DATUM      | WERT
-----------------------------------
1  | 1        | 01.08.2013 | 47,11
2  | 1        | 01.08.2013 |  8,15
3  | 3        | 03.08.2013 | 12,34
4  | 3        | 05.08.2013 | 58,65
5  | 3        | 05.08.2013 | 99,99
6  | 3        | 01.08.2013 | 88,33

Jetzt möchte ich eine Abfrage mit Datum von - bis als Eingangsparameter.

Raus kommen soll bei den Eingangsparametern 01.08.2013 - 05.08.2013 diese Tabelle:

markieren

Quelltext

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

PERSONID | DATUM      | SUMME(Wert)
------------------------------------------
1        | 01.08.2013 | 55,26
1        | 02.08.2013 | NULL
1        | 03.08.2013 | NULL
1        | 04.08.2013 | NULL
1        | 05.08.2013 | NULL
3        | 01.08.2013 | 88,33
3        | 02.08.2013 | NULL
3        | 03.08.2013 | 12,34
3        | 04.08.2013 | NULL
3        | 05.08.2013 | 158,64

Dabei kommen noch einige WHERE-Klauseln hinzu, nicht nur das Datum. Es wird sowohl auf PERSONEN, als auch auf BUCHUNGEN gefiltert. Allerdings sollte als erstes BUCHUNGEN gefiltert werden, da hier dann die Selektivität am stärksten ist.

Nun hab ich mir gedacht, ich mach mir erst mal ne Datumsliste, wo ich jeden Tag drin hab, den ich brauche. Also alles kein Hexenwerk, auf die Schnelle zusammengeschustert:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

create or alter procedure DATE_TABLE (
    DATUMVON DATUM,
    DATUMBIS DATUM)
returns (
    DATUM DATUM)
as
begin
  IF (:DATUMVON > :DATUMBIS) THEN
    EXIT;

  DATUM = :DATUMVON;
  WHILE (DATUM <= :DATUMBIS) DO
  begin
    datum = DATUM;
    suspend;

    DATUM = DATUM + 1;
  end
end

Die Idee war, dies meiner Abfrage hinzuzujoinenen, damit die fehlenden Tage aufgefüllt werden.
Irgendwie so:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:

SELECT 
  SUM(WERT) 
  dt.datum,
  personen.nachname
FROM PERSONEN 
LEFT OUTER JOIN DATE_TABLE(:DATUMVON, :DATUMBIS) dt WHERE (1=1)
LEFT OUTER JOIN BUCHUNGEN ON (BUCHUNGEN.PERSONID = PERSONEN.ID)

Aber irgendwie hab ich mich da doch verrannt.

Ich könnte natürlich meine PROCEDURE oben soweit aufbohren, dass sie mir auch die Summe und die PersonID zurückgibt. Allerdings würde das die Datenbank arg mit Anfragen bombadieren. Dies würde für jeden Tag * jeden Person eine Abfrage generieren. Und sowohl das Abfragedatum (von-bis) kann recht groß werden (auch mal übers ganze Jahr) als auch die PERSONEN-Tabelle (hier muss ich mit > 10.000 Datensätzen rechnen). Und da weiß ich ja noch nicht mal, ob ich die Person überhaupt in der Ergebnismenge haben will (siehe oben, Müller ist nicht der Ergebnismenge, weil er keine Buchungen hat). Daher hab ich so rum Angst um meine Performance.

Edit: Korrektur der Ergebnismenge

_________________
Gruß, Jens
Zuerst ignorieren sie dich, dann lachen sie über dich, dann bekämpfen sie dich und dann gewinnst du. (Mahatma Gandhi)

Ralf Jansen · Verfasst: Do 15.08.13 15:14

Warum gibt es für Person 3 am 05.08. 2 Datensätze im Ergebnis?

Martok · Verfasst: Do 15.08.13 15:16

Hey, das ist wirklich was spannedes :lol:

So wirklich bekomm ich das auch nicht hin. Ich hab mal die Generator-Procedure in eine Tabelle ausgepackt (ich bin zu doof das hier zu übersetzen), dann funktioniert mit MySQL:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

(SELECT
  dt.DATUM,
  personen.id,
  personen.name,
  SUM(WERT)
FROM DATE_TABLE dt
JOIN BUCHUNGEN ON (BUCHUNGEN.DATUM=dt.DATUM)
LEFT JOIN PERSONEN ON (BUCHUNGEN.PERSON = PERSONEN.ID)
WHERE (1=1)
group by dt.DATUM, personen.ID)

UNION

(select
  dt.DATUM,
  personen.id,
  personen.name,
  NULL
FROM DATE_TABLE dt, personen
WHERE not exists (select id from BUCHUNGEN where (BUCHUNGEN.DATUM=dt.DATUM) and (BUCHUNGEN.PERSON = PERSONEN.ID))
)
order by id, datum

Mit UNION ist Müller komplett drin, ohne erscheinen nur die Tage, an denen die Person irgendwas getan hat. Beides nicht das was du willst, aber vielleicht hilfts ja irgendwie :nixweiss:

Man könnte noch ein Select from ... where exists (buchung) ganz außen drumrumpacken und Müller wieder rausfiltern

Ralf Jansen hat folgendes geschrieben :

Warum gibt es für Person 3 am 05.08. 2 Datensätze im Ergebnis?

Tippfehler im Beispiel hab ich mal vermutet.

€:/ Wie oft wird das ausgeführt? Wenn eine Abfrage mal ne Sekunde dauert ist das ja nicht sooo das Problem. Wenn das die häufigste im Programm ist schon eher

_________________
"The phoenix's price isn't inevitable. It's not part of some deep balance built into the universe. It's just the parts of the game where you haven't figured out yet how to cheat."

Nersgatt · Verfasst: Do 15.08.13 15:27

Ralf Jansen hat folgendes geschrieben :

Warum gibt es für Person 3 am 05.08. 2 Datensätze im Ergebnis?

Tippfehler, hab ich im ersten Beitrag korrigiert. Nicht mal manuell bekomme ich das hin...

@Martok: Exists verwende ich nur in Ausnahmefällen und dann auch nur, wenn ich mir das gut überlegt hab. Denn das erzeugt ja noch mehr Arbeit auf dem Server.
Das ganze ist eine Auswertung, die vermutlich nicht so oft benutzt wird. Trotzdem hab ich den Anspruch, dass das so flott wie möglich läuft.
Mit dem Union hast Du mich auf eine Idee gebracht, dafür erst mal danke.

_________________
Gruß, Jens
Zuerst ignorieren sie dich, dann lachen sie über dich, dann bekämpfen sie dich und dann gewinnst du. (Mahatma Gandhi)

Ralf Jansen · Verfasst: Do 15.08.13 16:19

Warum joinst du überhaupt Personen dazu wenn dich keine Info aus Personen interessiert?
Ich würde mir die distinct PersonID aus Buchungen nehmen und damit joinen.

Beispiel aus dem SQL server (hab gerade kein Firebird parat)

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

 DECLARE @FirstDay as DateTime = '2013-01-08';
 DECLARE @LastDay as DateTime = '2013-05-08';
  
 WITH DATES AS (
  SELECT @FirstDay as [date]
  UNION ALL
  SELECT DATEADD(Day, 1, [date])
    FROM DATES
   WHERE DATEADD(Day, 1, [date]) <= @LastDay
)

select p.ID, d.[date], sum(b.Wert)
  from Dates d
          left join (select distinct(PersonID) as ID from Buchungen where Datum between @FirstDay and @LastDay) p on 1=1
          left join Buchungen b on p.ID = b.PersonID and d.[date] = b.Datum 
group by p.ID, d.[date]
order by 1, 2

WasWeißDennIch · Beiträge: 653 Erhaltene Danke: 160

Und woher sollen die Nachnamen kommen, wenn nicht aus Personen?

Ralf Jansen · Verfasst: Do 15.08.13 16:36

In seinem Ergebnis steht nur die PersonID und die gibts in Buchungen.

Nersgatt · Verfasst: Do 15.08.13 17:32

Ralf Jansen hat folgendes geschrieben :

In seinem Ergebnis steht nur die PersonID und die gibts in Buchungen.

Zum einen brauche ich auch Daten aus der Personentabelle, wie die Namen (ich hab sie der Einfachheit halber im Beispiel weg gelassen), zum anderen muss ich auch auf Felder in PERSONEN filtern.
Vielen Dank auch für Dein Beispiel, da werde ich mich reindenken.

_________________
Gruß, Jens
Zuerst ignorieren sie dich, dann lachen sie über dich, dann bekämpfen sie dich und dann gewinnst du. (Mahatma Gandhi)

Nersgatt · Verfasst: Do 15.08.13 18:23

Ich hab mich jetzt für das Konzept von Ralf entschieden. Es macht genau, was ich brauche und läuft auch mit großen Datenbeständen noch performant. Nochmals vielen Dank dafür.

_________________
Gruß, Jens
Zuerst ignorieren sie dich, dann lachen sie über dich, dann bekämpfen sie dich und dann gewinnst du. (Mahatma Gandhi)

Blup · Beiträge: 174 Erhaltene Danke: 43

Für optimale Geschwindigkeit sollte über PersonenID und Datum ein gemeinsamer Index angelegt sein.
Vieleicht hilft das beim Optimieren weiter:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:

create procedure AW_BUCHUNGEN(
    DATUMVON date,
    DATUMBIS date)
returns (
    PERSONENID integer,
    NACHNAME varchar(80),
    DATUM date,
    WERT numeric(15,2))
as
declare variable P integer;
declare variable D date;
declare variable w numeric(15,2);
begin
  personenid = null;
  wert       = null;
  for select personenid, datum, sum(wert)
  from       buchungen
  group by   personenid, datum
  into       :p, :d, :w
  do begin
    /* vorherige Person bis zum Ende des Zeitraums auffüllen */ 
    if (personenid <> p) then
    begin
      while (datum <= datumbis) do
      begin
        suspend;
        datum = datum + 1;
      end
    end
    /* neue aktuelle Person */
    if ((personenid is null) or (personenid <> p)) then
    begin
      personenid = p;
      datum      = datumvon;
      /* hier zusätzliche Stammdaten zur Person lesen */
      select nachname from personen where id = :personenid into :nachname;
    end
    /* Lücke vor dem Datum auffüllen */
    while (datum < d) do
    begin
      suspend;
      datum = datum + 1;
    end
    /* Wert zum Datum */
    wert = w;
    suspend;
    /* Anfangsdatum für mögliche nächste Lücke */
    datum = datum + 1;
    wert = null;
  end
  /* letzte Person bis zum Ende des Zeitraums auffüllen */
  if (personenid is not null) then
  begin
    while (datum <= datumbis) do
    begin
      suspend;
      datum = datum + 1;
    end
  end
end

Eventuell ist es aber sinnvoller die Anzeigedaten erst auf dem Client zu generieren.

	Mitgliederliste
	Gruppen
	Das Team
	Richtlinien
	Synonyme