ABAP SQL Window Functions: Analytische Berechnungen direkt in der Datenbank

kategorie

ABAP

Veröffentlicht

Feb 14, 2026

autor

Window Functions (auch analytische Funktionen genannt) sind eine der mächtigsten Erweiterungen in ABAP SQL. Sie ermöglichen komplexe Berechnungen über Gruppen von Zeilen hinweg, ohne diese zu aggregieren – jede Zeile behält ihre Identität.

Was sind Window Functions?

Window Functions berechnen Werte über eine “Fenster” (Window) von Zeilen, das durch die OVER()-Klausel definiert wird. Im Gegensatz zu GROUP BY reduzieren sie die Ergebnismenge nicht.

Funktion	Beschreibung	Verfügbar seit
`ROW_NUMBER()`	Fortlaufende Zeilennummer	ABAP 7.50
`RANK()`	Rang mit Lücken bei Gleichstand	ABAP 7.50
`DENSE_RANK()`	Rang ohne Lücken	ABAP 7.50
`SUM() OVER()`	Laufende/Partitionierte Summe	ABAP 7.50
`AVG() OVER()`	Laufender/Partitionierter Durchschnitt	ABAP 7.50
`COUNT() OVER()`	Laufende/Partitionierte Anzahl	ABAP 7.50
`LAG()`	Wert der vorherigen Zeile	ABAP 7.50
`LEAD()`	Wert der nächsten Zeile	ABAP 7.50
`FIRST_VALUE()`	Erster Wert im Fenster	ABAP 7.50
`LAST_VALUE()`	Letzter Wert im Fenster	ABAP 7.50

Window Functions vs. GROUP BY

" GROUP BY: Eine Zeile pro Gruppe
SELECT carrid, SUM( price ) AS total
  FROM sflight
  GROUP BY carrid
  INTO TABLE @DATA(lt_grouped).
" Ergebnis: LH | 50000
"           AA | 30000

" Window Function: Alle Zeilen bleiben erhalten
SELECT carrid, connid, price,
       SUM( price ) OVER( PARTITION BY carrid ) AS carrier_total
  FROM sflight
  INTO TABLE @DATA(lt_windowed).
" Ergebnis: LH | 400 | 500  | 50000
"           LH | 401 | 600  | 50000
"           AA | 100 | 800  | 30000

Die OVER()-Klausel

Die OVER()-Klausel definiert das Fenster für die Berechnung. Sie besteht aus optionalen Komponenten:

funktion() OVER(
  PARTITION BY spalte1, spalte2    -- Gruppierung (optional)
  ORDER BY spalte3 [ASC|DESC]      -- Sortierung (optional/erforderlich)
  ROWS BETWEEN ... AND ...         -- Fensterrahmen (optional)
)

PARTITION BY

PARTITION BY teilt die Daten in Gruppen (Partitionen). Die Window Function wird für jede Partition separat berechnet:

CLASS zcl_window_partition DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_window_partition IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Summe pro Carrier berechnen, alle Flüge behalten
    SELECT carrid, connid, fldate, price,
           SUM( price ) OVER( PARTITION BY carrid ) AS carrier_total,
           AVG( price ) OVER( PARTITION BY carrid ) AS carrier_avg,
           COUNT( * ) OVER( PARTITION BY carrid ) AS carrier_count
      FROM sflight
      WHERE carrid IN ( 'LH', 'AA' )
      ORDER BY carrid, connid
      INTO TABLE @DATA(lt_flights).

    LOOP AT lt_flights INTO DATA(ls_flight).
      out->write( |{ ls_flight-carrid } { ls_flight-connid }: | &&
                  |Preis { ls_flight-price }, | &&
                  |Carrier-Summe { ls_flight-carrier_total }, | &&
                  |Carrier-Schnitt { ls_flight-carrier_avg DECIMALS = 2 }| ).
    ENDLOOP.

    " Anteil am Carrier-Umsatz berechnen
    SELECT carrid, connid, price,
           price / SUM( price ) OVER( PARTITION BY carrid ) * 100
             AS percentage
      FROM sflight
      WHERE carrid = 'LH'
      INTO TABLE @DATA(lt_shares).

    out->write( '--- Anteil am Carrier-Umsatz ---' ).
    LOOP AT lt_shares INTO DATA(ls_share).
      out->write( |{ ls_share-connid }: { ls_share-percentage DECIMALS = 1 }%| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

ORDER BY in Window Functions

ORDER BY in der OVER()-Klausel bestimmt die Reihenfolge innerhalb jeder Partition:

CLASS zcl_window_order DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_window_order IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Laufende Summe (Running Total) mit ORDER BY
    SELECT carrid, fldate, price,
           SUM( price ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS running_total
      FROM sflight
      WHERE carrid = 'LH'
      ORDER BY carrid, fldate
      INTO TABLE @DATA(lt_running).

    out->write( '--- Laufende Summe pro Carrier ---' ).
    LOOP AT lt_running INTO DATA(ls_run).
      out->write( |{ ls_run-fldate DATE = USER }: | &&
                  |Preis { ls_run-price }, | &&
                  |Laufend { ls_run-running_total }| ).
    ENDLOOP.

    " Ohne ORDER BY: Gesamtsumme der Partition
    " Mit ORDER BY: Laufende Summe bis zur aktuellen Zeile
  ENDMETHOD.

ENDCLASS.

Ranking-Funktionen: ROW_NUMBER, RANK, DENSE_RANK

Die drei Ranking-Funktionen unterscheiden sich im Umgang mit Gleichständen:

Funktion	Verhalten bei Gleichstand	Beispiel (Werte: 10, 20, 20, 30)
`ROW_NUMBER()`	Eindeutige Nummer	1, 2, 3, 4
`RANK()`	Gleicher Rang, dann Lücke	1, 2, 2, 4
`DENSE_RANK()`	Gleicher Rang, keine Lücke	1, 2, 2, 3

CLASS zcl_window_ranking DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_window_ranking IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Alle drei Ranking-Funktionen im Vergleich
    SELECT carrid, connid, price,
           ROW_NUMBER( ) OVER( ORDER BY price DESC ) AS row_num,
           RANK( ) OVER( ORDER BY price DESC ) AS rank,
           DENSE_RANK( ) OVER( ORDER BY price DESC ) AS dense_rank
      FROM sflight
      WHERE carrid IN ( 'LH', 'AA' )
      INTO TABLE @DATA(lt_ranked).

    out->write( '--- Ranking aller Flüge nach Preis ---' ).
    out->write( |{ 'Carrier'(car) WIDTH = 8 }| &&
                |{ 'Conn'(con) WIDTH = 6 }| &&
                |{ 'Preis'(pri) WIDTH = 10 }| &&
                |{ 'Row#'(row) WIDTH = 6 }| &&
                |{ 'Rank'(rnk) WIDTH = 6 }| &&
                |{ 'Dense'(dns) WIDTH = 6 }| ).
    out->write( |{ '=' WIDTH = 42 PAD = '=' }| ).

    LOOP AT lt_ranked INTO DATA(ls_rank).
      out->write( |{ ls_rank-carrid WIDTH = 8 }| &&
                  |{ ls_rank-connid WIDTH = 6 }| &&
                  |{ ls_rank-price WIDTH = 10 }| &&
                  |{ ls_rank-row_num WIDTH = 6 }| &&
                  |{ ls_rank-rank WIDTH = 6 }| &&
                  |{ ls_rank-dense_rank WIDTH = 6 }| ).
    ENDLOOP.

    " Top 3 pro Carrier
    out->write( '' ).
    out->write( '--- Top 3 Flüge pro Carrier ---' ).

    SELECT carrid, connid, price, rank
      FROM (
        SELECT carrid, connid, price,
               DENSE_RANK( ) OVER(
                 PARTITION BY carrid
                 ORDER BY price DESC
               ) AS rank
          FROM sflight
      )
      WHERE rank <= 3
      INTO TABLE @DATA(lt_top3).

    LOOP AT lt_top3 INTO DATA(ls_top).
      out->write( |{ ls_top-carrid } #{ ls_top-rank }: | &&
                  |{ ls_top-connid } - { ls_top-price }| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

ROW_NUMBER für Deduplizierung

ROW_NUMBER ist nützlich, um Duplikate zu eliminieren oder nur die erste/letzte Zeile einer Gruppe zu behalten:

" Nur den letzten Flug pro Verbindung behalten
SELECT carrid, connid, fldate, price
  FROM (
    SELECT carrid, connid, fldate, price,
           ROW_NUMBER( ) OVER(
             PARTITION BY carrid, connid
             ORDER BY fldate DESC
           ) AS rn
      FROM sflight
  )
  WHERE rn = 1
  INTO TABLE @DATA(lt_latest_flights).

Aggregatfunktionen als Window Functions

Standard-Aggregatfunktionen können mit OVER() zu Window Functions werden:

CLASS zcl_window_aggregates DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_window_aggregates IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Verschiedene Aggregate pro Partition
    SELECT carrid, connid, price,
           " Aggregate über gesamte Partition
           SUM( price ) OVER( PARTITION BY carrid ) AS sum_carrier,
           AVG( price ) OVER( PARTITION BY carrid ) AS avg_carrier,
           MIN( price ) OVER( PARTITION BY carrid ) AS min_carrier,
           MAX( price ) OVER( PARTITION BY carrid ) AS max_carrier,
           COUNT( * ) OVER( PARTITION BY carrid ) AS cnt_carrier,
           " Abweichung vom Durchschnitt
           price - AVG( price ) OVER( PARTITION BY carrid ) AS diff_from_avg
      FROM sflight
      WHERE carrid IN ( 'LH', 'AA' )
      ORDER BY carrid, connid
      INTO TABLE @DATA(lt_stats).

    out->write( '--- Statistiken pro Carrier ---' ).
    LOOP AT lt_stats INTO DATA(ls_stat).
      out->write( |{ ls_stat-carrid } { ls_stat-connid }: | &&
                  |Preis { ls_stat-price }, | &&
                  |Schnitt { ls_stat-avg_carrier DECIMALS = 0 }, | &&
                  |Abweichung { ls_stat-diff_from_avg SIGN = LEFT }| ).
    ENDLOOP.

    " Prozentualer Anteil
    SELECT carrid, connid, price,
           CAST( price AS DECFLOAT34 ) /
             SUM( price ) OVER( PARTITION BY carrid ) * 100
           AS pct_of_carrier,
           CAST( price AS DECFLOAT34 ) /
             SUM( price ) OVER( ) * 100
           AS pct_of_total
      FROM sflight
      INTO TABLE @DATA(lt_pct).

    out->write( '' ).
    out->write( '--- Prozentuale Anteile ---' ).
    LOOP AT lt_pct INTO DATA(ls_pct).
      out->write( |{ ls_pct-carrid } { ls_pct-connid }: | &&
                  |{ ls_pct-pct_of_carrier DECIMALS = 1 }% von Carrier, | &&
                  |{ ls_pct-pct_of_total DECIMALS = 2 }% von Gesamt| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

Laufende Summen und gleitende Durchschnitte

Mit ORDER BY und optionalem Fensterrahmen berechnen Aggregatfunktionen laufende Werte:

CLASS zcl_window_running DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_window_running IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Laufende Summe und Durchschnitt
    SELECT carrid, fldate, price,
           SUM( price ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS running_sum,
           AVG( price ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS running_avg,
           COUNT( * ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS running_count
      FROM sflight
      WHERE carrid = 'LH'
      ORDER BY fldate
      INTO TABLE @DATA(lt_running).

    out->write( '--- Laufende Werte für LH ---' ).
    LOOP AT lt_running INTO DATA(ls_run).
      out->write( |{ ls_run-fldate DATE = USER }: | &&
                  |Preis { ls_run-price }, | &&
                  |Summe { ls_run-running_sum }, | &&
                  |Schnitt { ls_run-running_avg DECIMALS = 0 }, | &&
                  |Anzahl { ls_run-running_count }| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

LAG und LEAD für Vorgänger/Nachfolger

LAG() und LEAD() greifen auf Werte vorheriger oder nachfolgender Zeilen zu:

CLASS zcl_window_lag_lead DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_window_lag_lead IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " LAG: Vorherige Zeile, LEAD: Nächste Zeile
    SELECT carrid, fldate, price,
           LAG( price, 1 ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS prev_price,
           LEAD( price, 1 ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS next_price,
           " Preisänderung zur Vorperiode
           price - LAG( price, 1 ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS price_change
      FROM sflight
      WHERE carrid = 'LH'
      ORDER BY fldate
      INTO TABLE @DATA(lt_comparison).

    out->write( '--- Preisvergleich mit Vorgänger ---' ).
    LOOP AT lt_comparison INTO DATA(ls_comp).
      DATA(lv_change) = COND string(
        WHEN ls_comp-price_change IS NULL THEN 'n/a'
        WHEN ls_comp-price_change > 0 THEN |+{ ls_comp-price_change }|
        WHEN ls_comp-price_change < 0 THEN |{ ls_comp-price_change }|
        ELSE '±0'
      ).
      out->write( |{ ls_comp-fldate DATE = USER }: | &&
                  |Preis { ls_comp-price }, | &&
                  |Vorher { ls_comp-prev_price }, | &&
                  |Änderung { lv_change }| ).
    ENDLOOP.

    " LAG/LEAD mit Offset > 1 und Default-Wert
    SELECT carrid, fldate, price,
           LAG( price, 2, 0 ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS price_2_periods_ago,
           LEAD( price, 2, 0 ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS price_in_2_periods
      FROM sflight
      WHERE carrid = 'LH'
      INTO TABLE @DATA(lt_offset).

    out->write( '' ).
    out->write( '--- Vergleich mit 2 Perioden Abstand ---' ).
    LOOP AT lt_offset INTO DATA(ls_off).
      out->write( |{ ls_off-fldate DATE = USER }: | &&
                  |Aktuell { ls_off-price }, | &&
                  |Vor 2: { ls_off-price_2_periods_ago }, | &&
                  |In 2: { ls_off-price_in_2_periods }| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

Prozentuale Änderungen berechnen

" Prozentuale Preisänderung zur Vorperiode
SELECT carrid, fldate, price,
       CASE
         WHEN LAG( price, 1 ) OVER(
           PARTITION BY carrid ORDER BY fldate ) IS NULL THEN 0
         WHEN LAG( price, 1 ) OVER(
           PARTITION BY carrid ORDER BY fldate ) = 0 THEN 0
         ELSE ( price - LAG( price, 1 ) OVER(
                  PARTITION BY carrid ORDER BY fldate ) ) /
              LAG( price, 1 ) OVER(
                PARTITION BY carrid ORDER BY fldate ) * 100
       END AS pct_change
  FROM sflight
  WHERE carrid = 'LH'
  INTO TABLE @DATA(lt_pct_change).

FIRST_VALUE und LAST_VALUE

Diese Funktionen geben den ersten oder letzten Wert im Fenster zurück:

CLASS zcl_window_first_last DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

CLASS zcl_window_first_last IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Erster und letzter Preis pro Carrier
    SELECT carrid, connid, fldate, price,
           FIRST_VALUE( price ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS first_price,
           " Differenz zum ersten Preis
           price - FIRST_VALUE( price ) OVER(
             PARTITION BY carrid
             ORDER BY fldate
           ) AS diff_from_first
      FROM sflight
      WHERE carrid IN ( 'LH', 'AA' )
      ORDER BY carrid, fldate
      INTO TABLE @DATA(lt_first).

    out->write( '--- Vergleich zum ersten Preis ---' ).
    LOOP AT lt_first INTO DATA(ls_first).
      out->write( |{ ls_first-carrid } { ls_first-fldate DATE = USER }: | &&
                  |Preis { ls_first-price }, | &&
                  |Erster { ls_first-first_price }, | &&
                  |Diff { ls_first-diff_from_first SIGN = LEFT }| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

Praktisches Beispiel: Verkaufsanalyse

Hier ein vollständiges Beispiel, das mehrere Window Functions kombiniert:

CLASS zcl_sales_analysis DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

    TYPES: BEGIN OF ty_s_sale,
             sale_id     TYPE sysuuid_x16,
             region      TYPE c LENGTH 10,
             product     TYPE c LENGTH 20,
             sale_date   TYPE d,
             amount      TYPE p DECIMALS 2,
           END OF ty_s_sale.
    TYPES ty_t_sales TYPE STANDARD TABLE OF ty_s_sale WITH EMPTY KEY.

ENDCLASS.

CLASS zcl_sales_analysis IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    " Beispieldaten (normalerweise aus DB-Tabelle)
    DATA(lt_sales) = VALUE ty_t_sales(
      ( region = 'NORD'  product = 'Laptop' sale_date = '20260101' amount = '1200.00' )
      ( region = 'NORD'  product = 'Mouse'  sale_date = '20260102' amount = '30.00' )
      ( region = 'NORD'  product = 'Laptop' sale_date = '20260115' amount = '1100.00' )
      ( region = 'SUED'  product = 'Laptop' sale_date = '20260105' amount = '1250.00' )
      ( region = 'SUED'  product = 'Monitor' sale_date = '20260110' amount = '400.00' )
      ( region = 'SUED'  product = 'Laptop' sale_date = '20260120' amount = '1300.00' )
      ( region = 'WEST'  product = 'Laptop' sale_date = '20260108' amount = '1150.00' )
    ).

    " Analyse mit Window Functions
    " Hinweis: Bei echten DB-Tabellen direkt in SELECT verwenden
    SELECT FROM @lt_sales AS sales
      FIELDS
        region,
        product,
        sale_date,
        amount,
        " Ranking pro Region
        ROW_NUMBER( ) OVER(
          PARTITION BY region
          ORDER BY amount DESC
        ) AS rank_in_region,
        " Anteil am Regions-Umsatz
        CAST( amount AS DECFLOAT34 ) /
          SUM( amount ) OVER( PARTITION BY region ) * 100
        AS pct_of_region,
        " Laufende Summe pro Region
        SUM( amount ) OVER(
          PARTITION BY region
          ORDER BY sale_date
        ) AS running_total,
        " Vergleich mit vorherigem Verkauf der Region
        amount - LAG( amount, 1, amount ) OVER(
          PARTITION BY region
          ORDER BY sale_date
        ) AS diff_to_prev,
        " Regions-Durchschnitt
        AVG( amount ) OVER( PARTITION BY region ) AS region_avg,
        " Gesamtdurchschnitt
        AVG( amount ) OVER( ) AS total_avg
      ORDER BY region, sale_date
      INTO TABLE @DATA(lt_analysis).

    " Ausgabe
    out->write( '=== Verkaufsanalyse mit Window Functions ===' ).
    out->write( '' ).

    DATA(lv_current_region) = VALUE #( lt_analysis[ 1 ]-region OPTIONAL ).

    LOOP AT lt_analysis INTO DATA(ls_a).
      " Regionsüberschrift
      IF ls_a-region <> lv_current_region.
        out->write( '' ).
        lv_current_region = ls_a-region.
      ENDIF.

      IF sy-tabix = 1 OR ls_a-region <> VALUE #( lt_analysis[ sy-tabix - 1 ]-region OPTIONAL ).
        out->write( |--- Region { ls_a-region } ---| ).
        out->write( |  Durchschnitt: { ls_a-region_avg DECIMALS = 2 }| ).
      ENDIF.

      out->write( |  #{ ls_a-rank_in_region } { ls_a-product WIDTH = 10 }: | &&
                  |{ ls_a-amount WIDTH = 10 } | &&
                  |({ ls_a-pct_of_region DECIMALS = 1 }% der Region) | &&
                  |Laufend: { ls_a-running_total }| ).
    ENDLOOP.

    out->write( '' ).
    out->write( |Gesamtdurchschnitt: { lt_analysis[ 1 ]-total_avg DECIMALS = 2 }| ).

    " Top-Verkauf pro Region extrahieren
    out->write( '' ).
    out->write( '=== Top-Verkauf pro Region ===' ).

    LOOP AT lt_analysis INTO DATA(ls_top) WHERE rank_in_region = 1.
      out->write( |{ ls_top-region }: { ls_top-product } - { ls_top-amount }| ).
    ENDLOOP.
  ENDMETHOD.

ENDCLASS.

Performance-Tipps

Window Functions effizient nutzen

Tipp	Beschreibung
Partitionen klein halten	Große Partitionen erhöhen den Speicherbedarf
Indizes nutzen	ORDER BY-Spalten sollten indiziert sein
Mehrere berechnen	Mehrere Window Functions in einem SELECT sind effizienter als mehrere Queries
Subqueries für Filter	Ranking-Filter (WHERE rank <= 3) erfordern Subquery

Wann keine Window Functions

Bei sehr großen Partitionen: Alternative Lösungen prüfen
Wenn nur Aggregate benötigt werden: GROUP BY ist effizienter
Bei komplexen bedingten Logiken: ABAP-Nachverarbeitung kann sinnvoller sein

Zusammenfassung

Window Functions in ABAP SQL ermöglichen mächtige analytische Berechnungen direkt in der Datenbank:

OVER() definiert das Berechnungsfenster mit PARTITION BY und ORDER BY
ROW_NUMBER, RANK, DENSE_RANK für Rankings und Top-N-Abfragen
SUM, AVG, COUNT OVER() für partitionierte und laufende Aggregate
LAG, LEAD für Vergleiche mit Vorgänger/Nachfolger-Zeilen
FIRST_VALUE, LAST_VALUE für Randwerte im Fenster

Der große Vorteil: Die Berechnungen erfolgen in der Datenbank und reduzieren den Datentransfer zur Applikationsschicht erheblich.