Join-Frage

[Ykcim]

Hallo Zusammen,

ich finde die Beschreibung einer Abfrage immer schwierig, aber ich versuche es verständlich zu schreiben...

Ich habe eine Tabelle, in der Kundenbedarf mit Datum und verantwortlicher Mitarbeiter drinstehen. Es gibt auf viele Kundenbedarfe für ein ein Teil, die von unterschiedlichen Kunden und damit auch von unterschiedlichen Mitarbeiter bearbeitet werden.

Ich möchte eine Abfrage erstellen, in der ich alle Kundenbedarfe eines Artikels nach Kalenderwochen summiert darstellen kann. Dabei sollen nur die Bedarfe angezeigt werden, von Artikeln, für die der jeweilige Benutzer verantwortlich ist. Aber von diesen Artikeln sollen auch die Bedarfe anderer Kunden angezeigt werden.

Bei dieser Abfrage raucht der SQL-Server leider ab, bzw. bringt nach langer Zeit einfach kein Ergebnis:

Delphi-Quellcode:

select 0 as Nr,
       o.watenr as ArtikelNr,
       sum(o.ltrest) as Menge ,
       case when week(o.ltlite,3)<10 then concat(year(o.ltlite),'0',week(o.ltlite,3)) else concat(year(o.ltlite),week(o.ltlite,3)) end as KW,
       o.tebez1 as ArtikelBez
from liefersituation o
where (o.ltlite >= current_date and week(o.ltlite,3)<week(current_date,3)+25 )
and o.watenr in (select a.watenr from liefersituation a where a.lpdivk='SSC'group by a.watenr)
group by o.watenr, kw
order by o.watenr, kw

Diese Abfrage bringt zwar nach 23 Sekunden ein Ergebnis, aber hier stimmen die Mengen nicht. Sie ist immer ein Vielfaches von der wirklichen Menge, sodass ich glaube, dass die Berechnung so oft durchgeführt, wie Anzahl einzelner Kundenbedarfe vorhanden sind...

Delphi-Quellcode:

select 0 as Nr,
       a.watenr as Artikel,
       sum(o.ltrest) as Menge ,
       case when week(o.ltlite,3)<10 then concat(year(o.ltlite),'0',week(o.ltlite,3)) else concat(year(o.ltlite),week(o.ltlite,3)) end as KW,
       o.tebez1 as ArtikelBez
from liefersituation o
cross join liefersituation a on o.watenr=a.watenr and a.lpdivk='SSC'
where (o.ltlite >= current_date and week(o.ltlite,3)<week(current_date,3)+25 )
group by o.watenr, kw
order by o.watenr, kw

Ich habe mal ein Bild vom Ergebnis der Abfrage hochgeladen. Die eingekreiste Menge ist um den Factor 10 zu hoch, die Mengen des nachfolgenden Artikel sogar um den Faktor 20...

Hat jemand eine Idee für mich, wie ich das lösen kann?

Vielen Dank
Patrick

[Delphi.Narium]

Warum Cross Join? Willst Du das wirklich? https://www.w3resource.com/sql/joins/cross-join.php

Achtung, alte SQL-Syntax, da für mich leichter lesbar:

SQL-Code:

select 
  0 as Nr,
  a.watenr as Artikel,
  sum(o.ltrest) as Menge,
  case 
    when week(o.ltlite,3) < 10 then concat(year(o.ltlite),'0',week(o.ltlite,3)) 
    else concat(year(o.ltlite),week(o.ltlite,3)) 
  end as KW,
  o.tebez1 as ArtikelBez
from 
  liefersituation o, 
  liefersituation a 
where o.ltlite >= current_date 
and   week(o.ltlite,3) < week(current_date,3) + 25)
and   o.watenr = a.watenr 
and   a.lpdivk = 'SSC'
group by 
  o.watenr, 
  kw
order by 
  o.watenr, 
  kw

Kommt das Deinem Wunsch näher?

Du möchtest zu den Daten aus liefersituation, die ein Datum von heute oder in der Zukunft haben und deren Woche innerhalb des nächsten halben Jahres liegt, alle weiteren Datensätze aus liefersituation bekommen, bei denen lpdivk gleich 'SSC' ist und die über die gleiche watenr verfügen?

Oder eventuell dashier?

SQL-Code:

select 
  0 as Nr,
  o.watenr as ArtikelNr,
  sum(o.ltrest) as Menge,
  case 
    when week(o.ltlite,3) < 10 then concat(year(o.ltlite),'0',week(o.ltlite,3)) 
    else concat(year(o.ltlite),week(o.ltlite,3))
  end as KW,
  o.tebez1 as ArtikelBez
from liefersituation o
where o.ltlite >= current_date 
and week(o.ltlite,3) < week(current_date,3) + 25
and exists 
(
  select 1 from liefersituation a 
  where a.watenr = o.watenr
  and   a.lpdivk = 'SSC'
)
group by 
  o.watenr, 
  kw
order by 
  o.watenr, 
  kw

Aber sicher bin ich mir da nicht.

[Ykcim]

Vielen Dank für die Hilfe. Leider hat die erste Lösung den gleichen Fehler, dass die Menge zu hoch ist und die zweite brachte nach über 90 Sekunden noch kein Ergebnis...
Hat noch jemand eine weitere Idee?

Vielen Dank
Patrick

[stifflersmom]

Vielelciht so:

Code:

select 0 as Nr,
       a.watenr as Artikel,
       sum(o.ltrest) as Menge ,
       case when week(o.ltlite,3)<10 then concat(year(o.ltlite),'0',week(o.ltlite,3)) else concat(year(o.ltlite),week(o.ltlite,3)) end as KW,
       o.tebez1 as ArtikelBez
from liefersituation o
join (select watenr from liefersituation where lpdivk='SSC') a on o.watenr=a.watenr
where (o.ltlite >= current_date and week(o.ltlite,3)<week(current_date,3)+25 )
group by o.watenr, kw
order by o.watenr, kw

Ist sicher gestellt, dass:
- auf liefersituation.watenr ein Index vorhanden ist
- auf liefersituation.watenr ein Index vorhanden ist
- auf liefersituation.lipdivk ein Index vorhanden ist
?

[Delphi.Narium]

@Ykcim

Um wieviel zu hoch?

4-fache, 7849-fache, irgendwie bei jedem Satz anders falsch? Immer einmal mehr als erwartet?

Etwas präzisere Fehlermeldungen wären hilfreich.

Grob: Zu hohe Summen bei der Gruppierung im SQL deuten auf eine zu geringe Einschränkung in der Where-Bedingung hin.

90 Sekunden, ja und? Wieso muss es schneller sein?

Datenmenge?

5 Sätze, 5 Millionen Sätze, 5 Millarden Sätze?

Die Zeitangabe ist ohne Mengenangabe absolut wertlos.

Tabellenstruktur?
Alles erforderliche an Indizes vorhanden?
Oder immer Full-Table-Scan?

Ich fragte dashier:

Zitat:

Du möchtest zu den Daten aus liefersituation, die ein Datum von heute oder in der Zukunft haben und deren Woche innerhalb des nächsten halben Jahres liegt, alle weiteren Datensätze aus liefersituation bekommen, bei denen lpdivk gleich 'SSC' ist und die über die gleiche watenr verfügen?

Was nun? Richtig geraten von mir, falsch geraten oder irgendwo dazwischen?

Ein Vielfaches der Menge bei Summenberechnungen liegt fast immer ein einer unpräzisen Formulierung der Aufgabenstellung bezüglich der zu summierenden Datenmenge oder an einer fehlerhaften Umsetzung der Aufgabenstellung.

Momentan bin ich mit den vorliegenden Informationen nicht in der Lage zu entscheiden, was hier die Ursache sein könnte: Unpräzise Aufgabenstellung oder fehlerhafte Umsetzung.

[himitsu]

Passende Indize sind aber vorhanden?



Bei einem JOIN mußt du etwas aufpassen, denn wenn der der angejointen Tabelle pro Datensatz mehr als 1 Datensatz passt, dann dupplizierst du deine Datensätze und mußt das Ergebnis eventuell über GROUP BY wieder zusammenfassen.

Joar, im Grunde wäre es wohl eher ein INNER JOIN -> nur wenn in beiden Tabellen was zusammenpassendes drin ist.


Eigentlich könnte man Denken MySQL ist so schlau und führt das SubSelect nur einmal aus und schaut dann als TempTable nur noch in dessen Result nach.
Man könnte vielleicht noch über ein WITH-Clause das machen ... ich glaub MySQL ab 8 kann sowas inzwischen.

[Ykcim]

Hallo Zusammen,

vielen Dank, dass Ihr versucht mir zu helfen!!!

@stifflersmom: Deine Abfrage ist zwar erheblich schneller (9sek statt 22 sek), aber erzeugt leider auch wieder den Fehler, dass die Menge zu hoch ist.

@Delphi.Narium: Tut mir leid für die unpräzise Beschreibung - ich tue mich immer ein wenig schwer damit...

Zitat:

Um wieviel zu hoch?
4-fache, 7849-fache, irgendwie bei jedem Satz anders falsch? Immer einmal mehr als erwartet?

Die Menge ist immer zu hoch und auch unterschiedlich viel zu hoch. Beim ersten Artikel sind alle Mengen um den Faktor 10 zu hoch, bei dem nächsten um den Faktor 20.

Als jetzt gerade die anderen Fragen beantworten wollte kam der Post von von himitsu. Ich ging auch davon aus, dass es daran liegt, dass beim join mehrere Datensätz passen. Daher habe ich die Lösung von stifflersmom um ein group by im join-select erweitert. Damit scheint es zu funktionieren...

Delphi-Quellcode:

select 0 as Nr,
       a.watenr as Artikel,
       sum(o.ltrest) as Menge ,
       case when week(o.ltlite,3)<10 then concat(year(o.ltlite),'0',week(o.ltlite,3)) else concat(year(o.ltlite),week(o.ltlite,3)) end as KW,
       o.tebez1 as ArtikelBez
from liefersituation o
join (select watenr from liefersituation where lpdivk='SSC' group by watenr) a on o.watenr=a.watenr
where (o.ltlite >= current_date and week(o.ltlite,3)<week(current_date,3)+25 )
group by o.watenr, kw
order by o.watenr, kw

Vielen Dank für Eure Mühe!!!

Gruß Patrick

[stifflersmom]

Ich weiß jetzt natürlich nicht, wue groß Deine Tabellen sind und was für eine Maschine diese hostet... Aber 9 Sekunden empfinde ich schon als kleine Ewigkeit.
Wichtig für eine optimale Geschwindigkeit ist in jedem Fall, dass abfragerelevante Spalten einen Index besitzen!! Das kann man gar nicht oft genug wiederholen!

Vieleicht kommst Du auch mit eine

Code:

select DISTINCT watenr

in Deinem subselect besser klar, als mit dem GROUP BY.

[Ykcim]

Es ist ein alter MySQL 5.5. Eigentlich ist mir das auch zu lange, aber es handelt sich in der Applikation um ein Analyse-Modul, daher wird es nicht zu oft aufgerufen. Ich habe ein bißchen Schwierigkeiten mit den Indizes, weil die Tabelle alle 30 Minuten komplett neue Daten erhält. Es sind immer ca. 60.000-70.000 Datensätze. Leider habe ich auch bei der Kombination der Felder nicht die Möglichkeit, eine Eindeutigkeit zu definieren - jedenfalls kann ICH das nicht.

Aber ich bin schon sehr froh, dass es so läuft...

Vielen Dank
Patrick

[Ykcim]

Ich habe es mit Distinct ausgeführt. Wir sind jetzt bei unter 0,5 Sekunden. Das ist Top!
Vielen Dank!