SQL Abfrage für Rekursive Struktur mit Nested-Sets

Question

Hallo,
ich habe eine Rekursive Struktur mit Nested-Sets.

Es gibt verschiedene Mitarbeiter mit unterschiedlichen Stundsätzen
Mitarbeiter a) 10 EUR
Mitarbeiter b) 15 EUR
Mitarbeiter c) 20 EUR

Nun soll der Durchschnittstundensatz aller Mitarbeiter berechnet werden, wobei diese Mitarbeiter in einer Rekursiven Struktur mit Nested-Sets abgebildet werden.
Ebenfalls sollen Zwischenergebnisse je Struktur ermittelt werden können.
Mitarbeiter a) 10 Stunden mit je 10 EUR = 100 EUR
Mitarbeiter b) 5 Stunden mit je 15 EUR =75 EUR
Mitarbeiter c) 2 Stunden mit je 20 EUR =40 EUR

Rechenweise: Summe von (Stundensatz * Anzahl Stunden) geteilt durch Summe der Anzahl Stunden
Mitarbeiter a und Mitarbeiter gehören zu dem Vorgesetztem „Meier“
Vorgesetzter Meier=( (10 Std *10 EUR) + (5 Std * 15 EUR))/15 Std =11,6 EUR

Mitarbeiter c gehört zu Vorgesetztem „Müller“
Vorgesetzter Müller =( (2 Std *20 EUR) )/2 Std =20 EUR

Gesamt: 215 EUR geteilt durch 17 Stunden=12,64 EUR

Struktur:
Tabelle „Struktur“

ID Bezeichnung IDRekursiv lft rht
1--Gesamt-------0---------1---6
2--Meier--------1---------2---3
3--Müller-------2---------4---5

Tabelle „Mitarbeiter“

ID Bezeichnung Stundensatz AnzahlStunden
1--a-------------10------------10
2--b-------------15-------------5
3--c-------------20-------------2

Tabelle „Verknüpfung“

ID IDStruktur IDMitarbeiter
1--2----------1
2--2----------2
3--3----------3

Meine Frage ist nun, ob ich einen SQL Befehl bauen kann, der mir für jeden Mitarbeiter anzeigt, sowie für jede Ebene der Struktur einen Durchschnittlichen Stundensatz ausgibt.
Folgende Tabelle soll meine Ausgabe sein.

chef-----Mitarbeiter Lohn Stundensatz Durchsch. 
Gesamt---------------215--17-------------12,64
Meier----------------175--15-------------11,6
Meier----a-----------100--10-------------10
Meier----b-----------75---5--------------15
Müller---------------40---2--------------20
Müller---c-----------40---2--------------20

Mit dem folgenden Befehl erhalte ich "fast" das gewünschte Ergebnis.
Nur die Zeile mit dem "Gesamt" erhalte ich nicht.
Das Ergebnis von der Gruppe "Gesamt" soll von allen untergeordneten Gruppen und davon aller zugeordneten Mitarbeiten das Ergebnis ermitteln.
Wichtig: Die Struktur ist beliebig tief erweiterbar und es ist möglich, dass es viele "Zwischengruppen" ohne direkt zugeordnete Mitarbeitern gibt. Für jede Zwischengruppe benötige ich jedoch ein Ergebnis.

SELECT chef, ' ' as Mitarbeiter, sum(lohn) as Lohn, sum(AnzahlStunden) as StdSatz, sum(lohn)/sum(AnzahlStunden) as Durchschnitt
FROM (SELECT mitarbeiter.Bezeichnung , mitarbeiter.AnzahlStunden , mitarbeiter.Stundensatz, mitarbeiter.Stundensatz*mitarbeiter.AnzahlStunden AS lohn, 
	Struktur.Bezeichnung AS chef 
FROM Struktur,Mitarbeiter,Verknuepfung 
Where Mitarbeiter.ID = Verknuepfung.IDMitarbeiter and Struktur.ID = Verknuepfung.IDStruktur
) AS help
WHERE NOT chef IS NULL
GROUP BY chef
HAVING NOT sum(AnzahlStunden) = 0
UNION 
SELECT chef, Mitarb as Mitarbeiter, sum(lohn) as Lohn, sum(AnzahlStunden) as StdSatz, sum(lohn)/sum(AnzahlStunden) as Durchschnitt
FROM (SELECT mitarbeiter.Bezeichnung as Mitarb, mitarbeiter.AnzahlStunden , mitarbeiter.Stundensatz, mitarbeiter.Stundensatz*mitarbeiter.AnzahlStunden AS lohn, 
	Struktur.Bezeichnung AS chef
FROM Struktur,Mitarbeiter,Verknuepfung 
Where Mitarbeiter.ID = Verknuepfung.IDMitarbeiter and Struktur.ID = Verknuepfung.IDStruktur
) AS help
WHERE NOT AnzahlStunden = 0
GROUP BY chef, Mitarb
Order by chef

Anbei das Script für die Datenstruktur

USE [TEST_TS]
GO
/****** Object:  Table [dbo].[Struktur]    Script Date: 01/23/2013 11:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Struktur](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Bezeichnung] [nvarchar](255) NULL,
	[IDRekursiv] [int] NULL,	
	[lft] [int] NULL,
	[rgt] [int] NULL,
 CONSTRAINT [aaaaaStruktur_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[Struktur] ON
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv], [lft],[rgt]) VALUES (1, N'Gesamt', 0,1,6)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv], [lft],[rgt]) VALUES (2, N'Meier', 1,2,3)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv], [lft],[rgt]) VALUES (3, N'Müller', 2,4,5)
SET IDENTITY_INSERT [dbo].[Struktur] OFF

/****** Object:  Table [dbo].[Verknuepfung]    Script Date: 01/23/2013 11:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Verknuepfung](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IDStruktur] [int] NULL,
	[IDMitarbeiter] [int] NULL,
 CONSTRAINT [aaaaaVerknuepfung_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Verknuepfung] ON
INSERT [dbo].[Verknuepfung] ([ID], [IDStruktur], [IDMitarbeiter]) VALUES (2, 2, 1)
INSERT [dbo].[Verknuepfung] ([ID], [IDStruktur], [IDMitarbeiter]) VALUES (3, 2, 2)
INSERT [dbo].[Verknuepfung] ([ID], [IDStruktur], [IDMitarbeiter]) VALUES (4, 3, 3)
SET IDENTITY_INSERT [dbo].[Verknuepfung] OFF


/****** Object:  Table [dbo].[Mitarbeiter]    Script Date: 01/23/2013 11:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Mitarbeiter](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Bezeichnung] [nvarchar](255) NULL,
	[Stundensatz] [int] NULL,
	[AnzahlStunden] [int] NULL,
 CONSTRAINT [aaaaaMitarbeiter_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Mitarbeiter] ON
INSERT [dbo].[Mitarbeiter] ([ID], [Bezeichnung], [Stundensatz], [AnzahlStunden]) VALUES (1, N'a', 10, 10)
INSERT [dbo].[Mitarbeiter] ([ID], [Bezeichnung], [Stundensatz], [AnzahlStunden]) VALUES (2, N'b', 15, 5)
INSERT [dbo].[Mitarbeiter] ([ID], [Bezeichnung], [Stundensatz], [AnzahlStunden]) VALUES (3, N'c', 20, 2)
SET IDENTITY_INSERT [dbo].[Mitarbeiter] OFF

Bin für jede Info dankbar.

Gruß

Thomas

Answer 1

Im Grunde so:

WITH Data AS
	(
		SELECT	S.ID, S.Bezeichnung, S.lft, S.Rgt,
				M.Stundensatz, M.AnzahlStunden
		FROM Struktur S 
			INNER JOIN Mitarbeiter M ON S.ID = M.ID
	)
	SELECT	O.ID, O.Bezeichnung,
			(	SELECT	SUM(I.Stundensatz * I.AnzahlStunden)
				FROM Data I
				WHERE (O.lft <= I.lft) AND (I.rgt <= O.rgt)
			) AS Mittel
	FROM Data O;

Allerdings ist dein Datenmodell a bisserl komisch. Ich würde ja so was erwarten:

DECLARE @Mitarbeiter TABLE
(
  MitarbeiterID INT,
  MitarbeiterName NVARCHAR(255),
  VorgesetzterMitarbeiterID INT,
  NestedSetLeft INT,
  NestedSetRight INT,
  Stundensatz INT
);

INSERT INTO @Mitarbeiter VALUES
	( 1, 'a', NULL, 1, 14, 100 ),
	( 2, 'b', 1, 2, 7, 50 ),
	( 3, 'c', 2, 3, 4, 25 ),
	( 4, 'd', 2, 5, 6, 25 ),
	( 5, 'e', 1, 8, 13, 50 ),
	( 6, 'f', 5, 9, 10, 25),
	( 7, 'g', 5, 11, 12, 25 );

DECLARE @Aufwaende TABLE 
(
	MitarbeiterID INT,
	ProjektID INT,
	Stunden INT
);

INSERT INTO @Aufwaende VALUES
	( 1, 1, 10 ),
	( 2, 1, 20 ),
	( 3, 1, 40 ),
	( 4, 1, 40 ),
	( 5, 1, 20 ),
	( 6, 1, 40 ),
	( 7, 1, 40 ),
	( 1, 2, 30 ),
	( 2, 2, 60 ),
	( 3, 2, 120 ),
	( 4, 2, 120 ),
	( 5, 2, 60 ),
	( 6, 2, 120 ),
	( 7, 2, 120 );

WITH Data AS
	( 
		SELECT M.MitarbeiterID, M.MitarbeiterName, M.NestedSetLeft, M.NestedSetRight, M.Stundensatz, SUM(Stunden) AS StundenSumme
		FROM	@Aufwaende A
			INNER JOIN @Mitarbeiter M ON A.MitarbeiterID = M.MitarbeiterID
		GROUP BY M.MitarbeiterID, M.MitarbeiterName, M.NestedSetLeft, M.NestedSetRight, M.Stundensatz
	)
	SELECT	N.MitarbeiterName,		
			COUNT(*) - 1 AS Ebene,
			(N.NestedSetRight - N.NestedSetLeft - 1) / 2 AS AnzahlMitarbeiter,
			(	
				SELECT	SUM(I.Stundensatz * I.StundenSumme)
				FROM Data I
				WHERE (N.NestedSetLeft <= I.NestedSetLeft) AND (I.NestedSetRight <= N.NestedSetRight)
			) AS Summe
	FROM	Data N
		INNER JOIN Data P ON N.NestedSetLeft BETWEEN P.NestedSetLeft AND P.NestedSetRight
	GROUP BY N.MitarbeiterName, 
		N.NestedSetLeft,
		N.NestedSetRight
	ORDER BY n.NestedSetLeft;

Habe noch das Mittel vergessen:

WITH Data AS
	( 
		SELECT M.MitarbeiterID, M.MitarbeiterName, M.NestedSetLeft, M.NestedSetRight, M.Stundensatz, SUM(Stunden) AS StundenSumme
		FROM	@Aufwaende A
			INNER JOIN @Mitarbeiter M ON A.MitarbeiterID = M.MitarbeiterID
		GROUP BY M.MitarbeiterID, M.MitarbeiterName, M.NestedSetLeft, M.NestedSetRight, M.Stundensatz
	),
	Aggregated AS
	(
	SELECT	N.MitarbeiterName,		
			COUNT(*) - 1 AS Ebene,
			(N.NestedSetRight - N.NestedSetLeft - 1) / 2 AS AnzahlMitarbeiter,
			(	
				SELECT	SUM(I.Stundensatz * I.StundenSumme)
				FROM Data I
				WHERE (N.NestedSetLeft <= I.NestedSetLeft) AND (I.NestedSetRight <= N.NestedSetRight)
			) AS Summe,
			(	
				SELECT	SUM(I.StundenSumme)
				FROM Data I
				WHERE (N.NestedSetLeft <= I.NestedSetLeft) AND (I.NestedSetRight <= N.NestedSetRight)
			) AS StundenSumme,
			N.NestedSetLeft AS Reihenfolge
	FROM	Data N
		INNER JOIN Data P ON N.NestedSetLeft BETWEEN P.NestedSetLeft AND P.NestedSetRight
	GROUP BY N.MitarbeiterName, 
		N.NestedSetLeft,
		N.NestedSetRight
	)
	SELECT	MitarbeiterName, Ebene, AnzahlMitarbeiter, Summe, StundenSumme , Summe / StundenSumme AS Mittel
	FROM	Aggregated	
	ORDER BY Reihenfolge;

Answer 2

Hallo Stefan,

zu der Frage, warum die Struktur genau so aufbebaut ist!

Die Struktur soll beliebig erweiterbar sein (auch in die Tiefe) Es ist auch möglich, dass ein Mitarbeitern mehreren Vorgesetzten zugeordnet ist (Darum die n-m Tabelle Verknuepfung!).  Die "Anzahl Stunden" müsste natürlich in einer separaten Tabelle sein. Das ist mir klar.

Leider funktioniert der Befehl nicht richtig, da ich wohl vergessen habe die Tabellenbeziehungen zu erklären

korrekt

Struktur.ID = Verknuepfung.IDStruktur and Verknuepfung.IDMitarbeiter= Mitarbeiter.ID

falsch

FROM Struktur S INNER JOIN Mitarbeiter M ON S.ID = M.ID (S.ID=M.ID ist leider nicht richtig!)

Wenn ich die Verknüpfung entsprechen ändere, dann erhalte ich wieder die "Gesamt" Zeile nicht.

Thomas

Answer 3

Es ist auch möglich, dass ein Mitarbeitern mehreren Vorgesetzten zugeordnet ist (Darum die n-m Tabelle Verknuepfung!).

Das geht zwar, ist auch im Grunde eine wirklich simple Kiste. Allerdings ist das Ergebnis dann nicht mehr korrekt und führt die Verwendung von Nested Sets ad absurdum - ein Nested Set ist eine hierarchische Struktur im Sinn von Baum, deine Mehrfachverwendung macht ein Netz (Mesh) daraus:

DECLARE @Mitarbeiter TABLE
    (
      MitarbeiterID INT ,
      MitarbeiterName NVARCHAR(255) ,
      Stundensatz INT
    );

INSERT  INTO @Mitarbeiter
VALUES  ( 1, 'a', 100 ),
        ( 2, 'b', 50 ),
        ( 3, 'c', 25 ),
        ( 4, 'd', 25 ),
        ( 5, 'e', 50 ),
        ( 6, 'f', 25 ),
        ( 7, 'g', 25 ),
        ( 8, 'h', 25 ),
        ( 9, 'i', 25 );

DECLARE @Hierarchie TABLE
    (
      MitarbeiterID INT ,
      NestedSetLeft INT ,
      NestedSetRight INT
    );

INSERT  INTO @Hierarchie
VALUES  ( 1, 1, 22 ),
        ( 2, 2, 11 ),
        ( 3, 3, 4 ),
        ( 4, 5, 6 ),
        ( 8, 7, 8 ),
        ( 9, 9, 10 ),
        ( 5, 12, 21 ),
        ( 6, 13, 14 ),
        ( 7, 15, 16 ),
        ( 8, 17, 18 ),
        ( 9, 19, 20 );

DECLARE @Aufwaende TABLE
    (
      MitarbeiterID INT ,
      ProjektID INT ,
      Stunden INT
    );

INSERT  INTO @Aufwaende
VALUES  ( 1, 1, 10 ),
        ( 2, 1, 20 ),
        ( 3, 1, 40 ),
        ( 4, 1, 40 ),
        ( 5, 1, 20 ),
        ( 6, 1, 40 ),
        ( 7, 1, 40 ),
        ( 8, 1, 20 ),
        ( 9, 1, 20 ),
        ( 1, 2, 30 ),
        ( 2, 2, 60 ),
        ( 3, 2, 120 ),
        ( 4, 2, 120 ),
        ( 5, 2, 60 ),
        ( 6, 2, 120 ),
        ( 7, 2, 120 ),
        ( 8, 2, 20 ),
        ( 9, 2, 20 );

WITH    Data
          AS ( SELECT   M.MitarbeiterID ,
                        M.MitarbeiterName ,
                        H.NestedSetLeft ,
                        H.NestedSetRight ,
                        M.Stundensatz ,
                        SUM(Stunden) AS StundenSumme
               FROM     @Aufwaende A
                        INNER JOIN @Mitarbeiter M ON A.MitarbeiterID = M.MitarbeiterID
                        INNER JOIN @Hierarchie H ON M.MitarbeiterID = H.MitarbeiterID
               GROUP BY M.MitarbeiterID ,
                        M.MitarbeiterName ,
                        H.NestedSetLeft ,
                        H.NestedSetRight ,
                        M.Stundensatz
             ),
        Aggregated
          AS ( SELECT   N.MitarbeiterName ,
                        COUNT(*) - 1 AS Ebene ,
                        ( N.NestedSetRight - N.NestedSetLeft - 1 ) / 2 AS AnzahlMitarbeiter ,
                        ( SELECT    SUM(I.Stundensatz * I.StundenSumme)
                          FROM      Data I
                          WHERE     ( N.NestedSetLeft <= I.NestedSetLeft )
                                    AND ( I.NestedSetRight <= N.NestedSetRight )
                        ) AS Summe ,
                        ( SELECT    SUM(I.StundenSumme)
                          FROM      Data I
                          WHERE     ( N.NestedSetLeft <= I.NestedSetLeft )
                                    AND ( I.NestedSetRight <= N.NestedSetRight )
                        ) AS StundenSumme ,
                        N.NestedSetLeft AS Reihenfolge
               FROM     Data N
                        INNER JOIN Data P ON N.NestedSetLeft BETWEEN P.NestedSetLeft AND P.NestedSetRight
               GROUP BY N.MitarbeiterName ,
                        N.NestedSetLeft ,
                        N.NestedSetRight
             )
    SELECT  REPLICATE(' ', Ebene * 4) + MitarbeiterName ,
            Ebene ,
            AnzahlMitarbeiter ,
            Summe ,
            StundenSumme ,
            Summe / StundenSumme AS Mittel
    FROM    Aggregated
    ORDER BY Reihenfolge;

Answer 4

Jetzt stelle dir vor, der Mitarbeiter a hat keine Aufwände

(Alle Aufwände mit ID=1 wegmachen)

Dann wird Mitarbeiter a nicht mehr im Ergebnis ausgegeben.

Ich will aber trotzdem von dem Mitarbeiter a) den Durchschnitt all seiner zugeordneten und untergeordneten Mitarbeiter.

Thomas

PS: Leider kann ich meine Struktur nicht ändern, da mein "Echt-Anwendungsfall" in der Struktur nichts direkt mit den Mitarbeitern zu tun hat. Hier können beliebige Informationen strukturiert und dann auch zugeordnet werden. Ich hoffe, dass ich dein Select entsprechend auf meine Struktur umgebogen bekomme:-)

Answer 5

Komm schon, der LEFT JOIN statt 'nem INNER JOIN ist ja wohl kaum ein Problem..

WITH    Data
          AS ( SELECT   M.MitarbeiterID ,
                        M.MitarbeiterName ,
                        H.NestedSetLeft ,
                        H.NestedSetRight ,
                        M.Stundensatz ,
                        SUM(Stunden) AS StundenSumme
               FROM     @Mitarbeiter M
                        INNER JOIN @Hierarchie H ON M.MitarbeiterID = H.MitarbeiterID
                        LEFT JOIN @Aufwaende A ON A.MitarbeiterID = M.MitarbeiterID
               GROUP BY M.MitarbeiterID ,
                        M.MitarbeiterName ,
                        H.NestedSetLeft ,
                        H.NestedSetRight ,
                        M.Stundensatz
             ) ...

Answer 6

Hallo Stefan,

vielen Dank. Für dein Beispiel funktioniert nun das ganze.

Jedoch bekomme ich es leider für meine Struktur immer noch nicht hin. Geht das mit meiner Struktur eventuell gar nicht? Wie schon gesagt, habe ich leider keine Möglichkeit meine Struktur entsprechend anzupassen!

Die Verknüfungen der Tabellen in meinem Beispiel zueinander sind folgendermassen:

Struktur.ID = Verknuepfung.IDStruktur and Verknuepfung.IDMitarbeiter= Mitarbeiter.ID

Thomas