This does the job:
CREATE MATERIALIZED VIEW speedy_materialized_view AS
WITH RECURSIVE tree AS (
SELECT id, parent_id, ARRAY[id] AS path
FROM gear_category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, path || c.id
FROM tree t
JOIN gear_category c ON c.parent_id = t.id
)
, tree_ct AS (
SELECT t.id, t.path, COALESCE(i.item_ct, 0) AS item_ct
FROM tree t
LEFT JOIN (
SELECT category_id AS id, count(*) AS item_ct
FROM gear_item
GROUP BY 1
) i USING (id)
)
SELECT t.id
, t.item_ct AS count_direct_child_items
, sum(t1.item_ct) AS count_recursive_child_items
FROM tree_ct t
LEFT JOIN tree_ct t1 ON t1.path[1:array_upper(t.path, 1)] = t.path
GROUP BY t.id, t.item_ct;
count_recursive_child_items
is counted separately for each category, so I am not convinced it's the fastest possible way for deep trees.
However, aggregate functions are not allowed in recursive CTEs.
Recursive Function
Strictly speaking, it's really iterative - but so is a "recursive" CTE.
You could build a function working with temp tables. You need to know your way around plpgsql or there is too much to explain.
CREATE OR REPLACE FUNCTION f_tree_ct()
RETURNS TABLE (id int, count_direct_child_items int, count_recursive_child_items int) AS
$func$
DECLARE
_lvl int;
BEGIN
-- basic table with added path and count
CREATE TEMP TABLE t1 AS
WITH RECURSIVE tree AS (
SELECT c.id, c.parent_id, '{}'::int[] AS path, 0 AS lvl
FROM gear_category c
WHERE c.parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, path || c.parent_id, lvl + 1
FROM tree t
JOIN gear_category c ON c.parent_id = t.id
)
, tree_ct AS (
SELECT t.id, t.parent_id, t.path, t.lvl, COALESCE(i.item_ct, 0) AS item_ct
FROM tree t
LEFT JOIN (
SELECT i.category_id AS id, count(*)::int AS item_ct
FROM gear_item i
GROUP BY 1
) i USING (id)
)
TABLE tree_ct;
-- CREATE INDEX ON t1 (lvl); -- only for very deep trees
SELECT INTO _lvl max(lvl) FROM t1; -- identify max lvl to start bottom up
-- recursively aggregate each level in 2nd temp table
CREATE TEMP TABLE t2 AS
SELECT t1.id, t1.parent_id, t1.lvl
, t1.item_ct
, t1.item_ct AS sum_ct
FROM t1
WHERE t1.lvl = _lvl;
IF _lvl > 0 THEN
FOR i IN REVERSE _lvl .. 1 LOOP
INSERT INTO t2
SELECT t1.id, t1.parent_id, t1.lvl, t1.item_ct
, CASE WHEN t2.sum_ct IS NULL THEN t1.item_ct ELSE t1.item_ct + t2.sum_ct END
FROM t1
LEFT JOIN (
SELECT t2.parent_id AS id, sum(t2.sum_ct) AS sum_ct
FROM t2
WHERE t2.lvl = i
GROUP BY 1
) t2 USING (id)
WHERE t1.lvl = i - 1;
END LOOP;
END IF;
RETURN QUERY -- only requested columns, unsorted
SELECT t2.id, t2.item_ct, t2.sum_ct FROM t2;
DROP TABLE t1, t2; -- to allow repeated execution in one transaction
RETURN;
END
$func$ LANGUAGE plpgsql;
This cannot be included in a CREATE MATERIALIZED VIEW
statement because of the use of temporary tables. You could just create another (temp) table with it, acting as a manually maintained "materialized view":
CREATE TABLE speedy_materialized_view AS
SELECT * FROM f_tree_ct();
Alternatively you could TRUNCATE speedy_materialized_view
in the function and write to it directly. The function would RETURNS void
instead or you could return some meta-information like a row count ...
SQL Fiddle.
Aside:
Column aliases in the recursive term of a CTE are just for documentation since output column names are determined by the non-recursive term only.
Best Answer
You can use the
RANK()
function for this:Tested in SQLFiddle.
There is also a more complicated approach: