RaqForum 41 No.
41 View  • 
     ...

From SQL to SPL: Calculate the hierarchy of recursive references

 

A certain table in the MS SQL database has a multi-level self-association structure, where the second field parent node ID is a foreign key pointing to the first field node ID of the table, and the third field is a zone.

product_identifier

parent_product_identifier

Zone

1

5

E

2

6

F

3

7

G

4

8

H

5

11

R

6

12

B

7

13

C

8

14

D

11

15

A

Now we need to find the hierarchy of nodes with a higher level of 2 or more, as well as the zones of the highest-level nodes. For example, the first record has 3 levels of hierarchy, which are 5-11-15, and the highest level is 15; The second record has two levels of superiors, namely 6-12, with the highest level being 12.

product_identifier

hierarchy

Zone

1

3

A

2

2

B

3

2

C

4

2

D

5

2

A

SQL solution：


WITH dt AS (
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent 
  FROM temp
  UNION ALL
  SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1, 
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1 
AND hierarchy = (
   SELECT MAX(hierarchy) FROM dt dt2 
   WHERE dt2.product_identifier = dt.product_identifier) 
ORDER BY product_identifier;
WITH dt AS (
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent 
  FROM temp
  UNION ALL
  SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1, 
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1 
AND hierarchy = (
   SELECT MAX(hierarchy) FROM dt dt2 
   WHERE dt2.product_identifier = dt.product_identifier) 
ORDER BY product_identifier;

As long as all the levels recursively referenced by each node are found, the results can be easily filtered out. However, SQL does not have directly available functions, and complex recursive subqueries plus self joins need to be used to implement them. The code is lengthy and difficult to understand.

SPL provides directly available functions that can obtain all levels of recursive references to nodes.

　A

1

=mssql.query("select product_identifier,parent_product_identifier,zone from temp order by product_identifier”)

2

=A1.switch(parent_product_identifier, A1:product_identifier)

3

=A2.derive(~.prior(parent_product_identifier):t, t.len():hierarchy, t.m(-1).zone:z)

4

=A3.select(hierarchy>=2)

5

=A4.new(product_identifier, hierarchy, z:zone)

A2： Establish a reference relationship and replace parent_product_identifier with the record of this table pointed to by the foreign key.

A3： Add a calculated column and use the prior function to calculate all levels recursively referenced by this node, the number of levels in these levels, and the zone of the last level.

A4： Select nodes with levels greater than or equal to 2 in all levels of recursive references.

A5： Generate the target result set.

Question source：https://stackoverflow.com/questions/78299370/assistance-to-fix-query-to-identify-recursive-relationships-in-data-in-sql-dw


 
 •  41 View  •