SQL-Abfrage - Ausgabe einer Summe zu verschiedenen Zeiträumen

[Stefan Einspender]

Hallo ForaumsleserInnen,

also ich habe eine Tabelle mit Namen, wo verschiedene Personen drinstehen:

name
Heike
Igor
Tom

In einer andere Tabelle tragen die Leute einmal täglich ein, wieviel sie
eingenommen haben, für jeden Nutzer und Tag gibt es max. einen Eintrag:

datum        name    wert
2008-06-29   Heike   5
2008-06-30   Heike   3
2008-07-01   Tom     2
2008-07-03   Igor    10
2008-07-03   Tom     7
2008-07-04   Igor    1

Jetzt will ich ausgeben, wieviel die Nutzer insgesamt eingenommen haben,
dabei soll vorgestern, gestern und heute angezeigt werden. Die Liste der
Tage kann ich mit PHP erzeugen oder mit SQL, falls es sinnvoll ist. Das
Wunschergebnis sieht dann so aus:

name    2008-07-02   2008-07-03   2008-07-04
Heike   8            8            8
Igor    0            10           11
Tom     2            9            9

Die Kopzeile ist nur zum Verständnis, die braucht natürlich nicht mit aus-
gegeben werden. Wie kann ich dieses Ergebnis möglichst effizient mit einer
SQL-Abfrage bekommen?

SELECT name, (SELECT SUM(wert) FROM einnahmen WHERE datum<='2008-07-02' ...), (SELECT SUM(wert) FROM einnahmen WHERE datum<='2008-07-03' ...), (SELECT SUM(wert) FROM einnahmen WHERE datum<='2008-07-04' ...) FROM namen ...

wäre möglich, aber es geht am Ende um eine sehr viele Daten und da will ich
die Vielzahl von Subselects gern vermeiden. Es gibt 200 Namen und insgesamt
11 Tage, die abgefragt werden sollen. Auch gibt es eine Tabelle ausgaben,
die in der gleichen SQL-Abfrage mit abgefragt werden soll (auch zu allen
elf Tagen). Und dann noch einige Infos mehr aus anderen Datenbanktabellen.

Hat jemand für das obige Problem eine Idee?

Viele Grüße,
Stefan

[Cheatah]

Hi,

name    2008-07-02   2008-07-03   2008-07-04
Heike   8            8            8
Igor    0            10           11
Tom     2            9            9

Die Kopzeile ist nur zum Verständnis, die braucht natürlich nicht mit aus-
gegeben werden.

unabhängig davon sind hier aber die Spalten abhängig von den Datenbankinhalten. Du kannst kein SQL-Statement formulieren, wenn Du die zu selektierenden Spalten nicht kennst; ergo benötigst Du entweder zwei Statements, um zunächst die Spalten zu ermitteln ...

... oder Du ermittelst die Daten in anderen Dimensionen, als Du sie ausgeben willst. Ein Statement mit den Ergebnisspalten Datum, Name und Anzahl kann bei günstiger Sortierung verhältnismäßig leicht zu dieser Ausgabe umgeformt werden.

Cheatah

[Stefan Einspender]

Hallo Cheatah,

name    2008-07-02   2008-07-03   2008-07-04
Heike   8            8            8
Igor    0            10           11
Tom     2            9            9

Die Kopzeile ist nur zum Verständnis, die braucht natürlich nicht mit aus-
gegeben werden.

unabhängig davon sind hier aber die Spalten abhängig von den Datenbankinhalten. Du kannst kein SQL-Statement formulieren, wenn Du die zu selektierenden Spalten nicht kennst; ergo benötigst Du entweder zwei Statements, um zunächst die Spalten zu ermitteln ...

naja, das Ergebnis der Spalte name ist ja klar (alle Einträge in der
Tabelle namen) und die drei Tagen sind einfach die letzten drei, ab-
steigend von heute. Sowas kann ich ja mit PHP zurückrechnen. Damit
sind die Spalten bekannt, bevor ich das SQL starte.

... oder Du ermittelst die Daten in anderen Dimensionen, als Du sie ausgeben willst. Ein Statement mit den Ergebnisspalten Datum, Name und Anzahl kann bei günstiger Sortierung verhältnismäßig leicht zu dieser Ausgabe umgeformt werden.

Obiges Ergebnis soll später tabellarisch dargestellt werden, wobei für
jeden Namen und jeden Tag etwas ausgegeben werden muß. Wenn da jetzt der
Eintrag für Igor am 2008-07-02 fehlt, läuft meine while-Schleife weiter
und es wird an dieser Stelle der Wert für 2008-07-03 ausgegeben, was ich
ja nicht will. Außerdem gibt es auch die Besonderheit, dass ich zum Teil
nur jeden zweiten oder dritten Tag ausgebe. Wenn ich da die drei von Dir
genannten Spalten habe, woher soll ich wissen, ob der Fehltag absichtlich
nicht enthalten ist (weil dieser nicht ausgegeben werden soll) oder ob es
da keinen Treffer gibt?!

Viele Grüße,
Stefan

[Cheatah]

Hi,

naja, das Ergebnis der Spalte name ist ja klar (alle Einträge in der
Tabelle namen) und die drei Tagen sind einfach die letzten drei, ab-
steigend von heute. Sowas kann ich ja mit PHP zurückrechnen. Damit
sind die Spalten bekannt, bevor ich das SQL starte.

ja. Bei dieser Variante wirst Du ein Statement-Monster erstellen müssen, wie Du es im Ursprungsposting angedeutet hast.

... oder Du ermittelst die Daten in anderen Dimensionen, als Du sie ausgeben willst. Ein Statement mit den Ergebnisspalten Datum, Name und Anzahl kann bei günstiger Sortierung verhältnismäßig leicht zu dieser Ausgabe umgeformt werden.

Obiges Ergebnis soll später tabellarisch dargestellt werden, wobei für
jeden Namen und jeden Tag etwas ausgegeben werden muß. Wenn da jetzt der
Eintrag für Igor am 2008-07-02 fehlt, läuft meine while-Schleife weiter
und es wird an dieser Stelle der Wert für 2008-07-03 ausgegeben, was ich
ja nicht will.

Deswegen sagte ich, die Ausgabe sei _verhältnismäßig_ einfach. Wenn Du das Resultset zunächst in ein günstiges Datenmodell Deiner Programmiersprache überführst, wird es jedoch trivial.

Außerdem gibt es auch die Besonderheit, dass ich zum Teil
nur jeden zweiten oder dritten Tag ausgebe. Wenn ich da die drei von Dir
genannten Spalten habe, woher soll ich wissen, ob der Fehltag absichtlich
nicht enthalten ist (weil dieser nicht ausgegeben werden soll) oder ob es
da keinen Treffer gibt?!

Keine Ahnung. Die Logik, wann das eine und wann das andere der Fall sein soll, musst Du kennen. Nutze dieses Wissen in der Ausgabe.

Cheatah

[Matze]

Hallo!
Vom PostgreSQL habe ich zwar keine Ahnung, aber ich habe dir mal  exemplarisch was für den MSSQL-Server geschrieben. (TABELLE ist der Name der Eingangstabelle!)

Ich habe 2 Lösungen. Die erste geht direkt über 2 SELECT´s und ist evtl. sogar übernehmbar (sollte dann aber in eine Sicht etc. rein...):
---------ANFANG
select 'Name / Datum' as 'Name',
 CONVERT(char(10), DATEADD(dd, - 2, GETDATE()), 104) as Vorgestern,
 CONVERT(char(10), DATEADD(dd, - 1, GETDATE()), 104) as Gestern,
 CONVERT(char(10), DATEADD(dd, - 0, GETDATE()), 104) as Heute
union all
Select distinct [Name],
 (Select case when sum(Wert) is null then '0' else convert(varchar(20),sum(Wert)) end as Wert from  TABELLE  where [Name]=[DatenbasisNamen].[Name] and CONVERT(char(10), Datum, 104)=CONVERT(char(10), DATEADD(dd, - 2, GETDATE()), 104)),
 (Select case when sum(Wert) is null then '0' else convert(varchar(20),sum(Wert)) end as Wert from  TABELLE  where [Name]=[DatenbasisNamen].[Name] and CONVERT(char(10), Datum, 104)=CONVERT(char(10), DATEADD(dd, - 1, GETDATE()), 104)),
 (Select case when sum(Wert) is null then '0' else convert(varchar(20),sum(Wert)) end as Wert from  TABELLE  where [Name]=[DatenbasisNamen].[Name] and CONVERT(char(10), Datum, 104)=CONVERT(char(10), DATEADD(dd, - 0, GETDATE()), 104))
from TABELLE as [DatenbasisNamen]
---------ENDE
Die 2. geht über eine Funktion, ist also sehr wahrscheinlich nicht so übertragbar, gibt aber vielleicht einen Ansatz.
---------ANFANG
CREATE FUNCTION Auswertung()
RETURNS @retContactInformation TABLE([Name] varchar(255) not null,[Vorgestern] varchar(255) not null, [Gestern] varchar(255) not null,[Heute] varchar(255) not null)
AS
BEGIN

Insert into @retContactInformation
select 'Name / Datum',
  CONVERT(char(10), DATEADD(dd, - 2, GETDATE()), 104),
  CONVERT(char(10), DATEADD(dd, - 1, GETDATE()), 104),
  CONVERT(char(10), DATEADD(dd, - 0, GETDATE()), 104)
Insert into @retContactInformation  Select distinct [Name],
 (Select case when sum(Wert) is null then 0 else sum(Wert) end as Wert from  TABELLE  where [Name]=[DatenbasisNamen].[Name] and CONVERT(char(10), Datum, 104)=CONVERT(char(10), DATEADD(dd, - 2, GETDATE()), 104)),
 (Select case when sum(Wert) is null then 0 else sum(Wert) end as Wert from  TABELLE  where [Name]=[DatenbasisNamen].[Name] and CONVERT(char(10), Datum, 104)=CONVERT(char(10), DATEADD(dd, - 1, GETDATE()), 104)),
 (Select case when sum(Wert) is null then 0 else sum(Wert) end as Wert from  TABELLE  where [Name]=[DatenbasisNamen].[Name] and CONVERT(char(10), Datum, 104)=CONVERT(char(10), DATEADD(dd, - 0, GETDATE()), 104))
from TABELLE as [DatenbasisNamen]
RETURN
END

die dan per

SELECT     Name, Vorgestern, Gestern, Heute
FROM         dbo.Auswertung()

abgefragt werden kann.
---------ANFANG

Das Resultat ist in beiden Fällen folgendes:

Name            Vorgestern      Gestern         Heute
----------------------------------------------------------
Name / Datum    03.07.2008      04.07.2008      05.07.2008
Heike           0.00            0.00            0.00
Igor            10.00           1.00            0.00
Tom             7.00            0.00            0.00

Vielleicht hilft dir das ja weiter.
Eine kompaktere Lösung fällt mir nicht ein, aber funktionieren tut sie ;-)

Gruß
Matze

[Vinzenz Mai]

Hallo Stefan,

datum        name    wert
2008-06-29   Heike   5
2008-06-30   Heike   3
2008-07-01   Tom     2
2008-07-03   Igor    10
2008-07-03   Tom     7
2008-07-04   Igor    1

Jetzt will ich ausgeben, wieviel die Nutzer insgesamt eingenommen haben,
dabei soll vorgestern, gestern und heute angezeigt werden. Die Liste der
Tage kann ich mit PHP erzeugen oder mit SQL, falls es sinnvoll ist. Das
Wunschergebnis sieht dann so aus:

name    2008-07-02   2008-07-03   2008-07-04
Heike   8            8            8
Igor    0            10           11
Tom     2            9            9

Hat jemand für das obige Problem eine Idee?

das ist eine typische Kreuztabelle. MS Access kann das schon seit Urzeiten, der MS SQL-Server seit der Version 2005, das prinzipielle Vorgehen bei DBMS, die das nicht können, habe ich vor ein paar Tagen beschrieben.

Freundliche Grüße

Vinzenz

[Stefan Einspender]

Hallo Vinzenz,

Wunschergebnis sieht dann so aus:

name    2008-07-02   2008-07-03   2008-07-04
Heike   8            8            8
Igor    0            10           11
Tom     2            9            9

Hat jemand für das obige Problem eine Idee?

das ist eine typische Kreuztabelle. MS Access kann das schon seit Urzeiten, der MS SQL-Server seit der Version 2005, das prinzipielle Vorgehen bei DBMS, die das nicht können, habe ich vor ein paar Tagen beschrieben.

gehe ich recht in der Annahme, dass dann für jeden Namen zu jedem Tag ein
Ergebnis ausgegeben wird? Also für Igor am 2008-07-02 zurückgeliefert wird?
Wäre wichtig, weil ich das Ergebnis in einer Schleife durchgehen möchte und
da müssen auch die Tage genannt werden, wo es eigentlich keinen Treffer in
der Tabelle einnahmen gibt. Und die Tabelle ausgaben soll ja auch abgefragt
werden, kann man da eventuell die CASE-Teilstücke "doppelt" nutzen oder muß
ich die dann alle doppelt in der SQL-Abfrage haben? Wären dann 22 Stück,
was ja nicht wenig ist. Die SQL-Abfrage wird damit sicher sehr lang, was mir
erstmal egal wäre (weil ja dynamisch zusammengesetzt), aber ist sowas wirk-
lich effizient?

Tragisch ist, dass ich momentan nichts testen kann, erst wieder am Montag
auf Arbeit. Und dort ist PostgreSQL im Einsatz.

Viele Grüße,
Stefan

[Vinzenz Mai]

Hallo

Hat jemand für das obige Problem eine Idee?

das ist eine typische Kreuztabelle. MS Access kann das schon seit Urzeiten, der MS SQL-Server seit der Version 2005, das prinzipielle Vorgehen bei DBMS, die das nicht können, habe ich vor ein paar Tagen beschrieben.

gehe ich recht in der Annahme, dass dann für jeden Namen zu jedem Tag ein
Ergebnis ausgegeben wird? Also für Igor am 2008-07-02 zurückgeliefert wird?

Ja.

Wäre wichtig, weil ich das Ergebnis in einer Schleife durchgehen möchte und
da müssen auch die Tage genannt werden, wo es eigentlich keinen Treffer in
der Tabelle einnahmen gibt. Und die Tabelle ausgaben soll ja auch abgefragt
werden, kann man da eventuell die CASE-Teilstücke "doppelt" nutzen oder muß
ich die dann alle doppelt in der SQL-Abfrage haben?

Sind das eigene Spalten in der Ausgabe? Wenn ja, dann musst Du sie doppelt haben.

was ja nicht wenig ist. Die SQL-Abfrage wird damit sicher sehr lang, was mir
erstmal egal wäre (weil ja dynamisch zusammengesetzt), aber ist sowas wirk-
lich effizient?

Messe es? Frage Dich, ob es überhaupt relevant ist.

Tragisch ist, dass ich momentan nichts testen kann, erst wieder am Montag
auf Arbeit. Und dort ist PostgreSQL im Einsatz.

Das ist eine gute Nachricht. PL/pgSQL ist recht leistungsfähig. Du könntest das Statement in einer Stored Procedure ähnlich wie in diesem Archivposting beschrieben zusammenbauen und ausführen.

Freundliche Grüße

Vinzenz

[Stefan Einspender]

Hallo Vinzenz,

werden, kann man da eventuell die CASE-Teilstücke "doppelt" nutzen oder muß
ich die dann alle doppelt in der SQL-Abfrage haben?

Sind das eigene Spalten in der Ausgabe? Wenn ja, dann musst Du sie doppelt haben.

ja, sind es. Also erstmal die Einnahmen bis zu diesem Tag, dann die Aus-
gaben, anschließend diese beiden Spalten für den nächsten Tag usw. ...

was ja nicht wenig ist. Die SQL-Abfrage wird damit sicher sehr lang, was mir
erstmal egal wäre (weil ja dynamisch zusammengesetzt), aber ist sowas wirk-
lich effizient?

Messe es? Frage Dich, ob es überhaupt relevant ist.

Stimmt, wenn ich erstmal die Abfrage generell zusammen habe, dann kann
man (1) sich ja ans Optimieren machen.

Das ist eine gute Nachricht. PL/pgSQL ist recht leistungsfähig. Du könntest das Statement in einer Stored Procedure ähnlich wie in diesem Archivposting beschrieben zusammenbauen und ausführen.

Werde ich mir mal durchlesen, auch wenn ich, im Gegensatz zu unserem
Admin (1) davon wirklich sehr, sehr wenig verstehe. Aber ist ja nicht
schlimm, ich will erstmal das Script ans Laufen bringen, Optimieren
kann er es ja dann.

(1) Der EDV-Admin meines Arbeitgebers ist Mitglied der PostgreSQL User
Group und versteht davon schon "etwas" mehr als ich ;-)
Nur ist er gerade paar Tage nicht da und da wollte ich es mal auf eigene
Faust versuchen. Nun, hinbekommen werde ich es sicher und wenn dann sein
Server qualmt, wird er sich schon Gedanken machen und mein Anfänger-SQL
entsprechend optimieren :-)

Viele Grüße,
Stefan