ABAP 7.4 and beyond [14] : SQL Window Expression with Frames

In the previous post ABAP 7.4 and beyond [13] : SQL Window Expression you learned about using Windowing for Aggregate functions.

In this post, you will learn to use ORDER BY to create Windows and Frames within the windows.

Windows can be created in two ways

• PARTITION BY which we have seen in previous post
• ORDER BY – This creates a frame within the current window so that Ranking Function can be applied on it.

Let us see an example,

Simple Window Expression – PARTITION BY

We will get average seats occupied for a carrier.

SELECT FROM /dmo/flight
  FIELDS carrier_id,
    seats_occupied,
    AVG( seats_occupied ) OVER( PARTITION BY carrier_id )
                 AS avg_seats_occupied
    INTO TABLE @DATA(lt_window).

Note that Window Expression will provide individual records also along with aggregates.

Simple Window Expression – ORDER BY

Using Order By we can create windows using SORT sequence rather than field values. For example, if we sort by Average Seats Occupied we can get the average number of seats occupied from the first row to the current row.

SELECT FROM /dmo/flight
  FIELDS carrier_id,
    seats_occupied,
    AVG( seats_occupied ) OVER( ORDER BY seats_occupied
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
                 AS avg_seats_occupied
    INTO TABLE @DATA(lt_window).

Simple Window Expression – Frames Using ORDER BY

The ROWS BETWEEN the addition create the FRAMES.

ROWS BETWEEN A	AND B
What can we specify in A?	What can we specify in B?
UNBOUNDED PRECEDING Frame starts at the first row of the window	UNBOUNDED FOLLOWING Frame ends at the last row of the partition
CURRENT ROW Frame starts at current row	CURRENT ROW Frame ends at current row
(n) PRECEDING Frame starts n rows above the current row	(n) PRECEDING Frame ends n rows above the current row
(n) FOLLOWING Frame starts n rows beneath the current row	(n) FOLLOWING Frame ends n rows beneath the current row

We can use combinations including but not limited to the below examples.

• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Combining PARTITION and ORDER BY

Example: Get seats occupied average for the last 3 flights for a carrier id. i.e. 2 previous flights and the current row should be considered.

SELECT FROM /dmo/flight
  FIELDS carrier_id,
    flight_date,
    seats_occupied,
    AVG( seats_occupied ) OVER( PARTITION BY carrier_id
                                ORDER BY flight_date
                                ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )
                 AS avg_seats_occupied
    INTO TABLE @DATA(lt_window).

Let us complicate this a little

It’s not really making it complex but using more functionality to show a glimpse of the potential of these queries.

With the below single query, we can produce data to analyze the trend of no. of travelers for any carrier.

By looking at the data, we can see that all 3 carriers i.e. SQ, AA, and SQ peaked at a certain point and then on a downward trend.

Now, so far we are only using aggregate functions. In the next post we will use Rank and Value window functions to make things more interesting – ABAP 7.4 and beyond [15] : SQL Window Function (Rank and Value).

Read about more such ABAP expressions and exciting new syntaxes: ABAP Expressions (7.4+)

---

If you like the content, please subscribe…