6. Mai 2008

Den "jüngsten" Datensatz selektieren: Mit analytischen Funktionen

English title:

Eine recht gängige Aufgabe ist es, aus einer Tabelle mit mehreren Zeilen pro "Geschäftsvorfall" jeweils nur einen bestimmten zu selektieren. Enthält eine Tabelle bspw. mehrere Versionen eines Datensatzes mit einer aufsteigenden Versionsnummer, so muss häufig die jeweils letzte Version selektiert werden. Da ich schon recht häufig danach gefragt wurde: Hier eine Lösungsvariante mit Hilfe von analytischen Funktionen.
It's a quite common requirement to select only the last (or the first) row of a table containing multiple rows per "entity". An example for this is a table containing multiple versions of an entity. In most cases only the last version has to be selected. In the recent time I was asked very often how to achieve this - so here is an example using analytic functions.
Als Beispiel sollen anhand der Tabelle EMP pro Abteilung (DEPTNO) die zuletzt eingestellten (HIREDATE) Mitarbeiter selektiert werden (das wäre analog zum Selektieren der jüngsten Version eines Datensatzes):
The example is about (table EMP) selecting for each department (DEPTNO) the person employed most recently (HIREDATE). This is similar to selecting the most recent version of an entity:
Zunächst stellen wir das Datum des zuletzt eingestellten Mitarbeiters neben alle anderen ...
In the first step the HIREDATE of the most recently employed person per department is selected beyond the other columns of interest.
select
  deptno,
  ename,
  hiredate,
  max(hiredate) over (partition by deptno) last_date
from emp
Wir verwenden die Funktion MAX aber interessanterweise ohne GROUP BY. Wie das Maximum gebildet werden soll, steht in der OVER-Klausel der analytischen Funktion. Und zwar werden die Zeilen nach DEPTNO aufgeteilt (PARTITION BY). das Ergebnis sieht dann so aus ...
The MAX function is used here but without a GROUP BY clause. So this is the analytic variant of the MAX function. The OVER clause determines how the maximum has to be computed. In this example the rows get partiotioned by department (PARTITION BY). The intermediate result then looks like this:
    DEPTNO ENAME      HIREDATE LAST_EMP
---------- ---------- -------- --------
        10 CLARK      09.06.81 23.01.82
        10 KING       17.11.81 23.01.82
        10 MILLER     23.01.82 23.01.82
        20 JONES      02.04.81 23.05.87
        20 FORD       03.12.81 23.05.87
        20 ADAMS      23.05.87 23.05.87
        20 SMITH      17.12.80 23.05.87
        20 SCOTT      19.04.87 23.05.87
        30 WARD       22.02.81 03.12.81
        30 TURNER     08.09.81 03.12.81
        30 ALLEN      20.02.81 03.12.81
        30 JAMES      03.12.81 03.12.81
        30 BLAKE      01.05.81 03.12.81
        30 MARTIN     28.09.81 03.12.81
... und der Rest ist einfach ...
... finalizing is then very easy ...
with tab as (
 select 
  deptno,
  ename,
  hiredate,
  max(hiredate) over (partition by deptno) last_employed_date
 from emp
)
select 
 deptno,
 ename,
 hiredate 
from tab where last_employed_date = hiredate
/

    DEPTNO ENAME      HIREDATE
---------- ---------- --------
        10 MILLER     23.01.82
        20 ADAMS      12.01.83
        30 JAMES      03.12.81

3 Zeilen ausgewählt.
Mit der WITH-Klausel stellen wir das obige Zwischenergebnis als Inline-View bereit und selektieren daraus mit der WHERE-Klausel nur die Zeilen, deren HIREDATE gleich dem HIREDATE des zuletzt eingestellten Mitarbeiters ist. Und ein Blick auf den Ausführungsplan ...
The WITH clause provides the above intermediate result as inline view. From this inline view only those rows get selected where the HIREDATEequals the HIREDATE of the least employed person per department. And a look at the execution plan...
Plan hash value: 4130734685

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   532 |     4  (25)| 00:00:01 |
|*  1 |  VIEW               |      |    14 |   532 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   238 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   238 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LAST_EMPLOYED_DATE"="HIREDATE")
... zeigt, dass es hierfür sogar eine eigene Operation gibt - die Datenbank macht einen sogenannten Window Sort. Im Normalfall sollte eine analytische Funktion also stets besser, mindestens aber genauso effizient sein wie etwas Selbstprogrammiertes (Ausnahmen bestätigen die Regel).
... shows that there is an own operation for this analytic function: The window sort. Using analytic functions is therefore at least as good as writing one's own code - in most cases better (as always: exceptions confirm the rule).

Kommentare:

Anonym hat gesagt…

Wow, nette Sache. Das habe ich bisher immer deutlich komplizierter gehandhabt. Vielen Dank für diesen Tipp! :)

metzguar hat gesagt…

Das hier macht fast dasselbe und ist noch "oraclischer":

SELECT deptno, MAX(ename)KEEP (DENSE_RANK LAST ORDER BY hiredate) AS ename, MAX(hiredate) AS hiredate
FROM emp
GROUP BY deptno
/

Der Unterschied ist, dass diese Version nur einen Datensatz liefert, wenn zwei "emps" das gleiche "hiredate" haben.

Carsten Czarski hat gesagt…

dem ist nichts hinzuzufügen ...

:-)

-Carsten

Axel Halkow hat gesagt…

Hi,

laut deinem Datenbestand kann das Ergebnis nicht stimmen! Es ging doch, darum: wer wurde in welcher Abteilung zuletzt eingestellt. Und das war z. B. in DEPTNO 10 Miller (sieht man schon in der 1 Gegenüberstellung). Korrektes Endergebnis wäre also:

DEPTNO ENAME HIREDATE
---------- ---------- --------
10 MILLER 23.01.82
20 ADAMS 23.05.87
30 JAMES 03.12.81

Die Lösung finde ich interessant aber sehr proprietär. Einfacher & SQL-kompatibler wäre es auch gegangen:

select deptno, ename, hiredate
from scott.emp
where (hiredate,deptno) in
(
select max(hiredate), deptno
from scott.emp
group by deptno
)
order by deptno

Gruß,
Axel

Carsten Czarski hat gesagt…

Hallo Axel,

zum ersten Punkt hast Du recht. "Damn Copy and Paste" (ich hatte auch einen Test mit MIN() gemacht). Wenn ich das SQL laufen lassen, kommt auch bei mir folgendes heraus (ist jetzt auch im Posting selbst korrigiert):

DEPTNO ENAME HIREDATE
---------- ---------- --------
10 MILLER 23.01.82
20 ADAMS 12.01.83
30 JAMES 03.12.81

Zum zweiten Punkt habe ich schon was einzuwenden. Die analytischen Funktionen sind Teil des SQL-Standards (Zwar nicht SQL92, aber eben Teil des SQL-Standards).

Wenn ich in allen meinen Anwendungen stets nur SQL92 verwende und damit auf SQL zurückgehe, welches von allen Datenbanken dieser Welt verstanden wird), kann ich im Gegenzug auch nur einen Bruchteil der Möglichkeiten nutzen - und werde anschließend einen großen Aufwand in Tuning-Maßnahmen stecken müssen, weil ich keinerlei Performance-Erweiterungen genutzt habe - diese sind eben meistens herstellerspezifisch.

Viele Grüße

-Carsten

Axel Halkow hat gesagt…

Hallo Carsten,

proprietär ist ein hässliches Wort, dass war vielleicht ein bisschen hart. Bei Performance Tuning - da hast Du völlig Recht – wird das einhalten des SQL Standards sehr schwer. Dann heißt das aber nicht mehr proprietär, sondern: "wir haben eine auf die Plattform hoch optimierte Lösung erstellt, die bla bla bla ... " ;-)

Gruß,
Axel

Carsten Czarski hat gesagt…

:-)

... und am Ende des Tages zählt nur eins: Ein Projekt, welches ein Problem mit guten Antwortzeiten (Performance) innerhalb der Abgabezeiten löst. Daran sollte sich alles orientieren.


Viele Grüße

-Carsten

Reinhold Stattelmann hat gesagt…

"Kann ich Oracle Funktionen verwenden oder nicht?" - Bedenken die es nur in der Softwarebranche gibt.

Was würde ich wohl sagen, wenn der freundliche Mann aus meiner Autwerkstatt mir stolz erklären würde, er habe jetzt Standardreifen auf mein Auto montiert, die man auf jeden anderen PKW der Welt montieren kann! ... Nur für den Fall, daß ich mal das Auto wechseln wollte ... Ich könnte dann die Reifen ja wiederverwenden.

Diesen "enormen" Vorteil würde ich mir allerdings mit einer kleinen Einschränkung bei der erlaubten Höchstgeschwindigkeit meines PKW's erkaufen. Ich dürfte nur noch max 80 km/h schnell fahren!

Grüße Reinhold

Anonym hat gesagt…

"Was würde ich wohl sagen, wenn der freundliche Mann aus meiner Autwerkstatt mir stolz erklären würde, er habe jetzt Standardreifen auf mein Auto montiert, die man auf jeden anderen PKW der Welt montieren kann! ... Nur für den Fall, daß ich mal das Auto wechseln wollte ... Ich könnte dann die Reifen ja wiederverwenden.

Diesen "enormen" Vorteil würde ich mir allerdings mit einer kleinen Einschränkung bei der erlaubten Höchstgeschwindigkeit meines PKW's erkaufen. Ich dürfte nur noch max 80 km/h schnell fahren!"

Beispiele die man nicht miteinander Vergleichen kann. Ich will prinzipiell nicht sagen, dass es falsch ist was du sagst, nur dass der Vergleich schlecht ist, denn der technische Fortschritt in Sachen IT wesentlich schneller vorrangeht und eher einen Vorteil mit sich bringt, als in der Autobranche, sprich du hast eine bessere Begründung in Sachen IT eine Änderung durchzuführen ;)

Beliebte Postings