In the previous post ABAP 7.4 and beyond  : 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?|
Frame starts at the first row of the window
Frame ends at the last row of the partition
Frame starts at current row
Frame ends at current row
Frame starts n rows above the current row
Frame ends n rows above the current row
Frame starts n rows beneath the current row
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  : 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…