Window Functions
 enterprise edition
 Couchbase Server 6.5
Window functions are used to compute cumulative, moving, and reporting aggregations.
Window functions are used to compute an aggregate or cumulative value, based on a group of objects. The objects are not grouped into a single output object — each object remains separate in the query output.
All window functions must have a window definition. This divides the query result set into one or more partitions, and specifies the order of objects in those partitions. Within each partition, a movable window frame is defined for every input object. The window frame determines the objects to be used by the window function.
The OVER
clause specifies the window definition.
Some window functions take additional window options, which are specified by further clauses before the OVER
clause.
N1QL has a dedicated set of window functions.
In Couchbase Server 6.5 Enterprise Edition and later, aggregate functions can also be used as window functions, when they are used with an OVER
clause.
This page gives details of the syntax of individual window functions. See the OVER clause and Window Functions page for the generic syntax of window functions, window options, and the window definition.
Usage
Any expression within a window function may be a subquery. However, this will lead to repeated evaluation when the query is processed. If required, use a LET clause, a WITH clause, or an intervening subquery to avoid repeated evaluation. 
An expression within the window function may not contain another, nested window function. If necessary, you can specify one window function in a subquery, and another window function in the parent query. 
CUME_DIST()
Description
Returns the percentile rank of the current object as part of the cumulative distribution – that is, the number of objects ranked lower than or equal to the current object, including the current object, divided by the total number of objects in the window partition.
Syntax
cumedistfunction ::= CUME_DIST '()' OVER '(' [ windowpartitionclause ] windoworderclause ')'
Return Value
A number greater than 0 and less than or equal to 1. The higher the value, the higher the ranking.
Example
For each destination airport, find the cumulative distribution of all routes in order of distance.
SELECT d.id, d.destinationairport, CUME_DIST() OVER (
PARTITION BY d.destinationairport
ORDER BY d.distance NULLS LAST
) AS `rank`
FROM `travelsample` AS d
WHERE d.type="route"
LIMIT 7;
[
{
"destinationairport": "AAE",
"id": 10201,
"rank": 0.25
},
{
"destinationairport": "AAE",
"id": 10190,
"rank": 0.5
},
{
"destinationairport": "AAE",
"id": 10240,
"rank": 0.75
},
{
"destinationairport": "AAE",
"id": 10136,
"rank": 1
},
{
"destinationairport": "AAL",
"id": 14392,
"rank": 0.3333333333333333
},
{
"destinationairport": "AAL",
"id": 14867,
"rank": 0.6666666666666666
},
{
"destinationairport": "AAL",
"id": 22505,
"rank": 1
},
]
DENSE_RANK()
Description
Returns the dense rank of the current object – that is, the number of distinct objects preceding this object in the current window partition, plus one.
The objects are ordered by the window order clause. If any objects are tied, they will have the same rank.
For this function, when any objects have the same rank, the rank of the next object will be consecutive, so there will not be a gap in the sequence of returned values. For example, if there are three objects ranked 2, the next dense rank is 3.
Syntax
denserankfunction ::= DENSE_RANK '()' OVER '(' [ windowpartitionclause ] windoworderclause ')'
Example
For each country, find the dense rank of all airports in order of altitude.
SELECT a.airportname, a.geo.alt, DENSE_RANK() OVER (
PARTITION BY a.country
ORDER BY a.geo.alt NULLS LAST
) AS `rank`
FROM `travelsample` AS a
WHERE a.type="airport"
LIMIT 10;
[
{
"airportname": "Croisette Heliport",
"alt": 0,
"rank": 1
},
{
"airportname": "AndernosLesBains",
"alt": 0,
"rank": 1
},
{
"airportname": "La Defense Heliport",
"alt": 0,
"rank": 1
},
{
"airportname": "Marigot Bus Stop",
"alt": 0,
"rank": 1
},
{
"airportname": "Lille",
"alt": 1,
"rank": 2
},
{
"airportname": "Le Palyvestre",
"alt": 7,
"rank": 3
},
{
"airportname": "Frejus Saint Raphael",
"alt": 7,
"rank": 3
},
{
"airportname": "Calais Dunkerque",
"alt": 12,
"rank": 4
},
{
"airportname": "Cote D\\'Azur",
"alt": 12,
"rank": 4
},
{
"airportname": "Propriano",
"alt": 13,
"rank": 5
}
]
FIRST_VALUE(expr
)
Description
Returns the requested value from the first object in the current window frame, where the window frame is specified by the window definition.
Syntax
firstvaluefunction ::= FIRST_VALUE '(' expr ')' [ nullstreatment ] OVER '(' windowdefinition ')'
Arguments
 expr

[Required] The value that you want to return from the first object in the window frame. ^{[1]}
Nulls Treatment
The nulls treatment clause determines how NULL or MISSING values are treated when finding the first object in the window frame:
IGNORE NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are not included when finding the first object. In this case, the function returns the first nonNULL, nonMISSING value.
RESPECT NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are included when finding the first object.
If the nulls treatment clause is omitted, the default is RESPECT NULLS
.
Return Values
The specified value from the first object.
If all values are NULL or MISSING it returns NULL.
In the following cases, this function may return unpredictable results.
To make the function return deterministic results, add a window order clause, or add further ordering terms to the window order clause so that no objects are tied. If the window frame is defined by 
Example
For each airport, show each route starting at that airport, including the distance of the route and the distance of the shortest route from that airport.
SELECT r.sourceairport, r.destinationairport, r.distance,
FIRST_VALUE(r.distance) OVER (
PARTITION BY r.sourceairport
ORDER BY r.distance
) AS `shortest_distance`
FROM `travelsample` AS r
WHERE r.type="route"
LIMIT 7;
[
{
"destinationairport": "MRS",
"distance": 767.6526005881392,
"shortest_distance": 767.6526005881392,
"sourceairport": "AAE"
},
{
"destinationairport": "LYS",
"distance": 1015.6529968903878,
"shortest_distance": 767.6526005881392,
"sourceairport": "AAE"
},
{
"destinationairport": "ORY",
"distance": 1395.3690007167947,
"shortest_distance": 767.6526005881392,
"sourceairport": "AAE"
},
{
"destinationairport": "CDG",
"distance": 1420.6731433915318,
"shortest_distance": 767.6526005881392,
"sourceairport": "AAE"
},
{
"destinationairport": "AAR",
"distance": 99.89861063028253,
"shortest_distance": 99.89861063028253,
"sourceairport": "AAL"
},
{
"destinationairport": "OSL",
"distance": 352.33081791745275,
"shortest_distance": 99.89861063028253,
"sourceairport": "AAL"
},
{
"destinationairport": "LGW",
"distance": 928.284226131001,
"shortest_distance": 99.89861063028253,
"sourceairport": "AAL"
}
]
LAG(expr
[, offset
[, default
] ] )
Syntax
lagfunction ::= LAG '(' expr [ ',' offset [ ',' default ] ] ')' [ nullstreatment ] OVER '(' [ windowpartitionclause ] windoworderclause ')'
Arguments
 expr

[Required] The value that you want to return from the offset object. ^{[1]}
 offset

[Optional] A positive integer greater than 0. If omitted, the default is 1.
 default

[Optional] The value to return when the offset goes out of window scope. If omitted, the default is NULL.
Nulls Treatment
The nulls treatment clause determines how NULL or MISSING values are treated when counting the offset:
IGNORE NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are not included when counting the offset.
RESPECT NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are included when counting the offset.
If the nulls treatment clause is omitted, the default is RESPECT NULLS
.
Return Values
The specified value from the offset object.
If the offset object is out of scope, it returns the default value, or NULL if no default is specified.
Example
For each airline, show each route operated by that airline, including the length of the route and the length of the nextshortest route.
SELECT r.airline, r.id, r.distance,
LAG(r.distance, 1, "No previous distance") OVER (
PARTITION BY r.airline
ORDER BY r.distance NULLS LAST
) AS `previousdistance`
FROM `travelsample` AS r
WHERE r.type="route"
LIMIT 7;
[
{
"airline": "2L",
"distance": 770.9691328580009,
"id": 125,
"previousdistance": "No previous distance"
},
{
"airline": "2L",
"distance": 770.969132858001,
"id": 117,
"previousdistance": 770.9691328580009
},
{
"airline": "2L",
"distance": 922.7579695456559,
"id": 118,
"previousdistance": 770.969132858001
},
{
"airline": "2L",
"distance": 922.7579695456559,
"id": 126,
"previousdistance": 922.7579695456559
},
{
"airline": "3F",
"distance": 23.957943869396804,
"id": 274,
"previousdistance": "No previous distance"
},
{
"airline": "3F",
"distance": 23.957943869396804,
"id": 276,
"previousdistance": 23.957943869396804
},
{
"airline": "3F",
"distance": 26.397914084363418,
"id": 282,
"previousdistance": 23.957943869396804
}
]
LAST_VALUE(expr
)
Description
Returns the requested value from the last object in the current window frame, where the window frame is specified by the window definition.
Syntax
lastvaluefunction ::= LAST_VALUE '(' expr ')' [ nullstreatment ] OVER '(' windowdefinition ')'
Arguments
 expr

[Required] The value that you want to return from the last object in the window frame. ^{[1]}
Nulls Treatment
The nulls treatment clause determines how NULL or MISSING values are treated when finding the last object in the window frame:
IGNORE NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are not included when finding the last object. In this case, the function returns the last nonNULL, nonMISSING value.
RESPECT NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are included when finding the last object.
If the nulls treatment clause is omitted, the default is RESPECT NULLS
.
Return Values
The specified value from the last object.
If all values are NULL or MISSING it returns NULL.
In the following cases, this function may return unpredictable results.
To make the function return deterministic results, add a window order clause, or add further ordering terms to the window order clause so that no objects are tied. If the window frame is defined by 
Example
For each airport, show each route starting at that airport, including the distance of the route and the distance of the longest route from that airport.
SELECT r.sourceairport, r.destinationairport, r.distance,
LAST_VALUE(r.distance) OVER (
PARTITION BY r.sourceairport
ORDER BY r.distance
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (1)
) AS `longest_distance`
FROM `travelsample` AS r
WHERE r.type="route"
LIMIT 7;
[
{
"destinationairport": "MRS",
"distance": 767.6526005881392,
"longest_distance": 1420.6731433915318,
"sourceairport": "AAE"
},
{
"destinationairport": "LYS",
"distance": 1015.6529968903878,
"longest_distance": 1420.6731433915318,
"sourceairport": "AAE"
},
{
"destinationairport": "ORY",
"distance": 1395.3690007167947,
"longest_distance": 1420.6731433915318,
"sourceairport": "AAE"
},
{
"destinationairport": "CDG",
"distance": 1420.6731433915318,
"longest_distance": 1420.6731433915318,
"sourceairport": "AAE"
},
{
"destinationairport": "AAR",
"distance": 99.89861063028253,
"longest_distance": 928.284226131001,
"sourceairport": "AAL"
},
{
"destinationairport": "OSL",
"distance": 352.33081791745275,
"longest_distance": 928.284226131001,
"sourceairport": "AAL"
},
{
"destinationairport": "LGW",
"distance": 928.284226131001,
"longest_distance": 928.284226131001,
"sourceairport": "AAL"
}
]
1  This clause specifies that the window frame should extend to the end of the window partition. Without this clause, the end point of the window frame would always be the current object. This would mean that the longest distance would always be the same as the current distance. 
LEAD(expr
[, offset
[, default
] ] )
Syntax
leadfunction ::= LEAD '(' expr [ ',' offset [ ',' default ] ] ')' [ nullstreatment ] OVER '(' [ windowpartitionclause ] windoworderclause ')'
Arguments
 expr

[Required] The value that you want to return from the offset object. ^{[1]}
 offset

[Optional] A positive integer greater than 0. If omitted, the default is 1.
 default

[Optional] The value to return when the offset goes out of window scope. If omitted, the default is NULL.
Nulls Treatment
The nulls treatment clause determines how NULL or MISSING values are treated when counting the offset:
IGNORE NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are not included when counting the offset.
RESPECT NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are included when counting the offset.
If the nulls treatment clause is omitted, the default is RESPECT NULLS
.
Return Values
The specified value from the offset object.
If the offset object is out of scope, it returns the default value, or NULL if no default is specified.
Example
For each airline, show each route operated by that airline, including the length of the route and the length of the nextlongest route.
SELECT r.airline, r.id, r.distance,
LEAD(r.distance, 1, "No next distance") OVER (
PARTITION BY r.airline
ORDER BY r.distance NULLS LAST
) AS `nextdistance`
FROM `travelsample` AS r
WHERE r.type="route"
LIMIT 7;
[
{
"airline": "2L",
"distance": 770.9691328580009,
"id": 125,
"nextdistance": 770.969132858001
},
{
"airline": "2L",
"distance": 770.969132858001,
"id": 117,
"nextdistance": 922.7579695456559
},
{
"airline": "2L",
"distance": 922.7579695456559,
"id": 118,
"nextdistance": 922.7579695456559
},
{
"airline": "2L",
"distance": 922.7579695456559,
"id": 126,
"nextdistance": "No next distance"
},
{
"airline": "3F",
"distance": 23.957943869396804,
"id": 274,
"nextdistance": 23.957943869396804
},
{
"airline": "3F",
"distance": 23.957943869396804,
"id": 276,
"nextdistance": 26.397914084363418
},
{
"airline": "3F",
"distance": 26.397914084363418,
"id": 282,
"nextdistance": 26.397914084363418
}
]
NTH_VALUE(expr
, offset
)
Description
Returns the requested value from an object in the current window frame, where the window frame is specified by the window definition.
Syntax
nthvaluefunction ::= NTH_VALUE '(' expr ',' offset ')' [ nthvalfrom ] [ nullstreatment ] OVER '(' windowdefinition ')'
Arguments
 expr

[Required] The value that you want to return from the offset object in the window frame. ^{[1]}
 offset

[Required] The number of the offset object within the window frame, counting from 1.
Nth Val From
The nth val from clause determines where the function starts counting the offset.
FROM FIRST

Offset counting starts at the first object in the window frame. In this case, an offset of 1 is the first object in the window frame, 2 is the second object, and so on.
FROM LAST

Offset counting starts at the last object in the window frame. In this case, an offset of 1 is the last object in the window frame, 2 is the secondtolast object, and so on.
Nulls Treatment
The nulls treatment clause determines how NULL or MISSING values are treated when counting the offset:
IGNORE NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are not included when counting the offset.
RESPECT NULLS

If the values for any objects evaluate to NULL or MISSING, those objects are included when counting the offset.
If the nulls treatment clause is omitted, the default is RESPECT NULLS
.
Return Values
The specified value from the offset object.
In the following cases, this function may return unpredictable results.
To make the function return deterministic results, add a window order clause, or add further ordering terms to the window order clause so that no objects are tied. If the window frame is defined by 
Examples
For each airport, show each route starting at that airport, including the distance of the route and the distance of the second shortest route from that airport.
SELECT r.sourceairport, r.destinationairport, r.distance,
NTH_VALUE(r.distance, 2) FROM FIRST OVER (
PARTITION BY r.sourceairport
ORDER BY r.distance
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (1)
) AS `shortest_distance_but_1`
FROM `travelsample` AS r
WHERE r.type="route"
LIMIT 7;
[
{
"destinationairport": "MRS",
"distance": 767.6526005881392,
"shortest_distance_but_1": 1015.6529968903878,
"sourceairport": "AAE"
},
{
"destinationairport": "LYS", (2)
"distance": 1015.6529968903878,
"shortest_distance_but_1": 1015.6529968903878,
"sourceairport": "AAE"
},
{
"destinationairport": "ORY",
"distance": 1395.3690007167947,
"shortest_distance_but_1": 1015.6529968903878,
"sourceairport": "AAE"
},
{
"destinationairport": "CDG",
"distance": 1420.6731433915318,
"shortest_distance_but_1": 1015.6529968903878,
"sourceairport": "AAE"
},
{
"destinationairport": "AAR",
"distance": 99.89861063028253,
"shortest_distance_but_1": 352.33081791745275,
"sourceairport": "AAL"
},
{
"destinationairport": "OSL", (2)
"distance": 352.33081791745275,
"shortest_distance_but_1": 352.33081791745275,
"sourceairport": "AAL"
},
{
"destinationairport": "LGW",
"distance": 928.284226131001,
"shortest_distance_but_1": 352.33081791745275,
"sourceairport": "AAL"
}
]
1  This clause specifies that the window frame should extend to the end of the window partition. Without this clause, the end point of the window frame would always be the current object. This would mean that for the route with the shortest distance, the function would be unable to find the route with the second shortest distance. 
2  The route with the second shortest distance from this airport. 
For each airport, show each route starting at that airport, including the distance of the route and the distance of the second longest route from that airport.
SELECT r.sourceairport, r.destinationairport, r.distance,
NTH_VALUE(r.distance, 2) FROM LAST OVER (
PARTITION BY r.sourceairport
ORDER BY r.distance
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (1)
) AS `longest_distance_but_1`
FROM `travelsample` AS r
WHERE r.type="route"
LIMIT 7;
[
{
"destinationairport": "MRS",
"distance": 767.6526005881392,
"longest_distance_but_1": 1395.3690007167947,
"sourceairport": "AAE"
},
{
"destinationairport": "LYS",
"distance": 1015.6529968903878,
"longest_distance_but_1": 1395.3690007167947,
"sourceairport": "AAE"
},
{
"destinationairport": "ORY",
"distance": 1395.3690007167947,
"longest_distance_but_1": 1395.3690007167947, (2)
"sourceairport": "AAE"
},
{
"destinationairport": "CDG",
"distance": 1420.6731433915318,
"longest_distance_but_1": 1395.3690007167947,
"sourceairport": "AAE"
},
{
"destinationairport": "AAR",
"distance": 99.89861063028253,
"longest_distance_but_1": 352.33081791745275,
"sourceairport": "AAL"
},
{
"destinationairport": "OSL",
"distance": 352.33081791745275,
"longest_distance_but_1": 352.33081791745275, (2)
"sourceairport": "AAL"
},
{
"destinationairport": "LGW",
"distance": 928.284226131001,
"longest_distance_but_1": 352.33081791745275,
"sourceairport": "AAL"
}
]
1  This clause specifies that the window frame should extend to the end of the window partition. Without this clause, the end point of the window frame would always be the current object. This would mean the function would be unable to find the route with the second longest distance for routes with shorter distances. 
2  The route with the second longest distance from this airport. 
NTILE(num_tiles
)
Description
Divides the window partition into the specified number of tiles, and allocates each object in the window partition to a tile, so that as far as possible each tile has an equal number of objects. When the set of objects is not equally divisible by the number of tiles, the function puts more objects into the lowernumbered tiles. For each object, the function returns the number of the tile into which that object was placed.
Syntax
ntilefunction ::= NTILE '(' num_tiles ')' OVER '(' [ windowpartitionclause ] windoworderclause ')'
Arguments
 num_tiles

[Required] The number of tiles into which you want to divide the window partition. This argument can be an expression and must evaluate to a number. If the number is not an integer, it will be truncated. If the expression depends on an object, it evaluates from the first object in the window partition.
Example
For each airline, allocate each route to one of three tiles by distance.
SELECT r.airline, r.distance, NTILE(3) OVER (
PARTITION BY r.airline
ORDER BY r.distance
) AS `ntile`
FROM `travelsample` AS r
WHERE r.type="route"
LIMIT 16;
[
{
"airline": "2L",
"distance": 770.9691328580009,
"ntile": 1
},
{
"airline": "2L",
"distance": 770.969132858001,
"ntile": 1
},
{
"airline": "2L",
"distance": 922.7579695456559,
"ntile": 2
},
{
"airline": "2L",
"distance": 922.7579695456559,
"ntile": 3
},
{
"airline": "3F",
"distance": 23.957943869396804,
"ntile": 1
},
{
"airline": "3F",
"distance": 23.957943869396804,
"ntile": 1
},
{
"airline": "3F",
"distance": 26.397914084363418,
"ntile": 1
},
{
"airline": "3F",
"distance": 26.397914084363418,
"ntile": 1
},
{
"airline": "3F",
"distance": 31.613003135476145,
"ntile": 2
},
{
"airline": "3F",
"distance": 31.613003135476145,
"ntile": 2
},
{
"airline": "3F",
"distance": 60.49012512494272,
"ntile": 2
},
{
"airline": "3F",
"distance": 60.490125124942736,
"ntile": 2
},
{
"airline": "3F",
"distance": 63.640308584677314,
"ntile": 3
},
{
"airline": "3F",
"distance": 63.640308584677314,
"ntile": 3
},
{
"airline": "3F",
"distance": 91.53839302649642,
"ntile": 3
},
{
"airline": "3F",
"distance": 91.53839302649642,
"ntile": 3
}
]
PERCENT_RANK()
Description
Returns the percentile rank of the current object – that is, the rank of the objects minus one, divided by the total number of objects in the window partition minus one.
Syntax
percentrankfunction ::= PERCENT_RANK '()' OVER '(' [ windowpartitionclause ] windoworderclause ')'
Example
For each destination airport, find the percentile rank of all routes in order of distance.
SELECT d.id, d.destinationairport, PERCENT_RANK() OVER (
PARTITION BY d.destinationairport
ORDER BY d.distance NULLS LAST
) AS `rank`
FROM `travelsample` AS d
WHERE d.type="route"
LIMIT 7;
[
{
"destinationairport": "AAE",
"id": 10201,
"rank": 0
},
{
"destinationairport": "AAE",
"id": 10190,
"rank": 0.3333333333333333
},
{
"destinationairport": "AAE",
"id": 10240,
"rank": 0.6666666666666666
},
{
"destinationairport": "AAE",
"id": 10136,
"rank": 1
},
{
"destinationairport": "AAL",
"id": 14392,
"rank": 0
},
{
"destinationairport": "AAL",
"id": 14867,
"rank": 0.5
},
{
"destinationairport": "AAL",
"id": 22505,
"rank": 1
}
]
RANK()
Description
Returns the rank of the current object – that is, the number of distinct objects preceding this object in the current window partition, plus one.
The objects are ordered by the window order clause. If any objects are tied, they will have the same rank.
When any objects have the same rank, the rank of the next object will include all preceding objects, so there may be a gap in the sequence of returned values. For example, if there are three objects ranked 2, the next rank is 5.
Example
For each country, find the rank of all airports in order of altitude.
SELECT a.airportname, a.geo.alt, RANK() OVER (
PARTITION BY a.country
ORDER BY a.geo.alt NULLS LAST
) AS `rank`
FROM `travelsample` AS a
WHERE a.type="airport"
LIMIT 10;
[
{
"airportname": "Croisette Heliport",
"alt": 0,
"rank": 1
},
{
"airportname": "AndernosLesBains",
"alt": 0,
"rank": 1
},
{
"airportname": "La Defense Heliport",
"alt": 0,
"rank": 1
},
{
"airportname": "Marigot Bus Stop",
"alt": 0,
"rank": 1
},
{
"airportname": "Lille",
"alt": 1,
"rank": 5
},
{
"airportname": "Le Palyvestre",
"alt": 7,
"rank": 6
},
{
"airportname": "Frejus Saint Raphael",
"alt": 7,
"rank": 6
},
{
"airportname": "Calais Dunkerque",
"alt": 12,
"rank": 8
},
{
"airportname": "Cote D\\'Azur",
"alt": 12,
"rank": 8
},
{
"airportname": "Propriano",
"alt": 13,
"rank": 10
}
]
RATIO_TO_REPORT(expr
)
Description
Returns the fractional ratio of the specified value for each object to the sum of values for all objects in the window frame. If the window frame clause is not specified, the fractional ratio is calculated for the whole window partition.
Return Values
A number between 0 and 1, representing the fractional ratio of the value for the current object to the sum of values for all objects in the current window frame. The sum of values for all objects in the current window frame is 1.
If the input expression does not evaluate to a number, or the sum of values for all objects is zero, it returns NULL.
Example
For each destination airport, calculate the distance of each route as a fraction of the total distance of all routes.
SELECT d.id, d.destinationairport, RATIO_TO_REPORT(d.distance) OVER (
PARTITION BY d.destinationairport
) AS `distanceratio`
FROM `travelsample` AS d
WHERE d.type="route" LIMIT 7;
[
{
"destinationairport": "AAE",
"distanceratio": 0.3088857862487639,
"id": 10136
},
{
"destinationairport": "AAE",
"distanceratio": 0.22082544177013463,
"id": 10190
},
{
"destinationairport": "AAE",
"distanceratio": 0.3033841055547952,
"id": 10240
},
{
"destinationairport": "AAE",
"distanceratio": 0.16690466642630636,
"id": 10201
},
{
"destinationairport": "AAL",
"distanceratio": 0.25521719160354467,
"id": 14867
},
{
"destinationairport": "AAL",
"distanceratio": 0.6724194454614251,
"id": 22505
},
{
"destinationairport": "AAL",
"distanceratio": 0.07236336293503035,
"id": 14392
}
]
ROW_NUMBER()
Description
Returns a unique row number for every object in every window partition. In each window partition, the row numbering starts at 1.
The window order clause determines the sort order of the objects. If the window order clause is omitted, the return values may be unpredictable.
Syntax
rownumberfunction ::= ROW_NUMBER '()' OVER '(' [ windowpartitionclause ] [ windoworderclause ] ')'
Example
For each destination airport, number all routes in order of distance.
SELECT d.id, d.destinationairport, ROW_NUMBER() OVER (
PARTITION BY d.destinationairport
ORDER BY d.distance NULLS LAST
) AS `row`
FROM `travelsample` AS d
WHERE d.type="route"
LIMIT 7;
[
{
"destinationairport": "AAE",
"id": 10201,
"row": 1
},
{
"destinationairport": "AAE",
"id": 10190,
"row": 2
},
{
"destinationairport": "AAE",
"id": 10240,
"row": 3
},
{
"destinationairport": "AAE",
"id": 10136,
"row": 4
},
{
"destinationairport": "AAL",
"id": 14392,
"row": 1
},
{
"destinationairport": "AAL",
"id": 14867,
"row": 2
},
{
"destinationairport": "AAL",
"id": 22505,
"row": 3
}
]