Oracle分层总和(从叶到根的距离) [英] Oracle hierarchical sum (distance from leaf to root)
问题描述
我想获得有关分层查询(Oracle 11gR2)的帮助.这些查询让我很难受...
I would like to get help for a hierarchical query (Oracle 11gR2). I have a hard time with those kind of queries...
实际上，这是2合1问题(需要2种不同的方法).
In fact, it's a 2 in 1 question (2 different approches needed).
我正在寻找一种方法来获取所有个人记录到根的距离(不是相反的).我的数据位于树状结构中:
I’m looking for a way to get the distance from all individials records to the root (not the opposite). My data are in a tree like structure:
CREATE TABLE MY_TREE
(ID_NAME VARCHAR2(1) PRIMARY KEY,
PARENT_ID VARCHAR2(1),
PARENT_DISTANCE NUMBER(2)
);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('A',NULL,NULL);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('B','A',1);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('C','B',3);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('D','B',5);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('E','C',7);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('F','D',11);
INSERT INTO MY_TREE (ID_NAME,PARENT_ID,PARENT_DISTANCE) VALUES('G','D',13);
从层次上讲，我的数据看起来像这样(但是我有多个独立的根以及更多的级别):
Hierarchically, my data look like this (but I have multiple independents roots and many more levels):
在第一个方法中，我正在寻找一个查询，该查询将为我提供以下结果:
In the first approch, I'm looking for a query that will give me this result:
LEVEL ROOT NODE ID_NAME ROOT_DISTANCE
----- ---- ---- ------- -------------
1 A null A null
2 A null B 1
3 A B C 4
4 A B E 11
3 A B D 6
4 A D F 17
4 A D G 19
在此结果中，
- "NODE"列表示最接近的拆分元素的ID_NAME
- "ROOT_DISTANCE"列表示从元素到根的距离(例如:ID_NAME = G的ROOT_DISTANCE是从G到A的距离:G(13)+ D(5)+ B(1)= 19 )
在此方法中，我将始终最多指定2个根.
In this approch, I will always specify a maximum of 2 roots.
第二个方法必须是PL/SQL脚本，该脚本将执行相同的计算(ROOT_DISTANCE)，但采用迭代方式，并将结果写入新表中.我想一次运行此脚本，因此将处理所有根(〜1000).
The second approch must be a PL/SQL script that will do the same calculation (ROOT_DISTANCE), but in a iterative way, and that will write the result in a new table. I want to run this script one time, so all roots (~1000) will be processed.
这是我看脚本的方式:
- 对于所有根，我们需要找到关联的叶子，然后计算从叶子到根的距离(对于叶子和根之间的所有元素)，并将其放入表格中.
性能角度"需要此脚本，因此，如果已经计算出一个元素(例如:由另一片叶子计算出的拆分节点)，则需要停止计算并传递到下一个叶子，因为我们已经知道从那里到根的结果.例如，如果系统先计算E-C-B-A，然后计算F-D-B-A，则不应再次计算B-A部分，因为它是在第一遍中完成的.
This script is needed for "performance perspectives", so if an element have been already calculated (ex: a split node that was calculated by another leaf), we need to stop the calculation and pass to the next leaf because we already know the result from there to the root. For example, if the system calculates E-C-B-A, and then F-D-B-A, the B-A section should not be calcultated again because it was done in the first pass.
您可以对这两个问题中的一个或两个都加篷，但我需要为这两个问题加篷.
You can awnser one or both of those questions, but i will need the awnser to those two questions.
谢谢！
推荐答案
尝试以下方法:
WITH brumba(le_vel,root,node,id_name,root_distance) AS (
SELECT 1 as le_vel, id_name as root, null as node, id_name, to_number(null) as root_distance
FROM MY_TREE WHERE parent_id IS NULL
UNION ALL
SELECT b.le_vel + 1, b.root,
CASE WHEN 1 < (
SELECT count(*) FROM MY_TREE t1 WHERE t1.parent_id = t.parent_id
)
THEN t.parent_id ELSE b.node
END,
t.id_name, coalesce(b.root_distance,0)+t.parent_distance
FROM MY_TREE t
JOIN brumba b ON b.id_name = t.parent_id
)
SELECT * FROM brumba
演示: https://dbfiddle.uk/?rdbms=oracle_11.2& ; fiddle = d5c231055e989c3cbcd763f4b3d3033f
不需要使用PL/SQL进行第二种处理"-上面的SQL将立即计算所有根节点(在parent_id列中为空)的结果.
只需在上述查询中添加INSERT INTO tablename(col1,col2, ... colN) ...或CREATE TABLE name AS ...前缀即可.
上面的演示包含后一个选项CREATE TABLE xxx AS query
There is no need for "the second approch" using PL/SQL - the above SQL will calculate results for all root nodes (which have null in parent_id column) at once.
Just add a prefix either INSERT INTO tablename(col1,col2, ... colN) ... or CREATE TABLE name AS ... to the above query.
The above demo contains an example for the latter option CREATE TABLE xxx AS query
这篇关于Oracle分层总和(从叶到根的距离)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
