MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > SQL
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 01.08.2018, 08:51   #1
hcscherzer
MOF Guru
MOF Guru
Standard MySQL - Jahreasuswertung

Ich habe gerade einen Knoten im Hirn.
Vielleicht kann ihn jemand lösen?

Für eine Jahresauswertung habe ich forlgede Query:
Code:

select p.products_id, year(o.date_purchased) yp, sum(op.products_quantity) m from
xt_products p
inner join xt_orders_products op on p.products_id = op.products_id
inner join xt_orders o on op.orders_id = o.orders_id
where year(o.date_purchased) >= year(current_timestamp) - 2
group by p.products_id, year(o.date_purchased);
Da bekomme ich alle Verkäufe des aktuellen und der letzten beiden Jahre.

Jetzt möchte ich aber die Jahre, in denen ein Artikel nicht verkauft worden ist, zusätzlich aufführen mit der Menge 0.

Code:

select year(current_timestamp) - 2 y2, year(current_timestamp) - 1 y1, year(current_timestamp) y0;
liefert mir diese drei Jahre.
Aber ich schaffe es nicht, sie zu der obigen Abfrage hinzuzujoinen ...

/Edit:
Ich stelle gerade fest, der Ansatz, die fraglichen Jahre in drei Spalten auszugeben, ist falsch.
Es müssten drei Zeilen (Datensätze) sein.
Muss ich dafür eine Hilfstabelle mit den fraglichen Jahren generieren?
Oder kann ich diese drei Datensätze auch irgendwie dynamisch herbeizaubern?

__________________

Freundlichen Gruß
Hans-Christian
-----------------------------------------
Oft erwünscht, selten beachtet: nach Erledigung des Problems den Thread als erledigt zu markieren
-----------------------------------------
Ich möchte nur Mitglied in einem Verein sein, der Leute wie mich nicht als Mitglied aufnimmt (Groucho Marx).
-----------------------------------------
Ab sofort regelmässig: MOF Stammtisch in Bremen. Näheres hier.

Geändert von hcscherzer (01.08.2018 um 09:12 Uhr).
hcscherzer ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.08.2018, 11:30   #2
Josef P.
MOF Guru
MOF Guru
Standard

Hallo!

Nur Luftcode:
Code:

select
     X.products_id, Cal.Y yp, sum(X.products_quantity) m 
from
   (
      select 
           year(current_timestamp) - 2 as Y, 
           DATEFROMPARTS(year(current_timestamp)-2, 1, 1) as Von,
           DATEFROMPARTS(year(current_timestamp)-1, 1, 1) as BisEx
      union all
      select 
           year(current_timestamp) - 1 as Y, 
           DATEFROMPARTS(year(current_timestamp)-1, 1, 1) as Von,
           DATEFROMPARTS(year(current_timestamp), 1, 1) as BisEx
      union all
      select
           year(current_timestamp) as Y, 
           DATEFROMPARTS(year(current_timestamp), 1, 1) as Von,
           DATEFROMPARTS(year(current_timestamp)+1, 1, 1) as BisEx
   ) Cal
   left join
   (
   select p.products_id, o.date_purchased, op.products_quantity
   from xt_products p
          inner join xt_orders_products op on p.products_id = op.products_id
          inner join xt_orders o on op.orders_id = o.orders_id
   where o.date_purchased >= DATEFROMPARTS(year(current_timestamp) - 2, 1, 1)
   ) X
   ON X.date_purchased >= Cal.Von and X.date_purchased < Cal.BisEx
group by 
   X.products_id, Cal.Y
Anm.: DATEFROMPARTS bitte für MySQL anpassen (ist der Funtionsname von T-SQL)

Praktischer als die UNION-Anweisung wäre die Verwendung eine "Zahlentabelle"
Code:

..
from
   (
      select 
          year(current_timestamp) - ZT.Zahl as Y, 
          DATEFROMPARTS(year(current_timestamp) - ZT.Zahl , 1, 1) as Von,
          DATEFROMPARTS(year(current_timestamp) - ZT.Zahl + 1 , 1, 1) as BisEx
     from
         ZahlenTabelleDieMit0Beginnt ZT
     where
          ZT.Zahl between 0 and 2
   ) Cal
   left join
...
LG
Josef

Geändert von Josef P. (01.08.2018 um 11:34 Uhr).
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.08.2018, 13:33   #3
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

@Josef:

Danke für die Anregung ... auf die UNION hätte ich auch selbst kommen können.
Aber wenn das Hirn nicht will, dann will es nicht - außerdem ist mir zu heiß.

Warum fügst Du noch die beiden Felder von und BisEx ein? Das verstehe ich nicht. Wenn ich die Verknüpfung über das Jahr des Verkaufsdatums anlege, dann sorgt doch der Join dafür, dass keine früheren Jahre reinkommen.

Ich hab es jetzt mal so probiert:
Code:

select x.products_id, c.y, sum(ifnull(x.q, 0)) M
from
(select year(current_timestamp) - 2 y
 union select year(current_timestamp) - 1 
 union select year(current_timestamp)) c
left join
(select p.products_id, year(o.date_purchased) y, op.products_quantity q 
from xt_products p 
inner join xt_orders_products op on p.products_id = op.products_id
inner join xt_orders o on op.orders_id = o.orders_id) x
on x.y = c.y 
group by x.products_id, c.y;
Allerdings bringt er mir nach wie vor die "leeren" Jahre nicht.

__________________

Freundlichen Gruß
Hans-Christian
-----------------------------------------
Oft erwünscht, selten beachtet: nach Erledigung des Problems den Thread als erledigt zu markieren
-----------------------------------------
Ich möchte nur Mitglied in einem Verein sein, der Leute wie mich nicht als Mitglied aufnimmt (Groucho Marx).
-----------------------------------------
Ab sofort regelmässig: MOF Stammtisch in Bremen. Näheres hier.
hcscherzer ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.08.2018, 13:44   #4
Josef P.
MOF Guru
MOF Guru
Standard

Hallo!

Ich neige dazu Index-freundliche SQL-Anweisungen zu schreiben.

Code:

ON X.date_purchased >= Cal.Von and X.date_purchased < Cal.BisEx
vs.
ON Year(X.date_purchased) = Cal.Y
Die erste Variante macht eine Indexsuche auf date_purchased möglich.


Zitat:

Allerdings bringt er mir nach wie vor die "leeren" Jahre nicht.

.. in Verbindung zu products_id, oder? (Für products_id = null sollte es bereits die Jahre geben.

Du musst dann noch per cross joine die wErte von products_id auf die linke Seite vom Join bringen und im ON-Ausdruck berücksichtigen.

Code:

...
from
   (
   Cal
   cross join
   ProdIdList PL
   )
   left join
   (
      ....
   ) X ON X.products_id = PL.products_id AND ...
LG
Josef

Geändert von Josef P. (01.08.2018 um 13:48 Uhr).
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.08.2018, 14:12   #5
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Ah ja, danke für den Hinweis.

Leider gibt es in MySQL weder ein DATEFROMPARTS() noch ein DateSerial().
Es ist nur so zu lösen:
Code:

STR_TO_DATE(CONCAT_WS('-', 2006, 11, 22), '%Y-%c-%e');
Na ja, ich muss in der Auswertung ja nicht nach einzelnen Werten von date_purchased suchen und brauche nur die Jahre.

Die Artikelnummer auf die linke Seite bringen:
da wäre ich nie drauf gekommen.

Bei über 5.000 Artikeln und knapp 50.000 Verkaufspositionen braucht die Abfrage aber jetzt ein Weilchen ...

__________________

Freundlichen Gruß
Hans-Christian
-----------------------------------------
Oft erwünscht, selten beachtet: nach Erledigung des Problems den Thread als erledigt zu markieren
-----------------------------------------
Ich möchte nur Mitglied in einem Verein sein, der Leute wie mich nicht als Mitglied aufnimmt (Groucho Marx).
-----------------------------------------
Ab sofort regelmässig: MOF Stammtisch in Bremen. Näheres hier.
hcscherzer ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.08.2018, 14:38   #6
Josef P.
MOF Guru
MOF Guru
Standard

Zitat:

... braucht die Abfrage aber jetzt ein Weilchen ...

.. aber nur wenn keine passenden Indizes genutzt werden können.
50k sind keine große Menge.

Zitat:

Na ja, ich muss in der Auswertung ja nicht nach einzelnen Werten von date_purchased suchen und brauche nur die Jahre.

Du nimmst durch den Join auf das berechnete Datenfeld dem DBMS die Möglichkeit einen Index auf date_purchased zu verwenden.

LG
Josef
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.08.2018, 14:50   #7
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Da die zu Grunde liegende DB ein Fremdprodukt ist, habe ich leider keinen Einfluss auf die Gestaltung der Struktur.

Aber das war mir schon klar, dass es ohne Index und mit der Verknüpfung über die Funktion Year() etwas dauern könnte ...

Nachdem ich nach ein paar Versuchen der Optimierung jeweils ein paar Minuten lang "Kaffee getrunken" habe vor dem Bildschirm, hab ich das mal abgebrochen ...

Und sowohl das Ergebnis der Query mit den Artikelnummern und den Auswertungsjahren in eine und die kumulierten Verkaufszahlen in eine andere temporäre Tabelle geschrieben (was jeweils 300 ms dauerte).

Die Ausführung des LEFT JOIN zwischen diesen beiden ergab dann das erwünschte Ergebnis. Und es dauerte knapp 9 s, bis es da war.

Ziel für heute erreicht.
Merci viele Male !

__________________

Freundlichen Gruß
Hans-Christian
-----------------------------------------
Oft erwünscht, selten beachtet: nach Erledigung des Problems den Thread als erledigt zu markieren
-----------------------------------------
Ich möchte nur Mitglied in einem Verein sein, der Leute wie mich nicht als Mitglied aufnimmt (Groucho Marx).
-----------------------------------------
Ab sofort regelmässig: MOF Stammtisch in Bremen. Näheres hier.
hcscherzer ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 01.08.2018, 15:17   #8
Josef P.
MOF Guru
MOF Guru
Standard

Wenn du Temp-Tabelle verwendest, musst du doch dann nur in der Temp-Tabelle mit den Verkaufszahlen (auf der rechten join-Seite) einen passenden Index ergänzen.

Falls du Zeit findest, würde es mich interessieren, ob die anfangs von mir vorgeschlagene Variante mit dem Datumsfilter statt dem Filter auf das berechnete Jahr schneller laufen würde. (Gesamte Abfrage vom DBMS ohne Temp-Tabelle ausführen lassen.)

Anm.: Ähnliches führe ich im MSSQL regelmäßig aus, ohne lange Wartezeiten zu erhalten.

LG
Josef
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 02.08.2018, 15:53   #9
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Zitat:

Falls du Zeit findest

War selber neugierig.
Deine #2a braucht zwischen 832 ms und 1'09 s
Meine #3 braucht zwischen 804 ms und 1'02 s

Jetzt habe ich mal testweise einen Index auf date_purchased gelegt.
Dann braucht Deine aber auch noch 813 ms ... im besten Fall ... ich hab aber auch bis zu 1'11 s gesehen.

Irritiert schon etwas, dass die Ausführung mit Datumsvergleich fast genauso lang braucht wie die mit der Verknüpfung über das Jahr.

Und dass die Index-Erstellung fast nichts bringt ist echt ein Rätsel.
Könnte es sein, dass MySQL den Index beim JOIN nicht verwendet?

Vielleicht exportiere ich die Daten mal in eine MSSQL und teste es da.

Um diese Mengen geht es dabei:
xt_orders = 27.292
xt_orders_products = 44.681
xt_products = 5.447
und in der kumulierten Ergebnismenge 7.647 Datensätze

__________________

Freundlichen Gruß
Hans-Christian
-----------------------------------------
Oft erwünscht, selten beachtet: nach Erledigung des Problems den Thread als erledigt zu markieren
-----------------------------------------
Ich möchte nur Mitglied in einem Verein sein, der Leute wie mich nicht als Mitglied aufnimmt (Groucho Marx).
-----------------------------------------
Ab sofort regelmässig: MOF Stammtisch in Bremen. Näheres hier.
hcscherzer ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 09:15 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.