Агрегация на основе иерархии

У меня есть иерархическая таблица в SQL Server 2005, которая содержит сотрудников -> менеджеры -> отдел -> местоположение -> состояние.

Пример таблицы для таблицы иерархии:

ID   Name           ParentID   Type
1    PA             NULL       0 (group)
2    Pittsburgh     1          1 (subgroup)
3    Accounts       2          1 
4    Alex           3          2 (employee)
5    Robin          3          2
6    HR             2          1
7    Robert         6          2

Вторая - это таблица фактов, которая содержит идентификатор зарплаты сотрудника и зарплату.

Пример данных для таблицы фактов:

ID    Salary
4     6000
5     5000
7     4000

Есть ли хороший способ отобразить иерархию из таблицы иерархии с агрегированной суммой заработной платы на основе сотрудников. Ожидаемый результат такой

Name              Salary
PA                15000   (Pittsburgh + others(if any)) 
  Pittusburgh     15000   (Accounts + HR)
    Accounts      11000   (Alex + Robin)
      Alex         6000   (direct values)
      Robin        5000
    HR             4000
      Robert       4000

В моей производственной среде таблица иерархии может содержать более 23 000 строк, а таблица фактов может содержать более 300 000 строк. Итак, я подумал о том, чтобы предоставить запросу любой уровень идентификатора группы, чтобы получить только его дочерние элементы и соответствующее им агрегированное значение. Любое лучшее решение?


sql-server aggregation hierarchy sql-server-2005
person Community    schedule 12.03.2010    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Это решение дает правильный результат. Пока индексы на месте, это должно работать нормально с вашим производственным набором данных, но я не проверял его больше, чем на ваших примерных данных.

DECLARE @tree TABLE
(ID INT
,name VARCHAR(15)
,ParentID INT
,TYPE TINYINT
)

DECLARE @salary TABLE
(ID INT
,Salary INT
)

INSERT @tree
      SELECT 1,'PA',NULL,0
UNION SELECT 2,'Pittsburgh',1,1
UNION SELECT 3,'Accounts',2,1
UNION SELECT 4,'Alex',3,2
UNION SELECT 5,'Robin',3,2
UNION SELECT 6,'HR',2,1
UNION SELECT 7,'Robert',6,2


INSERT @salary
      SELECT 4,6000
UNION SELECT 5,5000
UNION SELECT 7,4000


;WITH salaryCTE
AS
(
        SELECT t.*
               ,s.Salary
        FROM      @tree         AS t
        LEFT JOIN @salary       AS s
        ON        s.ID = t.ID
)
,recCTE
AS
(
        SELECT t.ID
               ,CAST(t.name AS VARCHAR(MAX)) AS name
               ,t.ParentID 
               ,ISNULL(t.Salary,0) AS Salary
               ,0 AS LEVEL
               ,CAST(t.ID AS VARCHAR(100)) AS ord
        FROM  salaryCTE   AS t
        WHERE t.ParentID IS NULL

        UNION ALL

        SELECT t.ID
               ,CAST(REPLICATE(' ',r.LEVEL) + t.name AS VARCHAR(MAX)) AS name
               ,t.ParentID
               ,ISNULL(t.Salary,0) AS Salary
               ,r.LEVEL + 1
               ,CAST(r.ord + '|' + CAST(t.ID AS VARCHAR(11)) AS VARCHAR(100)) AS ord
        FROM      salaryCTE     AS t        
        JOIN      recCTE        AS r
        ON        r.ID = t.ParentID
)
SELECT name
       ,salary
FROM (       
        SELECT r1.name
               ,r1.ord
               ,SUM(r2.salary) AS salary

        FROM recCTE AS r1
        LEFT JOIN recCTE AS r2
        ON   r2.ord LIKE r1.ord + '%'
        GROUP BY r1.name,r1.ord 
     ) AS x
ORDER BY ord,name
person Ed Harper    schedule 12.03.2010