迭代SQL查询 [英] Iterative SQL Query

问题描述

我对二叉树有一个棘手的要求

这是我的数据的样子

ID     IDNEXT
1A       2E
3D       4A
2E       6F
6F       3D
4A       

我的输出应该在此fashin中

ID     IDNEXT
1A      2E
2E      6F
6F      3D
3D      4A
4A 

根据当前的IDNEXT，我应该为mynext ID，我们应该继续这样，直到结尾和深度未知为止

任何建议

预先感谢

解决方案

正如我的评论中所述，SAP HANA提供了

此问题的要求可以表示为先深度遍历有向图，然后按访问顺序返回被访问的节点"..

具有易于执行的层次结构功能:

create column table con (ID nvarchar(2) not null
                       , IDNEXT nvarchar(2) not null);
/*
ID     IDNEXT
1A       2E
3D       4A
2E       6F
6F       3D
4A
*/

insert into con values ('1A', '2E');
insert into con values ('3D', '4A');
insert into con values ('2E', '6F');
insert into con values ('6F', '3D');
insert into con values ('4A', '');

SELECT   
     hierarchy_rank AS rank,
     node_id,
     parent_id
FROM HIERARCHY (
    SOURCE (SELECT  ID  as "NODE_ID"
                  , IDNEXT as "PARENT_ID" 
            FROM con)
    START WHERE IDNEXT =''
    )
ORDER BY
    hierarchy_rank desc;

/*
RANK    NODE_ID PARENT_ID
5       1A      2E       
4       2E      6F       
3       6F      3D       
2       3D      4A       
1       4A               
*/

这种方法得益于

• 如何根据表数据创建层次结构以及应该对层次结构进行哪些计算的表现力
在SAP HANA中高效地(在空间和时间上)实现层次结构处理，而不是通过SQL函数来仿真功能.当结构被自动缓存时应重用相同的层次结构时，这特别方便.

具有这些特殊功能的最大障碍可能是，它们需要仔细考虑要定义的层次结构/图形.幸运的是， SAP HANA学院提供了一些教程视频跟随.

I have a tricky requirement with a binary tree

This is how my data looks like

ID     IDNEXT
1A       2E
3D       4A
2E       6F
6F       3D
4A       

My OutPut should be in this fashin

ID     IDNEXT
1A      2E
2E      6F
6F      3D
3D      4A
4A 

Based on the current IDNEXT should me mynext ID and we i should continue like this till the end and depth is unknown

Any suggestions

Thanks in advance

解决方案

As mentioned in my comment, SAP HANA provides Hierarchy-Functions that allow for very efficient processing of hierarchy-data that has been encoded in tables via the "node"/"parent node" scheme.

The requirement of this question can be stated as "traverse the directed graph depth-first and return the visited nodes in the order of visit".

With the hierarchy functions that is easy to do:

create column table con (ID nvarchar(2) not null
                       , IDNEXT nvarchar(2) not null);
/*
ID     IDNEXT
1A       2E
3D       4A
2E       6F
6F       3D
4A
*/

insert into con values ('1A', '2E');
insert into con values ('3D', '4A');
insert into con values ('2E', '6F');
insert into con values ('6F', '3D');
insert into con values ('4A', '');

SELECT   
     hierarchy_rank AS rank,
     node_id,
     parent_id
FROM HIERARCHY (
    SOURCE (SELECT  ID  as "NODE_ID"
                  , IDNEXT as "PARENT_ID" 
            FROM con)
    START WHERE IDNEXT =''
    )
ORDER BY
    hierarchy_rank desc;

/*
RANK    NODE_ID PARENT_ID
5       1A      2E       
4       2E      6F       
3       6F      3D       
2       3D      4A       
1       4A               
*/

This approach benefits from

• expressiveness of how the hierarchy is created based on the table data and what computation should be done with the hierarchy
• efficient (both space and time) implementation of the hierarchy processing in SAP HANA instead of emulating the functionality via SQL functions. This comes in especially handy when the same hierarchy should be reused as the structure gets cached automatically.

The biggest hurdle with these special functions probably is that they require to think precisely about the hierarchy/graph one wants to define. Fortunately, the SAP HANA Academy provides some tutorial videos to follow.

这篇关于迭代SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！