SELECT FIRST 1 im JOIN möglich ?

[MyRealName]

Mal ne Frage...

Ich habe eine Tablle "Cp_Fecha" wo ich zu einem Datum einen Preis in einer Lagerposition speichere.

Code:

CREATE TABLE CP_FECHA (
    ITEMID           INTEGER NOT NULL,            -- mein produkt
    LOCATION         CHAR(3) NOT NULL,            -- mein Lager
    FECHA            DATE NOT NULL,               -- Datum
    CP               NUMERIC(15,2) NOT NULL,      -- Mittel-Preis 
    QTY_ACUM         FLOAT,
    TOTPARCIAL_ACUM  FLOAT
);

Dabei kommt es aber zu Lücken, da man nur ein Register erstellt, wenn es zu einer Änderung kommt. Normalerweise frage ich das dann so ab:

Code:

SELECT FIRST 1 Cp FROM Cp_Fecha WHERE ItemId=:ItemId AND Location=:Loc AND Fecha <= :Fecha ORDER BY Fecha DESCENDING

Könnte man das in ein SELECT als JOIN einbauen oder nur als SubSelect ?
Also Sum Bsp:

Code:

SELECT IA.ItemId, IA.Item, Sum(IA.Qty), CF.CP
FROM ItemAct IA
LEFT JOIN Cp_Fecha CF ON (CP.ItemId=IA.ItemId AND CP.Location=IA.Location AND Fecha <= :Fecha) -- ich weiss, da fehlt was um nur 1 register zu bekommen

anstelle von :

Code:

SELECT IA.ItemId, IA.Item, Sum(IA.Qty),
       (SELECT FIRST 1 Cp FROM Cp_Fecha WHERE ItemId=:ItemId AND Location=:Loc AND Fecha <= :Fecha ORDER BY Fecha DESCENDING) CP
FROM ItemAct IA

Ist das möglich ?

Danke schonma

[mkinzler]

Als Derived Table sollte es funktionieren:

SQL-Code:

SELECT 
    IA.ItemId, IA.Item, Sum(IA.Qty),
FROM 
    ItemAct IA 
        join (SELECT FIRST 1 Cp FROM Cp_Fecha WHERE ItemId=:ItemId AND Location=:Loc AND Fecha <= :Fecha ORDER BY Fecha DESCENDING) CP
          on cp.ItemID = IA.ItemID;

[jobo]

Zitat:

Zitat von MyRealName (Beitrag 1381197)

Code:

SELECT IA.ItemId, IA.Item, Sum(IA.Qty), CF.CP
FROM ItemAct IA
LEFT JOIN Cp_Fecha CF ON (CP.ItemId=IA.ItemId AND CP.Location=IA.Location AND Fecha <= :Fecha) -- ich weiss, da fehlt was um nur 1 register zu bekommen

Ist das möglich ?

So wie Du es formuliert hast nicht, aber wie Markus es macht, kann man jedes (funktionierende) SQL Statement gegen andere Tabellen oder Statements joinen.

[MyRealName]

OK, das hilft. Danke

[MyRealName]

Krame das Thema nochmal hervor. Die Abfrage müsste lauten :

Code:

SELECT 
    IA.ItemId, IA.Item, Sum(IA.Qty), MAX(CP1.CP) Costo
FROM 
    ItemAct IA 
        join (SELECT FIRST 1 Cp, ItemId FROM Cp_Fecha WHERE ItemId=IA.ItemId AND Fecha <= :Fecha ORDER BY Fecha DESCENDING) CP1 on cp1.ItemID = IA.ItemID
GROUP BY IA.ItemId, IA.Item

Aber er sagt, er kenne IA.ItemId nicht.

Gibt es eine Möglichkeit mit SQL das folgende rauszubekomen :

Tabelle Cp_Fecha mit den Feldern ItemId, Fecha (Datum auf spanisch) und CP (Mittelpreis)
Jedesmal wenn der mittelpreis sich ändert, dann wird ein Register geschrieben, max 1 pro tag mit dem letzten Mittelpreis. Manchmal hat man Löcher von Tagen, Wochen und Monaten ohne eine Änderung.

ItemId Fecha CP

10 01/01/2017 3.50

10 05/01/2017 3.80

10 30/01/2017 3.60

105 02/01/2017 45.30

105 12/01/2017 46.00

105 03/02/2017 46.10

544 02/01/2017 5.63

544 07/01/2017 6.12

Jetzt will ich den Preis eines jeden Produktes, also 10, 105 und 544) zum 06/01/2017 wissen. Die Abfrage sollte den Preis zum Tag oder den nächst vorherigen nehmen. Im Fall des Produktes 10 den vom 05/01/2017.

Ideen ?

[Towmuz]

Hi,

das sollte mit

Code:

with recursive

gut funktionieren.

FireBird-Doku
Obwohl das Beispiel sicherlich Geschmackssache ist, mit google kommste da aber auch schnell weiter.

[jobo]

Zitat:

Zitat von MyRealName (Beitrag 1385809)

Aber er sagt, er kenne IA.ItemId nicht.

Ja und kennen wir IA bzw den Tabellenaufbau von IA? Offenbar gibt es in der Tabelle dieses Feld ItemID nicht. Das Subselect für den nächst kleineren Wert mit neuestem Mittelwert sollte schon passen. Aber joinen ohne passende ID geht halt nicht.

Ob with recursive da hilft, würde ich auf die Schnelle bezweifeln, denn es fehlt ja eben ein konkreter (also passender) join Wert.

[MyRealName]

IA (also ItemAct) hat ein Feld ItemId, das sollte nicht das Problem sein

Hier die genauen Strukturen :

Code:

CREATE TABLE CP_FECHA (
    ITEMID           INTEGER NOT NULL,
    LOCATION         CHAR(3) NOT NULL,
    FECHA            DATE NOT NULL,
    CP               NUMERIC(15,2) NOT NULL,
    QTY_ACUM         FLOAT,
    TOTPARCIAL_ACUM  FLOAT
);

CREATE TABLE ITEMACT (
    CONTEO          INTEGER NOT NULL,
    LOCATION        CHAR(3) NOT NULL,
    ITEM            CHAR(15) NOT NULL,
    ID_N            BIGINT,
    TIPO            CHAR(2),
    BATCH           INTEGER,
    FECHA           DATE,
    QTY             NUMERIC(15,5),
    VALUNIT         NUMERIC(15,2),
    VALCDCT         NUMERIC(15,2),
    VALCFLE         NUMERIC(15,2),
    QTYB            NUMERIC(15,2),
    E               SMALLINT,
    S               SMALLINT,
    LOTE            CHAR(20),
    TOTPARCIAL      NUMERIC(15,2),
    PRIORIDAD       INTEGER,
    COLOR           CHAR(25),
    COD_TALLA       CHAR(5),
    PRECIO          NUMERIC(15,2),
    COSTP_KARDEX    NUMERIC(15,2) DEFAULT  0,
    SALDO_KARDEX    NUMERIC(15,2) DEFAULT  0,
    SALDODU_KARDEX  NUMERIC(15,2) DEFAULT  0,
    PARCIAL_KARDEX  NUMERIC(15,2) DEFAULT  0,
    TOTAL_KARDEX    NUMERIC(15,2) DEFAULT  0,
    COD_LISTA       INTEGER DEFAULT  -1,
    COSTO_PROMEDIO  TNUMERICO DEFAULT  0 /* TNUMERICO = NUMERIC(15,2) DEFAULT 0 */,
    ER              INTEGER,
    SR              INTEGER,
    TIPOR           CHAR(2),
    NUMEROR         INTEGER,
    HORA            DATE,
    FCH_VNCMNTO     DATE,
    ID_CURVA        INTEGER,
    ID_LOTE         INTEGER,
    ITEMID          INTEGER,
    AUX             INTEGER,
    DOCID           BIGINT NOT NULL
);

[p80286]

Zitat:

Zitat von MyRealName (Beitrag 1385809)

SQL-Code:

..
    ItemAct IA 
        join (SELECT FIRST 1 Cp, ItemId FROM Cp_Fecha WHERE ItemId=IA.ItemId AND Fecha <= :Fecha ORDER BY Fecha DESCENDING) CP1 on cp1.ItemID = IA.ItemID
...

==>
SELECT FIRST 1 Cp, ItemId
FROM Cp_Fecha
WHERE ItemId=IA.ItemId AND ....

Auch in einer Unterabfrage sollten in FROM alle Tabellen definiert werden, die in WHERE angesprochen werden.

Gruß
K-H

P.S. ggf ist es einfacher für jedes Produkt den Datensatz auszugeben, der die Datumsbedingung erfüllt. So lange es gerade mal eine gute Hand voll Produkte ist...

[jobo]

Achso gar nicht gesehen, im Join select wird bereist der Alias IA verwendet. Ist da noch unbekannt, also hier " WHERE ItemId=IA.ItemId" muss das raus und in den "On" Teil des join als Kriterium und ItemID muss natürlich vom Subselect ausgegeben werden.

P.S:
Ich merke gerade, dass mach so keinen Sinn. Das Beispiel von Markus enthält ja diese Bedingung als Parameter. Die Gesamtmenge auf diese Weise abzufragen ist mit der Kombi first 1 natürlich unzureichend.

[MyRealName]

Naja, was ich will ist von allen Produkten zu einem bestimmten Datum den Durchschnittspreis.
Weiss nicht warum, ob es an der Db liegt oder so aber neuerdings ist die abfrage mit dem SubSelect extrem langsam geworden. Die tabelle hat 750k Datensätze mit 1120 verschiedenen Produkten

[jobo]

Was Du in #5 gemacht hast, das Subselect aus der Select Clause in den Join verschieben geht in der Form nicht. Der Bezug zu IA fehlt da innen drin, man kann da drinnen nichts referenzieren, was draußen in der Nachbarmenge liegt.
Das geht nur beim Subselect in der Select Clause, wie Du es vorher hattest oder auch mit CTE, wie vorgeschlagen, aber da ist mir auf die Schnelle nichts zu eingefallen.
Die Subselects wie Du es gemacht hast, sind praktisch, aber nicht die schnellsten. Für jeden Satz der Hauptmenge wird das Subselect neu gemacht.
Wenn man das Subselect als separaten Join baut, läuft das eleganter. Man muss allerdings die Joinkriterien mit nach außen legen.
Und dass ein First dann keinen Sinn mehr ergibt, weil eine große Ergebnismenge erzeugt wird, ist dann auch klar.

Was ich bei Dir in #5 nicht verstehe, was die Aggregate im äußeren Select sollen. Mir ist allerdings auch nicht klar, wie die Tabelle ItemAct strukturiert ist und was es mit der qty auf sich hat.
Ich würde es so versuchen:

Code:

SELECT IA.ItemId, IA.Item, IA.Qty, CP1.CP Costo    
  FROM ItemAct IA join
      (select f.itemid, f.cp, f.fecha, f.LOCATION      -- Eintrag am nächstem zum Stichtag mit CP
         from cp_fecha f join
             (SELECT ItemId , LOCATION, max(fecha) as fechamax -- Eintrag am nächstem zum Stichtag
                FROM Cp_Fecha                                  -- cp fehlt noch
               WHERE Fecha <= :cp_param 
               group by itemid, LOCATION) fm
           on f.itemid = fm.itemid  
          and f.fecha = fm.fechamax 
          and f.LOCATION = fm.LOCATION) CP1 
           on cp1.ItemID = IA.ItemID;

Ob die Location bei der Sache eine Rolle spielt oder nicht, musst Du selber wissen, wenn nicht dann raus.

[himitsu]

Zitat:

Zitat von jobo (Beitrag 1386040)

Was Du in #5 gemacht hast, das Subselect aus der Select Clause in den Join verschieben geht in der Form nicht. Der Bezug zu IA fehlt da innen drin, man kann da drinnen nichts referenzieren, was draußen in der Nachbarmenge liegt.

In einigen DBMS gibt es sowas wie einen LATERAL JOIN (in Postgre SQL nennt es sich so), wo man auch im JOIN (links vom ON) auf die Ergebnismenge der anderen Tabellen zugreifen kann.

https://heap.engineering/postgresqls...-type-lateral/
https://oracle-base.com/articles/12c...ly-joins-12cr1

[p80286]

Unter Umständen wäre es sinnvoll die Durschnittspreise täglich fortzuschreiben. Dann würde das Gehampel mit dem Datum entfallen.

Gruß
K-H

[jobo]

Es ist sowieso fraglich, ob das so schneller ist.
Wenn das Mengenverhältnis 750K:1K ist, dann ist das ursprüngliche Subselect gar nicht so dramatisch. Bei wirksamer Indexierung, sind es halt 1000 gezielte Zugriff aus der 750K Menge.
Meine Variante macht riesige Aggregationen wahrscheinlich sogar Fulltablescan, die eigentlich nur einseitig über das Datum eingeschränkt sind.

Angenommen, man untersucht die Menge und stellt das größte Datumsloch fest, könnte man das sicher auch als berechnete Untergrenze (fecha-größteLücke) in das Statement einbauen.

Gemäß der Frage des TE ist mein Vorschlag erstmal eine ziemlich allgemeingültige Lösung per Join. Dürfte überall laufen.
Ggf. hilft die CTE Nummer doch, das wäre quasi eine Lateral Variante.
MyRealName soll mal ausprobieren, wie das läuft.

Jeden Tag einen Mittelwert einzutragen, macht die 750K sicher auch nicht schlanker, dann spart man sich die Aggregierung, aber braucht "etwas" mehr Platz für die Daten und Indizierung.

[p80286]

Zitat:

Zitat von jobo (Beitrag 1386055)

Jeden Tag einen Mittelwert einzutragen, macht die 750K sicher auch nicht schlanker, dann spart man sich die Aggregierung, aber braucht "etwas" mehr Platz für die Daten und Indizierung.

Eben drum, meiner Meinung nach kommt es auf die Häufigkeit der Abfrage an. Läuft sie jeden oder jeden zweiten Tag sollte man den Platz spendieren, wird die Frage nur jeden Monat gestellt, darf sie auch etwas Zeit Kosten.

Gruß
K-H

[MyRealName]

Zitat:

Zitat von jobo (Beitrag 1386040)

Was Du in #5 gemacht hast, das Subselect aus der Select Clause in den Join verschieben geht in der Form nicht. Der Bezug zu IA fehlt da innen drin, man kann da drinnen nichts referenzieren, was draußen in der Nachbarmenge liegt.
Das geht nur beim Subselect in der Select Clause, wie Du es vorher hattest oder auch mit CTE, wie vorgeschlagen, aber da ist mir auf die Schnelle nichts zu eingefallen.
Die Subselects wie Du es gemacht hast, sind praktisch, aber nicht die schnellsten. Für jeden Satz der Hauptmenge wird das Subselect neu gemacht.
Wenn man das Subselect als separaten Join baut, läuft das eleganter. Man muss allerdings die Joinkriterien mit nach außen legen.
Und dass ein First dann keinen Sinn mehr ergibt, weil eine große Ergebnismenge erzeugt wird, ist dann auch klar.

Was ich bei Dir in #5 nicht verstehe, was die Aggregate im äußeren Select sollen. Mir ist allerdings auch nicht klar, wie die Tabelle ItemAct strukturiert ist und was es mit der qty auf sich hat.
Ich würde es so versuchen:

Code:

SELECT IA.ItemId, IA.Item, IA.Qty, CP1.CP Costo    
  FROM ItemAct IA join
      (select f.itemid, f.cp, f.fecha, f.LOCATION      -- Eintrag am nächstem zum Stichtag mit CP
         from cp_fecha f join
             (SELECT ItemId , LOCATION, max(fecha) as fechamax -- Eintrag am nächstem zum Stichtag
                FROM Cp_Fecha                                  -- cp fehlt noch
               WHERE Fecha <= :cp_param 
               group by itemid, LOCATION) fm
           on f.itemid = fm.itemid  
          and f.fecha = fm.fechamax 
          and f.LOCATION = fm.LOCATION) CP1 
           on cp1.ItemID = IA.ItemID;

Ob die Location bei der Sache eine Rolle spielt oder nicht, musst Du selber wissen, wenn nicht dann raus.

Habe die query probiert, die braucht aber sehr lange.
Für die Bewegungen des Inventars nutzt unser Programm eine Tablle ItemAct (Item Accounting nehme ich mal an, der Name kam schon so als ich in die Firma gekommen bin).
Qty (Quantity) ist die Menge, die pro Datensatz bewegt wird. Da steht dann z.B. drin dass zur Datum (Fecha) X das Produkt (Item) A -5 Einheiten durch das Document Y den Inventar der Bodega (das ist sowas wie eine Lagerhalle) entzogen wurden (zum Bsp durch Verkauf).
Mache ich dann SUM(Qty) zu einem Stichtag, danns ehe ich, wieviele Einheiten ich da zur Verfügung habe und die Idee ist ja, den Durchschnittspreis (Costo promedio = CP) daneben zu schreiben.

Sorry wenn mir machmal die Worte auf deutsch nicht geläufig sind, aber in Deutschland habe ich fast nichts in Warenwirtschaften programmiert, alles was ich darüber weiss ist eigentlich in spanisch.

[MyRealName]

Zitat:

Zitat von p80286 (Beitrag 1386070)

Zitat:

Zitat von jobo (Beitrag 1386055)

Jeden Tag einen Mittelwert einzutragen, macht die 750K sicher auch nicht schlanker, dann spart man sich die Aggregierung, aber braucht "etwas" mehr Platz für die Daten und Indizierung.

Eben drum, meiner Meinung nach kommt es auf die Häufigkeit der Abfrage an. Läuft sie jeden oder jeden zweiten Tag sollte man den Platz spendieren, wird die Frage nur jeden Monat gestellt, darf sie auch etwas Zeit Kosten.

Gruß
K-H

Ich habe hier eine Kundentabelle eine Firma welche Airlines in Bogota das Essen für die Flüge zubereitet. Die kaufen produkte nur 2-4x pro Monat und manchmal ändert sich der Preis das ganze Jahr nicht, deswegen macht eine Tabelle wue CP_Fecha (CP_Durchschnittspreis, Fecha=Datum) sinn, da es
1. eine kleinere Tabelle ist als die Bewegungstablle (ItemAct), also weniger Felder und ich habe das gefühl, dass FB da schenller abfragt
2. halt doch sehr viel weniger Register drin stehen und es sich schneller sucht.

[jobo]

Zitat:

Zitat von MyRealName (Beitrag 1386240)

Habe die query probiert, die braucht aber sehr lange.
..
Sorry wenn mir machmal die Worte auf deutsch nicht geläufig sind, aber in Deutschland habe ich fast nichts in Warenwirtschaften programmiert, alles was ich darüber weiss ist eigentlich in spanisch.

Das habe ich befürchtet. Deine alte Abfrage wird immer langsamer, weil sie per inline subselect alle Werte einzeln nachschlägt. Das sind aber nur 1000 +x wenn ich das richtig verstanden habe und es läuft vermutlich über den pk index, also sozusagen Direktzugriff.
Meine Version mit klassischem Join baut die ursprüngliche "First 1" Menge erstmal über alle Daten auf, das sind 750 K, ein riesiges Aggregat.

Du hast noch nicht verraten, wie das indiziert ist, z.B. Index auf fetch? Wäre natürlich doof, wenn der nicht da ist, macht aber in Summe nicht so einen Unterschied.
Wie gesagt, die Join Variante kann man wahrscheinlich tunen, z.B. mit fetcha Untergrenze = Wunschdatum - größte Lücke.

Wenn Du noch die Index scripte der beiden Tabellen lieferst und das Mengengerüst bestätigst (cp_Fetch 750k*, itemAct 1k*), könnte man da noch mal genau hinschauen.

Das mit den Worten finde ich nicht wild, man kann ja fragen, wenn es unverständlich ist.

* passt irgendwie nicht zu "kleine Tabelle" aus Deinem letzten Beitrag