graph-lakehouse
Window Aggregate and Ranking Functions
Window aggregate functions enable you to compute aggregate values on a particular partition or window of the result set. Unlike grouped aggregate functions that group the results and return a single value, window aggregates return a value for each row in the specified window. For example, using the grouped aggregate SUM function to add up the total number of tickets sold in a year returns one value: the total number of tickets sold for the year. By using the SUM window aggregate instead, the results could be partitioned by month so that the query returns 12 values: the sum of the number of tickets sold in each month of the year.
This topic describes the window aggregate and ranking functions in Graph Lakehouse:
- AVG: Calculates the average value of each group of values.
- COUNT: Counts the number of values in each group of values.
- MAX: Calculates the maximum value of each group of values.
- MIN: Calculates the minimum value of each group of values.
- NTILE: Divides the rows in the partition into the specified number of ranked groups and returns the group that each value belongs to.
- PERCENTILE: Divides the rows in the partition into 100 ranked groups and returns the group that each value belongs to.
- PRODUCT: Calculates the product of each group of values.
- QUARTILE: Divides the rows in the partition into four ranked groups and returns the group that each value belongs to.
- ROW_NUMBER: Assigns unique numbers to each row in the partition.
- SUM: Calculates the sum of each group of values.
Typographical Conventions
The following list describes the conventions used to document function syntax:
CAPS: Although SPARQL is case-insensitive, SPARQL keywords in this section are written in uppercase for readability.[ argument ]: Brackets indicate an optional argument or keyword.|: Means OR. Indicates that you can use one or more of the specified options.
AVG
This function calculates the average value of each group of values.
Syntax
(AVG(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ]
frame clause
[ [ ROWS ] frame_start | [ ROWS ] BETWEEN frame_start AND frame_end ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
| frame clause | The reference point for all window frames is the current row. The optional frame clause further defines the frame by specifying the rows in a partition to combine with the current row. There are two types of window frames: |
- A fixed frame with two moving endpoints: Each row becomes the current row as the window frame slides forward in the partition. This type of frame is ideal for computing aggregations over moving time frames.
- A resizing frame with one anchored endpoint: One row is a fixed endpoint and the frame resizes up (PRECEDING) or down (FOLLOWING). This type of frame is ideal for computing running totals.
The frame clause can be one of the following options:
[ ROWS ] frame_start [ ROWS ] BETWEEN frame_start AND frame_end
When a frame clause is not included, the window frame is unbounded: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
| | frame_start | The starting point of the frame. This argument can be one of the following values:
UNBOUNDED PRECEDING positive_int PRECEDING CURRENT ROW positive_int FOLLOWING
| | frame_end | The end of the frame. This argument can be one of the following values:
positive_int PRECEDING CURRENT ROW positive_int FOLLOWING UNBOUNDED FOLLOWING
|
COUNT
This function counts the number of values in each group of values.
Syntax
(COUNT(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ]
frame clause
[ [ ROWS ] frame_start | [ ROWS ] BETWEEN frame_start AND frame_end ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
| frame clause | The reference point for all window frames is the current row. The optional frame clause further defines the frame by specifying the rows in a partition to combine with the current row. There are two types of window frames: |
- A fixed frame with two moving endpoints: Each row becomes the current row as the window frame slides forward in the partition. This type of frame is ideal for computing aggregations over moving time frames.
- A resizing frame with one anchored endpoint: One row is a fixed endpoint and the frame resizes up (PRECEDING) or down (FOLLOWING). This type of frame is ideal for computing running totals.
The frame clause can be one of the following options:
[ ROWS ] frame_start [ ROWS ] BETWEEN frame_start AND frame_end
When a frame clause is not included, the window frame is unbounded: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
| | frame_start | The starting point of the frame. This argument can be one of the following values:
UNBOUNDED PRECEDING positive_int PRECEDING CURRENT ROW positive_int FOLLOWING
| | frame_end | The end of the frame. This argument can be one of the following values:
positive_int PRECEDING CURRENT ROW positive_int FOLLOWING UNBOUNDED FOLLOWING
|
MAX
This function calculates the maximum value of each group of values.
Syntax
(MAX(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ]
frame clause
[ [ ROWS ] frame_start | [ ROWS ] BETWEEN frame_start AND frame_end ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
| frame clause | The reference point for all window frames is the current row. The optional frame clause further defines the frame by specifying the rows in a partition to combine with the current row. There are two types of window frames: |
- A fixed frame with two moving endpoints: Each row becomes the current row as the window frame slides forward in the partition. This type of frame is ideal for computing aggregations over moving time frames.
- A resizing frame with one anchored endpoint: One row is a fixed endpoint and the frame resizes up (PRECEDING) or down (FOLLOWING). This type of frame is ideal for computing running totals.
The frame clause can be one of the following options:
[ ROWS ] frame_start [ ROWS ] BETWEEN frame_start AND frame_end
When a frame clause is not included, the window frame is unbounded: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
| | frame_start | The starting point of the frame. This argument can be one of the following values:
UNBOUNDED PRECEDING positive_int PRECEDING CURRENT ROW positive_int FOLLOWING
| | frame_end | The end of the frame. This argument can be one of the following values:
positive_int PRECEDING CURRENT ROW positive_int FOLLOWING UNBOUNDED FOLLOWING
|
MIN
This function calculates the minimum value of each group of values.
Syntax
(MIN(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ]
frame clause
[ [ ROWS ] frame_start | [ ROWS ] BETWEEN frame_start AND frame_end ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
| frame clause | The reference point for all window frames is the current row. The optional frame clause further defines the frame by specifying the rows in a partition to combine with the current row. There are two types of window frames: |
- A fixed frame with two moving endpoints: Each row becomes the current row as the window frame slides forward in the partition. This type of frame is ideal for computing aggregations over moving time frames.
- A resizing frame with one anchored endpoint: One row is a fixed endpoint and the frame resizes up (PRECEDING) or down (FOLLOWING). This type of frame is ideal for computing running totals.
The frame clause can be one of the following options:
[ ROWS ] frame_start [ ROWS ] BETWEEN frame_start AND frame_end
When a frame clause is not included, the window frame is unbounded: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
| | frame_start | The starting point of the frame. This argument can be one of the following values:
UNBOUNDED PRECEDING positive_int PRECEDING CURRENT ROW positive_int FOLLOWING
| | frame_end | The end of the frame. This argument can be one of the following values:
positive_int PRECEDING CURRENT ROW positive_int FOLLOWING UNBOUNDED FOLLOWING
|
NTILE
This function divides the rows in the partition into the specified number of ranked groups and returns the group that each value belongs to.
Syntax
(NTILE(number_of_groups) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ] ) AS ?variable )
| Argument | Description |
|---|---|
| number_of_groups | Required argument that defines the number of ranking groups. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
PERCENTILE
This function divides the rows in the partition into 100 ranked groups and returns the group that each value belongs to.
Syntax
(PERCENTILE(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
PRODUCT
This function calculates the product of each group of values.
Syntax
(PRODUCT(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ]
frame clause
[ [ ROWS ] frame_start | [ ROWS ] BETWEEN frame_start AND frame_end ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
| frame clause | The reference point for all window frames is the current row. The optional frame clause further defines the frame by specifying the rows in a partition to combine with the current row. There are two types of window frames: |
- A fixed frame with two moving endpoints: Each row becomes the current row as the window frame slides forward in the partition. This type of frame is ideal for computing aggregations over moving time frames.
- A resizing frame with one anchored endpoint: One row is a fixed endpoint and the frame resizes up (PRECEDING) or down (FOLLOWING). This type of frame is ideal for computing running totals.
The frame clause can be one of the following options:
[ ROWS ] frame_start [ ROWS ] BETWEEN frame_start AND frame_end
When a frame clause is not included, the window frame is unbounded: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
| | frame_start | The starting point of the frame. This argument can be one of the following values:
UNBOUNDED PRECEDING positive_int PRECEDING CURRENT ROW positive_int FOLLOWING
| | frame_end | The end of the frame. This argument can be one of the following values:
positive_int PRECEDING CURRENT ROW positive_int FOLLOWING UNBOUNDED FOLLOWING
|
QUARTILE
This function divides the rows in the partition into four ranked groups and returns the group that each value belongs to.
Syntax
(QUARTILE(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
ROW_NUMBER
This function assigns unique numbers to each row in the partition.
Syntax
(ROW_NUMBER() OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ] ) AS ?variable )
| Argument | Description |
|---|---|
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
SUM
This function calculates the sum of each group of values.
Syntax
(SUM(value) OVER ( [ PARTITION BY partition_value ] [ ORDER BY order_value ]
frame clause
[ [ ROWS ] frame_start | [ ROWS ] BETWEEN frame_start AND frame_end ] ) AS ?variable )
| Argument | Description |
|---|---|
| value | Required argument that defines the group of values to operate on. |
| partition_value | The optional PARTITION BY clause forms the groups of rows, dividing the result set into the partitions defined by the given partition_value. If you do not include PARTITION BY, the partition becomes the entire result set. When PARTITION BY is included, the function is applied to the group of rows in each partition. |
| order_value | The optional ORDER BY clause defines the order or sequence of rows within each partition. |
| frame clause | The reference point for all window frames is the current row. The optional frame clause further defines the frame by specifying the rows in a partition to combine with the current row. There are two types of window frames: |
- A fixed frame with two moving endpoints: Each row becomes the current row as the window frame slides forward in the partition. This type of frame is ideal for computing aggregations over moving time frames.
- A resizing frame with one anchored endpoint: One row is a fixed endpoint and the frame resizes up (PRECEDING) or down (FOLLOWING). This type of frame is ideal for computing running totals.
The frame clause can be one of the following options:
[ ROWS ] frame_start [ ROWS ] BETWEEN frame_start AND frame_end
When a frame clause is not included, the window frame is unbounded: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
| | frame_start | The starting point of the frame. This argument can be one of the following values:
UNBOUNDED PRECEDING positive_int PRECEDING CURRENT ROW positive_int FOLLOWING
| | frame_end | The end of the frame. This argument can be one of the following values:
positive_int PRECEDING CURRENT ROW positive_int FOLLOWING UNBOUNDED FOLLOWING
|
Examples
This example queries the sample Tickit data set to return the percentage of a salesperson's total sales that came from the "Gypsy" event:
PREFIX tickit: http://anzograph.com/tickit/ SELECT ?event_name ?fname ?lname ((?dollars * 100.0/(SUM(?dollars) OVER(PARTITION BY ?event))) as ?percent_of_sales) FROM http://anzograph.com/tickit WHERE { ?sale tickit:eventid ?event . ?event tickit:eventname ?event_name . ?sale tickit:sellerid ?salesperson . ?sale tickit:pricepaid ?dollars . ?salesperson tickit:firstname ?fname . ?salesperson tickit:lastname ?lname . FILTER(?event_name = "Gypsy"). } ORDER BY ?event_name desc(?percent_of_sales)
event_name | fname | lname | percent_of_sales -----------+-------------+-------------+------------------ Gypsy | Zoe | Sosa | 100 Gypsy | Xaviera | Jacobson | 50.9415 Gypsy | Brianna | Mcfarland | 50.5076 Gypsy | Alexa | Baird | 45.7926 Gypsy | Roanna | Wood | 42.0408 Gypsy | Colette | Clay | 36.9388 Gypsy | Amela | Holman | 35.7277 Gypsy | Aubrey | Terrell | 32.2457 Gypsy | Bruno | Griffin | 31.8139 Gypsy | Damian | Berger | 31.2459 Gypsy | Zelenia | Woods | 31.1616 Gypsy | Imogene | Mclean | 31.0005 ... 857 rows
This example queries the sample Tickit data set to return a running total of the number of tickets sold for the event "Mamma Mia!":
PREFIX tickit: http://anzograph.com/tickit/ SELECT ?event ?month (SUM(?qty) OVER (PARTITION BY ?month ORDER BY ?event ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ?tickets) FROM http://anzograph.com/tickit WHERE { ?s tickit:qtysold ?qty . ?s tickit:eventid ?eventid . ?eventid tickit:eventname ?event . ?s tickit:dateid ?date . ?date tickit:month ?month . filter(?event="Mamma Mia!") } ORDER BY ?tickets LIMIT 100
event | month | tickets -----------+-------+--------- Mamma Mia! | FEB | 1 Mamma Mia! | AUG | 1 Mamma Mia! | MAR | 1 Mamma Mia! | MAY | 1 Mamma Mia! | JUN | 2 Mamma Mia! | SEP | 2 Mamma Mia! | AUG | 2 Mamma Mia! | DEC | 2 Mamma Mia! | NOV | 2 Mamma Mia! | OCT | 2 Mamma Mia! | JAN | 2 Mamma Mia! | MAR | 2 Mamma Mia! | NOV | 3 Mamma Mia! | JAN | 3 Mamma Mia! | JUN | 4 Mamma Mia! | JUL | 4 Mamma Mia! | SEP | 4 ... 100 rows
Source: https://docs.sw.siemens.com/documentation/external/PL20260518131381558/en-US/html/system-window.htm · retrieved 2026-08-23