尝试构建层次结构并在Oracle中查找根目录和根目录下的子目录总数

vlf7wbxs 于 2023-06-22 发布在 Oracle

关注(0)|答案(1)|浏览(91)

问题陈述：

1.尝试在Oracle中创建层次结构（query1）
1.需要查找每个根下的根和子项总数（query2）
1.必须在PowerBI中构建具有数据输出的报告（对于1和2）

Query1：构建层次结构

SELECT 

PARENT AS "PARENT"
,CHILD AS "CHILD"
,LEVEL AS "LEVEL"
,PARENT_CLASS AS "PARENT_CLASS"
,CHILD_CLASS AS "CHILD_CLASS"
,CREATED_ON_RELCI AS "CREATED_ON_RELCI"
,UPDATED_ON_RELCI AS "UPDATED_ON_RELCI"

FROM
(   

    SELECT DISTINCT
   CI1.NAME AS PARENT
    ,CI2.NAME AS CHILD
     ,CI1.SYS_CLASS_NAME AS PARENT_CLASS
     , CI2.SYS_CLASS_NAME AS CHILD_CLASS  
     ,RELCI.SYS_CREATED_ON AS CREATED_ON_RELCI
    ,RELCI.SYS_UPDATED_ON AS UPDATED_ON_RELCI

    FROM  SN_REPORTING.REP_CMDB_REL_CI RELCI  
    LEFT JOIN SN_REPORTING.REP_CMDB_CI_SERVICE CIS
    ON RELCI.PARENT=CIS.SYS_ID
    LEFT JOIN SN_REPORTING.REP_CMDB_CI CI1
    ON RELCI.PARENT=CI1.SYS_ID
    LEFT JOIN SN_REPORTING.REP_CMDB_CI CI2
    ON RELCI.CHILD=CI2.SYS_ID

WHERE RELCI.SYS_UPDATED_ON >= TO_DATE('01-01-18', 'MM-DD-YYYY')

)A

CONNECT BY NOCYCLE A.PARENT = PRIOR A.CHILD)

查询2：查找Root和每个Root下的子节点总数

select ( count(*)-1 ) Child_Node_Count
,      root
from ( select connect_by_root(parent) root
       from  SN_REPORTING.SERVICE_LIFECYCLE_WITHOUT_HIERARCHY_FILTER_VW
       connect by nocycle parent = prior child
     )
     
group
by     root
/

问题描述：

1.如果我运行query1，我会得到Oracle中的层次结构，如果我用相同的查询创建一个过程，它会抛出“HIERARCHY”列的错误。
1.我在Oracle中创建了一个排除“HIERARCHY”列的过程&使用相同的方法来查找根和子项计数，但查询继续运行，一段时间后抛出表空间错误。
1.另外，在将数据导入POWERBI时，数据会继续加载，并且永远不会结束。无法检查数据库中的总数据计数，因为它显示表空间错误。
由于我是Oracle的新手，需要帮助来解决上述问题，还需要结合query1和query2来接收整个问题陈述。
随附示例输出供您参考。query1 output

oracle

来源：https://stackoverflow.com/questions/76417720/trying-to-build-a-hierarchy-and-find-the-root-and-total-count-of-children-under

1条答案

按热度按时间

pgvzfuti1#

您可以使用标量选择来深入到每一行的层次结构中，例如

SQL> select empno
  2       , lpad(' ', (level-1)*2) || ename as ename
  3       , ( select count(*)
  4           from emp sub
  5           start with sub.mgr = emp.empno
  6           connect by sub.mgr = prior sub.empno
  7         ) reports
  8    from emp
  9   start with mgr is null
 10   connect by mgr = prior empno
 11   order siblings by empno;

     EMPNO ENAME                   REPORTS
---------- -------------------- ----------
      7839 KING                         13
      7566   JONES                       4
      7788     SCOTT                     1
      7876       ADAMS                   0
      7902     FORD                      1
    ...

但是当然，这可能意味着对每一行都要进行昂贵的查询。另一种方法是模式匹配（假设你是12c或以上）

SQL> with raw_data as (
  2     select lvl, empno, ename, rownum as rn
  3     from ( select level as lvl, empno, ename
  4            from emp
  5            start with mgr is null
  6            connect by mgr = prior empno
  7            order siblings by empno  )
  8      )
  9  select empno
 10       , lpad(' ', (lvl-1)*2) || ename as ename
 11       , reports
 12  from raw_data
 13  match_recognize (
 14     order by rn
 15     measures
 16        starting_level.rn as rn
 17      , starting_level.lvl as lvl
 18      , starting_level.empno as empno
 19      , starting_level.ename as ename
 20      , count(higher_level.lvl) as reports
 21     one row per match
 22     after match skip to next row
 23     pattern (starting_level higher_level*)
 24     define  higher_level as lvl > starting_level.lvl
 25  )
 26  order by rn;

     EMPNO ENAME                   REPORTS
---------- -------------------- ----------
      7839 KING                         13
      7566   JONES                       4
      7788     SCOTT                     1
      7876       ADAMS                   0
      7902     FORD                      1
      7369       SMITH                   0
      7698   BLAKE                       5
      7499     ALLEN                     0
      ...

赞(0）回复(0）举报 2023-06-22

我来回答

尝试构建层次结构并在Oracle中查找根目录和根目录下的子目录总数

1条答案

相关问题

热门标签

最新问答