How to deal with a recursive aggregation in postgresql?

by darrion.kuhn , in category: MySQL , a month ago

How to deal with a recursive aggregation in postgresql?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by scotty_walker , a month ago

@darrion.kuhn 

To deal with a recursive aggregation in PostgreSQL, you can use a common table expression (CTE) with a recursive query. Here is an example of how you can use a recursive query to perform a recursive aggregation in PostgreSQL:

  1. Start by defining a CTE with a recursive query that will generate the desired aggregation. For example, if you want to calculate the sum of a column recursively, you can use a query like this:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
WITH RECURSIVE recursive_agg AS (
    SELECT parent_id, child_id, value
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    SELECT t.parent_id, t.child_id, t.value
    FROM your_table t
    JOIN recursive_agg r ON t.parent_id = r.child_id
)
SELECT parent_id, SUM(value) AS total
FROM recursive_agg
GROUP BY parent_id;


  1. In this query, the recursive CTE "recursive_agg" starts by selecting the rows where the parent_id is NULL (i.e., the root of the recursion). It then recursively adds rows where the parent_id of the current row matches the child_id of the previous row until all related rows are aggregated.
  2. Finally, the main query groups the results by the parent_id and calculates the total sum of the values for each parent row.


By using a recursive query in a CTE, you can perform recursive aggregations in PostgreSQL efficiently and effectively.