hive-rolling从叶节点到顶层父节点的余额

5w9g7ksd  于 2021-05-27  发布在  Hadoop
关注(0)|答案(0)|浏览(212)

我有层次结构表有组织级的父子关系。另一个表有层次结构表中最低级别子级的帐户余额。我需要找到从上到下的所有层次结构。所有顶端父节点的顶端父节点都为“****”。请建议配置单元查询以解决此问题。
输入表:
层次结构表:

+---------------+----------------+
|parent_node_id | child_node_id  |
+---------------+----------------+
|  C1           |    C11         |
+---------------+----------------+
|  C11          |    C12         |
+---------------+----------------+
|  C12          |    123         |
+---------------+----------------+
|  C12          |    456         |
+---------------+----------------+
|  P1           |    C1          |
+---------------+----------------+
|  P1           |    C2          | 
+---------------+----------------+
|  P2           |    C3          | 
+---------------+----------------+

Account Balance:
+-------+----------+
| node  | balance  |
+-------+----------+
|  123  |  100     |
+-------+----------+
|  456  |  150     |
+-------+----------+
|  C2   |  400     |
+-------+----------+
|  C3   |  200     |
+-------+----------+

输出数据:
扁平层次结构:

+---------------+-------------+---------+----------+----------+
|Parent_node_id |   Level 1   | Level 2 | Level 2  | Level3   | 
+---------------+-------------+---------+----------+----------+
|  P1           |    C1       | C11     |  C12     |  123     |
+---------------+-------------+---------+----------+----------+
|  P1           |    C1       | C11     |  C12     |  456     |
+---------------+-------------+---------+----------+----------+
|  P1           |    C2       | NULL    |  NULL    |  NULL    |
+---------------+-------------+---------+----------+----------+
|  P2           |    C3       | NULL    |  NULL    |  NULL    |
+---------------+-------------+---------+----------+----------+

从最低的孩子到最高的平衡

+-------+----------+
| node  | balance  |
+-------+----------+
|  123  |  100     |
+-------+----------+
|  456  |  150     |
+-------+----------+
|  C12  |  250     |
+-------+----------+
|  C11  |  250     |
+-------+----------+
|  C1   |  250     |
+-------+----------+
|  P1   |  650     |
+-------+----------+
|  C2   |  200     |
+-------+----------+
|  P2   |  200     |
+-------+----------+

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题