Berechnung der gewichteten durchschnittlichen Kosten des Produktbestands

Ich muss die Lagerkosten meiner Produkte berechnen, also muss ich für jedes Produkt nach jedem Kauf die gewichteten Durchschnittskosten neu berechnen.

Ich habe eine view, die mir die aktuelle Produkt-Aktie nach jedem In / Out bringt:

document_type document_date product_id qty_out qty_in price row_num stock_balance SI 01/01/2014 52 0 600 1037.28 1 600 SI 01/01/2014 53 0 300 1357.38 2 300 LC 03/02/2014 53 100 0 1354.16 3 200 LC 03/02/2014 53 150 0 1355.25 4 50 LC 03/02/2014 52 100 0 1035.26 5 500 LC 03/02/2014 52 200 0 1035.04 6 300 LF 03/02/2014 53 0 1040 1356.44 7 1090 LF 03/02/2014 52 0 1560 1045 8 1860 LC 04/02/2014 52 120 0 1039.08 9 1740 LC 04/02/2014 53 100 0 1358.95 10 990 LF 04/02/2014 52 0 600 1038.71 11 2340 LF 04/02/2014 53 0 1040 1363.3 12 2030 LC 05/02/2014 52 100 0 1037.78 13 2240 LF 15/03/2014 53 0 20 1365.87 14 2050 LF 15/03/2014 52 0 50 1054.19 15 2290 

Ich möchte ein berechnetes WAC Feld wie oben hinzufügen:

 document_type document_date product_id qty_out qty_in price row_num stock_balance WAC SI 01/01/2014 52 0 600 1 037,28 1 600 1037,28000000000 SI 01/01/2014 53 0 300 1 357,38 2 300 1357,38000000000 LC 03/02/2014 53 100 0 1 354,16 3 200 1357,38000000000 LC 03/02/2014 53 150 0 1 355,25 4 50 1357,38000000000 LC 03/02/2014 52 100 0 1 035,26 5 500 1037,28000000000 LC 03/02/2014 52 200 0 1 035,04 6 300 1037,28000000000 LF 03/02/2014 53 0 1040 1 356,44 7 1090 1356,48311926606 --((1357,38*50)+(1040*1356,44))/(1090) LF 03/02/2014 52 0 1560 1 045,00 8 1860 1043,75483870968 --((1037,28*300)+(1560*1045))/(1860) LC 04/02/2014 52 120 0 1 039,08 9 1740 1043,75483870968 LC 04/02/2014 53 100 0 1 358,95 10 990 1356,48311926606 LF 04/02/2014 52 0 600 1 038,71 11 2340 1042,46129032258 --((1043,75483870968*1740)+(600*1038,71))/(2340) LF 04/02/2014 53 0 1040 1 363,30 12 2030 1359,97000000000 --((1356,48311926606*990)+(1040*1363,3))/(2030) LC 05/02/2014 52 100 0 1 037,78 13 2240 1042,46129032258 LF 15/03/2014 53 0 20 1 365,87 14 2050 1360,03301857239 --((1359,97551136621*2030)+(20*1365,87))/2050 LF 15/03/2014 52 0 50 1 054,19 15 2290 1042.71737568672 --((1042.46129032258*2240)+(50*1054.19))/2290 

Es gibt nur einen und nur einen Dokumenttyp 'SI' (Anfangsbestand) für jedes Produkt, und der damit verbundene Preis ist der initial WAC .

Hier ist ein SQL Fiddle- Beispiel.

Wenn jemand dabei helfen kann, kann ich es nicht herausfinden.

Edit : Ich habe juste aktualisiert die berechneten Zahlen durch Erhöhung der Genauigkeit durch Anzeige von 9 Zahlen nach dem Komma.

Solutions Collecting From Web of "Berechnung der gewichteten durchschnittlichen Kosten des Produktbestands"

Sie müssen den rekursiven CTE verwenden:

SQLFiddle

 with recursive stock_temp as ( select *, row_number() over(partition by product_id order by row_num) as rn from stock_table ) ,cte as ( select document_type, document_date, product_id, qty_out, qty_in, price, row_num, stock_balance, rn, price as wac from stock_temp where document_type = 'SI' union all select sub.document_type, sub.document_date, sub.product_id, sub.qty_out, sub.qty_in, sub.price, sub.row_num, sub.stock_balance, sub.rn, case when sub.qty_in = 0 then main.wac else ((sub.stock_balance - sub.qty_in) * main.wac + sub.qty_in * sub.price) / ((sub.stock_balance - sub.qty_in) + sub.qty_in) end as wac from cte as main join stock_temp as sub on (main.product_id = sub.product_id and main.rn + 1 = sub.rn) ) select * from cte 

Ich habe mehrere Stunden mit diesem gleitenden Durchschnitt verbracht! Vor allem wegen der nicht zuverlässigen windowsfunktion vor / last / nth_value, laut Postgresql Dokumentation zu windowsfunktionen : This is likely to give unhelpful results for nth_value and particularly last_value.

Die Antwort ist unvollständig. Dinge die zu tun sind:

  • lasse WAC den gleichen Wert erhalten, wenn qty_in = 0 (jetzt wird es auf Null gesetzt)
  • WAC wird nicht basierend auf vorherigem WAC, sondern auf "vorherigem" Preis berechnet, deshalb gibt es zunehmende Unterschiede zwischen WAC berechnet von mir und präsentiert von Ihnen

SQLFiddle

Hier was ich mit der function gemacht habe:

 CREATE TYPE stock_table_with_wac AS (document_type character varying, document_date date, product_id bigint, qty_out double precision, qty_in double precision, price double precision, row_num bigint, stock_balance double precision, wac double precision); CREATE OR REPLACE FUNCTION calculate_wac_value() RETURNS SETOF stock_table_with_wac AS $BODY$ DECLARE r_article stock_table_with_wac%rowtype;--maintain the liste of all products with there wac's value r_in_out_article stock_table_with_wac%rowtype;--Each other records BEGIN --For each products FOR r_article IN SELECT *, price FROM stock_table where document_type='SI' order by row_num LOOP return next r_article; FOR r_in_out_article IN SELECT * FROM stock_table where document_type<>'SI' and product_id=r_article.product_id order by row_num LOOP --If there is an entry calculate the wac if r_in_out_article.qty_in >0 then r_in_out_article.wac:=((r_article.price * (r_in_out_article.stock_balance - r_in_out_article.qty_in)) + (r_in_out_article.qty_in * r_in_out_article.price))/(r_in_out_article.stock_balance); --Update the wac value of the product r_article.price:= r_in_out_article.wac; else --The waca value still inchanged: r_in_out_article.wac:= r_article.price; end if; RETURN NEXT r_in_out_article; -- return current row with caluculated wac if any END LOOP; return next r_article; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000; ALTER FUNCTION calculate_wac_value() OWNER TO postgres; select * from calculate_wac_value(); 

Es scheint eine korrekte Ausgabe zu haben. Ist es eine gute Idee, so zu verfahren?

Es ist bereits eine Aggregatfunktion in C für PostgreSQL verfügbar, und sie wird wahrscheinlich viel schneller berechnen als jede Lösung in SQL:

https://github.com/Kozea/gewichtete_Mean