spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Laurens Janssen (Jira)" <j...@apache.org>
Subject [jira] [Commented] (SPARK-24497) ANSI SQL: Recursive query
Date Mon, 01 Feb 2021 15:16:00 GMT

    [ 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-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org


Mime
View raw message