PostgreSQL - Aggregate attributes from different columns recursively

GeoGyro
id_start id_end
1 2
2 3
3 4

I want to collect elements who are "connected" like [1, 2, 3, 4]. (in an array for example)

I tried a recursive query like:

WITH RECURSIVE q_rec AS (
    SELECT id_start,
           id_end
    FROM my_table
    UNION
    SELECT t.id_start
           t.id_end
    FROM       my_table t
    INNER JOIN q_rec r ON r.id_start = t.id_end
) 
SELECT *
FROM q_rec;

But how can I aggregate them in an array despite they are not in the same column ?

lemon

Another approach gathers all ids in the recursive query, as a table. Then applies aggregation.

WITH RECURSIVE cte AS (
  SELECT id_start AS first_id, 
         id_start AS id
  FROM tab t1
  WHERE NOT EXISTS(SELECT 1 FROM tab t2 WHERE t1.id_start = t2.id_end)

  UNION ALL

  SELECT cte.first_id,
         tab.id_end
  FROM       cte
  INNER JOIN tab
          ON cte.id = tab.id_start
)
SELECT first_id, 
       ARRAY_AGG(id) AS ids
FROM cte
GROUP BY first_id

Output:

first_id ids
1 [1,2,3,4]

Check the demo here.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

PostgreSQL aggregate windows recursively

PostgreSQL aggregate nodes recursively

Aggregate parents recursively in PostgreSQL

Aggregate count of different columns

Merging columns from 2 different tables to apply aggregate function

Aggregate the result in different years and subtract from 2 columns.Oracle

Aggregate strings from two columns in two different tables JOINed in Postgres

Aggregate columns based on different conditions?

PostgreSQL group by on different columns

Recursively set object attributes from dict

Different aggregate operations on different columns pyspark

PostgreSQL: Efficiently aggregate array columns as part of a group by

How to retrieve two aggregate columns from two different tables using joins?

How to aggregate a different number of columns for each row

Hive aggregate columns based on different conditions

sum aggregate dataframe columns with different Shapes

How to groupby multiple columns and aggregate diff on different columns?

python pandas: applying different aggregate functions to different columns

using resample to aggregate data with different rules for different columns in a pandas dataframe

How to aggregate two different columns with two different functions in R dataframe

How to apply different aggregate functions to different columns in pandas?

Aggregate by multiple columns and reshape from long to wide

Aggregate unique values from multiple columns in R

How to aggregate two PostgreSQL columns to an array separated by brackets

PostgreSQL aggregate function for each row across multiple unknown number of columns

How to aggregate data and find min,max,avg of numeric columns in postgresql

Concatenate parent attributes recursively

How to aggregate the results from different machine in Jmeter?

Access and aggregate choices from different classes django