mysql Addieren von 2 Ergebnissmengen möglich?

[Franco]

Hallo,

ich habe 2 Abfragen:

  
SELECT FROM_UNIXTIME(tabelle_1_Timestamp, '%Y%|%m') AS ym, SUM(tabelle_1_Zeit) AS sum  
FROM tabelle_1_  
WHERE Bedingung = '1'  
GROUP BY ym DESC LIMIT 12  
  
SELECT FROM_UNIXTIME(tabelle_2_Timestamp, '%Y%|%m') AS ym, SUM(tabelle_2_Zeit) AS sum  
FROM tabelle_2_  
WHERE Bedingung = '1'  
GROUP BY ym DESC LIMIT 12  

tabelle_1_Zeit und tabelle_2_Zeit sind Spalten vom Typ decimal.

Ich würde gerne eine Möglichkeit schaffen, die monatlichen Summenwerte dieser beider Abfragen auch addiert auszugeben.

Wie macht man das?

Grüße, Franco

[Vinzenz Mai]

Hallo,

SELECT FROM_UNIXTIME(tabelle_1_Timestamp, '%Y%|%m') AS ym, SUM(tabelle_1_Zeit) AS sum
FROM tabelle_1_
WHERE Bedingung = '1'
GROUP BY ym DESC LIMIT 12

SELECT FROM_UNIXTIME(tabelle_2_Timestamp, '%Y%|%m') AS ym, SUM(tabelle_2_Zeit) AS sum
FROM tabelle_2_
WHERE Bedingung = '1'
GROUP BY ym DESC LIMIT 12

  
  

> Ich würde gerne eine Möglichkeit schaffen, die monatlichen Summenwerte dieser beider Abfragen auch addiert auszugeben.  
>   
> Wie macht man das?  
  
zum Beispiel mit einer UNION ALL der beiden Ergebnismengen und einem Subselect auf das Ergebnis der UNION.  
  
Noch ein paar Hinweise:  
  

> tabelle\_1\_Zeit und tabelle\_2\_Zeit sind Spalten vom Typ decimal.  
  
warum? Warum speicherst Du Integer-Timestamps statt DATETIME zu verwenden?  
Deine Abfrage gibt keinen Hinweis darauf, warum Du überhaupt zwei verschiedene Tabellen verwendest.  
  
Tabellennamen als Spaltenpräfixe zu verwenden halte ich persönlich für eine schlechte Idee. Es gibt qualifizierte Bezeichner.  
  
  
Freundliche Grüße  
  
Vinzenz

[Franco]

Hallo Vinzenz,

zum Beispiel mit einer UNION ALL der beiden Ergebnismengen und einem Subselect auf das Ergebnis der UNION.

Könntest Du da etwas genauer werden? Ich glaube, so verstehe ich das noch nicht.

warum? Warum speicherst Du Integer-Timestamps statt DATETIME zu verwenden?

Frage ich mich heute auch, aber das sind "Altlasten" von vor (gefühlten) 100 Jahren.

Deine Abfrage gibt keinen Hinweis darauf, warum Du überhaupt zwei verschiedene Tabellen verwendest.

Es geht um 2 verschiedene Werte. Z.B. könnte eine Tabelle die Zeit sein, die ich joggend verbracht habe, die andere könnte sich auf das Fitnessstudio beziehen beziehen.
Ich möchte mir die Zeiten kummuliert einzeln ausgeben lassen. Wenn ich aber wissen will, wieviel Stunden Sport ich insgesamt gemacht habe, wäre eine Addition ganz schön.

Tabellennamen als Spaltenpräfixe zu verwenden halte ich persönlich für eine schlechte Idee. Es gibt qualifizierte Bezeichner.

Gefiehl mir selber vorhin nicht.
Nur zur Klarstellung, die Daten befinden sich in der selben db, aber einmal ist es die Tabelle Kino und einmal Studio. Die Spalten heißen jeweils einfach nur Zeit bzw. timestamp.

Grüße, Franco

[Franco]

Nur zur Klarstellung, die Daten befinden sich in der selben db, aber einmal ist es die Tabelle Kino und einmal Studio.

Ich meine natürlich: Jogging und Studio.

Grüße, Franco

[ChrisB]

Hi,

Deine Abfrage gibt keinen Hinweis darauf, warum Du überhaupt zwei verschiedene Tabellen verwendest.

Es geht um 2 verschiedene Werte. Z.B. könnte eine Tabelle die Zeit sein, die ich joggend verbracht habe, die andere könnte sich auf das Fitnessstudio beziehen beziehen.

Spätestens damit wird das Datenmodell höchstvermutlich dringendst überarbeitungsbedürftig.

Nur zur Klarstellung, die Daten befinden sich in der selben db, aber einmal ist es die Tabelle Kino und einmal Studio.

Wenn die Datensätze gleich aufgebaut sind, dann gehören sie in *eine* Tabelle.
„Kino“ oder „Studio“ ist dann lediglich ein Merkmal, das in einer weiteren Spalte abgelegt wird.

MfG ChrisB

[Franco]

Spätestens damit wird das Datenmodell höchstvermutlich dringendst überarbeitungsbedürftig.

Wenn die Datensätze gleich aufgebaut sind, dann gehören sie in *eine* Tabelle.
„Kino“ oder „Studio“ ist dann lediglich ein Merkmal, das in einer weiteren Spalte abgelegt wird.

Siehe diesen Beitrag, da erkläre ich das Datenmodell.

mfg, Franco

[Vinzenz Mai]

Hallo,

zum Beispiel mit einer UNION ALL der beiden Ergebnismengen und einem Subselect auf das Ergebnis der UNION.

Könntest Du da etwas genauer werden? Ich glaube, so verstehe ich das noch nicht.

ausführlicher war ich in </archiv/2009/10/t192108/#m1281663>.

Freundliche Grüße

Vinzenz

[Franco]

Hallo Vinzenz,

ausführlicher war ich in </archiv/2009/10/t192108/#m1281663>.

Ich habe versucht, das umzusetzen, aber es klappt noch nichnt so recht.

  
SELECT  
gesamt.ym,  
SUM(gesamt.zeit)  
FROM (  
SELECT FROM_UNIXTIME(JoggingTimestamp, '%Y%|%m') AS ym, SUM(JoggingZeit) AS zeit  
FROM Jogging  
WHERE UserId ='1'  
  
UNION ALL  
  
SELECT FROM_UNIXTIME(KinoTimestamp, '%Y%|%m') AS ym, SUM(KinoZeit) AS zeit  
FROM Kino  
WHERE UserID='1'  
  
) gesamt  
GROUP BY gesamt.ym DESC LIMIT 12  

Nochmal die Problembeschreibung:

Der User mit der ID 1 geht ab und an joggen und ab und an ins Kino.

In der Tabelle "Jogging" stehen als die Zeit, aber auch Strecke, Wetter, Puls, usw.
In der Tabelle Kino stehen neben der Zeit noch Film, Schauspieler, Bewertung, usw.

Als Ergebniss möchte ich eine monatliche Auflistung der Zeit, die der User im letzen Jahr im Kino oder beim Jogging verbracht hat.

Nur die im Kino verbrachte Zeit frage ich z.B. so ab:

  
SELECT FROM_UNIXTIME(KinoTimestamp, '%Y%|%m') AS ym, SUM(KinoZeit) AS zeit  
FROM Kino  
WHERE UserID='1'  
GROUP BY ym DESC LIMIT 12  

Was muss ich an der obigen Gesamtabfrage ändern, bzw. was ist falsch an ihr?

Grüße, Franco

[Vinzenz Mai]

Hallo,

ausführlicher war ich in </archiv/2009/10/t192108/#m1281663>.

Ich habe versucht, das umzusetzen, aber es klappt noch nichnt so recht.

SELECT
gesamt.ym,
SUM(gesamt.zeit)
FROM (
SELECT FROM_UNIXTIME(JoggingTimestamp, '%Y%|%m') AS ym, SUM(JoggingZeit) AS zeit
FROM Jogging
WHERE UserId ='1'

wo ist die GROUP-BY-Klausel?

UNION ALL

SELECT FROM_UNIXTIME(KinoTimestamp, '%Y%|%m') AS ym, SUM(KinoZeit) AS zeit
FROM Kino
WHERE UserID='1'

wo ist die GROUP-BY-Klausel?

) gesamt
GROUP BY gesamt.ym DESC LIMIT 12

  

> Nur die im Kino verbrachte Zeit frage ich z.B. so ab:  
  

> ~~~sql
  

> SELECT FROM_UNIXTIME(KinoTimestamp, '%Y%|%m') AS ym, SUM(KinoZeit) AS zeit  
> FROM Kino  
> WHERE UserID='1'  
> GROUP BY ym DESC LIMIT 12  
> 

Da ist sie da. Warum entfernst Du diese in Deinen beiden Abfragen, die Du per UNION verknüpfst? Abgesehen davon geht Dir diese Abfrage bei keinem mir bekannten DBMS außer MySQL durch.

Gehe schrittweise vor:

a) Du hast Deine Einzelabfragen, die Dir Kino und Joggen liefern.
b) Fasse die beiden Ergebnisse in *einer* Ergebnismenge zusammen: UNION
c) Summiere und gruppiere über das Ergebnis aus b)

Freundliche Grüße

Vinzenz

[Franco]

wo ist die GROUP-BY-Klausel?
wo ist die GROUP-BY-Klausel?
Da ist sie da. Warum entfernst Du diese in Deinen beiden Abfragen, die Du per UNION verknüpfst?

Das hatte ich ursprünglich nicht, aber

  
SELECT  
gesamt.ym,  
SUM(gesamt.zeit)  
FROM (  
SELECT FROM_UNIXTIME(JoggingTimestamp, '%Y%|%m') AS ym, SUM(JoggingZeit) AS zeit  
FROM Jogging  
WHERE UserId ='1'  
GROUP BY ym DESC LIMIT 12  
  
UNION ALL  
  
SELECT FROM_UNIXTIME(KinoTimestamp, '%Y%|%m') AS ym, SUM(KinoZeit) AS zeit  
FROM Kino  
WHERE UserID='1'  
GROUP BY ym DESC LIMIT 12  
  
) gesamt  
GROUP BY gesamt.ym DESC LIMIT 12  

liefert dasselbe Ergebniss wie die alleinige Abfrage nach der beim Jogging verbrachten Zeit. Die Kinozeit wird einfach nicht aufaddiert.

Gehe schrittweise vor:

a) Du hast Deine Einzelabfragen, die Dir Kino und Joggen liefern.

Ja.

b) Fasse die beiden Ergebnisse in *einer* Ergebnismenge zusammen: UNION

So wie oben gemacht??

c) Summiere und gruppiere über das Ergebnis aus b)

Da liegt wohl noch ein Fehler bei meiner Abfrage vor?

Ich les nun schon seit Stunden alles über union und Konsorten, aber der Groschen will nicht fallen.

Grüße, Franco

[Vinzenz Mai]

Hallo,

wo ist die GROUP-BY-Klausel?
wo ist die GROUP-BY-Klausel?
Da ist sie da. Warum entfernst Du diese in Deinen beiden Abfragen, die Du per UNION verknüpfst?

Das hatte ich ursprünglich nicht, aber

<seufz>
Ich zitiere aus dem Handbuch:

To apply ORDER BY or LIMIT  to an individual SELECT,
    place the clause inside the parentheses that enclose the SELECT:
</seufz>

SELECT
gesamt.ym,
SUM(gesamt.zeit)
FROM (

(

SELECT FROM_UNIXTIME(JoggingTimestamp, '%Y%|%m') AS ym, SUM(JoggingZeit) AS zeit
FROM Jogging
WHERE UserId ='1'
GROUP BY ym DESC LIMIT 12

)

UNION ALL

(

SELECT FROM_UNIXTIME(KinoTimestamp, '%Y%|%m') AS ym, SUM(KinoZeit) AS zeit
FROM Kino
WHERE UserID='1'
GROUP BY ym DESC LIMIT 12

)

) gesamt
GROUP BY gesamt.ym DESC LIMIT 12

> > b) Fasse die beiden Ergebnisse in \*einer\* Ergebnismenge zusammen: UNION  
>   
> So wie oben gemacht??  
  
nein, natürlich nicht. Du versuchst Dich bereits an Schritt c)  
  
Nächster Punkt: LIMIT ist hier meiner Meinung nach der falsche Weg. Du möchtest die Daten der letzten 12 Monate. Was ist, wenn Du in einem Monat nicht im Kino warst oder nicht joggen konntest? Deine Abfrage versagt. Du solltest den Zeitraum mit einer WHERE-Klausel einschränken, so dass Du auf die 12 gewünschten Monate kommst.  
  
  
Freundliche Grüße  
  
Vinzenz

[Franco]

<seufz>

<auchseufz>

Ich zitiere aus dem Handbuch:

To apply ORDER BY or LIMIT  to an individual SELECT,
    place the clause inside the parentheses that enclose the SELECT:
</seufz>

Was immer das genau heißen mag, ich vermute, ich darf ausschließlich gruppieren, aber nicht limitieren.

War ehrlich gesagt, mein letzter Versuch vor der Kapitulation. Ich hätte mir dann alle Werte in arrays eingelesen und manuell addiert, aber siehe da, folgendes funktioniert tatsächlich:

  
SELECT  
gesamt.ym,  
SUM(gesamt.zeit)  
FROM (  
SELECT FROM_UNIXTIME(JoggingTimestamp, '%Y%|%m') AS ym, SUM(JoggingZeit) AS zeit  
FROM Jogging  
WHERE UserId ='1'  
GROUP BY ym  
  
UNION ALL  
  
SELECT FROM_UNIXTIME(KinoTimestamp, '%Y%|%m') AS ym, SUM(KinoZeit) AS zeit  
FROM Kino  
WHERE UserID='1'  
GROUP BY ym  
  
) gesamt  
GROUP BY gesamt.ym DESC LIMIT 12  

Nächster Punkt: LIMIT ist hier meiner Meinung nach der falsche Weg. Du möchtest die Daten der letzten 12 Monate. Was ist, wenn Du in einem Monat nicht im Kino warst oder nicht joggen konntest? Deine Abfrage versagt.

Das allerdings ist korrekt!
Ich hätte nämlich dann tatsächlich gerne eine 0 für diesen Monat.

Du solltest den Zeitraum mit einer WHERE-Klausel einschränken, so dass Du auf die 12 gewünschten Monate kommst.

1 Frage: Wie denn? Über where timestamp between...?
2 Frage: Ist das nicht sehr belastend für die db, wenn ich gar kein Limit setze? Weil Limit bedeuted doch, die db hört zu arbeiten auf, wenn sie die 12 fertig hat. Where bedeutet, dass sie immer alle Daten durchforsten muss, selbst wenn klar ist, dass sie keine weiteren Ergebnisse mehr finden kann. Oder?

Grüße und übrigens danke für Deine Hilfe

Franco

[ChrisB]

Hi,

Ist das nicht sehr belastend für die db, wenn ich gar kein Limit setze? Weil Limit bedeuted doch, die db hört zu arbeiten auf, wenn sie die 12 fertig hat.

Nein, bedeutet es nicht.

Es wird die *komplette* Ergebnismenge gebildet, weil in der ja ggf. noch sortiert werden muss.
Erst wenn das passiert wird, werden mit LIMIT die entsprechenden Datensätze „ausgeschnitten“, und der Rest wieder verworfen.

Where bedeutet, dass sie immer alle Daten durchforsten muss, selbst wenn klar ist, dass sie keine weiteren Ergebnisse mehr finden kann. Oder?

Nein.

MfG ChrisB

[Franco]

Es wird die *komplette* Ergebnismenge gebildet, weil in der ja ggf. noch sortiert werden muss.
Erst wenn das passiert wird, werden mit LIMIT die entsprechenden Datensätze „ausgeschnitten“, und der Rest wieder verworfen.

Heißt das, dass mir mein LIMIT gar nichts hilft, wenn ich der db Last abnehmen möchte?

Ich habe z.B. eine Tabelle mit über 1 Mio. Einträge und eine belastende Suchabfrage. Nun möchte ich, dass die db nach 50 gefundenen Einträgen die weitere Suche abbricht.

Wie mache ich ihr das klar, wenn nicht über LIMIT?

mfg, Franco

[ChrisB]

Hi,

Heißt das, dass mir mein LIMIT gar nichts hilft, wenn ich der db Last abnehmen möchte?

Nein, tut's nicht.

Ich habe z.B. eine Tabelle mit über 1 Mio. Einträge und eine belastende Suchabfrage.

Informiere dich, wie und wo man sinnvolle Indexe setzt.

MfG ChrisB

[dedlfix]

Hi!

Ist das nicht sehr belastend für die db, wenn ich gar kein Limit setze? Weil Limit bedeuted doch, die db hört zu arbeiten auf, wenn sie die 12 fertig hat.
Nein, bedeutet es nicht.
Es wird die *komplette* Ergebnismenge gebildet, weil in der ja ggf. noch sortiert werden muss.
Erst wenn das passiert wird, werden mit LIMIT die entsprechenden Datensätze „ausgeschnitten“, und der Rest wieder verworfen.

Das kommt drauf an. Dieses Szenario wäre der worst case. Aber MySQL kann auch optimieren, wenn die Bedingungen stimmen. Zum Sortieren kann ein vorhandener Index genommen werden und bei solch einer vorsortierten Menge kann LIMIT auch von vorn herein die Ergebnismenge begrenzen.

Zur Optimierung kennt das MySQL-Handbuch übrigens ein eigenes Hauptkapitel: http://dev.mysql.com/doc/refman/5.1/en/optimization.html

Lo!

[Vinzenz Mai]

Hallo,

Ich zitiere aus dem Handbuch:

To apply ORDER BY or LIMIT  to an individual SELECT,
    place the clause inside the parentheses that enclose the SELECT:
</seufz>

Was immer das genau heißen mag, ich vermute, ich darf ausschließlich gruppieren, aber nicht limitieren.

doch darfst Du, kannst Du. Wie's geht, habe ich doch ergänzt: Du musst die einzelnen SELECTS in Klammern (parentheses) setzen.

  
SELECT  
    gesamt.ym,  
    SUM(gesamt.zeit)  
FROM (  
        (   -- Du willst die Ergebnisse limitieren, also klammere  
            SELECT  
                FROM_UNIXTIME(JoggingTimestamp, '%Y%|%m') AS ym,  
                SUM(JoggingZeit) AS zeit  
            FROM  
                Jogging  
            WHERE  
                UserId ='1'  
            GROUP BY  
                ym  
            ORDER BY  
                ym DESC  
            LIMIT 12  
        )  
        UNION ALL  
        -- analoges Statement, weggelassen  
  
) gesamt  
GROUP BY  
    gesamt.ym  
ORDER BY ym DESC  

Nächster Punkt: LIMIT ist hier meiner Meinung nach der falsche Weg. Du möchtest die Daten der letzten 12 Monate. Was ist, wenn Du in einem Monat nicht im Kino warst oder nicht joggen konntest? Deine Abfrage versagt.

Du solltest den Zeitraum mit einer WHERE-Klausel einschränken, so dass Du auf die 12 gewünschten Monate kommst.

1 Frage: Wie denn? Über where timestamp between...?

prinzipiell ja. Nutze Umrechnungsfunktionen.
In beiden Fällen hast Du übrigens das "Problem", dass Monate, in denen Du weder im Kino warst, noch gejoggt hast, nicht in der Ergebnismenge auftauchen. Du kannst das in der API (hier PHP) lösen, Du kannst einfach einen LEFT OUTER JOIN auf eine Hilfstabelle machen. Und nein, wenn Du diesen Join ganz zum Schluss machst, ist das kein bisschen belastend. Ein Join einer "Tabelle" mit weniger als 12 Datensätzen mit einer Tabelle, die genau 12 Datensätze enthält, ist ein Klacks.

2 Frage: Ist das nicht sehr belastend für die db, wenn ich gar kein Limit setze?

Nein, selbstverständlich nicht. Dafür ist ein Index über die Timestamp-Spalte sehr nützlich.

Gelegentlich, aber auch nur gelegentlich kann LIMIT zur Optimierung genutzt werden, siehe von dedlfix verlinktes Optimierungskapitel. Es kann sogar vorkommen, dass LIMIT eine eklige Performancebremse wird :-)

Freundliche Grüße

Vinzenz

[ChrisB]

Hi,

Der User mit der ID 1 geht ab und an joggen und ab und an ins Kino.

In der Tabelle "Jogging" stehen als die Zeit, aber auch Strecke, Wetter, Puls, usw.
In der Tabelle Kino stehen neben der Zeit noch Film, Schauspieler, Bewertung, usw.

Was hat die Information, welche Schauspieler im Film mitwirken, *zusammen* mit dem Zeitpunkt, zu dem sich der Nutzer diesen Film ansieht, in dieser Tabelle verloren?

An den mitwirkenden Schauspielern ändert sich nichts, egal ob er den Film vor zwei Wochen gesehen hat, oder ihn sich erst Übermorgen anschaut.

Hinzu kommt bei diesem Datenmodell eine Redundanz, wenn sich der Nutzer einen Film mehr als ein mal anschaut - dann speicherst du wieder die gleichen Informationen über die im gleichen Film mitwirkenden gleichen Schauspieler.

MfG ChrisB