SQL group by jedoch nur 10 records pro Gruppe

[Kostas]

Hallo Zusammen,

ein fiktives Beispiel und die Anforderung zu beschreiben:

In einer großer Firma gibt es mehrere Abteilungen. Jede Abteilungen hat mehrere Mitarbeiter.
Jetzt suche ich von jeder Abteilung die 10 Mitarbeiter mit dem höchsten Gehalt. Die 10 Gehälter werden zusammenaddiert zu einem ranging der Abteilung.

// so werden alle Gehälter aller Mitarbeiter der Gruppe zusammenaddiert. Ich benötige jedoch nur die 10 besten einer Abteilung

Code:

select Abteilung, sum(gehalt) as AbteilungGehalt
from firma 
group by Abteilung 
order by AbteilungGehalt desc

hat jemand eine Idee?

[mjustin]

Das ist ein klassischer Anwendungsfall für die Partition By Funktionen. Dabei filtert man je Abteilung die Ergebnisse mit RowNumber <= 10 aus.

Hier ist ein ähnliches Beispiel:

https://stackoverflow.com/questions/...-each-category

Firebird 3.0 Dokumentation dazu:

https://firebirdsql.org/file/documen...partition.html

[Kostas]

Hallo Michael,

oh, damit habe ich mich noch nicht beschäftigt. Ich erkenne in der FB Doku nicht wie ich die Anzahl an Mitarbeiter pro Abteilung begrenzen kann. Hast du mir einen Hinweis?

[hoika]

Hallo,
FB kann jetzt auch Rank, Rank Over
Im ersten Link ist dazu ein Bsp., zwar mit SQL-Server, aber ist ähnlich ...

[IBExpert]

die erweiterung die Firebird da kann sind auf jeden Fall ein Weg, wie man das umsetzen
kann, ist aber nicht immer intuitiv für alte säcke (wie mich) zu verstehen, daher geht das
wenn ich das richtig verstanden hab auch so (beispiel auf basis der ibexpert demodb db1)

Code:

select CATEGORY.TXT,
       (select sum(X_PRICE)
        from (select first 10 X_PRODUCT.PRICE X_PRICE
              from PRODUCT X_PRODUCT
              where X_PRODUCT.CATEGORY_ID = CATEGORY.ID
              order by PRICE desc))  
from category

wie komm ich da drauf?

1. im inneren select willst du immer nur die 10 höchsten records haben

Code:

select first 10 X_PRODUCT.PRICE X_PRICE
              from PRODUCT X_PRODUCT
              order by X_PRODUCT.PRICE desc

eine alias benutze ich da schon damit diese eben dann später mit den äußeren selects nix durcheinander kommt

2. du brauchst die gruppierte summe der 10 records also ermittelst du nur die summe zur gruppe, die kommt dann gleich von einem noch drumherum liegenden äußeren select

Code:

select sum(X_PRICE)
from (select first 10 X_PRODUCT.PRICE X_PRICE
              from PRODUCT X_PRODUCT
              where X_PRODUCT.CATEGORY_ID = CATEGORY.ID
              order by PRICE desc))

3. und das jeweils zeilenweise als spaltenwert ermitteln für jede gruppe

Code:

select CATEGORY.TXT,
       (select sum(X_PRICE)
        from (select first 10 X_PRODUCT.PRICE X_PRICE
              from PRODUCT X_PRODUCT
              where X_PRODUCT.CATEGORY_ID = CATEGORY.ID
              order by PRICE desc))  
from category

fazit: subselects sind wenn indiziert zugegriffen wird, auch schon sehr hilfreich, es ginge auch per execute block mit zwischenvariablen
aber ganz wichtig: das was ich da oft bei kunden sehe sind subselects auf prozeduren und damit nie indiziert, es sei denn die sp ist dafür
schon brauchbar definiert, das wird dann oft extrem performancekritisch. Oder ganz einfach: wenn so ein SQL dann bei euch sehr lang braucht
liegt das nicht an Firebird, sondern an eurer mieserablen Implementation, an der man aber immer was optimieren kann, ohne gleich alles
über den haufen zu werfen.

[Kostas]

Hallo Holger,

vielen Dank für deinen Vorschlag.
Du hast es angesprochen. Manchmal komme die Daten eben nicht aus einer Tabelle die schön selectiert werden können, sondern von einer SPs. Auch in meinem Fall ist es so dass die einzelnen Mitarbeiter über eine SP die auch eine andere SP aufruft um alle Daten zu ermitteln. Ist ein komplexer Vorgang der nicht über Selects zu ermitteln ist.

Aus deinem Vorschlag würde der innere select mit first 10 die Daten von der SP holen. Das übergeordnete Select um die Abteilungssumme zu bilden ebenfalls aus der gleichen SP. Dummerweise benötige ich jetzt auch noch die 10 "teuersten" Mitarbeiter einer Abteilung aufgeführt. Also die komplette Konstruktion nochmals wobei diesmal die Abteilungssumme nicht benötigt wird sondern die Mitarbeiter.

Ich dachte, ich erzeug ein Execute Block oder eine neue SP. Die erste Aufgabe, die SP die die Daten liefert in einer GTT per CREATE GLOBAL TEMPORARY TABLE abzulegen. Dann kann ich deinen Vorschlag verwenden und indexiert mehrfach auf die GTT zugreifen. Da in einer SP die GTT nicht angelegt werden kann, habe ich gesehen dass IBExpert diese anlegen kann. :-) Habe ich noch nie gebraucht. Mal sehen ob das ein Vorteil bringt.