英文:
How to query leaf nodes using a recursive CTE in DolphinDB？
问题
我想遍历一个类似树状数据结构的内容并查询叶子节点。我已经编写了一条使用 WITH RECURSIVE 语法来完成这个任务的 SQL 查询。但是，我现在正在使用 DolphinDB，并需要使用其语法编写类似的脚本。我该如何在 DolphinDB 中编写类似的脚本？
with recursive at as (
SELECT id, name, json_array(id) as path_id, json_array(name) as path_name, false as cycle
FROM t1
where pid is null and dir_group = 4
union ALL
SELECT t1.id, t1.name, JSON_MERGE_PRESERVE(at.path_id, json_array(t1.id)) as path_id,
JSON_MERGE_PRESERVE(at.path_name, json_array(t1.name)) as path_name,
json_contains(at.path_id, json_array(t1.id)) as cycle
FROM t1 join at on t1.pid = at.id
where t1.pid is not null and not at.cycle
)
select * from at
请注意，这是你原始 SQL 查询的 DolphinDB 版本。
英文:
I want to traverse a tree-like data structure and query the leaf nodes. I have already written an SQL query to accomplish this task using the WITH RECURSIVE syntax. However, I am now working with DolphinDB and need to write a similar script using its syntax. How can I write a similar script in DolphinDB?
with recursive at as(
SELECT id, name,json_array(id) as path_id,json_array(name) as path_name,false as cycle
FROM t1
where pid is null and dir_group = 4
union ALL
SELECT t1.id, t1.name,JSON_MERGE_PRESERVE(at.path_id,json_array(t1.id)) as path_id ,
JSON_MERGE_PRESERVE(at.path_name,json_array(t1.name)) as path_name ,
json_contains(at.path_id,json_array(t1.id)) as cycle
FROM t1 join at on t1.pid=at.id
where t1.pid is not null and not at.cycle)
select * from at
答案1
得分: 1
你可以参考以下的脚本：
t=table(1..7 as nodeID,`a1`a2`a3`a4`a5`a6`a7 as nodeName, 0 1 1 2 2 3 3 as parentID)
def getchildnode(t1,t){
return select * from t1 union select t.nodeID,t.nodeName,t1.pathid+"->"+string(t.nodeID) as pathid,t1.pathname+"->"+t.nodeName as pathname from ej(t1,t,`nodeID,`parentID)
}
t1=select nodeID,nodeName,string(nodeID) as pathid,nodeName as pathname from t where parentID=0
g = def (x,a)-> x.size()<a
reduce(getchildnode{,t}, g{,t.size()}, t1)
这里使用了“等值连接”（equi join）和“减少”（reduce）来帮助你查询从根节点开始的所有叶子节点。
输出：
英文:
You can refer to the following script:
t=table(1..7 as nodeID,`a1`a2`a3`a4`a5`a6`a7 as nodeName, 0 1 1 2 2 3 3 as parentID)
def getchildnode(t1,t){
return select * from t1 union select t.nodeID,t.nodeName,t1.pathid+"->"+string(t.nodeID) as pathid,t1.pathname+"->"+t.nodeName as pathname from ej(t1,t,`nodeID,`parentID)
}
t1=select nodeID,nodeName,string(nodeID) as pathid,nodeName as pathname from t where parentID=0
g = def (x,a)-> x.size()<a
reduce(getchildnode{,t}, g{,t.size()}, t1)
Here, equi join and reduce are used to help you query all leaf nodes starting from the root node.
Output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论