Problem bei einer Query

[Franco]

Hallo,

es geht um folgende Query:

  
$query="SELECT FROM_UNIXTIME(timestamp, '%Y%|%m') AS ym, sum(Menge*Preis*berechnung*(1-rabatt) ) AS count  
FROM ausgaben  
WHERE ...  
GROUP BY ym DESC LIMIT 12  
";  

Prob.1: Der Wert in der Tabellenspalte "berechnung" hat entweder einen float-wert oder ist NULL oder ist "ja oder nein".
Ich weiß, das ist ziemlich doof, aber derzeit ist es halt so.

Kann ich in der obigen Abfrdage irgendwie verankern, dass zur Berechnung der Summe das "ja" und "NULL" mit der Zahl 1 und das "nein" mit der Zahl 0 ausgetauscht wird?

Prob.2: GROUP BY ym DESC LIMIT 12 ist schön, solange in jedem Monat etwas ausgegeben wurde. Wenn das aber nicht so war, fehlen mir die Monate der Nullausgaben komplett. Wie kriegt man die auch noch in die Ergebnissmenge mit ihrem Wert (wenn auch =0)?

Grüße, Frank

[suit]

Kann ich in der obigen Abfrdage irgendwie verankern, dass zur Berechnung der Summe das "ja" und "NULL" mit der Zahl 1 und das "nein" mit der Zahl 0 ausgetauscht wird?

IF(), IFNULL()

Prob.2: GROUP BY ym DESC LIMIT 12 ist schön, solange in jedem Monat etwas ausgegeben wurde. Wenn das aber nicht so war, fehlen mir die Monate der Nullausgaben komplett. Wie kriegt man die auch noch in die Ergebnissmenge mit ihrem Wert (wenn auch =0)?

Wenn ich dich richtig verstehe: siehe oben.

[dedlfix]

Hi!

Kann ich in der obigen Abfrdage irgendwie verankern, dass zur Berechnung der Summe das "ja" und "NULL" mit der Zahl 1 und das "nein" mit der Zahl 0 ausgetauscht wird?

Ja, mit IF() oder CASE aus den Control-Flow-Functions.

Prob.2: GROUP BY ym DESC LIMIT 12 ist schön, solange in jedem Monat etwas ausgegeben wurde. Wenn das aber nicht so war, fehlen mir die Monate der Nullausgaben komplett. Wie kriegt man die auch noch in die Ergebnissmenge mit ihrem Wert (wenn auch =0)?

Warum limitierst du auf 12, wenn du augenscheinlich nach Monaten gruppierst und nur ein Jahr ausgegeben haben möchtest? Ist deine Where-Bedingung nicht ausreichend einschränkend formuliert?

Nicht vorhandenes kann man nicht abfragen. Mir fallen spontan zwei Workaround dazu ein.

• Ermittle das Nichtvorhandensein eines Monats beim Abfragen und Weiterverarbeiten des Ergebnisses.
• Erstell eine (temporäre) Tabelle mit den Zahlen von 1 bis 12. Bilde einen Left-Join mit der anderen Ergebnismenge.

Lo!

[Franco]

Hi,

Ja, mit IF() oder CASE aus den Control-Flow-Functions.

Hab ich das missverstanden?

  
$query="SELECT FROM_UNIXTIME(timestamp, '%Y%|%m') AS ym, sum(Menge*Preis*CASE 'berechnung' WHEN 'ja' THEN 100 WHEN 'nein' THEN 0  WHEN NULL THEN 100 END *(1-rabatt) ) AS count  
FROM ausgaben  
WHERE ...  
GROUP BY ym DESC LIMIT 12  

Ist deine Where-Bedingung nicht ausreichend einschränkend formuliert?

Doch ist sie.

• Erstell eine (temporäre) Tabelle mit den Zahlen von 1 bis 12. Bilde einen Left-Join mit der anderen Ergebnismenge.

Und das alles in _einer_ Query??

Ich glaube nicht, dass ich das hinbekomme.

Gruß, Frank

[Vinzenz Mai]

Hallo,

Ja, mit IF() oder CASE aus den Control-Flow-Functions.
Hab ich das missverstanden?

ja.

$query="SELECT FROM_UNIXTIME(timestamp, '%Y%|%m') AS ym, sum(MengePreisCASE 'berechnung' WHEN 'ja' THEN 100 WHEN 'nein' THEN 0  WHEN NULL THEN 100 END *(1-rabatt) ) AS count
FROM ausgaben
WHERE ...
GROUP BY ym DESC LIMIT 12

  
berechnung ist eine Spaltenbezeichnung. Spaltenbezeichner werden \*nicht\* mit Anführungszeichen (nicht mit einfachen, nicht mit doppelten) maskiert. In generiertem Code ist es eine gute Idee, alle Spaltenbezeichner zu maskieren, dann muss man sich über reservierte Wörter keine Gedanken machen. In handgeschriebenem Code bevorzuge ich Spaltennamen, die keine Maskierung erfordern.  
  

> > Ist deine Where-Bedingung nicht ausreichend einschränkend formuliert?  
> Doch ist sie.  
  
.. dann benötigst Du keine Limitierung, weil Du maximal 12 Datensätze erhalten kannst.  
  

> > - Erstell eine (temporäre) Tabelle mit den Zahlen von 1 bis 12. Bilde einen Left-Join mit der anderen Ergebnismenge.  
  

> Und das alles in \_einer\_ Query??  
  
Wenn die (Hilfs-)Tabelle nicht temporär ist, kommst Du natürlich mit einer Query aus.  
  
  
Freundliche Grüße  
  
Vinzenz

[Franco]

Hallo Vinzenz,

berechnung ist eine Spaltenbezeichnung. Spaltenbezeichner werden *nicht* mit Anführungszeichen (nicht mit einfachen, nicht mit doppelten) maskiert.

Aber ohne Hochkomma meckert mysql bei obiger Query.
Oder habe ich wieder etwas missverstanden? Ich bin momentan etwas verwirrt.

.. dann benötigst Du keine Limitierung, weil Du maximal 12 Datensätze erhalten kannst.

Ich habe sie auch schon entfernt. Danke für den Hinweis.

• Erstell eine (temporäre) Tabelle mit den Zahlen von 1 bis 12. Bilde einen Left-Join mit der anderen Ergebnismenge.

//erzeugt eine temporäre Tabelle...
CREATE TEMPORARY TABLE tmp
//...füllt diese mit den Zahlen 1-12
?? weiß ich nicht, wie geht.

Grüße, Frank

[dedlfix]

Hi!

Aber ohne Hochkomma meckert mysql bei obiger Query.

Was genau?

//erzeugt eine temporäre Tabelle...
//...füllt diese mit den Zahlen 1-12
?? weiß ich nicht, wie geht.

Mit einem ganz normalen (Multi-)INSERT.

Lo!

[Franco]

Hallo dedlfix,

Aber ohne Hochkomma meckert mysql bei obiger Query.

Was genau?

Sorry, mein Fehler. Ich hatte den Spaltennamen falsch geschrieben und mysql meckerte über die nicht vorhanden Spalte.

//erzeugt eine temporäre Tabelle...
//...füllt diese mit den Zahlen 1-12
?? weiß ich nicht, wie geht.

Mit einem ganz normalen (Multi-)INSERT.

Ich kenne keinen Multiinsert. Aber ich weiß auch nicht genau, worauf Du mit dem LEFT JOIN hinauswillst.

Bzw. ich weiß schon, worauf Du hinaus willst, aber nicht, wie mir ein LEFT JOIN mit einer Tabelle, in der lediglich die Zahlen 1-12 eingetragen sind, hilft.

Gruß, Frank

[dedlfix]

Hi!

Mit einem ganz normalen (Multi-)INSERT.
Ich kenne keinen Multiinsert.

Das ist die Variante des INSERT, mit dem man mit einem Statement mehrere Datensätze anlegen kann.

INSERT table (month) VALUES (1), (2), ...

Aber ich weiß auch nicht genau, worauf Du mit dem LEFT JOIN hinauswillst.

Nun, ein Left Join liefert von der linken Tabelle alle Datensätze, auch wenn in der rechten keine Entsprechung gemäß der Join-Bedingung existiert. Da heißt, du bekommst garantiert deine 12 Datensätze (aus der linken temporären Tabelle mit den Zahlen von 1 bis 12), auch wenn sich keine Monatsdaten finden lassen.

Bzw. ich weiß schon, worauf Du hinaus willst, aber nicht, wie mir ein LEFT JOIN mit einer Tabelle, in der lediglich die Zahlen 1-12 eingetragen sind, hilft.

Die Verknüpfungsbedingung verknüpft die 12 Zahlen mit den 12 (vorhandenen oder nicht vorhandenen) Monaten.

Lo!

[Franco]

Hi,

Das ist die Variante des INSERT, mit dem man mit einem Statement mehrere Datensätze anlegen kann.

INSERT table (month) VALUES (1), (2), ...

Alles klar. Ich hatte die ganze Zeit einen kleinen Denkfejhler, weil ich dachte, Du wolltest tatsächlich die Zahlen 1-12 hineinschreiben. Das geht wohl, aber nicht in meinem Fall, da ich rükwirkend auch über einen Jahreswechsel hinweg die Zahlen haben möchte.

Nun, ein Left Join liefert von der linken Tabelle alle Datensätze, auch wenn in der rechten keine Entsprechung gemäß der Join-Bedingung existiert. Da heißt, du bekommst garantiert deine 12 Datensätze (aus der linken temporären Tabelle mit den Zahlen von 1 bis 12), auch wenn sich keine Monatsdaten finden lassen.

Korrekt. Mein Problem ist leider, dass mein Vergleichswert der "rechten Tabelle" sich so gar nicht vergleichen lassen will.

ON t.Monat = (und hier steht mein FROM_UNIXTIME-Konstrukt), wobei ich nicht sicher weiß, ob ich da nicht einen Syntaxfehler eingebaut habe.

Ich bekomme nämlich nur die Monate angezeigt, die Daten beinhalten:

Aus einer Test-db:

SELECT t.Monat, COUNT(m.TestID) AS count  
FROM tmp t  
LEFT JOIN meinetabelle m  
ON t.Monat = FROM_UNIXTIME(m.Timestamp, '%Y%|%m')  
WHERE...  
GROUP BY t.Monat DESC

Tabelle tmp:
1-2010|09
2-2010|08
3-2010|07
4-2010|06
...

Und zum 2. Teil des Problems:

Die Query:

SELECT ID,  
CASE Multi  
WHEN 'ja'  
THEN 1  
WHEN 'nein'  
THEN 0  
WHEN 'NULL'  
THEN 0  
END FROM ausgaben

wandelt meine NULL-Werte nicht um.

Danke für Deine Geduld übrigens.

Gruß, Frank

[Vinzenz Mai]

Hallo,

Und zum 2. Teil des Problems:

Die Query:

SELECT ID,

CASE Multi
WHEN 'ja'
THEN 1
WHEN 'nein'
THEN 0
WHEN 'NULL'
THEN 0
END FROM ausgaben

>   
> wandelt meine NULL-Werte nicht um.  
  
NULL ist nicht die Zeichenkette 'NULL' und [Operationen mit NULL-Werten sind anders](http://dev.mysql.com/doc/refman/5.1/en/problems-with-null.html) als Operationen auf "normalen" Werten.  
  
Du kannst daher \*nicht\* die einfache CASE-WHEN-Syntax verwenden, sondern die komplexere:  
  
~~~sql
CASE  
    WHEN Multi = 'ja' THEN 1  
    WHEN Multi = 'nein' THEN 0  
    WHEN [link:http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_isnull@title=ISNULL](Multi) THEN 0  
END  

Alternativ kannst Du mit einem geschachtelten IF-Statement arbeiten.

Freundliche Grüße

Vinzenz

[Franco]

Hi Vinzenz,

Alternativ kannst Du mit einem geschachtelten IF-Statement arbeiten.

Das sollte ich wohl machen, weil

SELECT
CASE Multi
WHEN 'ja'
THEN 100
WHEN 'nein'
THEN 0
WHEN ISNULL( Multi )
THEN 100
END
FROM ausgaben

wandelt mir auch die NULL_Werte nicht um.

Irgendwie klappt aber auch heute gar nichts ;-)

Grüße, Frank

[dedlfix]

Hi!

SELECT
CASE Multi
WHEN 'ja'
THEN 100
WHEN 'nein'
THEN 0
WHEN ISNULL( Multi )
THEN 100
END
FROM ausgaben

wandelt mir auch die NULL_Werte nicht um.

Schau mal genauer hin, wie Vinzenz das notiert hat. Die erste Variante von CASE hat hinter dem CASE einen Ausdruck stehen und hinter den WHENs die damit zu vergleichenden Ausdrücke. Die zweite Variante hat hinter dem CASE nichts weiter stehen und den WHENs folgen vollständige Vergleiche.

Lo!

[Franco]

Schau mal genauer hin, wie Vinzenz das notiert hat.

Boa, eh! Irgendwie ist heute in Sachen Computerei nicht mein Tag. Ich musste selbst nach Deinem Hinweis noch 5 Minuten die Queries vergleichen und habs nicht gerafft.

So, wie Vinzenz es notiert hat, klappt das ganz prima :-)

Also, Problem 2 ist gelöst.

Bleibt mein LEFT JOIN, der nicht so will, wie ich will.

Hast Du da auch noch eine Idee?

Gruß, Frank

[dedlfix]

Hi!

Bleibt mein LEFT JOIN, der nicht so will, wie ich will.
Hast Du da auch noch eine Idee?

Auch mit VARCHAR statt INT hatte ich kein Problem. Die Idee ist immer noch, mittels Kontrollausgaben nachzusehen, ob alle verwendeten Ausdrücke das liefern, was sie sollen.

Lo!

[Franco]

Die Idee ist immer noch, mittels Kontrollausgaben nachzusehen, ob alle verwendeten Ausdrücke das liefern, was sie sollen.

Hi,

entscheidend ist doch, dass die beiden Vergleichswerte miteinander vergleichbar sind und ich habe überprüft, dass sie es sind.

In Tabelle t stehen sie im Format Y|m und der Ausdruck FROM_UNIXTIME(m.Timestamp, '%Y|%m') bringt in seiner Ergebnismenge auch das Format Y|m.

Was gäbe es denn sonst noch an kontrollausgaben zu überprüfen?

Gruß, Frank

[dedlfix]

Hi!

Was gäbe es denn sonst noch an kontrollausgaben zu überprüfen?

Es ist irgendwas anders als in meinem Versuch. Ich kann nicht hellsehen und ein nachvollziehbares Beispiel hast du nicht veröffentlicht. Mach eine Kopie der Tabelle. Lösch alle Datensätze bis auf 2 oder drei signifikante (ungefähr analog zu meinem Versuch). Auch alle Spalten, die nicht weiter für das Problem relevant sind, kannst du löschen. Teste dann, ob der Fehler noch vorhanden ist. Poste in dem Fall die CREATE-, INSERT- und SELECT-Statements, so dass man sie nur noch kopiern muss, um den Fall nachzustellen.

Lo!

[Franco]

Es ist irgendwas anders als in meinem Versuch. Ich kann nicht hellsehen und ein nachvollziehbares Beispiel hast du nicht veröffentlicht. Mach eine Kopie der Tabelle. Lösch alle Datensätze bis auf 2 oder drei signifikante (ungefähr analog zu meinem Versuch). Auch alle Spalten, die nicht weiter für das Problem relevant sind, kannst du löschen. Teste dann, ob der Fehler noch vorhanden ist. Poste in dem Fall die CREATE-, INSERT- und SELECT-Statements, so dass man sie nur noch kopiern muss, um den Fall nachzustellen.

Hi dedlfix,

ok, das ist eine gute Idee. Danke für Deine Hilfe.

Aber ich glaub, für heute mach ich mal 'Buffalo', ich bin müde und diese Query schafft mich grad wirklich.

Morgen dann mit neuem Mut und frischem Elan.

Dir auch noch nen schönen Abend und entsprechende Nachtruhe und  nochmal Danke für Deine Mühe zu meinem Problem.

Gruß, Frank

[Franco]

Es ist irgendwas anders als in meinem Versuch. Ich kann nicht hellsehen und ein nachvollziehbares Beispiel hast du nicht veröffentlicht. Mach eine Kopie der Tabelle. Lösch alle Datensätze bis auf 2 oder drei signifikante (ungefähr analog zu meinem Versuch). Auch alle Spalten, die nicht weiter für das Problem relevant sind, kannst du löschen. Teste dann, ob der Fehler noch vorhanden ist. Poste in dem Fall die CREATE-, INSERT- und SELECT-Statements, so dass man sie nur noch kopiern muss, um den Fall nachzustellen.

Hi dedlfix,

ich habe es geschafft, nachvollziehbaren Code zu generieren, der mein Problem transparent machen dürfte:

  
CREATE TEMPORARY TABLE `tmp` (  
`ID` INT( 2 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
`Monat` VARCHAR( 10 ) NOT NULL  
) ENGINE = MYISAM ;  
  
INSERT INTO `tmp` (`ID`, `Monat`) VALUES  
(1, '2010|10'),  
(2, '2010|09'),  
(3, '2010|08'),  
(4, '2010|07'),  
(5, '2010|06');  
  
  
CREATE TEMPORARY TABLE meinetabelle (  
  ID int(10),  
  Unixstamp varchar (10) NOT NULL,  
`loesch` INT( 1 ) NOT NULL ,  
`Status` VARCHAR( 10 ) NOT NULL  
);  
  
INSERT INTO meinetabelle VALUES  
(1, UNIX_TIMESTAMP(NOW()),'0','qq'),  
(2, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(3, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(4, UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH),'0','qq');  
  
  
SELECT *, FROM_UNIXTIME(m.Unixstamp, '%Y|%m')  
FROM tmp t  
LEFT JOIN meinetabelle m ON t.Monat = FROM_UNIXTIME(m.Unixstamp, '%Y|%m')  
WHERE  
loesch != 1 AND  
Status = 'xx'  
GROUP BY t.Monat DESC;  

Als Ergebnismenge erhältst Du ein leeres Resultat.

Aber wir wollten ja über unseren LEFT JOIN auf jeden Fall die Monate haben, auch wenn in diesem Monat kein Eintrag vorliegt.

Gruß, Frank

[dedlfix]

Hi!

ich habe es geschafft, nachvollziehbaren Code zu generieren, der mein Problem transparent machen dürfte:
SELECT *, FROM_UNIXTIME(m.Unixstamp, '%Y|%m')
FROM tmp t
LEFT JOIN meinetabelle m ON t.Monat = FROM_UNIXTIME(m.Unixstamp, '%Y|%m')
WHERE
loesch != 1 AND
Status = 'xx'
GROUP BY t.Monat DESC;
Als Ergebnismenge erhältst Du ein leeres Resultat.

Das Problem ist die WHERE-Bedingung, die zieht "zu spät". Der Join bildet die verknüpfte Menge - da sind die alle Einträge noch da. Nun wirkt das WHERE und findet in dieser Menge nichts passendes. Das Resultat kennst du. Als Lösungen fallen mir zwei Vorgehensweise ein:
Die ausschließenden Bedingungen müssen als weitere Verknüpfungsbedingung hinzugefügt werden, dann werden von meinetabelle nur die Datensätze verknüpft, die in diese Bedingung passen, von tmp aber alle genommen. Der relevante Teil der Query sieht dann so aus:

FROM tmp t
LEFT JOIN meinetabelle m ON
  t.Monat = FROM_UNIXTIME(m.Unixstamp, '%Y|%m') AND
  loesch != 1 AND
  Status = 'xx'
GROUP BY ...

Die zweite Lösung wäre, die zu berücksichtigenden Daten aus meinetabelle in einer Subquery zu ermitteln und diese Menge dann mit der tmp zu joinen.

FROM tmp t
LEFT JOIN (SELECT whatever FROM meinetabelle WHERE loesch != 1 AND Status = 'xx') m ON
  t.Monat = FROM_UNIXTIME(m.Unixstamp, '%Y|%m')
GROUP BY ...

Bei der Subquery sieht man etwas besser, dass bestimmte Daten ausgeschlossen werden sollen, weil diese anders als die Variante mit der erweiterten Join-Bedingung schon durch die Klammerung eine syntaktische Einheit bildet. Zumdem kann man die Subquery einfacher rauskopieren und wieder einfügen, wenn man sie einzeln testen und weiterentwickeln will.

Lo!

[dedlfix]

Hi!

Das Problem ist die WHERE-Bedingung, die zieht "zu spät".

Vielleicht noch ein Wort zu meiner Vorgehensweise. Gewusst habe ich vorher schon, dass die Verknüpfungsbedingung um weitere Kriterien ergänzt werden kann, die nicht die Beziehung zwischen den beiden Tabellen ausdrücken, sondern eben auch nur einschränkend wirken können. (Das gehört oftmals nicht zum allgemein vorhandenen Wissen über Joins.)

Ich sah das WHERE und verdächtigte es, die bereits gejointe Menge zu plündern statt nur meinetabelle zu beschränken. Also raus mit dem WHERE - und siehe da, die Ergebnismenge war wieder vollzählig. Noch schnell das WHERE wieder rein und den status-Vergleich auf qq geändert - ergab drei Datensätze - Verdacht bestätigt. Das WHERE zum AND umschreiben (und qq zurück zu xx) ergab 5 Datensätze mit lauter NULL-Werten, aber so soll das sein. xx wieder zu qq geändert brachte ebenfalls 5 Datensätze, drei "volle", zwei "leere". Problem gelöst.

Im Prinzip ist das bereits erklärlich, wenn man sich das Prinzip der Abarbeitungsweise eines SELECT-Statements verdeutlicht. Abgesehen von der SELECT-Klausel werden alle anderen Klauseln von links nach rechts abgearbeitet und jeweils ein Zwischenergebnis an den nächsten Schritt weitergereicht. FROM und die Joins sammelen erstmal alles Quellen zusammen und verknüpfen diese. Die Menge daraus wird vom WHERE eingeschränkt und so weiter Richtung rechtes Ende. Eine Ausnahme bildet die SELECT-Klausen, die zwischen GROUP BY und HAVING die Berechnung der Spalten vornimmt. (Hier gibt es noch ein paar Abkürzungen seitens des internen Optimierers des DBMS, aber die beeinflussen das Ergebnis nicht.) Mit diesem prinzipiellen Ablauf kann man auch schön die Wirkung der Bedingungen in der JOIN-Klausel gegenüber der WHERE-Klausel sehen.

Lo!

[Vinzenz Mai]

Hallo,

ergänzend:

Gewusst habe ich vorher schon, dass die Verknüpfungsbedingung um weitere Kriterien ergänzt werden kann, die nicht die Beziehung zwischen den beiden Tabellen ausdrücken, sondern eben auch nur einschränkend wirken können. (Das gehört oftmals nicht zum allgemein vorhandenen Wissen über Joins.)

ich hatte dazu mal ein Beispiel gebracht.

Freundliche Grüße

Vinzenz

[Franco]

Hi,

Das Problem ist die WHERE-Bedingung, die zieht "zu spät".

Ok, da wäre ich nie drauf gekommen.

Ich sah das WHERE und verdächtigte es, die bereits gejointe Menge zu plündern statt nur meinetabelle zu beschränken.

So im Nachhinein und vor allem mit Deiner anschließenden Erklärung zur Abarbeitungsweise der Klauseln hört es sich so easy und logisch an. Aber das ist ja oft so, wenn man die Lösung erst mal kennt.

Ich habe auf jeden Fall hier im Thread jede Menge neu gelernt, nicht zuletzt auch, wie ich mithelfen kann, einem Helfer mein Problem nachzuvollziehen. Die Idee mit dem Codebeispiel war klasse! Erst das hat uns letztlich wirklich geholfen, abgesehen von Deiner Geduld und Deinem Willen, mir zu helfen.

Ich habe natürlich gleich beide Lösungen ausprobiert. Ein davon klappt vorzüglich, in der anderen habe ich einen Syntaxfehler und weiß nicht, welchen.

Diese hier klappt:

CREATE TEMPORARY TABLE `tmp` (  
`ID` INT( 2 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
`Monat` VARCHAR( 10 ) NOT NULL  
) ENGINE = MYISAM ;  
  
INSERT INTO `tmp` (`ID`, `Monat`) VALUES  
(1, '2010|10'),  
(2, '2010|09'),  
(3, '2010|08'),  
(4, '2010|07'),  
(5, '2010|06');  
  
  
CREATE TEMPORARY TABLE meinetabelle (  
  ID int(10),  
  Unixstamp varchar (10) NOT NULL,  
`loesch` INT( 1 ) NOT NULL ,  
`Status` VARCHAR( 10 ) NOT NULL  
);  
  
INSERT INTO meinetabelle VALUES  
(1, UNIX_TIMESTAMP(NOW()),'0','qq'),  
(2, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(3, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(4, UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH),'0','qq');  
  
  
SELECT *, FROM_UNIXTIME(m.Unixstamp, '%Y|%m')  
FROM tmp t  
LEFT JOIN meinetabelle m ON t.Monat = FROM_UNIXTIME(m.Unixstamp, '%Y|%m') AND  
loesch != 1 AND  
Status = 'xx'  
GROUP BY t.Monat DESC;

Und bei dieser, die Du als die zu bevorzugendere bezeichnest:

CREATE TEMPORARY TABLE `tmp` (  
`ID` INT( 2 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
`Monat` VARCHAR( 10 ) NOT NULL  
) ENGINE = MYISAM ;  
  
INSERT INTO `tmp` (`ID`, `Monat`) VALUES  
(1, '2010|10'),  
(2, '2010|09'),  
(3, '2010|08'),  
(4, '2010|07'),  
(5, '2010|06');  
  
  
CREATE TEMPORARY TABLE meinetabelle (  
  ID int(10),  
  Unixstamp varchar (10) NOT NULL,  
`loesch` INT( 1 ) NOT NULL ,  
`Status` VARCHAR( 10 ) NOT NULL  
);  
  
INSERT INTO meinetabelle VALUES  
(1, UNIX_TIMESTAMP(NOW()),'0','qq'),  
(2, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(3, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(4, UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH),'0','qq');  
  
  
SELECT *, FROM_UNIXTIME(m.Unixstamp, '%Y|%m')  
FROM tmp t  
LEFT JOIN  
(select ID from meinetabelle  
where  
loesch != 1 AND  
Status = 'xx'  
)  
meinetabelle m ON t.Monat = FROM_UNIXTIME(m.Unixstamp, '%Y|%m')  
GROUP BY t.Monat DESC;

meint mysql, dass die Syntax falsch ist. Möglicherweise habe ich die Subquery falsch eingebettet? Ich seh aber nicht, inwiefern.

Viele Grüße und ein riesen Dankeschön an Dich

Frank

[Vinzenz Mai]

Hallo Frank,

Ich habe natürlich gleich beide Lösungen ausprobiert. Ein davon klappt vorzüglich, in der anderen habe ich einen Syntaxfehler und weiß nicht, welchen.

SELECT *, FROM_UNIXTIME(m.Unixstamp, '%Y|%m')
FROM tmp t
LEFT JOIN

-- Du musst Deinem Subselect einen Aliasnamen geben
-- a) um in SELECT auf die Spalten zuzugreifen
-- b) um die Joinbedingung formulieren zu können.

(select ID from meinetabelle
where
loesch != 1 AND
Status = 'xx'
)

-- Du darfst aber nur *einen* Aliasnamen verwenden.
-- Sprich: Lösche in der folgenden Zeile "meinetabelle"

meinetabelle m ON t.Monat = FROM_UNIXTIME(m.Unixstamp, '%Y|%m')
GROUP BY t.Monat DESC;[/code]

Freundliche Grüße

Vinzenz

[Franco]

Hallo Vinzenz,

danke für die Hilfe. Ihc habe auch verstanden, was Du meinst, habe mich aber nun gegen die Subselect-Lösunbg entschieden, weil die andere für mich persönlich irgendwie "lesbarer" ist.

Nun aber noch ein kleiner nachschlag, denn das war je mein eigentliches Grundproblem:

  
SELECT  
Monat,  
sum(Menge*Preis*  
(CASE WHEN multi = 'ja' THEN 1  
WHEN multi = '100' THEN 1  
WHEN multi = '200' THEN 2  
WHEN multi = 'nein' THEN 0  
WHEN ISNULL(multi) THEN 1 END) *(1-rabatt) ) AS count  
FROM tmp  
LEFT JOIN ausgaben ON Monat = FROM_UNIXTIME(Unixstamp, '%Y|%m') AND  
Unixstamp != "" AND  
Status = 'qq' AND  
loesch != 1 AND  
FROM_UNIXTIME(Unixstamp) > NOW() - INTERVAL 1 YEAR  
GROUP BY Monat DESC  

Diese Query spuckt mir völlig "unsinnige" Werte raus, die definitiv nicht stimmen können.

Ich möchte aber die monatlich summierten Ausgaben im Sinne von Menge*Preis*multi*(1-Rabatt) haben.

Habe ich da irgend etwas missverstanden?

Denn (Lernerfolg des Threads, ich liefere Testcode mit - Nachteil, ich liefere den funktionierenden Code) hier funktioniert das einwandfrei:

  
CREATE TEMPORARY TABLE `tmp` (  
`ID` INT( 2 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
`Monat` VARCHAR( 10 ) NOT NULL  
) ENGINE = MYISAM ;  
  
INSERT INTO `tmp` (`ID`, `Monat`) VALUES  
(1, '2010|10'),  
(2, '2010|09'),  
(3, '2010|08'),  
(4, '2010|07'),  
(5, '2010|06');  
  
  
CREATE TEMPORARY TABLE meinetabelle (  
  ID int(10),  
  Unixstamp varchar (10) NOT NULL,  
`loesch` INT( 1 ) NOT NULL ,  
`Status` VARCHAR( 10 ) NOT NULL,  
`betrag` DECIMAL( 10,2 ) NOT NULL,  
`multi` DECIMAL( 10,2 ) NOT NULL  
  
);  
  
INSERT INTO meinetabelle VALUES  
(1, UNIX_TIMESTAMP(NOW()),'0','1','10','ja'),  
(2, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','2','20','100'),  
(3, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','1','30','ja'),  
(4, UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH),'0','3','40','100');  
  
  
SELECT Monat,  
sum(betrag*  
(CASE WHEN multi = '100' THEN 1  
WHEN multi = 'ja' THEN 1  
WHEN multi = 'nein' THEN 0  
WHEN ISNULL(multi) THEN 1 END)) AS count  
FROM tmp  
LEFT JOIN meinetabelle ON Monat = FROM_UNIXTIME(Unixstamp, '%Y|%m')  
AND  
FROM_UNIXTIME(Unixstamp) > NOW() - INTERVAL 1 YEAR  
GROUP BY Monat DESC;  

Was mach ich im unteren Beispiel denn soviel besser, dass das äluft und das obige Unsinn ausgibt?

Grüße, Frank

[Ilja]

moin,

Diese Query spuckt mir völlig "unsinnige" Werte raus, die definitiv nicht stimmen können.

kein wunder, du benutzt den teuflischen mysql bug bezüglich gruppierungen. dazu gibt es hier im forum schon zahlreiche beträge. jedes andere dbms würde dir bei der abfrage eine fehlermeldung ausgeben, mysql geht leider einen anderen weg. die regel für gruppierungen lautet, nimm du die spalten in der projektion, über die du auch gruppiert hast oder dieeine aggregatfunktion verwenden. warum das so sein muss, dazu gibt es aktuell hier im forum in einer parallelen beitrag, den du dir mal anschauen kannst. das beste ist, du freundet dich einwenig mit unterabfragen an.

undgewöhne dirmalaliasnamen an und setze sie vor den spalten.so kann kein schwein erkennen, welche spalte zu welcher tabelle gehört.......

Ilja

[dedlfix]

Hi!

kein wunder, du benutzt den teuflischen mysql bug bezüglich gruppierungen.

Ich seh da nur den Monat, über den gruppiert wurde, und eine Aggegatfunktion (sum), die allerdings etwas umfangreich ausfällt. Das sollte doch nicht gegen die Gruppier-Richtlinie verstoßen.

Lo!

[Franco]

Ich seh da nur den Monat, über den gruppiert wurde, und eine Aggegatfunktion (sum), die allerdings etwas umfangreich ausfällt. Das sollte doch nicht gegen die Gruppier-Richtlinie verstoßen.

Hi dedlfix,

genau das dachte ich auch gerade und wollte nachfragen, welche Spalte Ilja meint, die sowohl nicht gruppiert als auch nicht in einer Aggregatfunktion steht.

Gruß, Frank

[Ilja]

moin,

Ich seh da nur den Monat, über den gruppiert wurde, und eine Aggegatfunktion (sum), die allerdings etwas umfangreich ausfällt. Das sollte doch nicht gegen die Gruppier-Richtlinie verstoßen.

klarer fall von überarbeitung und panikmache. ich brauche drigend urlaub....

Ilja

[dedlfix]

Hi!

danke für die Hilfe. Ihc habe auch verstanden, was Du meinst, habe mich aber nun gegen die Subselect-Lösunbg entschieden, weil die andere für mich persönlich irgendwie "lesbarer" ist.

Da ist ein bisschen Schachtelei, das vielleicht unübersichtlich wirkt. Aber das Mischen der Querys wegen des Joins wird auch nicht übersichtlicher.

Diese Query spuckt mir völlig "unsinnige" Werte raus, die definitiv nicht stimmen können.
Ich möchte aber die monatlich summierten Ausgaben im Sinne von Menge*Preis*multi*(1-Rabatt) haben.

Ich nehme an, du hast zu viele Datensätze, um mal eins der "unsinnigen" Ergebnisse und der dazugehörigen Datensätze zu zeigen. Ich möchte dich dazu ermutigen, doch die Subselect-Lösung zu präferieren. Erstell dir zunächst die Query ohne den Join. Die kannst du ausgiebig testen. Abgesehen von den fehlenden Monaten sollte sie ja ein sinnvolles Ergebnis liefert. Dann hast du erst einmal Gewissheit, dass dir nicht der Join einen Streich spielt. Wenn alles fehlerfrei läuft, baust du den Join drumherum, und das Ergebnis müsste sich lediglich durch die hinzugekommenen Leer-Monate unterscheiden.

Dem unsinnigen Ergebnis würde ich wie folgt auf die Spur zu kommen versuchen. Reduziere deine Test-Datenmenge auf das Nötigste, also so, dass die Funktionalität nachgewiesen werden kann beziehungsweise bei Problemen, so dass das Problem nachvollzogen werden kann.

"Unsinnig" heißt vermutlich, dass die Rechnung nicht stimmen kann. Lass die Gruppierung weg und dir die Rechenergebnisse der einzelnen Datensätze anzeigen. Gegebenenfalls füge Zwischenergebnisberechnungen (und die Spalten der Originalwerte) in die SELECT-Klausel ein, um deren Ergebnis zu prüfen, zum Beispiel multi = 'ja' und ISNULL(multi). Vielleicht kommt da ja was ungewolltes raus. Wenn nicht, wüsste ich jetzt auch nicht, wo die Ursache sonst noch zu suchen wäre.

Lo!

[Franco]

Hi,

Ich nehme an, du hast zu viele Datensätze, um mal eins der "unsinnigen" Ergebnisse und der dazugehörigen Datensätze zu zeigen.

das mit den wenigen Datensätzen habe ich ja nun schon mehrfach gepostet und das funktioniert ja auch prächtig.
Aber das, auf das ich das "kleine" übertragen will, past nicht.

Ich möchte dich dazu ermutigen, doch die Subselect-Lösung zu präferieren.

Die Subselects sind für mich bömische Dörfer.

Erstell dir zunächst die Query ohne den Join. Die kannst du ausgiebig testen.

Die habe ich schon (für ein anderes Fallbeispiel) vor Monaten erstellt und sie läuft einwandfrei. Nur die Leermonate fehlen eben.
Sobald ich aber den Join implementieren möchte, spinnt sie rum.

"Unsinnig" heißt vermutlich, dass die Rechnung nicht stimmen kann.

Für das Fallbeispiel "Ausgaben", ja.
Aber schon bei den normalen "Zähl-Fallbeispielen, also über die Agg.-Funkt. count spinnt meine Abfrage, sobald der Join ins Spiel kommt.

Gruß, Frank

[Vinzenz Mai]

Hallo,

"Unsinnig" heißt vermutlich, dass die Rechnung nicht stimmen kann.
Für das Fallbeispiel "Ausgaben", ja.
Aber schon bei den normalen "Zähl-Fallbeispielen, also über die Agg.-Funkt. count spinnt meine Abfrage, sobald der Join ins Spiel kommt.

eben aus diesem Grund dürfte das Subselect - auch wenn es jetzt noch ein Böhmisches Dorf für Dich ist - die bessere Lösung sein. Durch den Join vervielfachst Du gegebenenfalls die Anzahl der Datensätze in der Ergebnismenge und COUNT kommt somit zu einer höheren Anzahl als Du annimmst.

Nimmst Du ein Subselect, so wird die Datensatzanzahl nicht beeinflusst und Du kommst zum sowohl gewünschten als auch richtigen Ergebnis.

Freundliche Grüße

Vinzenz

[Franco]

Hi Vinzenz,

eben aus diesem Grund dürfte das Subselect - auch wenn es jetzt noch ein Böhmisches Dorf für Dich ist - die bessere Lösung sein. Durch den Join vervielfachst Du gegebenenfalls die Anzahl der Datensätze in der Ergebnismenge und COUNT kommt somit zu einer höheren Anzahl als Du annimmst.

Am Join komme ich doch ohnehin nicht vorbei, denn den brauche ich ja für die Monate.

Nimmst Du ein Subselect, so wird die Datensatzanzahl nicht beeinflusst und Du kommst zum sowohl gewünschten als auch richtigen Ergebnis.

Ich glaube, ich bin davon grade weiter entfernt als je zuvor.

Gruß, Frank

[dedlfix]

Hi!

Ich möchte dich dazu ermutigen, doch die Subselect-Lösung zu präferieren.
Die Subselects sind für mich bömische Dörfer.

Dabei ist das doch eigentlich recht einfach. Das vorher getestete Statement kommt in Klammern und anstelle des Namens der zu joinenden Tabelle. Oder anders gesagt, statt der Datenmenge aus der zweiten Tabelle wird die Ergebnismenge des Subselects verwendet.

Erstell dir zunächst die Query ohne den Join. Die kannst du ausgiebig testen.
Die habe ich schon (für ein anderes Fallbeispiel) vor Monaten erstellt und sie läuft einwandfrei. Nur die Leermonate fehlen eben.
Sobald ich aber den Join implementieren möchte, spinnt sie rum.

Auch in der Subselect-Variante?

"Unsinnig" heißt vermutlich, dass die Rechnung nicht stimmen kann.
Für das Fallbeispiel "Ausgaben", ja.
Aber schon bei den normalen "Zähl-Fallbeispielen, also über die Agg.-Funkt. count spinnt meine Abfrage, sobald der Join ins Spiel kommt.

Sollte mit dem Subselect eigentlich kein Problem sein.

Es gibt noch eine weitere Variante mit einem Zwischenschritt:

CREATE TEMPORARY TABLE monatsdaten SELECT [das meinedaten-Statement - das mit den fehlenden Monaten aber der richtigen Rechnung]
SELECT [wasauchimmer] FROM monate LEFT JOIN monatsdaten ON [nur die verknüpfungsbedingung] ORDER BY [jahr/monat] DESC

Das macht das selbe wie wenn statt monatsdaten das Subselect direkt notiert wäre - nur aufwendiger, dafür vielleicht einfacher zu durchschauen.

Lo!

[Franco]

Es gibt noch eine weitere Variante mit einem Zwischenschritt:

CREATE TEMPORARY TABLE monatsdaten SELECT [das meinedaten-Statement - das mit den fehlenden Monaten aber der richtigen Rechnung]
SELECT [wasauchimmer] FROM monate LEFT JOIN monatsdaten ON [nur die verknüpfungsbedingung] ORDER BY [jahr/monat] DESC

Das macht das selbe wie wenn statt monatsdaten das Subselect direkt notiert wäre - nur aufwendiger, dafür vielleicht einfacher zu durchschauen.

Hi dedlfix,

ja, das hört sich für mich durchschaubarer an. Und wer weiß schon, vielleicht durchschau ich ja danach auch den Subselect.

Ich versuchs morgen mal.

Danke auch fürs heutige Zulesen und Helfen an Dich.

Frank

[Franco]

Es gibt noch eine weitere Variante mit einem Zwischenschritt:

»

ja, das hört sich für mich durchschaubarer an. Und wer weiß schon, vielleicht durchschau ich ja danach auch den Subselect.

Ich versuchs morgen mal.

Hi,

also Variante 2 war wirklich easy zu verstehen und umzusetzen.
Nun kommen wir an ein ganz anderes Problem. Mein Script hat anscheinend nicht die Rechte, Tabellen zu erstellen bzw. zu löschen :-(

Das bedeutet einserseits, dass ich mich nun doch mit dem Subselect beschäftigen muss, andererseits aber auch, dass ich mir in Sachen tmp-Tabelle was anderes einfallen lassen muss, als die temporäre Tabelle mit den Monaten.

Punkt Subselect fällt mir schwer, ich scheine mit den Teilen auf Kriegsfuß zu stehen.
Der andere Punkt ist deutlich einfacher, da lege ich eine Tabelle an, die eben immer bleibt und mein Script muss dafür sorgen, dass immer die letzten 12 Monate drin stehen.

Bleibt mal wieder der Punkt "Subselect".

Grüße, Frank

[dedlfix]

Hi!

Nun kommen wir an ein ganz anderes Problem. Mein Script hat anscheinend nicht die Rechte, Tabellen zu erstellen bzw. zu löschen :-(

Das Script hat sowieso keine Rechte sondern der Benutzer, den du beim Connect angegeben hast. Wenn du nur den einen hast, wäre das fatal, denn mit welchem sonst legst du Tabellen an? Wenn du einen Nur-Lese-Benutzer hast, solltest du klären, ob der nicht auch das Recht CREATE TEMPORARY TABLE bekommen kann.

Das bedeutet einserseits, dass ich mich nun doch mit dem Subselect beschäftigen muss, andererseits aber auch, dass ich mir in Sachen tmp-Tabelle was anderes einfallen lassen muss, als die temporäre Tabelle mit den Monaten.

Da fällt mir nur eine umständliche Variante ein:

SELECT 1 AS month
UNION
SELECT 2
UNION
SELECT 3
...

Punkt Subselect fällt mir schwer, ich scheine mit den Teilen auf Kriegsfuß zu stehen.

Versteh ich nicht. Es ist doch lediglich der Name einer Tabelle durch das Subselect auszutauschen und ein Aliasname hinten dranzuhängen, wenn noch keiner da ist. Versuch doch mal konkret auf den Punkt zu bringen, wo genau dir noch Verständnis fehlt.

Der andere Punkt ist deutlich einfacher, da lege ich eine Tabelle an, die eben immer bleibt und mein Script muss dafür sorgen, dass immer die letzten 12 Monate drin stehen.

Also hast du ja einen Benutzer, der Tabellen anlegen kann. Und der kann keine temporären Tabellen anlegen?
Wie auch immer, es ist jedenfalls ungünstig mit einer festen Tabelle zu arbeiten, denn die kann von mehreren gleichzeitig verwendet werden. Da muss dann konkurrierender Zugriff abgesichert werden. Die Temp-Tabelle hat das Problem nicht, weil sie nur für die Verbindung des Client existiert, der sie angelegt hat. Es ist einfacher, das Recht zu besorgen, als diesen Workaround sicher zu gestalten.

Andererseits ändern sich die Zahlen von 1 bis 12 nie. Du müsstest als Verknüpfungsbedingung nur mit der Monatsnummer arbeiten und nicht noch das Jahr mit hinzunehmen. Das geht jedoch nur, wenn mindestens der aktuelle angefangene Monat oder der vom Vorjahr ausgeschlossen wird.

Lo!

[Franco]

Hi dedlfix,

Das Script hat sowieso keine Rechte sondern der Benutzer, den du beim Connect angegeben hast. Wenn du nur den einen hast, wäre das fatal, denn mit welchem sonst legst du Tabellen an? Wenn du einen Nur-Lese-Benutzer hast, solltest du klären, ob der nicht auch das Recht CREATE TEMPORARY TABLE bekommen kann.

Sorry, ich hab wohl Blödsinn geredet.
Denn in einem anderen Zusammenhang nutze ich bereits mit diesem Benutzer temporäre Tabellen.

Versteh ich nicht. Es ist doch lediglich der Name einer Tabelle durch das Subselect auszutauschen und ein Aliasname hinten dranzuhängen, wenn noch keiner da ist. Versuch doch mal konkret auf den Punkt zu bringen, wo genau dir noch Verständnis fehlt.

Ich weiß einfach nicht, welcher Teil der Query was efasst.
 Um beim Beispiel zu bleiben:

  
CREATE TEMPORARY TABLE `tmp` (  
`ID` INT( 2 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
`Monat` VARCHAR( 10 ) NOT NULL  
) ENGINE = MYISAM ;  
  
INSERT INTO `tmp` (`ID`, `Monat`) VALUES  
(1, '2010|10'),  
(2, '2010|09'),  
(3, '2010|08'),  
(4, '2010|07'),  
(5, '2010|06'),  
(6, '2010|05'),  
(7, '2010|04'),  
(8, '2010|03'),  
(9, '2010|02'),  
(10, '2010|01'),  
(11, '2009|12'),  
(12, '2009|11');  
  
  
CREATE TEMPORARY TABLE meinetabelle (  
  ID int(10),  
  Unixstamp varchar (10) NOT NULL,  
`loesch` INT( 1 ) NOT NULL ,  
`Status` VARCHAR( 10 ) NOT NULL  
);  
  
INSERT INTO meinetabelle VALUES  
(1, UNIX_TIMESTAMP(NOW()),'0','qq'),  
(2, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(3, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH),'0','qq'),  
(4, UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH),'0','qq');  
  
  
SELECT t.monat, FROM_UNIXTIME(m.Unixstamp, '%Y|%m') AS ym  
FROM tmp t  
LEFT JOIN  
(select count(ID) from meinetabelle  
where  
loesch != 1 AND  
Status = 'xx'  
) m //(kommt das Alias hinter die Klammer?)  
  
m ON t.Monat = m.ym  
GROUP BY t.Monat DESC;  

So geht es jedenfalls schonmal nicht, wenn ich mich erinnere. Ich sitze gerade am falschen Rechner, um das zu verifizieren, aber ich gkaube, mysql mckert dann, dass es m.Unixtimestamp nicht kennt.

Wie auch immer, es ist jedenfalls ungünstig mit einer festen Tabelle zu arbeiten, denn die kann von mehreren gleichzeitig verwendet werden.

Da muss dann konkurrierender Zugriff abgesichert werden.

Fänd ich nicht schwer, denn es müsste nur überprüft werden, ob der richtige Inhalt in der Tabelle steht. Wenn ja,passiert gar nichts. Wenn nein, wird er hineingeschrieben. und zwar genau maximal 1 x im Monat.
Aber Du hast recht, wenn mein User temp. Tabellen anlegen kann, sollte ich das auch nutzen.

Gruß, Frank

[dedlfix]

Hi!

Versteh ich nicht. Es ist doch lediglich der Name einer Tabelle durch das Subselect auszutauschen und ein Aliasname hinten dranzuhängen, wenn noch keiner da ist. Versuch doch mal konkret auf den Punkt zu bringen, wo genau dir noch Verständnis fehlt.
Ich weiß einfach nicht, welcher Teil der Query was efasst.

Eine Subquery ist (mit der Ausnahme der korrelierten) ein autarkes Gebilde. Sie liefert ein (skalares) einfaches Ergebnis oder eine Ergebnismenge. So eine Ergebnismenge bildet quasi eine temporäre Tabelle.

Um beim Beispiel zu bleiben:

SELECT t.monat, FROM_UNIXTIME(m.Unixstamp, '%Y|%m') AS ym

FROM tmp t
LEFT JOIN
(select count(ID) from meinetabelle
where
loesch != 1 AND
Status = 'xx'
) m //(kommt das Alias hinter die Klammer?)

m ON t.Monat = m.ym
GROUP BY t.Monat DESC;

  
Ja, der Aliasname kommt hinter die Klammer, aber nur einer. Der Klammerausdruck ersetzt einen direkten Tabellennamen. Die Klammern halten die Syntax dieses Ausdrucks zusammen und der Alias kommt wie beim Tabellennamen hintendran.  
  

> So geht es jedenfalls schonmal nicht, wenn ich mich erinnere. Ich sitze gerade am falschen Rechner, um das zu verifizieren, aber ich gkaube, mysql mckert dann, dass es m.Unixtimestamp nicht kennt.  
  
Stimmt, denn die Subquery liefert keine solche Spalte zurück - muss und kann sie auch gar nicht. Zudem willst du ja eigentlich nur die bereits vorhandene Abfrage um die nicht vorhandenen Monate erweitern. Deshalb muss alles in die Subquery rein, was das bisherige Ergebnis geliefert hat. Das heißt, die Gruppierung muss in die Subquery und sich dann auch auf ein Feld der Subquery-Tabelle beziehen.  
  
~~~sql
SELECT FROM_UNIXTIME(Unixstamp, '%Y|%m') ym, COUNT(*) anz  
FROM meinetabelle  
WHERE  
  loesch != 1 AND  
  Status = 'xx'  
GROUP BY FROM_UNIXTIME(Unixstamp, '%Y|%m')

Das ist die Subquery, und sie muss das richtige Ergebnis ohne Leermonate liefern. Und an dieser Stelle gibts dann auch keinen Timestamp mehr sondern nur noch den formatierten Monatsstring.

SELECT t.monat, s.anz  
FROM tmp t  
LEFT JOIN  
([die Subquery von oben]) s  
  ON t.Monat = s.ym  
ORDER BY t.Monat DESC

Eine Sortierung braucht es in der Subquery nicht, stattdessen wird das Gesamtergebnis sortiert.

Wie auch immer, es ist jedenfalls ungünstig mit einer festen Tabelle zu arbeiten, denn die kann von mehreren gleichzeitig verwendet werden.
Da muss dann konkurrierender Zugriff abgesichert werden.
Fänd ich nicht schwer, denn es müsste nur überprüft werden, ob der richtige Inhalt in der Tabelle steht. Wenn ja,passiert gar nichts. Wenn nein, wird er hineingeschrieben. und zwar genau maximal 1 x im Monat.

Ok, in dem Fall kann es fast egal sein, ob da was kollidiert. Lediglich Zugriffe zwischen Löschen und erneutem Einfügen ergeben Mist. Wenn jeder Client hingegen eigene Zeiträume abfragt, kann man das nicht mehr einfach so auf die leichte Schulter nehmen.

Aber Du hast recht, wenn mein User temp. Tabellen anlegen kann, sollte ich das auch nutzen.

Auf jeden Fall. Das ist schon durch die implizite Bindung der Temp-Tabelle an die Client-Session bestens abgeschirmt.

P.S. Die Temp-Tabelle für die Monate benötigt keine ID-Spalte, da du sie nirgends verwendest.

Lo!

[Franco]

Hi dedlfix,

Eine Subquery ist (mit der Ausnahme der korrelierten) ein autarkes Gebilde. Sie liefert ein (skalares) einfaches Ergebnis oder eine Ergebnismenge. So eine Ergebnismenge bildet quasi eine temporäre Tabelle.

Die ersten 9 Pfennig des Groschens fallen gerade hier. Also ist das quasi tatsächlich identisch mit der temporären Tabelle.

Zudem willst du ja eigentlich nur die bereits vorhandene Abfrage um die nicht vorhandenen Monate erweitern. Deshalb muss alles in die Subquery rein, was das bisherige Ergebnis geliefert hat. Das heißt, die Gruppierung muss in die Subquery und sich dann auch auf ein Feld der Subquery-Tabelle beziehen.

Ja, ok. Die Subquery ist die eigentliche Abfrage und ich gehe dann mit ihr genauso um, als wenn ich eine Tabelle zum joinen hätte. Die Hauptquery ist bietet mir die Monate und - analog zum normalen JOIN mit einer anderen Tabelle - eine Spalte dieser "virtuellen" Tabelle.

Ja, und wie nicht anders zu erwarten liefern Deine beiden Queries exakt die Queries, die ich auch habe, wenn auch einmal auf eine temp.Tabelle bezogen und zum zweiten im anschließenden LEFT JOIN der beiden temporären Tabellen.

Mist. Ich wußte immer schon, dass ich besonders schnell lerne, wenn ich Code zum Vergleich habe. So im Vergleich zwischen den beiden Läösungen erscheint mir nun auch der Subselect völlig easy und nachvollziehbar. Ich muß aber einräumen, dass nur der Subselect ( ohne Deinen Zwischenschritt der 2. temporären Tabell zum  JOIN) mir auch der Code zum Verstehen alleine nicht gereicht hätte. Deshalb besonderen Dank für diese "Eselsbrücke".

Nun noch eine Frage zur Performance. Welche der Lösungen ist performanter. Ich habe in mein Beispiel ja nun bereits die Löung der 2 temporären Tabellen adaptiert.
Ich würde aber zu Gunsten besserer Performance nochmal auf die Subselect-Lösung umstricken.

Was meinst Du?

Ok, in dem Fall kann es fast egal sein, ob da was kollidiert. Lediglich Zugriffe zwischen Löschen und erneutem Einfügen ergeben Mist.

Korrekt. Das sehe ich genauso.

Wenn jeder Client hingegen eigene Zeiträume abfragt, kann man das nicht mehr einfach so auf die leichte Schulter nehmen.

Stimmt.

Auf jeden Fall. Das ist schon durch die implizite Bindung der Temp-Tabelle an die Client-Session bestens abgeschirmt.

Ist schon genau so umgesetzt. Ich hatte da heute morgen einfach ein Problem eines fehlenden Tabellenpräfixes, das mich irritiert hatte.

Wenn das bis hierher für Dich einfach war, wird es Dich freuen, dass sich für mich vorhin das Problem nochmal ein wenig verschärft hat, weil im Beispiel mit den zu addierenden und zu multiplizierenden Werten mein Timestamp in der Tabelle der falsche ist. Der korrekte Timestamp ist aber nicht verloren, sondern steht nur in einer anderen Tabelle, die ich über einen oder zwei JOINS ansprechen könnte.
Bevor ich aber frage, möchte ich erst ein wenig selber versuchen, der Lösung näher zu kommen.

Wenn ich das nicht schaffe, würde ich mich natürlich über weitere Hilfe freuen.

Bis dhin erstmal wieder vielen Dank

Frank

[Franco]

Bevor ich aber frage, möchte ich erst ein wenig selber versuchen, der Lösung näher zu kommen.

»»

Der Join war kein Problem.
Aber die Antwort von mysql war auch nicht gerade ermunternd.
Ich bae aber hierzu einen neuen Thread geöffnet, weil es mit dem Ursprungsthema wirklkich nicht mehr viel gemeinsam hat.

Gruß, Frank

[dedlfix]

Hi!

Nun noch eine Frage zur Performance. Welche der Lösungen ist performanter. Ich habe in mein Beispiel ja nun bereits die Löung der 2 temporären Tabellen adaptiert.
Ich würde aber zu Gunsten besserer Performance nochmal auf die Subselect-Lösung umstricken.
Was meinst Du?

Eine Lösung in einem Statement ist anzunehmenderweise besser, weil hier der Optimizer noch was rausholen kann, wenn es was zu holen gibt. Bei der Temp-Tabelle kann er das nicht, denn die könnte ja auch noch für andere Querys vorgesehen sein. Da kann er nur die Statements isoliert abarbeiten.

Was auch die Performance belastet, sind Funktionsaufrufe - also nicht unbedingt dass sie aufgerufen wird, sondern dass jeder Wert in der Tabellenspalte einzeln durchzurechnen ist. Kann der Feldinhalt direkt genommen werden, so kann dieser Zugriff eventuell durch einem Index beschleunigt werden. Ich weiß nicht, wie effizient das Umrechnen eines Timestamps im Gegensatz zu einem DateTime-Wert ist, kann mir aber vorstellen, dass das Extrahieren von Information aus einem DateTime-Wert einfacher anzustellen geht, als erst die Sekunden in ein Datum umrechnen zu müssen. Vielleicht lässt sich mit einem DateTime-Feld und EXTRACT(YEAR_MONTH FROM feld) ein schnellerer Zugriff hinbekommen. Dazu müsstest du aber von einem Timestamp im Stringfeld auf ein DATETIME umsteigen. - Beim Join bringt dir das übrigens nichts mehr, denn da sind nur noch 12 Werte vorhanden, aber beim Gruppieren kann dir das Punkte bringen.

Bevor du jedoch solche Performance-Verbesserungen versuchst, solltest du nach Gefühl oder mit Messung feststellen, ob dir die derzeitige Laufzeit zu lang ist. Wenn ja, dann miss exakt nach und vergleiche den Wert mit einer Messung nach dem Optimierungsversuch.

Lo!

[Franco]

SELECT *, FROM_UNIXTIME(m.Unixstamp, '%Y|%m')
FROM tmp t
LEFT JOIN
-- Du musst Deinem Subselect einen Aliasnamen geben

An welcher Stelle denn?

Gruß, Frank

[Ilja]

moin,

Bleibt mein LEFT JOIN, der nicht so will, wie ich will.

Hast Du da auch noch eine Idee?

mein kristallkugel sagt mir, du hast in der where klausel eine spalte drinne, die zur tabelle gehört, die du mit dem LEFT JOIN einbindest. poste doch mal das komplette sql, vor allem mit WHERE klausel.

Ilja

[dedlfix]

Hi!

Alles klar. Ich hatte die ganze Zeit einen kleinen Denkfejhler, weil ich dachte, Du wolltest tatsächlich die Zahlen 1-12 hineinschreiben. Das geht wohl, aber nicht in meinem Fall, da ich rükwirkend auch über einen Jahreswechsel hinweg die Zahlen haben möchte.

Die Monatszahlen der letzten 12 Monate bleiben auch über einen Jahreswechsel die Zahlen 1 bis 12. Das du zum Sortieren dazu auch noch das Jahr benötigst, ist für den Join nicht weiter relevant. Dem reichen die Zahlen, wenn du sie mit den nackigen Monatsnummern verknüpfst.

Mein Problem ist leider, dass mein Vergleichswert der "rechten Tabelle" sich so gar nicht vergleichen lassen will.

Achwas, ist nur eine Frage der richtigen Formel.

ON t.Monat = (und hier steht mein FROM_UNIXTIME-Konstrukt), wobei ich nicht sicher weiß, ob ich da nicht einen Syntaxfehler eingebaut habe.

Syntaxfehler werden bemängelt. Logische Fehler kannst du mit Kontrollausgaben prüfen. Bau den Ausdruck in die SELECT-Klausel ein, dann siehst du, was konkret rauskommt und ob das mit deiner Vorstellung übereinstimmt.

WHEN 'NULL'
wandelt meine NULL-Werte nicht um.

NULL ist kein String. Außerdem bin ich mir nicht sicher, ob das CASE auf diese Weise NULLs findet. Ein Vergleich mit = und NULL-Werten ergibt immer NULL als Ergebnis, weswegen man mit IS NULL testen muss. Für deine drei Bedingungen kommst du kürzer mit IF() und IFNULL() hin, wobei das IFNULL speziell NULLs korrekt auswerten kann.

Lo!

[Franco]

Hi,

Syntaxfehler werden bemängelt. Logische Fehler kannst du mit Kontrollausgaben prüfen. Bau den Ausdruck in die SELECT-Klausel ein, dann siehst du, was konkret rauskommt und ob das mit deiner Vorstellung übereinstimmt.

Ja. Es passt genau zu meiner Vorstellung.
Ich bekomme die Monate raus, die Daten enthalten, und zwar genau in dem Format Y|m. Und so stehen sie ja auch in der temporären Tabelle.

Dennoch bleiben als Ergebnissmenge der Gesamtabfrage derzeit nur die Monate der Temp-Tabelle übrig, in der es in der m.Tabelle auch Daten gibt.

Gruß, Frank

[dedlfix]

Hi!

Dennoch bleiben als Ergebnissmenge der Gesamtabfrage derzeit nur die Monate der Temp-Tabelle übrig, in der es in der m.Tabelle auch Daten gibt.

Das kann eigentlich nur passieren, wenn das Wörtchen LEFT fehlt.

Lo!

[Franco]

• Erstell eine (temporäre) Tabelle mit den Zahlen von 1 bis 12. Bilde einen Left-Join mit der anderen Ergebnismenge.

Aber mit welcher Vergleichsspalte denn?

$query="SELECT FROM_UNIXTIME(timestamp, '%Y%|%m') AS ym, sum(Menge*Preis*berechnung*(1-rabatt) ) AS count
LEFT JOIN tmp ON ???=???
FROM ausgaben
WHERE ...
GROUP BY ym DESC LIMIT 12
";

Gruß,Frank

[dedlfix]

Hi!

• Erstell eine (temporäre) Tabelle mit den Zahlen von 1 bis 12. Bilde einen Left-Join mit der anderen Ergebnismenge.
  Aber mit welcher Vergleichsspalte denn?

Ich denke, du willst Ergebnisse für jeden Monat haben, also musst du den Outer-Join auf den Monat anwenden.

$query="SELECT FROM_UNIXTIME(timestamp, '%Y%|%m') AS ym, sum(Menge*Preis*berechnung*(1-rabatt) ) AS count
LEFT JOIN tmp ON ???=???
FROM ausgaben

Erst FROM, dann JOIN. Wenn du dich mit Joins und dem Sinn eines Outer-Joins nicht auskennst, solltest du die Einführung in Joins lesen. Neben der Klauselreihenfolge ist auch die Richtung falsch. Die temporäre Tabelle muss "links" stehen. Alternativ auch rechts, dann aber mit einem RIGHT JOIN.

Und lass bitte den PHP-Code weg, wenn es sich um ein Datenbankproblem handelt.

Lo!

[Franco]

Hi,

Ich denke, du willst Ergebnisse für jeden Monat haben, also musst du den Outer-Join auf den Monat anwenden.

Also dann nützen mit ja die Zahlen 1-12 nichts. Ich dachte, Du hättest da einen besonderen Kniff.
Du meinst aber, ich soll eine temp. Tabelle mit den Monaten erstellen, also 09|2010, 08|2010 usw. und der Outer Join wird dann über das Vergleichspaar Monat ausgeführt.

Dann glaube ich das nämlich durchaus zu verstehen.

Gruß, Frank

[dedlfix]

Hi!

Du meinst aber, ich soll eine temp. Tabelle mit den Monaten erstellen, also 09|2010, 08|2010 usw. und der Outer Join wird dann über das Vergleichspaar Monat ausgeführt.

Wenn die Monate sich in einem Datumswert "verstecken", dann solltest du sie daraus extrahieren, um sie mit den Zahlen 1 bis 12 verknüpfen zu können. Für die Gruppierung machst du das ja auch schon nach diesem Prinzip.

Lo!

[Franco]

Ich denke, du willst Ergebnisse für jeden Monat haben, also musst du den Outer-Join auf den Monat anwenden.

Ich bekomme trotzdem nur die Monate angezeigt, die Daten beinhalten:

Aus einer Test-db:

SELECT t.Monat, COUNT(m.TestID) AS count  
FROM tmp t  
LEFT JOIN meinetabelle m  
ON t.Monat = FROM_UNIXTIME(m.Timestamp, '%Y%|%m')  
WHERE...  
GROUP BY t.Monat DESC 

Tabelle tmp:
1-2010|09
2-2010|08
3-2010|07
4-2010|06
...

Was habe ichg falsch gemacht?

Gruß, Frank

[dedlfix]

Hi!

Ich denke, du willst Ergebnisse für jeden Monat haben, also musst du den Outer-Join auf den Monat anwenden.
Ich bekomme trotzdem nur die Monate angezeigt, die Daten beinhalten:

Kann ich nicht nachvollziehen.

CREATE TEMPORARY TABLE y (  
  ID int(10),  
  d int NOT NULL  
);  
  
INSERT INTO y VALUES  
(1, UNIX_TIMESTAMP(NOW())),  
(2, UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH));  
  
CREATE TEMPORARY TABLE m (  
  m VARCHAR(10));  
  
INSERT m VALUES ('2010|07'),('2010|08'),('2010|09');  
  
SELECT *, FROM_UNIXTIME(d, '%Y|%m')  
FROM m  
LEFT JOIN y ON FROM_UNIXTIME(d, '%Y|%m') = m  
GROUP BY m DESC;

Ergibt bei mir 3 Datensätze.

Was habe ich falsch gemacht?

Kann ich dir nicht beantworten, weil mir dazu Details fehlen. Ich kenne von deiner Tabelle nicht den Aufbau und kann nur ahnen, dass du da Timestamp in Integerfeldern abgelegt hast. Den einzigen Fehler den ich sehe, ist das überflüssige % vor dem | im Formatstring, was aber keine Auswirkung hat.

Lo!

[Franco]

Hi,

Ergibt bei mir 3 Datensätze.

kann ich bestätigen.

Was habe ich falsch gemacht?

Kann ich dir nicht beantworten, weil mir dazu Details fehlen. Ich kenne von deiner Tabelle nicht den Aufbau und kann nur ahnen, dass du da Timestamp in Integerfeldern abgelegt hast. Den einzigen Fehler den ich sehe, ist das überflüssige % vor dem | im Formatstring, was aber keine Auswirkung hat.

Nein, mein Timestamp liegt in einem varchar-Feld.

Hm :-(

Gruß, Frank