如何计算一个层次结构的所有级别的总和

Question

我想获取每个数据库的空间用法以及所有孩子的总和，孙子孙女...

在teradata中，数据库沿数据库及其直接所有者在层次结构中组织。每个数据库都有其Maxperm(=允许的空间使用(和CurrentPerm(=实际使用的内容(

到目前为止，我拥有的内容如下：以两个步骤进行操作，首先将层次结构弄平，然后用rollup

总结一下

create volatile table dbHierarchy as
(
    WITH RECURSIVE dbs AS
    (
        SELECT
              cast(databasename AS VARCHAR(500)) AS L0
            , cast('' AS VARCHAR(500)) AS L1
            , cast('' AS VARCHAR(500)) AS L2
            --, cast(null AS VARCHAR(500)) AS L3
            , ownername
            , databasename
            , 0 AS depth
            --, CAST(DatabaseName AS VARCHAR(500)) AS pretty_hierarchy
        FROM DBC.databasesv
        WHERE DatabaseName = 'DBC'
        UNION ALL
        SELECT
              L0
            , case when dbs.depth = 0 then dbsv.DatabaseName else dbs.L1  end  as L1
            , case when dbs.depth = 1 then dbsv.DatabaseName else dbs.L2  end  as L2
            --,case when dbs.depth = 2 then dbsv.DatabaseName else dbs.L3  end  as L3
            , dbsv.ownername
            , dbsv.databasename
            , depth + 1 AS depth
            --, pretty_hierarchy || substring('            ' FROM 1 FOR (dbs.depth + 1)*3) ||'>'|| dbsv.DatabaseName  as pretty_hierarchy
        FROM dbs
        INNER JOIN "DBC".DatabasesV dbsv
            ON dbsv.OwnerName = dbs.databasename
            AND dbsv.DatabaseName <> dbs.databasename
        WHERE dbs.depth <=10
    )
    SELECT * FROM dbs
) WITH DATA
PRIMARY INDEX (databasename)
ON COMMIT PRESERVE ROWS;
select
      coalesce(L0,'sum') as L0
    , coalesce(L1,'sum') as L1
    , coalesce(L2,'sum') as L2
    --, coalesce(L3,'sum') as L3
    ,SUM(Space.MaxPerm)/(1024*1024)(bigint)  as Max_Perm
    ,SUM(Space.CurrentPerm)/(1024*1024) (bigint) as Current_Perm
from DBC.DiskSpace Space
    inner join dbHierarchy Hir
    on Space.databasename = Hir.databasename
    group by rollup (L0,L1,L2) --(L0, L1,L2,L3)
    order by L0, L1, L2 --, L3
;

到目前为止还不错，但是以某种方式受固定数量的限制。我可以为实际用例自定义(根据找到层次结构的实际深度增加级别(。

是否有不同的方法，可以适应层次结构中的深度？

实际输出为

sum;sum;sum;37780;301
DBC;sum;sum;37780;301
DBC;;sum;34369;125
DBC;;;34369;125
DBC;All;sum;0;0
DBC;All;;0;0
DBC;Crashdumps;sum;71;0
DBC;Crashdumps;;71;0
...
DBC;Samples;sum;1215;159
DBC;Samples;;9;0
DBC;Samples;financial;12;11
DBC;Samples;manufacturing;0;0
DBC;Samples;retail;22;21
DBC;Samples;sandbox;1024;0
DBC;Samples;tpch;52;46
DBC;Samples;transportation;0;0
DBC;Samples;twm_md;76;70
DBC;Samples;twm_results;4;0
DBC;Samples;twm_source;11;9
...
DBC;SysAdmin;sum;1043;2
DBC;SysAdmin;;19;2
DBC;SysAdmin;user1;1024;0

可以。我得到了用户1和样本的总和。但是，如果我添加一个级别，可以说使用用户1拥有的其他数据库，我必须添加一个额外的级别。添加级别或串联的databasenames在汇总中不起作用(至少我没有工作(。我想要该级别的每个名称的总和。

最终的目标是按组对空间使用的概述，该群体位于层次结构内的不同级别上。如果将用户1移至dbc-> groupspace-> group7-> project1-> subproject5-> sandbox，则SQL仍应工作。我希望能够回答有关Group7

Answer 1

dbc.ChildrenV将hierachy解析为每行parent/child，这是我用于计算所有数据库及其子女的电流/麦克斯珀姆的方法：

WITH DBSpace AS 
 ( -- PermSpace for each database
   SELECT
      DatabaseName
     ,Sum(MaxPerm) AS MaxPerm
     ,Sum(CurrentPerm) AS CurrentPerm
   FROM dbc.DiskSpaceV
   GROUP BY DatabaseName
 )
SELECT
   DBSpace.DatabaseName
  ,DBSpace.MaxPerm
  ,DBSpace.CurrentPerm
  ,ChildSpace.ChildrenCount
  ,ChildSpace.ChildrenMaxPerm
  ,ChildSpace.ChildrenCurrentPerm
  ,DBSpace.CurrentPerm + Coalesce(ChildSpace.ChildrenCurrentPerm,0)
FROM DBSpace
LEFT JOIN
 ( -- PermSpace for all children of a database
   SELECT
      ch.Parent
     ,Sum(sp.MaxPerm) AS ChildrenMaxPerm
     ,Sum(sp.CurrentPerm) AS ChildrenCurrentPerm
     ,Count(*)
        -- 4 rows (all/dbc/default/public) for parent = dbc are missing in dbc.Children:
      + CASE WHEN ch.Parent = 'dbc' THEN 4 ELSE 0 END AS ChildrenCount
   FROM
      dbc.ChildrenV AS ch 
   JOIN DBSpace AS sp
     ON ch.Child = sp.DatabaseName
   GROUP BY ch.Parent
 ) AS ChildSpace
ON DBSpace.DatabaseName = ChildSpace.Parent
-- uncomment to return only databases with PermSpace
-- WHERE ChildrenMaxPerm > 0 OR MaxPerm > 0;

您可以将其加入递归查询以按正确的顺序显示层次结构(EXTUSER将丢失，但这只是一个假人(：

WITH RECURSIVE cte (DatabaseName, Path, LEVEL) AS
 (
   SELECT Trim(DatabaseName)
          ,DatabaseName(VARCHAR(600))
          ,0 (BYTEINT)
   FROM   dbc.DatabasesV AS d
   WHERE  DatabaseName = 'dbc'
   UNION ALL
   SELECT Trim(d.DatabaseName)
          ,cte.Path || '.' || Trim(d.DatabaseName)
          ,LEVEL + 1
   FROM   dbc.DatabasesV AS d
          ,cte
   WHERE  d.OwnerName = cte.DatabaseName
   AND    d.DatabaseName <> d.OwnerName
   AND    LEVEL < 20
 )
,DBSpace AS 
 ( -- PermSpace for each database
   SELECT
      DatabaseName
     ,Sum(MaxPerm) AS MaxPerm
     ,Sum(CurrentPerm) AS CurrentPerm
   FROM dbc.DiskSpaceV
   GROUP BY DatabaseName
 )
SELECT LEVEL
       ,Substring(Cast('' AS CHAR(60)) FROM 1 FOR LEVEL * 2)  || cte.DatabaseName AS Hierarchy
       ,AllSpaces.*
FROM cte JOIN 
 (
   SELECT
      DBSpace.DatabaseName
     ,DBSpace.MaxPerm
     ,DBSpace.CurrentPerm
     ,Coalesce(ChildSpace.ChildrenCount, 0) AS ChildrenCount
     ,ChildSpace.ChildrenMaxPerm
     ,ChildSpace.ChildrenCurrentPerm
   FROM DBSpace
   LEFT JOIN
    ( -- PermSpace for all children of a database
      SELECT
         ch.Parent
        ,Sum(sp.MaxPerm) AS ChildrenMaxPerm
        ,Sum(sp.CurrentPerm) AS ChildrenCurrentPerm
        ,Count(*)
           -- 4 rows (all/dbc/default/public) for parent = dbc are missing in dbc.Children:
         + CASE WHEN ch.Parent = 'dbc' THEN 4 ELSE 0 END AS ChildrenCount
      FROM
         dbc.ChildrenV AS ch 
      JOIN DBSpace AS sp
        ON ch.Child = sp.DatabaseName
      GROUP BY ch.Parent
    ) AS ChildSpace
   ON DBSpace.DatabaseName = ChildSpace.Parent
 ) AS AllSpaces
  ON cte.DatabaseName = AllSpaces.DatabaseName
-- uncomment to return only databases with PermSpace
-- WHERE ChildrenMaxPerm > 0 OR MaxPerm > 0
ORDER BY Path;