MS-Office-Forum

MS-Office-Forum (http://www.ms-office-forum.net/forum/index.php)
-   SQL (http://www.ms-office-forum.net/forum/forumdisplay.php?f=41)
-   -   Daten verdichten (http://www.ms-office-forum.net/forum/showthread.php?t=346490)

hcscherzer 09.10.2017 14:03

Daten verdichten
 
Ich habe eine Tabelle mit etwa 25.000 Datensätzen.
Es ist eine m:n Tabelle mit Bestelldetails.
In der sind u.a. die Artikel-Nummer und der tatsächliche Verkaufspreis gespeichert. In der übergeordneten Tabelle mit den Bestellungen findet sich das Bestelldatum.

Mit dieser Abfrage kann ich mir die nach Artikeln und chronologisch auflisten:
Code:

select op.p_id,
 if(op.discount is null, op.price, op.price * 100 / (100-op.discount)) vk_preis,
 o.date_purchased
from orders_products op
inner join orders o on op.o_id = o.o_id
order by op.p_id, o.date_purchased

Jetzt hätte ich gern eine Liste mit den unterschiedlichen Preisen pro Artikel und dem jeweiligen frühesten Datum dazu.
Code:

select op.p_id, if(op.discount is null, op.price, op.price * 100 / (100-op.discount)) vk_preis,
min(o.date_purchased) ab_wann
from orders_products op
inner join orders o on op.o_id = o.o_id
group by op.p_id, vk_preis

So weit auch kein Problem.

Jetzt ist es aber so, dass die Preise sich manchmal so ändern, dass ein Artikel den Preis, den er schon mal hatte, nach einer zwischenzeitlichen Änderung erneut angenommen hatte (hier das Ergebnis der ersten Abfrage für Artikel 59):
Code:

59        7.4200        2015-10-19 18:45:18
59        7.4200        2015-11-09 21:55:20
59        9.0833        2015-11-12 13:19:27
59        7.4200        2015-11-27 12:21:31
59        7.4200        2015-12-17 14:36:49
59        9.0833        2016-06-01 21:15:07
59        7.4200        2016-12-14 16:48:17

Die zweite Abfrage macht mir daraus
Code:

59        7.4200        2015-10-19 18:45:18
59        9.0833        2015-11-12 13:19:27

Ich hätte aber gerne
Code:

59        7.4200        2015-10-19 18:45:18
59        9.0833        2015-11-12 13:19:27
59        7.4200        2015-11-27 12:21:31
59        9.0833        2016-06-01 21:15:07
59        7.4200        2016-12-14 16:48:17

Ich komm grad nicht drauf, wie ich das - möglichst mit einem SQL Statement - hinbekommen kann.

ebs17 09.10.2017 19:42

Zitat:

Liste mit den unterschiedlichen Preisen pro Artikel und dem jeweiligen frühesten Datum dazu
Das hieße, jeder Datensatz aus orders_products ist gefragt, wo sich der Preis zum Vorgängerdatensatz des gleichen Artikels unterscheidet.

hcscherzer 09.10.2017 23:13

Zitat:

von Eberhard
jeder Datensatz aus orders_products ist gefragt, wo sich der Preis zum Vorgängerdatensatz des gleichen Artikels unterscheidet

Sehr treffsicher formuliert.

Aber wie bekomme ich zu jedem Datensatz den passenden Vorgängerdatensatz des gleichen Artikels?
Habe jetzt diese Sub-Query ausprobiert, die braucht aber sehr, sehr lange ... 13 Minuten aber ohne den Rabatt einzuberechnen ...

Code:

select op.p_id, op.price, o.date_purchased,
 (select op1.price from orders_products op1
  where op1.p_id = op.p_id and op1.op_id < op.op_id
  order by op1.op_id desc limit 1) last_price
from orders_products op
inner join orders o on op.o_id = o._id
where op.price <> (select op1.price from orders_products op1
  where op1.p_id = op.p_id and op1.op_id < op.op_id
  order by op1.op_id desc limit 1)
order by op.p_id, o.date_purchased

Wahrscheinlich geht es eleganter und schneller?
Möglicherweise mit temporären Tabellen in Zwischenschritten?
Und ausserdem sehe ich gerade, dass er mir die allerersten Preise pro Artikel nicht bringt, für die ist ja last_price null.

ebs17 10.10.2017 08:20

Zitat:

Aber wie bekomme ich zu jedem Datensatz den passenden Vorgängerdatensatz des gleichen Artikels?
Das hatte ich bewusst noch nicht angefasst, weil ich die Tabellenstruktur und Daten nicht kenne - 13 Minuten deuten auf ein paar mehr Datensätze hin. Primär würde ich aber diese Vorgängerrechnung in der genannten Tabelle pur ausführen und danach erst eine Verknüpfung zu weiteren Tabellen verwenden wollen. Dann wird auch die Behandlung von NULL bezüglich des jeweils ersten Datensatzes übersichtlicher.
Code:

where op.price <> (select ...
Das bricht Dir eine Indexnutzung, zumindest in Jet-SQL.

Denkbar wäre es zum Beispiel, sich eine Zuordnungstabelle ID-VorgängerID zu schaffen, bei Bedarf dann als Temptabelle mit Nachindizierung der ID:
Code:

SELECT
  op.op_id,
  (
      SELECT
        op1.op_id
      FROM
        orders_products op1
      WHERE
        (
            op1.price > op.price
              OR
            op1.price < op.price
        )
            AND
        op1.p_id = op.p_id
            AND
        op1.op_id < op.op_id
      ORDER BY
        op1.op_id DESC limit 1
  ) AS vg_id
FROM
  orders_products op

Jetzt könntest Du orders_products über diese Zuordnungstabelle mit sich selber per OUTER JOIN verknüpfen und dabei auch die NULL-Behandlung erledigen.
Code:

WHERE
        (
            op1.price > op.price
              OR
            op1.price < op.price
        )

Wenn diese Filterung auf den berechneten Verkaufspreis ausgeführt wird, kann man sich die Aufspaltung von <> in < und > sparen, weil dann sowieso die Indexnutzung gebrochen ist.

Ach so: Ich habe keine Ahnung von MySQL. Vielleicht geht es auch ganz anders und besser.

hcscherzer 10.10.2017 10:29

Moin Eberhard, danke für Deine Mitarbeit.

Ja, es sind 25.000 Datensätze in orders_products (wie ich in #1 schrieb) und Deine Abfrage in #4 benötigt mit 11 Minuten tatsächlich fast genauso lang wie meine aus #3.
Ich hab jetzt mal meine nochmal laufen lassen ohne den Join auf die orders und auch ohne die Sortierung nach p_id ... braucht aber auch 12 Minuten ...

Und bei beiden Statements sind ja auch noch nicht die Rabatte herausgerechnet, denn es dürfen ja nur die unrabattierten Preise verglichen werden ...

Was den Index angeht: auf dem Preis liegt ohnehin keiner (in der Ausgangstabelle). Interessant finde ich den Hinweis darauf, dass
Code:

a < b or a > b
besser ist als
Code:

a <> b
Zitat:

Ich habe keine Ahnung von MySQL
Das Einzige, was hier MySQL von anderen Dialekten unterscheidet ist, dass es statt TOP den Bezeichner LIMIT verwendet, um eine begrenzte Anzahl von Datensätzen zu liefern.

ebs17 10.10.2017 11:26

Das Limit kannte ich gerade noch, hatte aber die Erwartung, dass das in MySQL deutlich performanter läuft, 25k sind nun noch nicht arg viel.

Bezüglich "<>": Bei Michael Zimmermann (Script in meiner Fußnote verlinkt) kann man einiges lesen. Somit sind Dinge wie "<>", "Not", "Like" mit Abstrichen und berechnete Felder beim Filtern und Verknüpfen erst einmal rote Tücher bei Auftreten und somit dann nur zweite Wahl.

Durch Deine Tests zeigt sich, dass die korrelierende Unterabfrage das Problem sein wird. Wenn diese im SELECT-Teil oder im WHERE-Teil platziert ist, wird sie entsprechend oft wie die Datenzahlanzahl der Hauptabfrage ausgeführt. Da summiert sich einiges.

In Jet würde ich jetzt eine laufende Nummer erzeugen - zufällig habe ich mich gerade mit Nummerierungen beschäftigt: Grundlagen - SQL ist leicht (10) - Nummerierung in Abfragen
Damit könnte man dann einen SELF JOIN per A.LfdNr = B.LfdNr - 1 verwenden.

hcscherzer 10.10.2017 13:08

Ja, danke.
Ich werde heute Abend mal die Daten in eine ACCDB importieren.
Und dann ein wenig damit herumspielen ...

hcscherzer 11.10.2017 17:11

Ich hab nur mal kurz die Abfrage von Eberhard aus #4 getestet.
Mit den 25.000 Datensätzen, die ich in eine accdb kopiert hatte.
Etwa 1 Sekunde.
WOW.
Meine #3 braucht auch nur wenig mehr als 1 Sekunde.

Erstaunlich, dass JET so viel performanter ist als MySQL.
Hätte ich nicht gedacht.


Alle Zeitangaben in WEZ +1. Es ist jetzt 04:07 Uhr.

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