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).

Beliebte Postings