Sql-Server

優化 CTE 層次結構

  • March 6, 2021

下面更新

我有一個具有典型帳戶/父帳戶體系結構的帳戶表來表示帳戶層次結構（SQL Server 2012）。我使用 CTE 創建了一個 VIEW 來散列層次結構，總的來說它工作得很好，而且符合預期。我可以查詢任何級別的層次結構，並輕鬆查看分支。

有一個業務邏輯欄位需要作為層次結構的函式返回。每個帳戶記錄中的一個欄位描述了企業的規模（我們將其稱為 CustomerCount）。我需要報告的邏輯需要從整個分支匯總 CustomerCount。換句話說，給定一個帳戶，我需要將該帳戶的 customercount 值以及沿層次結構的帳戶下方每個分支中的每個子項相加。

我使用 CTE 中建構的層次結構欄位成功計算了該欄位，該欄位看起來像 acct4.acct3.acct2.acct1。我遇到的問題只是讓它執行得很快。如果沒有這個計算欄位，查詢將在大約 3 秒內執行。當我添加計算欄位時，它變成了一個 4 分鐘的查詢。

這是我能想到的最好的版本，它可以返回正確的結果。我正在尋找有關如何在不犧牲性能的情況下重組此視圖的想法。

我理解這個變慢的原因（需要在 where 子句中計算謂詞），但我想不出另一種方式來建構它並且仍然得到相同的結果。

這是一些範常式式碼，用於建構表並在我的環境中幾乎完全按照它的工作方式執行 CTE。

Use Tempdb
go
CREATE TABLE dbo.Account
(
  Acctid varchar(1) NOT NULL
   , Name varchar(30) NULL
   , ParentId varchar(1) NULL
   , CustomerCount int NULL
);

INSERT Account
SELECT 'A','Best Bet',NULL,21  UNION ALL
SELECT 'B','eStore','A',30 UNION ALL
SELECT 'C','Big Bens','B',75 UNION ALL
SELECT 'D','Mr. Jimbo','B',50 UNION ALL
SELECT 'E','Dr. John','C',100 UNION ALL
SELECT 'F','Brick','A',222 UNION ALL
SELECT 'G','Mortar','C',153 ;


With AccountHierarchy AS

(                                                                           --Root values have no parent
   SELECT
       Root.AcctId                                         AccountId
       , Root.Name                                         AccountName
       , Root.ParentId                                     ParentId
       , 1                                                 HierarchyLevel  
       , cast(Root.Acctid as varchar(4000))                IdHierarchy     --highest parent reads right to left as in id3.Acctid2.Acctid1
       , cast(replace(Root.Name,'.','') as varchar(4000))  NameHierarchy   --highest parent reads right to left as in name3.name2.name1 (replace '.' so name parse is easy in last step)
       , cast(Root.Acctid as varchar(4000))                HierarchySort   --reverse of above, read left to right name1.name2.name3 for sorting on reporting only
       , cast(Root.Name as varchar(4000))                  HierarchyLabel  --use for labels on reporting only, indents names under sorted hierarchy
       , Root.CustomerCount                                CustomerCount   

   FROM 
       tempdb.dbo.account Root

   WHERE
       Root.ParentID is null

   UNION ALL

   SELECT
       Recurse.Acctid                                      AccountId
       , Recurse.Name                                      AccountName
       , Recurse.ParentId                                  ParentId
       , Root.HierarchyLevel + 1                           HierarchyLevel  --next level in hierarchy
       , cast(cast(recurse.Acctid as varchar(40)) + '.' + Root.IdHierarchy as varchar(4000))   IdHierarchy --cast because in real system this is a uniqueidentifier type needs converting
       , cast(replace(recurse.Name,'.','') + '.' + Root.NameHierarchy as varchar(4000)) NameHierarchy  --replace '.' for parsing in last step, cast to make room for lots of sub levels down the hierarchy
       , cast(Root.AccountName + '.' + Recurse.Name as varchar(4000)) HierarchySort    
       , cast(space(root.HierarchyLevel * 4) + Recurse.Name as varchar(4000)) HierarchyLabel
       , Recurse.CustomerCount                             CustomerCount

   FROM
       tempdb.dbo.account Recurse INNER JOIN
       AccountHierarchy Root on Root.AccountId = Recurse.ParentId
)


SELECT
   hier.AccountId
   , Hier.AccountName
   , hier.ParentId
   , hier.HierarchyLevel
   , hier.IdHierarchy
   , hier.NameHierarchy
   , hier.HierarchyLabel
   , parsename(hier.IdHierarchy,1) Acct1Id
   , parsename(hier.NameHierarchy,1) Acct1Name     --This is why we stripped out '.' during recursion
   , parsename(hier.IdHierarchy,2) Acct2Id
   , parsename(hier.NameHierarchy,2) Acct2Name
   , parsename(hier.IdHierarchy,3) Acct3Id
   , parsename(hier.NameHierarchy,3) Acct3Name
   , parsename(hier.IdHierarchy,4) Acct4Id
   , parsename(hier.NameHierarchy,4) Acct4Name
   , hier.CustomerCount

   /* fantastic up to this point. Next block of code is what causes problem. 
       Logic of code is "sum of CustomerCount for this location and all branches below in this branch of hierarchy"
       In live environment, goes from taking 3 seconds to 4 minutes by adding this one calc */

   , (
       SELECT  
           sum(children.CustomerCount)
       FROM
           AccountHierarchy Children
       WHERE
           hier.IdHierarchy = right(children.IdHierarchy, (1 /*length of id field*/ * hier.HierarchyLevel) + hier.HierarchyLevel - 1 /*for periods inbetween ids*/)
           --"where this location's idhierarchy is within child idhierarchy"
           --previously tried a charindex(hier.IdHierarchy,children.IdHierarchy)>0, but that performed even worse
       ) TotalCustomerCount
FROM
   AccountHierarchy hier

ORDER BY
   hier.HierarchySort


drop table tempdb.dbo.Account

2013 年 11 月 20 日更新

一些建議的解決方案讓我興奮不已，我嘗試了一種接近的新方法，但引入了一個新的/不同的障礙。老實說，我不知道這是否需要單獨發布，但這與解決此問題有關。

我的決定是，使總和（客戶數量）變得困難的是在從頂部開始並向下建立的層次結構的上下文中辨識孩子。因此，我首先創建了一個自下而上建構的層次結構，使用由“不是任何其他帳戶的父帳戶”定義的根並向後進行遞歸連接（root.parentacctid = recurse.acctid）

這樣我就可以在遞歸發生時將子客戶計數添加到父級。由於我需要報告和級別，除了自上而下之外，我正在執行自下而上的 cte，然後通過帳戶 ID 加入它們。這種方法比原來的外部查詢 customercount 快得多，但我遇到了一些障礙。

首先，我無意中為多個孩子的父賬戶擷取了重複的客戶數量。根據孩子的數量，我對某些 acctid 的客戶數量進行了兩倍或三倍的計算。我的解決方案是創建另一個 cte，它計算一個賬戶有多少個節點，並在遞歸期間劃分 acct.customercount，所以當我將整個分支相加時，賬戶不會被重複計算。

所以在這一點上，這個新版本的結果是不正確的，但我知道為什麼。自下而上的 cte 正在創建重複項。當遞歸通過時，它會在根（底層子級）中查找作為帳戶表中帳戶的子級的任何內容。在第三次遞歸時，它會提取與第二次相同的帳戶並再次放入。

關於如何進行自下而上的 cte 的想法，或者這是否會產生任何其他想法？

Use Tempdb
go


CREATE TABLE dbo.Account
(
   Acctid varchar(1) NOT NULL
   , Name varchar(30) NULL
   , ParentId varchar(1) NULL
   , CustomerCount int NULL
);

INSERT Account
SELECT 'A','Best Bet',NULL,1  UNION ALL
SELECT 'B','eStore','A',2 UNION ALL
SELECT 'C','Big Bens','B',3 UNION ALL
SELECT 'D','Mr. Jimbo','B',4 UNION ALL
SELECT 'E','Dr. John','C',5 UNION ALL
SELECT 'F','Brick','A',6 UNION ALL
SELECT 'G','Mortar','C',7 ;



With AccountHierarchy AS

(                                                                           --Root values have no parent
   SELECT
       Root.AcctId                                         AccountId
       , Root.Name                                         AccountName
       , Root.ParentId                                     ParentId
       , 1                                                 HierarchyLevel  
       , cast(Root.Acctid as varchar(4000))                IdHierarchy     --highest parent reads right to left as in id3.Acctid2.Acctid1
       , cast(replace(Root.Name,'.','') as varchar(4000))  NameHierarchy   --highest parent reads right to left as in name3.name2.name1 (replace '.' so name parse is easy in last step)
       , cast(Root.Acctid as varchar(4000))                HierarchySort   --reverse of above, read left to right name1.name2.name3 for sorting on reporting only
       , cast(Root.Acctid as varchar(4000))                HierarchyMatch 
       , cast(Root.Name as varchar(4000))                  HierarchyLabel  --use for labels on reporting only, indents names under sorted hierarchy
       , Root.CustomerCount                                CustomerCount   

   FROM 
       tempdb.dbo.account Root

   WHERE
       Root.ParentID is null

   UNION ALL

   SELECT
       Recurse.Acctid                                      AccountId
       , Recurse.Name                                      AccountName
       , Recurse.ParentId                                  ParentId
       , Root.HierarchyLevel + 1                           HierarchyLevel  --next level in hierarchy
       , cast(cast(recurse.Acctid as varchar(40)) + '.' + Root.IdHierarchy as varchar(4000))   IdHierarchy --cast because in real system this is a uniqueidentifier type needs converting
       , cast(replace(recurse.Name,'.','') + '.' + Root.NameHierarchy as varchar(4000)) NameHierarchy  --replace '.' for parsing in last step, cast to make room for lots of sub levels down the hierarchy
       , cast(Root.AccountName + '.' + Recurse.Name as varchar(4000)) HierarchySort    
       , CAST(CAST(Root.HierarchyMatch as varchar(40)) + '.' 
           + cast(recurse.Acctid as varchar(40))   as varchar(4000))   HierarchyMatch
       , cast(space(root.HierarchyLevel * 4) + Recurse.Name as varchar(4000)) HierarchyLabel
       , Recurse.CustomerCount                             CustomerCount

   FROM
       tempdb.dbo.account Recurse INNER JOIN
       AccountHierarchy Root on Root.AccountId = Recurse.ParentId
)

, Nodes as
(   --counts how many branches are below for any account that is parent to another
   select
       node.ParentId Acctid
       , cast(count(1) as float) Nodes
   from AccountHierarchy  node
   group by ParentId
)

, BottomUp as
(   --creates the hierarchy starting at accounts that are not parent to any other
   select
       Root.Acctid
       , root.ParentId
       , cast(isnull(root.customercount,0) as float) CustomerCount
   from
       tempdb.dbo.Account Root
   where
       not exists ( select 1 from tempdb.dbo.Account OtherAccts where root.Acctid = OtherAccts.ParentId)

   union all

   select
       Recurse.Acctid
       , Recurse.ParentId
       , root.CustomerCount + cast ((isnull(recurse.customercount,0) / nodes.nodes) as float) CustomerCount
       -- divide the recurse customercount by number of nodes to prevent duplicate customer count on accts that are parent to multiple children, see customercount cte next
   from
       tempdb.dbo.Account Recurse inner join 
       BottomUp Root on root.ParentId = recurse.acctid inner join
       Nodes on nodes.Acctid = recurse.Acctid
)

, CustomerCount as
(
   select
       sum(CustomerCount) TotalCustomerCount
       , hier.acctid
   from
       BottomUp hier
   group by 
       hier.Acctid
)


SELECT
   hier.AccountId
   , Hier.AccountName
   , hier.ParentId
   , hier.HierarchyLevel
   , hier.IdHierarchy
   , hier.NameHierarchy
   , hier.HierarchyLabel
   , hier.hierarchymatch
   , parsename(hier.IdHierarchy,1) Acct1Id
   , parsename(hier.NameHierarchy,1) Acct1Name     --This is why we stripped out '.' during recursion
   , parsename(hier.IdHierarchy,2) Acct2Id
   , parsename(hier.NameHierarchy,2) Acct2Name
   , parsename(hier.IdHierarchy,3) Acct3Id
   , parsename(hier.NameHierarchy,3) Acct3Name
   , parsename(hier.IdHierarchy,4) Acct4Id
   , parsename(hier.NameHierarchy,4) Acct4Name
   , hier.CustomerCount

   , customercount.TotalCustomerCount

FROM
   AccountHierarchy hier inner join
   CustomerCount on customercount.acctid = hier.accountid

ORDER BY
   hier.HierarchySort 



drop table tempdb.dbo.Account

編輯：這是第二次嘗試

根據@Hannah Vernon 的回答，這是一種繞過在內聯子查詢中使用 CTE 的方法，這就像自加入 CTE，我認為是效率低下的原因。它使用僅在 2012 版 SQL-Server 中可用的分析函式。在**SQL-Fiddle測試**

這部分可以從閱讀中跳過，這是漢娜回答的複制粘貼：

;With AccountHierarchy AS
(                                                                           
   SELECT
       Root.AcctId                                         AccountId
       , Root.Name                                         AccountName
       , Root.ParentId                                     ParentId
       , 1                                                 HierarchyLevel  
       , cast(Root.Acctid as varchar(4000))                IdHierarchyMatch        
       , cast(Root.Acctid as varchar(4000))                IdHierarchy
       , cast(replace(Root.Name,'.','') as varchar(4000))  NameHierarchy   
       , cast(Root.Acctid as varchar(4000))                HierarchySort
       , cast(Root.Name as varchar(4000))                  HierarchyLabel          ,
       Root.CustomerCount                                  CustomerCount   

   FROM 
       account Root

   WHERE
       Root.ParentID is null

   UNION ALL

   SELECT
       Recurse.Acctid                                      AccountId
       , Recurse.Name                                      AccountName
       , Recurse.ParentId                                  ParentId
       , Root.HierarchyLevel + 1                           HierarchyLevel
       , CAST(CAST(Root.IdHierarchyMatch as varchar(40)) + '.' 
           + cast(recurse.Acctid as varchar(40))   as varchar(4000))   IdHierarchyMatch
       , cast(cast(recurse.Acctid as varchar(40)) + '.' 
           + Root.IdHierarchy  as varchar(4000))           IdHierarchy
       , cast(replace(recurse.Name,'.','') + '.' 
           + Root.NameHierarchy as varchar(4000))          NameHierarchy
       , cast(Root.AccountName + '.' 
           + Recurse.Name as varchar(4000))                HierarchySort   
       , cast(space(root.HierarchyLevel * 4) 
           + Recurse.Name as varchar(4000))                HierarchyLabel
       , Recurse.CustomerCount                             CustomerCount
   FROM
       account Recurse INNER JOIN
       AccountHierarchy Root on Root.AccountId = Recurse.ParentId
)

在這裡，我們使用 對 CTE 的行進行排序，IdHierarchyMatch併計算行數和執行總數（從下一行到最後）。

, cte1 AS 
(
SELECT
   h.AccountId
   , h.AccountName
   , h.ParentId
   , h.HierarchyLevel
   , h.IdHierarchy
   , h.NameHierarchy
   , h.HierarchyLabel
   , parsename(h.IdHierarchy,1) Acct1Id
   , parsename(h.NameHierarchy,1) Acct1Name
   , parsename(h.IdHierarchy,2) Acct2Id
   , parsename(h.NameHierarchy,2) Acct2Name
   , parsename(h.IdHierarchy,3) Acct3Id
   , parsename(h.NameHierarchy,3) Acct3Name
   , parsename(h.IdHierarchy,4) Acct4Id
   , parsename(h.NameHierarchy,4) Acct4Name
   , h.CustomerCount
   , h.HierarchySort
   , h.IdHierarchyMatch
       , Rn = ROW_NUMBER() OVER 
                 (ORDER BY h.IdHierarchyMatch)
       , RunningCustomerCount = COALESCE(
           SUM(h.CustomerCount)
           OVER
             (ORDER BY h.IdHierarchyMatch
              ROWS BETWEEN 1 FOLLOWING
                       AND UNBOUNDED FOLLOWING)
         , 0) 
FROM
   AccountHierarchy AS h  
)

然後我們還有一個中間 CTE，我們使用之前的執行總計和行號 - 基本上是為了找到樹結構分支的端點：

, cte2 AS
(
SELECT
   cte1.*
   , rn3  = LAST_VALUE(Rn) OVER 
              (PARTITION BY Acct1Id, Acct2Id, Acct3Id 
               ORDER BY Acct4Id
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)       
   , rn2  = LAST_VALUE(Rn) OVER 
              (PARTITION BY Acct1Id, Acct2Id 
               ORDER BY Acct3Id, Acct4Id
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
   , rn1  = LAST_VALUE(Rn) OVER 
              (PARTITION BY Acct1Id 
               ORDER BY Acct2Id, Acct3Id, Acct4Id
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
   , rcc3 = LAST_VALUE(RunningCustomerCount) OVER 
              (PARTITION BY Acct1Id, Acct2Id, Acct3Id 
               ORDER BY Acct4Id
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)       
   , rcc2 = LAST_VALUE(RunningCustomerCount) OVER 
              (PARTITION BY Acct1Id, Acct2Id 
               ORDER BY Acct3Id, Acct4Id
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
   , rcc1 = LAST_VALUE(RunningCustomerCount) OVER 
              (PARTITION BY Acct1Id 
               ORDER BY Acct2Id, Acct3Id, Acct4Id
               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
FROM
   cte1 
)

最後我們建構最後一部分：

SELECT
   hier.AccountId
   , hier.AccountName
   ---                        -- columns skipped 
   , hier.CustomerCount

   , TotalCustomerCount = hier.CustomerCount
       + hier.RunningCustomerCount 
       - ca.LastRunningCustomerCount

   , hier.HierarchySort
   , hier.IdHierarchyMatch
FROM
   cte2 hier
 OUTER APPLY
   ( SELECT  LastRunningCustomerCount, Rn
     FROM
     ( SELECT LastRunningCustomerCount
             = RunningCustomerCount, Rn
       FROM (SELECT NULL a) x  WHERE 4 <= HierarchyLevel 
     UNION ALL
       SELECT rcc3, Rn3
       FROM (SELECT NULL a) x  WHERE 3 <= HierarchyLevel 
     UNION ALL
       SELECT rcc2, Rn2 
       FROM (SELECT NULL a) x  WHERE 2 <= HierarchyLevel 
     UNION ALL
       SELECT rcc1, Rn1
       FROM (SELECT NULL a) x  WHERE 1 <= HierarchyLevel 
     ) x
     ORDER BY Rn 
     OFFSET 0 ROWS
     FETCH NEXT 1 ROWS ONLY
     ) ca
ORDER BY
   hier.HierarchySort ;

和一個簡化，使用與cte1上面的程式碼相同。在**SQL-Fiddle-2 進行**測試。請注意，這兩種解決方案都假設您的樹中最多有四個級別：

SELECT
   hier.AccountId
   ---                      -- skipping rows
   , hier.CustomerCount

   , TotalCustomerCount = CustomerCount
       + RunningCustomerCount 
       - CASE HierarchyLevel
           WHEN 4 THEN RunningCustomerCount
           WHEN 3 THEN LAST_VALUE(RunningCustomerCount) OVER 
                  (PARTITION BY Acct1Id, Acct2Id, Acct3Id 
                   ORDER BY Acct4Id
                   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)       
           WHEN 2 THEN LAST_VALUE(RunningCustomerCount) OVER 
                  (PARTITION BY Acct1Id, Acct2Id 
                   ORDER BY Acct3Id, Acct4Id
                   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
           WHEN 1 THEN LAST_VALUE(RunningCustomerCount) OVER 
                  (PARTITION BY Acct1Id 
                   ORDER BY Acct2Id, Acct3Id, Acct4Id
                   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
         END

   , hier.HierarchySort
   , hier.IdHierarchyMatch
FROM cte1 AS hier
ORDER BY
   hier.HierarchySort ;

第三種方法，只有一個 CTE，用於遞歸部分，然後只有視窗聚合函式 ( SUM() OVER (...))，因此它應該適用於 2005 年以後的任何版本。在**SQL-Fiddle-3 進行**測試該解決方案與之前的解決方案一樣假設層次結構樹中最多有 4 個級別：

;WITH AccountHierarchy AS
(                                                                           
   SELECT
         AccountId      = Root.AcctId                                         
       , AccountName    = Root.Name                                         
       , ParentId       = Root.ParentId                                     
       , HierarchyLevel = 1                                                   
       , HierarchySort  = CAST(Root.Acctid AS VARCHAR(4000))                
       , HierarchyLabel = CAST(Root.Name AS VARCHAR(4000))                   
       , Acct1Id        = CAST(Root.Acctid AS VARCHAR(4000))                
       , Acct2Id        = CAST(NULL AS VARCHAR(4000))                       
       , Acct3Id        = CAST(NULL AS VARCHAR(4000))                       
       , Acct4Id        = CAST(NULL AS VARCHAR(4000))                       
       , Acct1Name      = CAST(Root.Name AS VARCHAR(4000))                  
       , Acct2Name      = CAST(NULL AS VARCHAR(4000))                       
       , Acct3Name      = CAST(NULL AS VARCHAR(4000))                       
       , Acct4Name      = CAST(NULL AS VARCHAR(4000))                       
       , CustomerCount  = Root.CustomerCount                                   

   FROM 
       account AS Root

   WHERE
       Root.ParentID IS NULL

   UNION ALL

   SELECT
         Recurse.Acctid 
       , Recurse.Name 
       , Recurse.ParentId 
       , Root.HierarchyLevel + 1 
       , CAST(Root.AccountName + '.' 
           + Recurse.Name AS VARCHAR(4000)) 
       , CAST(SPACE(Root.HierarchyLevel * 4) 
           + Recurse.Name AS VARCHAR(4000)) 
       , Root.Acct1Id 
       , CASE WHEN Root.HierarchyLevel = 1 
             THEN cast(Recurse.Acctid AS VARCHAR(4000)) 
             ELSE Root.Acct2Id 
         END 
       , CASE WHEN Root.HierarchyLevel = 2 
             THEN CAST(Recurse.Acctid AS VARCHAR(4000)) 
             ELSE Root.Acct3Id 
         END 
       , CASE WHEN Root.HierarchyLevel = 3 
             THEN CAST(Recurse.Acctid AS VARCHAR(4000)) 
             ELSE Root.Acct4Id 
         END 

       , cast(Root.AccountName as varchar(4000))          
       , CASE WHEN Root.HierarchyLevel = 1 
             THEN CAST(Recurse.Name AS VARCHAR(4000)) 
             ELSE Root.Acct2Name 
         END 
       , CASE WHEN Root.HierarchyLevel = 2 
             THEN CAST(Recurse.Name AS VARCHAR(4000)) 
             ELSE Root.Acct3Name 
         END 
       , CASE WHEN Root.HierarchyLevel = 3 
             THEN CAST(Recurse.Name AS VARCHAR(4000)) 
             ELSE Root.Acct4Name 
         END 
       , Recurse.CustomerCount 
   FROM 
       account AS Recurse INNER JOIN 
       AccountHierarchy AS Root ON Root.AccountId = Recurse.ParentId
)

SELECT
     h.AccountId
   , h.AccountName
   , h.ParentId
   , h.HierarchyLevel
   , IdHierarchy = 
         CAST(COALESCE(h.Acct4Id+'.','') 
              + COALESCE(h.Acct3Id+'.','') 
              + COALESCE(h.Acct2Id+'.','') 
              + h.Acct1Id AS VARCHAR(4000))
   , NameHierarchy = 
         CAST(COALESCE(h.Acct4Name+'.','') 
              + COALESCE(h.Acct3Name+'.','') 
              + COALESCE(h.Acct2Name+'.','') 
              + h.Acct1Name AS VARCHAR(4000))   
   , h.HierarchyLabel
   , h.Acct1Id
   , h.Acct1Name
   , h.Acct2Id
   , h.Acct2Name
   , h.Acct3Id
   , h.Acct3Name
   , h.Acct4Id
   , h.Acct4Name
   , h.CustomerCount
   , TotalCustomerCount =  
         CASE h.HierarchyLevel
           WHEN 4 THEN h.CustomerCount
           WHEN 3 THEN SUM(h.CustomerCount) OVER 
                  (PARTITION BY h.Acct1Id, h.Acct2Id, h.Acct3Id)       
           WHEN 2 THEN SUM(h.CustomerCount) OVER 
                  (PARTITION BY Acct1Id, h.Acct2Id) 
           WHEN 1 THEN SUM(h.CustomerCount) OVER 
                  (PARTITION BY h.Acct1Id) 
         END
   , h.HierarchySort
   , IdHierarchyMatch = 
         CAST(h.Acct1Id 
              + COALESCE('.'+h.Acct2Id,'') 
              + COALESCE('.'+h.Acct3Id,'') 
              + COALESCE('.'+h.Acct4Id,'') AS VARCHAR(4000))   
FROM
   AccountHierarchy AS h  
ORDER BY
   h.HierarchySort ;

第四種方法，作為中間 CTE 計算層次結構的閉合表。在**SQL-Fiddle-4 進行**測試。好處是對於總和計算，對級別數沒有限制。

;WITH AccountHierarchy AS
( 
   -- skipping several line, identical to the 3rd approach above
)

, ClosureTable AS
( 
   SELECT
         AccountId      = Root.AcctId  
       , AncestorId     = Root.AcctId  
       , CustomerCount  = Root.CustomerCount 
   FROM 
       account AS Root

   UNION ALL

   SELECT
         Recurse.Acctid 
       , Root.AncestorId 
       , Recurse.CustomerCount
   FROM 
       account AS Recurse INNER JOIN 
       ClosureTable AS Root ON Root.AccountId = Recurse.ParentId
)

, ClosureGroup AS
(                                                                           
   SELECT
         AccountId           = AncestorId  
       , TotalCustomerCount  = SUM(CustomerCount)                             
   FROM 
       ClosureTable AS a
   GROUP BY
       AncestorId
)

SELECT
     h.AccountId
   , h.AccountName
   , h.ParentId
   , h.HierarchyLevel 
   , h.HierarchyLabel
   , h.CustomerCount
   , cg.TotalCustomerCount 

   , h.HierarchySort
FROM
   AccountHierarchy AS h  
 JOIN
   ClosureGroup AS cg
     ON cg.AccountId = h.AccountId
ORDER BY
   h.HierarchySort ;

我相信這應該使它更快：

;With AccountHierarchy AS
(                                                                           
   SELECT
       Root.AcctId                                         AccountId
       , Root.Name                                         AccountName
       , Root.ParentId                                     ParentId
       , 1                                                 HierarchyLevel  
       , cast(Root.Acctid as varchar(4000))                IdHierarchyMatch        
       , cast(Root.Acctid as varchar(4000))                IdHierarchy
       , cast(replace(Root.Name,'.','') as varchar(4000))  NameHierarchy   
       , cast(Root.Acctid as varchar(4000))                HierarchySort
       , cast(Root.Name as varchar(4000))                  HierarchyLabel          ,
       Root.CustomerCount                                  CustomerCount   

   FROM 
       tempdb.dbo.account Root

   WHERE
       Root.ParentID is null

   UNION ALL

   SELECT
       Recurse.Acctid                                      AccountId
       , Recurse.Name                                      AccountName
       , Recurse.ParentId                                  ParentId
       , Root.HierarchyLevel + 1                           HierarchyLevel
       , CAST(CAST(Root.IdHierarchyMatch as varchar(40)) + '.' 
           + cast(recurse.Acctid as varchar(40))   as varchar(4000))   IdHierarchyMatch
       , cast(cast(recurse.Acctid as varchar(40)) + '.' 
           + Root.IdHierarchy  as varchar(4000))           IdHierarchy
       , cast(replace(recurse.Name,'.','') + '.' 
           + Root.NameHierarchy as varchar(4000))          NameHierarchy
       , cast(Root.AccountName + '.' 
           + Recurse.Name as varchar(4000))                HierarchySort   
       , cast(space(root.HierarchyLevel * 4) 
           + Recurse.Name as varchar(4000))                HierarchyLabel
       , Recurse.CustomerCount                             CustomerCount
   FROM
       tempdb.dbo.account Recurse INNER JOIN
       AccountHierarchy Root on Root.AccountId = Recurse.ParentId
)


SELECT
   hier.AccountId
   , Hier.AccountName
   , hier.ParentId
   , hier.HierarchyLevel
   , hier.IdHierarchy
   , hier.NameHierarchy
   , hier.HierarchyLabel
   , parsename(hier.IdHierarchy,1) Acct1Id
   , parsename(hier.NameHierarchy,1) Acct1Name
   , parsename(hier.IdHierarchy,2) Acct2Id
   , parsename(hier.NameHierarchy,2) Acct2Name
   , parsename(hier.IdHierarchy,3) Acct3Id
   , parsename(hier.NameHierarchy,3) Acct3Name
   , parsename(hier.IdHierarchy,4) Acct4Id
   , parsename(hier.NameHierarchy,4) Acct4Name
   , hier.CustomerCount
   , (
       SELECT  
           sum(children.CustomerCount)
       FROM
           AccountHierarchy Children
       WHERE
           Children.IdHierarchyMatch LIKE hier.IdHierarchyMatch + '%'
       ) TotalCustomerCount
       , HierarchySort
       , IdHierarchyMatch
FROM
   AccountHierarchy hier
ORDER BY
   hier.HierarchySort

我在 CTE 中添加了一個名為的列，該列IdHierarchyMatch是 的正向版本，IdHierarchy以使TotalCustomerCount子查詢WHERE子句可搜尋。

比較執行計劃的估計子樹成本，這種方式應該快大約 5 倍。

引用自：https://dba.stackexchange.com/questions/53348

相關問答

Sql-Server

SQL 伺服器總是在 3 節點主節點關閉

  • February 16, 2022
檢視問答
Sql-Server

如何在一行中分組/顯示相同的日期數據？

  • November 15, 2021
檢視問答
Sql-Server

如何在不使用靜態組 ID 的情況下動態執行 join 語句？

  • June 1, 2021
檢視問答
Sql-Server

如何針對索引改進受 DateAdd() 約束的視圖中 1 行的估計

  • February 8, 2021
檢視問答
Sql-Server

SQL Server 是否優化同一列上的兩個“>”操作

  • December 23, 2020
檢視問答
Sql-Server

如何重寫慢速 CTE 構造以匹配臨時表的速度

  • December 7, 2020
檢視問答