Aggregative Summe von objecten, die zu objecten gehören, die sich innerhalb der Hierarchiestruktur befinden

Mein Problem ist ähnlich in einer Weise zu diesem , aber anders genug in meinem Verständnis.

Ich habe drei Tische:

  1. Units ([UnitID] int, [UnitParentID] int)
  2. Students ([StudentID] int, [UnitID] int)
  3. Events ([EventID] int, [EventTypeID] int, [StudentID] int)

Students gehören zu Einheiten, Einheiten werden in einer Hierarchie gestapelt (treeform – ein Elternteil pro Kind), und jeder Schüler kann Ereignisse verschiedener Typen haben.

Ich muss die Anzahl der Ereignisse jedes Typs pro Benutzer zusammenfassen, dann für alle Benutzer in einer Einheit aggregieren und dann durch Hierarchie aggregieren, bis ich die Mutter aller Einheiten erreichst.

Das Ergebnis sollte so etwas sein: Ergebnisbeispiel

Meine Tools sind SQL server 2008 und Report Builder 3. Ich habe eine SQL-Geige mit Beispieldaten für Spaß.

Verwenden Sie diese Abfrage:

 ;WITH CTE(Id, ParentId, cLevel, Title, ord) AS ( SELECT u.UnitID, u.UnitParentID, 1, CAST('Unit ' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)) AS varchar(max)), CAST(RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)), 3) AS varchar(max)) FROM dbo.Units u WHERE u.UnitParentID IS NULL UNION ALL SELECT u.UnitID, u.UnitParentID, c.cLevel + 1, c.Title + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY c.cLevel ORDER BY c.Id) AS varchar(3)), c.ord + RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)), 3) FROM dbo.Units u JOIN CTE c ON c.Id = u.UnitParentID WHERE u.UnitParentID IS NOT NULL ), Units AS ( SELECT u.Id, u.ParentId, u.cLevel, u.Title, u.ord, SUM(CASE WHEN e.EventTypeId = 1 THEN 1 ELSE 0 END) AS EventA, SUM(CASE WHEN e.EventTypeId = 2 THEN 1 ELSE 0 END) AS EventB, SUM(CASE WHEN e.EventTypeId = 3 THEN 1 ELSE 0 END) AS EventC, SUM(CASE WHEN e.EventTypeId = 4 THEN 1 ELSE 0 END) AS EventD FROM CTE u LEFT JOIN dbo.Students s ON u.Id = s.UnitId LEFT JOIN dbo.[Events] e ON s.StudentId = e.StudentId GROUP BY u.Id, u.ParentId, u.cLevel, u.Title, u.ord ), addStudents AS ( SELECT * FROM Units UNION ALL SELECT s.StudentId, u.Id, u.cLevel + 1, 'Student ' + CAST(s.StudentId AS varchar(3)), u.ord + RIGHT('000' + CAST(s.StudentId AS varchar(3)), 0), SUM(CASE WHEN e.EventTypeId = 1 THEN 1 ELSE 0 END), SUM(CASE WHEN e.EventTypeId = 2 THEN 1 ELSE 0 END), SUM(CASE WHEN e.EventTypeId = 3 THEN 1 ELSE 0 END), SUM(CASE WHEN e.EventTypeId = 4 THEN 1 ELSE 0 END) FROM Units u JOIN dbo.Students s ON u.Id = s.UnitId LEFT JOIN dbo.[Events] e ON s.StudentId = e.StudentId GROUP BY s.StudentID, u.ID, u.cLevel, u.ord ) SELECT --TOP(10) REPLICATE(' ', cLevel) + Title As Title, EventA, EventB, EventC, EventD FROM addStudents ORDER BY ord 

Dafür:

 Title | EventA | EventB | EventC | EventD -----------------+--------+---------+--------+-------- Unit 1 | 0 | 1 | 0 | 0 Student 6 | 0 | 1 | 0 | 0 Unit 1.1 | 0 | 0 | 0 | 1 Student 21 | 0 | 0 | 0 | 1 Student 33 | 0 | 0 | 0 | 0 Unit 1.1.1 | 0 | 0 | 0 | 0 Student 23 | 0 | 0 | 0 | 0 Unit 1.1.1.1 | 3 | 2 | 3 | 0 Student 10 | 0 | 0 | 0 | 0 Student 17 | 1 | 0 | 0 | 0 ... 

SQL Fiddle Demo

Brauchen Sie auch die Hierarchie, die sortiert / visualisiert werden soll? Mindestens das wird die Summen berechnen, aber die Reihenfolge der data ist ziemlich random 🙂

 ;with CTE as ( select S.StudentId as UnitID, S.UnitId as UnitParentID, S.StudentID, 'Student' as Type from Students S union all select U.UnitId, U.UnitParentId, CTE.StudentId as StudentID, 'Unit ' as Type from Units U join CTE on U.UnitId = CTE.UnitParentId ) select C.Type + ' ' + convert(varchar, C.UnitId), sum(case when EventTypeId = 1 then 1 else 0 end) as E1, sum(case when EventTypeId = 2 then 1 else 0 end) as E2, sum(case when EventTypeId = 3 then 1 else 0 end) as E3, sum(case when EventTypeId = 4 then 1 else 0 end) as E4 from CTE C left outer join events E on C.StudentId = E.StudentId group by C.Type, C.UnitId 

SQL-Geige

Wenn du auch die Hierarchie brauchst, um in Ordnung zu sein, wirst du wahrscheinlich noch ein paar zusätzliche CTEs hinzufügen, um die Nummerierung von oben nach unten mit so etwas wie @ shA.t zu bekommen. Das sammelt die Hierarchie separat für jeden Schüler, so dass es nicht wirklich möglich ist, die Levelzahlen auf einfache Weise hinzuzufügen.