MS-Office-Forum
Google
   

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

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 09.10.2017, 14:03   #1
hcscherzer
MOF Guru
MOF Guru
Standard MySQL - 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.

__________________

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 09.10.2017, 19:42   #2
ebs17
MOF Guru
MOF Guru
Standard

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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.10.2017, 23:13   #3
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

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.

__________________

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 (09.10.2017 um 23:16 Uhr).
hcscherzer ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.10.2017, 08:20   #4
ebs17
MOF Guru
MOF Guru
Standard

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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}
Dein Dankeschön: DBWiki=>Spende

Geändert von ebs17 (10.10.2017 um 08:22 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.10.2017, 10:29   #5
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

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.

__________________

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 10.10.2017, 11:26   #6
ebs17
MOF Guru
MOF Guru
Standard

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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 10.10.2017, 13:08   #7
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

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

__________________

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 11.10.2017, 17:11   #8
hcscherzer
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

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.

__________________

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 10:25 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 - 2017, Jelsoft Enterprises Ltd.

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