Spezielle SQL Abfrage mit Zwischenergebnissen

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo,

ich habe eine rekursive Datenstruktur und muss dafür eine spezielle Abfrage bauen.

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 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
1-------Gesamt----------0
2-------Meier-----------1
3-------Müller----------2

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.

Struktur-Mitarbeiter-Stundensatz-AnzahlStunden-Durchschnitt
Gesamt---------------215---------17------------12,64
Meier----------------175---------15------------11,6
Meier----a-----------10----------10------------10
Meier----b-----------15----------5-------------15
Müller---------------20----------2-------------20
Müller—c-----------20----------2-------------20

So in der Art:
SELECT Struktur.Bezeichnung, Mitarbeiter.Bezeichnung, Mitarbeiter.Stundensatz, Mitarbeiter.AnzahlStunden, ‚Durchschnitt‘
FROM Struktur,Mitarbeiter,Verknuepfung
Where Mitarbeiter.ID = Verknuepfung.IDMitarbeiter and Struktur.ID = Verknuepfung.IDStruktur;
UNION
SELECT Struktur.Bezeichnung, ‚‘, ‚SummeStundensatz‘, ‚SummeStunden‘, ‚Durchschnitt‘
FROM Struktur

Das ganze habe ich auch in einer kleinen Access Datenbank abgebildet. Gerne kann ich diese zur Verfügung stellen.

Bin für jede Info dankbar.

Gruß

Thomas

#2

Hallo Thomas,

bevor ich jetzt kilometerlange Statements einkopiere um dir zu helfen:
hast du das Problem noch, bist du noch an einer Lösung interessiert ?

Gruss,
SomeOne

#3

Hallo SomeOne,
ich habe das Problem noch.
Du kannst mir gerne „Kilometerlange“ Statements geben.

Thomas

#4

du hast es so gewollt …
Hi Thomas,

ich gehe davon aus, dass sowohl Mitarbeiter wie auch Vorgesetzte in der selben Tabelle stehen, und dass in einer zweiten Tabelle das Verhältnis Mitarbeiter-Vorgesetzter dargestellt wird:

/* MIT COMMON TABLE EXPRESSION */

WITH help AS(
SELECT mitarbeiter.mitarbeiter,
_______mitarbeiter.stunden ,
_______mitarbeiter.stundensatz,
_______mitarbeiter.stundensatz*mitarbeiter.stunden AS lohn,
_______chef.Mitarbeiter AS chef
FROM arbeiter AS mitarbeiter
LEFT JOIN chefs ON chefs.mitarbeiter=mitarbeiter.id
LEFT JOIN arbeiter AS chef ON chef.id = chefs.vorgesetzter
)
SELECT chef, ’ ', sum(lohn), sum(stunden), sum(lohn)/sum(stunden)
FROM help
WHERE NOT chef IS NULL
GROUP BY chef
HAVING NOT sum(stunden) = 0
UNION ALL
UNION ALL
SELECT chef, mitarbeiter, lohn, stunden, lohn/stunden
FROM help
WHERE NOT stunden = 0
;

/* OHNE COMMON TABLE EXPRESSION */

SELECT chef, ’ ', sum(lohn), sum(stunden), sum(lohn)/sum(stunden)
FROM (SELECT mitarbeiter.mitarbeiter,
______________mitarbeiter.stunden ,
______________mitarbeiter.stundensatz,
______________mitarbeiter.stundensatz*mitarbeiter.stunden AS lohn,
______________chef.Mitarbeiter AS chef
_______FROM arbeiter AS mitarbeiter
_______LEFT JOIN chefs ON chefs.mitarbeiter=mitarbeiter.id
_______LEFT JOIN arbeiter AS chef ON chef.id = chefs.vorgesetzter) AS help
WHERE NOT chef IS NULL
GROUP BY chef
HAVING NOT sum(stunden) = 0

UNION ALL

SELECT chef, mitarbeiter, lohn, stunden, lohn/stunden
FROM (SELECT mitarbeiter.mitarbeiter,
______________mitarbeiter.stunden ,
______________mitarbeiter.stundensatz,
______________mitarbeiter.stundensatz*mitarbeiter.stunden AS lohn,
______________chef.Mitarbeiter AS chef
_______FROM arbeiter AS mitarbeiter
_______LEFT JOIN chefs ON chefs.mitarbeiter=mitarbeiter.id
_______LEFT JOIN arbeiter AS chef ON chef.id = chefs.vorgesetzter) AS help
WHERE NOT stunden = 0

Je nachdem was deine Datenbank zulässt:
„help“ ist jeweils eine Arbeitstabelle, sie entsteht entweder als

  • Common Table Expression
  • SubQuery
  • physisch erstellte Tabelle

In allen Fällen wird diese Zwischentabelle auf zwei verschiedenen Gruppenwechselstufen abgefragt, die Ergebnisse werden mit UNION ALL zusammengeführt.

Ich hoffe, ich konnte den Prozess einigermassen verständlich darstellen (falls nicht, höre ich wohl davon).
Im Test hat o.g. genau das von dir gewünschte Ergebnis geliefert.

Viel Erfolg und Danke für Rückmeldung,
SomeOne

#5

Hallo SomeOne,
ja, ich habe es so gewollt! Und das Ergebnis liefert fast mein gewünschtes Ergebis:smile:
Vorgesetzte und Mitarbeiter sind nicht in der gleichen Tabelle. Hier ist noch eine Zwischentabelle eingebaut.
Zusätzlich hätte ich gerne noch ein „Gesamtergebnis“, für einen „Obervorgesetzen“ welcher nicht direkt Mitarbeiter zugeordnet hat.

Anbei ein Script von mir, der die Tabellen anlegt sowie der, auf meine Tabellen angepassten, SQL Befehl ohne CTE.

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 NULL,
[IDRekursiv] [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]) VALUES (1, N’Gesamt’, 0)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv]) VALUES (2, N’Meier’, 1)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv]) VALUES (3, N’Müller’, 2)
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 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

Hier nun die Abfrage

/******Abfrage******/

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 ALL

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

Vielen Dank
Thomas

#6

Servus Thomas,

darf ich daraus schliessen dass du deine Aufgabe lösen konntest ?

Den Grossteil deines Scriptes verstehe ich nicht, arbeite halt auf einem völlig anderen System … aber die Grundidee hast du schon verwenden können, ja ?

Gruss,
SomeOne

PS: ich hatte noch ein abschliessendes ORDER BY 1, 2 unter der
SELECT-UNION-SELECT Gruppe vergessen, aber das siehst du wohl selbst

#7

Hallo SomeOne,
nein, leider konnte ich es nicht (komplett) lösen.

Zusätzlich hätte ich gerne noch ein „Gesamtergebnis“,für einen „Obervorgesetzen“ welcher nicht direkt Mitarbeiter zugeordnet hat.

Das Script ist für einen SQL Server!

Verstehst du was ich meine?

Struktur-Mitarbeiter-Stundensatz-AnzahlStunden-Durchschnitt
Gesamt---------------215---------17------------12,64
Meier----------------175---------15------------11,6
Meier----a-----------10----------10------------10
Meier----b-----------15----------5-------------15
Müller---------------20----------2-------------20
Müller—c-----------20----------2-------------20

Die „Gesamt“ Zeile ist mit der bisherhigen Abfrage nicht vorhanden, da diesem Vorgesetzten nicht direkt Mitarbeiter zugeordnet sind.
Der Vorgesetzte „Gesamt“ erhält seine Zuordnungen über die Rekursive Struktur der Tabelle „Struktur“

ID------Bezeichnung-----IDRekursiv
1-------Gesamt----------0
2-------Meier-----------1
3-------Müller----------2

Thomas

#8

Sevus Thomas

dazu musst du doch nur noch ein SELECT Statement vor die anderen stellen in dem die Summen Level 0 gebildet werden, also etwa so:

SELECT ‚Gesamt‘ , ’ ', sum(lohn), sum(stunden), sum(lohn)/sum(stunden)
FROM help
UNION ALL

(und dann das ganze Geraffel was wir schon hatten)

ORDER BY 1, 2

Müsste so funzen, Rückmeldung willkommen :smile:

Gruss,
SomeOne

#9

Hi SomeOne,
ganz so einfach ist es leider nicht.
Meine Hierarchie kann beliebig tief sein.
Es soll für alle Hierarchie Einträge, welche nicht direkte Mitarbeiter zugeordnet sind, genau so funktionieren.
Also muss ich irgendwie herausfinden, welche Mitarbeiter zu welcher Ober-hierarchie gehören.

Thomas

#10

Beispiel

-Nach Vorgesetztem
–Abteilung a
—Vorgesetzer Meier
-----Mitarbeiter a
-----Mitarbeiter b
—Vorgesetzer Müller
-----Mitarbeiter c
–Abteilung b
—Vorgesetzter Huber
-----Mitarbeiter d
—Vorgesetzter Kimmig
-----Mitarbeiter e

Nun will ich ein Ergebnis für „Nach Vorgesetzem“ Hier sollen Mitarbeiter (a,b,c,d,e) vorhanden sein
Ein Ergebnis für Abteilung a (Mitarbeiter a,b,c)
Ein Ergebnis für Vorgesetzter Meier (Mitarbeiter a,b)
Ein Ergebnis für Mitarbeiter a
Ein Ergebnis für Mitarbeiter b
Ein Ergebnis für Vorgesetzter Müller(Mitarbeiter c)
…
…
Diese Struktur ist Rekursiv in meiner Tabelle abgelegt

Thomas

#11

Oh je …
Hi Thomas,

da hast du jetzt ein richtiges Problem:

„beliebig viele“ Levels lassen sich nur mit rekursiven CTEs auflösen, die lassen aber nicht alle DB-Systeme zu. Hiermit habe ich auch keine ausreichende Erfahrung um hilfreich zu sein.

Zweite Möglichkeit wäre z.B. sich z.B. auf 10 Levels festzulegen und daher 10 mal Mitarbeiter/Struktur mit sich selbst zu JOINen.

Ich stelle mir die Tabelle „help“ etwa so vor:
Mitarbeiter/Stunden/Stundensatz/Lohn/Chef1/Chef2/Chef3/…/Chef10.

Anschliessend eben 10 Gruppen a la SELECT / UNION ALL hintereinander hängen und dort jeweils das ChefX-Level mittels GROUP BY abzufragen.

Vielleicht kann ja noch jemand etwas Schöneres zur Lösung beitragen ?
Einen Teilerfolg haben wir ja schon.

*GespanntBin*
SomeOne

#12

könntest du da nicht einfach …
… die Tabelle „help“ so aussehen lassen:

Mitarbeiter/Stunden/Stundensatz/Lohn/Chef/Abteilung

und die dann nach dem bisherigen Muster mit SELECT/UNION ALL
entsprechend abfragen ?

Damit könntest du Summen/Durchschnitte pro Abteilung/Chef/Mitarbeiter bilden und in eine Ausgabemenge vereinigen.

Gruss,
SomeOne

#13

Hi SomeOne,
das habe ich befürchtet:frowning:

Aber wie du sagst: Einen Teilerfolg haben wir:smile:

Noch einmal danke für deine Hilfe.
Vielleicht hat je jemand noch eine Idee?
Leider kann ich mich nicht fest auf 10 Levels festlegen.

Thomas

#14

geht leider auch nicht, da die Struktur von dem Endanwender beliebig erstellt und erweitert werden kann.
Manchmal habe ich nur eine Ebene, manchmal 20 Ebenen.

Thomnas

#15

Aufgabe neu formulieren
Hi Thomas,

anscheinend kann ich dir hier und jetzt nicht weiter helfen.

Bitte formuliere die verbliebene Aufgabenstellung auf Grundlage des bisher Erarbeiteten neu, damit vllt jemand anders weiter helfen kann.

Ich erkenne wohl , dass du das typische Mutter/Kind Problem hast
(Stücklisten: ein Produkt besteht aus N Komponenten, jede Komponente kann aus N Komponenten bestehen, n Levels tief).

Aber ich sehe richtig?:
ein Mitarbeiter hat genau einen Chef und ist in genau einer Abteilung tätig, aber jeder Chef kann mehrere Abteilungen haben ?

Bitte erläutere doch noch mal kurz die genauen Datenbeziehungen,
die Verknüpfungstechnik haben wir ja jetzt.

Erst mal Feierabend jetzt, hoffentlich gibts morgen ne gute Lösung.

Schönen FA,
SomeOne

#16

Moin, Tommi,

ich war mir nicht sicher, wie ernst

ich habe eine rekursive Datenstruktur

gemeint war, deshalb habe ich mich bisher zurückgehalten. Sollte das wirklich heißen, dass jeder Mitarbeiter (bis auf den letzten) auch Vorgesetzter sein kann, dann würde ich dringend davon abraten, eine meterlange Query basteln zu wollen. Statt dessen wäre hier stinknormale sequentielle Verarbeitung angesagt, so richtig mit old fashioned Gruppenwechsel-Logik.

SQL-Queries eignen sich einfach nicht dazu, Stücklisten aufzudröseln.

Gruß Ralf

#17

du hast voll Recht
Moins,

deswegen ja die Idee sich auf z.B. 10 Levels zu beschränken,
die „schlimmste“ Stückliste die ich je aufdröseln musste hatte 4 Levels,
das liess sich mit der bereits erörterten Strategie noch lösen.

Und erfahrungsgemäss: wenn Menschen diese Levels verwalten müssen, verlieren sie spätestens bei 4 den Überblick :wink: .

Aber du hat völlig Recht, wenn es tatsächlich N Levels gibt, ist klassische Verarbeitung gefordert.

Gruss,
SomeOne

#18

Hi SomeOne,
OK, ich formuliere meine Frage neu.
Ich habe auch schon einen Ansatz (Nested Sets)
Ich hoffe, mir kann jemand helfen!

Thomas

#19

Denkt ihr wirklich, das geht nicht?

Ich habe was von Nested Sets gelesen
http://www.klempert.de/nested_sets/

Damit scheint es einfacher zu sein, all seine Strukturen auszulesen. Damit muss man wohl nicht mehr rekursiv alles durchgehen.

Ich habe nun einen neuen Thread aufgemacht, in dem ich mein Problem nochmal beschreibe (mit Nested Sets)

Trotzdem danke für den Tip

Thomas

#20

Hi!

(eine doch etwas späte Antwort)

Sollte das wirklich heißen, dass jeder Mitarbeiter (bis auf
den letzten) auch Vorgesetzter sein kann, dann würde ich
dringend davon abraten, eine meterlange Query basteln zu
wollen. Statt dessen wäre hier stinknormale sequentielle
Verarbeitung angesagt, so richtig mit old fashioned
Gruppenwechsel-Logik.

SQL-Queries eignen sich einfach nicht dazu, Stücklisten
aufzudröseln.

Würde es sich um ein Oracle-System handeln, würde ich es trotzdem mit einem einzigen stinkigem Select inklusive Connect-By auflösen (nein, ich habe die Ausgangsfrage nur halb gelesen, weil ich der Meinung bin, dass man sich den Kopf selber zerbrechen kann :wink:

Ich habe mir selber gerade den Kopf zerbrochen, wie ich eine „Stückliste“ (derzeit existieren bis zu 51 Levels und ja, das Datenmodell ist schwerst in Ordnung, obwohl es nicht von mir ist :smiley:) inkl. Subsummen etc. zusammernzimmere … Rekursivität und analytische Funktionen machen mir Oracle wieder mal so richtig sympathisch.

Grüße,
Tomh