[
https://issues.apache.org/jira/browse/SPARK-24497?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17276395#comment-17276395
]
Laurens Janssen commented on SPARK-24497:
-----------------------------------------
Any progress on this? The pull request seems to be open for some time now with
no activity.
I work in finance, and recursive queries are quite common in this industry.
Think of company hierarchies, where one would like to get all parent companies
up to some ultimate parent company for a given subsidiary. Similarly this is
used a lot for investment portfolios, where portfolios of holdings can be
composite of other portfolios. So to get all the holdings, one would have to
recursively get the holdings of each (sub)portfolio. Having recursive CTE's
would greatly simplify the queries I use.
> ANSI SQL: Recursive query
> -------------------------
>
> Key: SPARK-24497
> URL: https://issues.apache.org/jira/browse/SPARK-24497
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 3.1.0
> Reporter: Yuming Wang
> Priority: Major
>
> h3. *Examples*
> Here is an example for {{WITH RECURSIVE}} clause usage. Table "department"
> represents the structure of an organization as an adjacency list.
> {code:sql}
> CREATE TABLE department (
> id INTEGER PRIMARY KEY, -- department ID
> parent_department INTEGER REFERENCES department, -- upper department ID
> name TEXT -- department name
> );
> INSERT INTO department (id, parent_department, "name")
> VALUES
> (0, NULL, 'ROOT'),
> (1, 0, 'A'),
> (2, 1, 'B'),
> (3, 2, 'C'),
> (4, 2, 'D'),
> (5, 0, 'E'),
> (6, 4, 'F'),
> (7, 5, 'G');
> -- department structure represented here is as follows:
> --
> -- ROOT-+->A-+->B-+->C
> -- | |
> -- | +->D-+->F
> -- +->E-+->G
> {code}
>
> To extract all departments under A, you can use the following recursive
> query:
> {code:sql}
> WITH RECURSIVE subdepartment AS
> (
> -- non-recursive term
> SELECT * FROM department WHERE name = 'A'
> UNION ALL
> -- recursive term
> SELECT d.*
> FROM
> department AS d
> JOIN
> subdepartment AS sd
> ON (d.parent_department = sd.id)
> )
> SELECT *
> FROM subdepartment
> ORDER BY name;
> {code}
> More details:
> [http://wiki.postgresql.org/wiki/CTEReadme]
> [https://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1141-160K/lqe1472241402390.html]
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
