WITH RECURSIVE query to choose the longest paths
I am new to
WITH RECURSIVE in PostgreSQL. I have a reasonably standard recursive query that is following an adjacency list. If I have, for example:
1 -> 2 2 -> 3 3 -> 4 3 -> 5 5 -> 6
1 1,2 1,2,3 1,2,3,4 1,2,3,5 1,2,3,5,6
What I would like is to have just:
But I can't see how to do this in Postgres. This would seem to be "choose the longest paths" or "choose the paths that are not contained in another path". I can probably see how to do this with a join on itself, but that seems quite inefficient.
An example query is:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle ) SELECT * FROM search_graph;
Find Longest Path Between Two Nodes using SQL Server , Find Longest Path Between Two Nodes using SQL Server Recursive CTE Query Then continue by selecting second path intervals starting from these new It can be proved using contradiction. So our algorithm reduces to simple two BFSs. First BFS to find an end point of the longest path and second BFS from this end point to find the actual longest path. For the proof of why does this algorithm works, there is nice explanation here Proof of correctness: Algorithm for diameter of a tree in graph
Recursive Graph Queries, longest, string, Query for the longest matching path, - recursive as A simple non-recursive graph query can easily map chosen relations of a whole collection. The query was what's known in database-land as a Recursive Common Table Expression (Recursive CTE) --- a rarely-used device, but one of some deep computational power and significance, as it happens to be the magic ingredient that makes SQL Turing Complete. To illustrate what the Recursive CTE is capable of, we'll make some brief forays into
I'm not sure that this should be considered as ugly join solution.
WITH recursive graph (child, parent) AS ( SELECT 2, 1 UNION SELECT 3, 2 UNION SELECT 4, 2 UNION SELECT 6, 5 UNION SELECT 7, 6 UNION SELECT 6, 7 ), paths (start, node, depth, path, has_cycle, terminated) AS ( SELECT ARRAY[g1.parent], false, false FROM graph g1 WHERE true AND NOT EXISTS (SELECT 1 FROM graph g2 WHERE g1.parent = g2.child) UNION ALL SELECT p.path || g.child, g.child = ANY(p.path), g.parent is null AS terminated FROM paths p LEFT OUTER JOIN graph g ON g.parent = p.node WHERE NOT has_cycle ) SELECT * from path WHERE terminated ;
So the trick is to use
terminated column via using
LEFT OUTER JOIN and then select only terminated paths.
Recursion Exercises, As a guide to test the accuracy of your SQL queries, the correct query results over the lengths of the shortest and longest paths between each pair of nodes. To practice writing hierarchical queries like this one, I recommend our interactive course Recursive Queries. Oracle – hierarchical queries In Oracle you can use either the hierarchical query clause (also known as “CONNECT BY query”) or recursive subquery factoring (introduced in version 11g release 2).
PostGIS recursive query in order to find the longest connected , WITH RECURSIVE connected_line AS ( SELECT 0 AS it, ARRAY[a.id] AS path, a.geom AS geom FROM network a WHERE id = 1 UNION ALL Execute recursive term with Ri as an input to return the result set Ri+1 as the output. Repeat step 2 until an empty set is returned. (termination check) Return the final result set that is a UNION or UNION ALL of the result set R0, R1, … Rn; PostgreSQL recursive query example. We will create a new table to demonstrate the PostgreSQL
Specifying Big Data Benchmarks: First Workshop, WBDB 2012, San , By our convention not having a from clause implies that the query is over the entire However, due to the construction process of the heterogeneous graphs, query will never use hierarchical graph components. A variant of this query will ask for select disjoint path(p). Find the longest path containing a single edge label. Recursive queries are used to query hierarchical data. The SQL standard defines a special syntax for common table expressions to enable recursive processing. Assume the following hierarchical definition of product categories:
Rules in Database Systems: Second International Workshop, RIDS , It also pays attention to the fact that linear recursive queries have to be of the longest path, the number of transitive closures and data skew for choosing the Showing the path from root to current row (sys_connect_by_path) Again, start by selecting the value you want in the base query. In the recursive part, append the values you want to add with an appropriate separator. Identifying the leaves (connect_by_isleaf) Displaying leaf rows is more complex with recursive with.
- Search for numbers that do not have children. Build from found childless numbers up.
- BTW: your intended output
1,2,3,4 | 1,2,3,5,6cannot exist, since each node only has one
linkfield, and thus only one successor. ('3' has both '4' and '5' as successors.
- Stupid user interface: I misclicked and marked Erwins comment as offensive :-)
- The other question was ltree specific. That one I haven't got back to. The answer below was helpful.
- Here is a related answer to a related question: stackoverflow.com/a/29738605/939860. Is your case "simple" as your demo suggest, less simple or an undirected graph where even cycles are possible?
- This is extremely helpful and very pretty solution.
- What if the results are '1,2,3,4' and '1,2,4,5'? Won't the first one be ignored although it's not a part of the other one?
- No, the not exists-clause tries to join exactly the same record as the second "leg" of the recursive union. So: they are mutually exclusive. (if it would exist, it should have been appended to the "list")
- Thank you. This is helpful, I was just thinking of a join which would be painful. And +1 for using 42.
- maybe by using ltree; but the plain recursive query will always yield the partial results, too. (as you know, I know) @ErwinBrandstetter