Problem mit Rekursion

Hallo

Ich darf mich mit einer Rekursion herumschlagen. Geben ist folgendes.

Kategorie 1
– Kat 1.1
– Kat 1.2
– -- Kat 1.2.1
Kategorie 2
– Kat 2.1
– -- Kat 2.1.1

Ich muss/m�sste nun eine Summe eines Feldes der root Nodes mit ihren Unternodes berechnen. Auf einem Node gibt es irgend ein Feld mit einer Zahl.
Ausgeben muss ich dann nur die root Nodes mit der Summer aller Subnodes.

Ich darf dabei nur EIN EINZIGES Query haben. Und genau das macht mir Probleme.

Du hast also sowas wie:

test=*# select * from nodes;
 id | parent | value
----+--------+-------
  1 |        |     1
  2 |      1 |     2
  3 |      2 |     3
  4 |      1 |     4
  5 |        |   100
  6 |      5 |   101
  7 |      6 |   102
(7 rows)

und willst nun z.B. id 1,2,3,4 sowie 5,6,7 die jeweiligen Values zusammenaddiert haben, oder?


Ich darf dabei nur EIN EINZIGES Query haben. Und genau das macht mir Probleme.

Natürlich:

test=*# select root_number, sum(value) from (with recursive r as (select id, parent, row_number() over () as root_number, value from nodes where parent is null union all select n.id, n.parent, r.root_number, n.value from nodes n inner join r on (n.parent=r.id)) select * from r) foo group by root_number;
 root_number | sum
-------------+-----
           1 |  10
           2 | 303
(2 rows)

Andreas

Hallo Andreas

Im Prinzip ja. Genau was es sein sollte.

Ich habe das nun versucht in meiner Umgebung nach zu bauen. Allerdings bekomme ich noch ID’s geliefert die nicht root Nodes sind. Ich habe hier 63 Kategorien, davon sind 26 roor Kategorien. Ich bekomme aber 33 Summen zurück.
Das erste Select, das vor dem UNION ALL, gibt mir nur root Nodes zurück. Das passt also. Aber irgend wie kommen noch Summen rein von nicht root Nodes.

Mein Query sieht wie folgt aus.

select product_category, sum(revenue)
from (
with recursive r as (
SELECT
c.id AS product_category,
l.unit_price * l.quantity AS revenue,
c.id AS id,
c.parent as parent,
c.create_uid AS create_uid,
c.create_date AS create_date,
c.write_uid AS write_uid,
c.write_date AS write_date

FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
WHERE l.invoice_type = ‘out_invoice’
AND c.parent IS NULL
AND i.state IN (‘open’, ‘paid’)

UNION ALL

SELECT
c.id AS product_category,
l.unit_price * l.quantity AS revenue,
c.id AS id,
c.parent as parent,
c.create_uid AS create_uid,
c.create_date AS create_date,
c.write_uid AS write_uid,
c.write_date AS write_date

FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
INNER JOIN r on (c.parent = r.id)
WHERE l.invoice_type = ‘out_invoice’
AND i.state IN (‘open’, ‘paid’)
)
select * from r
)
foo group by product_category
ORDER BY product_category

Habe noch was bemerkt.
Ich muss nicht eine Nummer zurück haben sondern die ID des root Nodes mit dessen Summe.



test=*# select * from nodes;
 id | parent | value
----+--------+-------
  1 |        |     1
  2 |      1 |     2
  3 |      2 |     3
  4 |      1 |     4
  5 |        |   100
  6 |      5 |   101
  7 |      6 |   102
(7 rows)

test=*# select id, sum(value) from (with recursive r as (select id, parent, row_number() over () as root_number, value from nodes where parent is null union all select n.id, n.parent, r.root_number, n.value from nodes n inner join r on (n.parent=r.id)), n as (select id, root_number from r where parent is null) select r.root_number, r.value, n.id from r left join n on r.root_number=n.root_number) foo group by id;
 id | sum
----+-----
  1 |  10
  5 | 303
(2 rows)

Andreas

Hallo Andreas

Ich bekomme jetzt Zahlen, die sehen auch nicht mal so schlecht aus. Vor allem habe ich nur noch root Kategorien die auch was verkauft haben.
Aber die Summen scheinen nicht zu stimmen. Ich habe mal exemplarisch ein Artikel, der nur 1 Level Unterartikel hat, nachgerechnet.

SELECT
SUM(l.unit_price * l.quantity) AS revenue
FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
WHERE l.invoice_type = ‘out_invoice’
AND i.state IN (‘open’, ‘paid’)
AND c.id = 40
AND c.parent is null
OR

l.invoice_type = ‘out_invoice’
AND i.state IN (‘open’, ‘paid’)
AND c.parent = 40

Hier ergibt die Summe 46959.5.

Wenn ich das rekursive Query laufen lasse bekomme ich auf dem Artikel 40 eine Summe 140872.5. Das sind ziemlich genau drei mal mehr.
Beim Artikel 25 stimmt es aber aufs Loch. Das verwirrt mich jetzt ein wenig. :slight_smile:

Mein Query:

select
id,
0 as create_uid,
current_timestamp as create_date,
0 as write_uid,
current_timestamp AS write_date,
id AS product_category,
sum(revenue) as revenue
from (
with recursive r as (
SELECT
row_number() over () as root_number,
c.id AS product_category,
l.unit_price * l.quantity AS revenue,
c.id AS id,
c.parent as parent

FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
WHERE l.invoice_type = ‘out_invoice’
AND c.parent IS NULL
AND i.state IN (‘open’, ‘paid’)

union all

SELECT
r.root_number,
c.id AS product_category,
l.unit_price * l.quantity AS revenue,
c.id AS id,
c.parent as parent

FROM account_invoice_line l
JOIN account_invoice i ON i.id = l.invoice
JOIN product_product p ON p.id = l.product
JOIN product_template t ON t.id = p.template
JOIN product_category c ON c.id = t.category
INNER JOIN r on (c.parent = r.id)
WHERE l.invoice_type = ‘out_invoice’
AND i.state IN (‘open’, ‘paid’)
), n as (
select id, root_number
from r where parent is null
)
select r.root_number, r.revenue, n.id
from r
left join n on r.root_number=n.root_number
) foo group by id
ORDER BY id

Hallo

Das Teil verhält sich ganz komisch.
Auf gewissen Kategorien konnte man neue Rechnungen erfassen und es wurde richtig gerechnet.
Auf anderen Kategorien wurde der Betrag mal 15 Gerechnet, oder mal 10 oder … :slight_smile:
Na ja. Suche ich halt weiter.