SQL-Frage für Fortgeschrittene

SQL-Frage für Fortgeschrittene (9 Beiträge, 976 Mal gelesen)

Du bist nicht angemeldet. [ Login/Registrieren ]

Hi,

ich beiße mir gerade an dieser SQL Abfrage (ORACLE 11) die Zähne aus.
Hat wer einen Tipp für mich?

Es soll eine Liste ausgegeben werden, die zu jeder Bestellung den günstigsten Lieferanten unter Berücksichtung der Bestellmenge und jenen Lieferanten der der günstigste wäre wenn man mindestens x Stück bestellt.

BESTELLUNGEN
artikelnr,datum, menge
1, 01.02.2020, 20
..
------------------------
PREISLISTEN
artikelnr, datumvon, lieferantennr, menge_ab, einkaufspreis
1, 01.01.2019, L1, 10, 1.0
1, 01.01.2019, L1, 100, 0.8
1, 01.08.2019, L2, 20, 0.9
..
-------------------------

SOLL-ERGEBNIS
artikelnr, datum, menge, lieferant1, lieferant1_ab_menge, lieferant1_preis, lieferant2_preis, lieferant2_ab_menge, lieferant2_preis,
1, 01.02.2020, 25, L2, 20, 0.9, L1, 100, 0.8

Also Lieferant L2 ist im Bsp. der günstigste, wenn man 25 Stück bestellt, Lieferant L1 wäre der günstigste, wenn man mindestens 100 Stück bestellt.

Ich hab es mit allen möglichen Sub-Selects probiert, mit row_number() over (order by einkaufspreis) usw.

SELECT artikelnr, datum, menge, p1.lieferantennr, p1.einkaufspreis, p2.lieferantennr, p2.einkaufspreis
FROM
BESTELLUNGEN,
(SELECT * FROM PREISLISTEN WHERE ...) p1,
(SELECT * FROM PREISLISTEN WHERE ...) p2
WHERE
..

Ich hab kein Oracle. Mal in Sqlite3 daran versucht. Keine Garantie auf Richtigkeit. Ich mache nur selten was mit SQL. Vielleicht ist auch ganz falsch.

Einzelabfrage

Query:


SELECT MAX(menge_ab*einkaufspreis, 25*einkaufspreis) AS bestellpreis, *
FROM preislisten
WHERE artikelnr=1
ORDER BY bestellpreis ASC

Result:


bestellpreis|artikelnr|datumvon|lieferantennr|menge_ab|einkaufspreis
22.5|1|01.01.2019|L2|20|0.9
25.0|1|01.01.2019|L1|10|1.0
80.0|1|01.01.2019|L1|100|0.8

Multiabfrage aus der Tabelle "Bestellungen" (nur ein Ergebnis pro Bestellung, hab ein paar mehr Bestellungen dazuerfunden)


SELECT MIN(MAX(pl.menge_ab*pl.einkaufspreis, b.menge*pl.einkaufspreis)) as bestellpreis, *
FROM bestellungen b LEFT JOIN preislisten pl ON b.artikelnr=pl.artikelnr
GROUP BY b.rowid
ORDER BY bestellpreis ASC;

Result:


bestellpreis|artikelnr|datum|menge|artikelnr|datumvon|lieferantennr|menge_ab|einkaufspreis
10.0|1|05.02.2020|10|1|01.01.2019|L1|10|1.0
18.0|1|01.02.2020|20|1|01.01.2019|L2|20|0.9
22.5|1|02.02.2020|25|1|01.01.2019|L2|20|0.9
80.0|1|03.02.2020|100|1|01.01.2019|L1|100|0.8
640.0|1|04.02.2020|800|1|01.01.2019|L1|100|0.8

Es soll eine Liste ausgegeben werden, die zu jeder Bestellung den günstigsten Lieferanten unter Berücksichtung der Bestellmenge und jenen Lieferanten der der günstigste wäre wenn man mindestens x Stück bestellt.

Das wird ja noch verquerter. Ich hab es nur hinbekommen wenn es immer einen Lieferanten gibt, der die geforderte Stückzahl überbietet und den Stückpreis unterbietet, also ggf. per Dummy-Eintrag dies garantiert wird.

Ansonsten fallen Einträge ganz heraus wenn du das nächst-höhere Stückzahlangebot bzw. nächst-niedrigere Stückpreisangebot suchst und keins da ist. Also das ist ja vom Ansatz her Käse. Irgendwann ist es auch einfach nicht mehr sinnvoll, alles in ein Query quetschen zu wollen.

Und ein halbes Jahr später versteht das Query auch keiner mehr.

Aber vielleicht gehts ja auch viel eleganter.

Also das ist ja vom Ansatz her Käse. Irgendwann ist es auch einfach nicht mehr
sinnvoll, alles in ein Query quetschen zu wollen.

This. Man muss nicht immer alles in einen einzelnen Query stopfen. Oft joint man dadurch unnötig groß rum.

Wo ist SirHondo, wenn ma ihn braucht..!?

Verzeih, kann Dir auch nicht weiterhelfen, lese aber interessiert mit.

Gesegnetes Wochenende,
j.

Hallo

ich habe mir jetzt nicht genau das SQL überlegt, aber mit einer WITH-Klausel könnte das übersichtlich gestaltet werden in folgendem Sinne:

1. Schritt: Man multipliziert für jeden Lieferanten die verschiedenen Anzahl der Mengen und den damit verbundenen Preis und bekommt dann den Preis pro Stück und sortiert sie nicht nach einkaufspreis, sondern nach dem errechneten Stückpreis (row_number over....)

Danach such man sich einfach für jeden Artikel den jeweils ersten Satz.

Habe ich da was vergessen?

emefge
Infosauger
_____________________________________________________________________________

Weil die Lichtgeschwindigkeit höher als die Schallgeschwindigkeit ist, hält man viele Leute für helle Köpfe bis man sie reden hört.

"Bekomme ich bei Ihnen so einen ganz kleinen Lötkolben zum CPU reparieren?"

Wieso Realität? Ich hab doch verdammt noch mal was Besseres zu tun!

Äh laut der Angabe ist das der Stückpreis, sonst würds wenig Sinn machen

Jo und? Grundsätzlich geht es ja darum, den Preis pro Stück für jeden Händler für jede Mengenaktion auszurechnen, sonst macht das ja gar keinen Sinn.

emefge
Infosauger
_____________________________________________________________________________

Weil die Lichtgeschwindigkeit höher als die Schallgeschwindigkeit ist, hält man viele Leute für helle Köpfe bis man sie reden hört.

"Bekomme ich bei Ihnen so einen ganz kleinen Lötkolben zum CPU reparieren?"

Wieso Realität? Ich hab doch verdammt noch mal was Besseres zu tun!

wie jo und?

du schreibst:

Man multipliziert für jeden Lieferanten die verschiedenen Anzahl der Mengen und den damit verbundenen Preis und bekommt dann den Preis pro Stück und sortiert sie nicht nach einkaufspreis, sondern nach dem errechneten Stückpreis (row_number over....)

Für den Stückpreis musst du gar nix multiplizieren oder errechnen, er ist als Spalte in der vom OP angegebenen Tabelle enthalten.

Spannendes Beispiel, falls die Frage noch aktuell ist, hier meine Lösung:

with cheapest_supplier_overall as
(
   select x.artnr, x.lieferantennr, x.menge_ab, x.einkaufspreis
     from (
           select p.*, row_number() over (partition by artnr order by einkaufspreis) rang
             from preislisten p
          ) x
    where x.rang = 1
),
cheapest_supplier_for_order as
(
   select x.artnr, x.datum, x.menge, x.lieferantennr, x.menge_ab, x.einkaufspreis
     from (
           select b.artnr, b.datum, b.menge, p.lieferantennr, p.menge_ab, p.einkaufspreis, row_number() over (partition by b.artnr order by p.einkaufspreis) rang
             from bestellungen b
             join preislisten p
               on (    p.artnr = b.artnr
                   and p.menge_ab <= b.menge
                  )

          ) x
    where x.rang = 1
)
select a.artnr, a.datum, a.menge,
       a.lieferantennr lieferant1,
       a.menge_ab lieferant1_ab_menge,
       a.einkaufspreis lieferant_1_einkaufspreis,
       b.lieferantennr lieferant2,
       b.menge_ab lieferant2_ab_menge,
       b.einkaufspreis lieferant2_einkaufspreis
  from cheapest_supplier_for_order a
  join cheapest_supplier_overall b
    on (b.artnr = a.artnr);

btw: Für Oracle SQL Fragen in Foren eignet sich livesql.oracle.com wunderbar.
https://livesql.oracle.com/apex/livesql/s/j02wllnvr8w3fyrf1jdso7fd4

Dieses Forum ist eine frei zugängliche Diskussionsplattform.
Der Betreiber übernimmt keine Verantwortung für den Inhalt der Beiträge und behält sich das Recht vor, Beiträge mit rechtswidrigem oder anstößigem Inhalt zu löschen.
Datenschutzerklärung