By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE items (
"item" VARCHAR(10),
"value" INTEGER
);
INSERT INTO items
("item", "value")
VALUES
('a', '6'),
('b', '8');
CREATE TABLE groups_items (
"group" VARCHAR(10),
"source_item" VARCHAR(10),
"weighting" FLOAT
);
INSERT INTO groups_items
("group", "source_item", "weighting")
VALUES
('x', 'a', '1'),
('x', 'b', '0.25'),
('y', 'a', '1');
CREATE TABLE groups_groups (
"group" VARCHAR(10),
"source_group" VARCHAR(10),
"weighting" FLOAT
);
INSERT INTO groups_groups
("group", "source_group", "weighting")
VALUES
('y', 'x', '0.5'),
('z', 'x', '1'),
('z', 'y', '1');
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
WITH RECURSIVE cte AS (
SELECT
gi."group",
SUM(i.value * gi.weighting) AS total
FROM groups_items gi
JOIN items i ON i.item = gi.source_item
GROUP BY
gi."group"
UNION ALL
SELECT
gg."group",
cte.total * gg.weighting AS total
FROM cte
JOIN groups_groups gg ON gg.source_group = cte."group"
)
SELECT
cte."group",
SUM(cte.total) AS total
FROM cte
GROUP BY
cte."group";
| group | total |
|---|---|
| z | 18 |
| y | 10 |
| x | 8 |
SELECT 3