| # Licensed to the Apache Software Foundation (ASF) under one | |
| # or more contributor license agreements. See the NOTICE file | |
| # distributed with this work for additional information | |
| # regarding copyright ownership. The ASF licenses this file | |
| # to you under the Apache License, Version 2.0 (the | |
| # "License"); you may not use this file except in compliance | |
| # with the License. You may obtain a copy of the License at | |
| # | |
| # http://www.apache.org/licenses/LICENSE-2.0 | |
| # | |
| # Unless required by applicable law or agreed to in writing, | |
| # software distributed under the License is distributed on an | |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY | |
| # KIND, either express or implied. See the License for the | |
| # specific language governing permissions and limitations | |
| # under the License | |
| name: LEAD | |
| summary: Returns a value from a row after the current row in its partition. | |
| syntax: | |
| sql: LEAD(expression [, offset [, default]]) OVER ([PARTITION BY expression [, ...]] [ORDER BY expression [ASC | DESC] [, ...]]) | |
| java: | | |
| lead($("expression")) | |
| lead($("expression"), offset) | |
| lead($("expression"), offset, defaultValue) | |
| python: | | |
| lead(col("expression")) | |
| lead(col("expression"), offset) | |
| lead(col("expression"), offset, default) | |
| arguments: | |
| - name: expression | |
| type: A number, a boolean, a string, a `DATE`, a `TIME` or a `TIMESTAMP` | |
| required: true | |
| description: The value to read from the other row. | |
| - name: offset | |
| type: An integer | |
| required: false | |
| default: "`1`" | |
| description: How many rows after the current row to look. `0` returns the value of the current row. A negative offset looks the other way, so `LEAD(x, -1)` returns the same as `LAG(x, 1)` in batch mode. | |
| - name: default | |
| type: The type of `expression` | |
| required: false | |
| default: NULL | |
| description: The value to return if there is no row at that offset. It is evaluated on the current row. Give it exactly the type of `expression`, such as `CAST(0 AS BIGINT)` for a `BIGINT` expression. A wider type, such as a `BIGINT` default for an `INT` expression, fails the query. | |
| returns: | |
| description: The value of `expression` in the row that comes `offset` rows after the current row in its partition, by the `ORDER BY` of the window. | |
| nulls: Returns NULL if there is no such row and `default` is NULL or omitted, or if that row has a NULL value and `default` is nullable or omitted. | |
| type: The type of `expression`. NOT NULL if `default` is NOT NULL. | |
| notes: | |
| - The window can't have a frame clause, such as `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW`. | |
| - Without an `ORDER BY`, the order of the rows is undefined. | |
| - Not supported in streaming mode. | |
| - With a NOT NULL `default`, a NULL value in that row doesn't come back as NULL. Depending on the query, it comes back as a placeholder, such as `-1` for an `INT`, or the job fails. To get NULL, omit `default` or use a column that can be NULL. | |
| - "`IGNORE NULLS` is accepted but has no effect, so a NULL value in that row still comes back." | |
| see_also: | |
| - {name: LAG, ref: window/lag} | |
| - {name: ROW_NUMBER, ref: window/row-number} | |
| examples: | |
| - title: Compare each row with its neighbor | |
| description: Return the score of the row after each row. | |
| sql: | | |
| SELECT name, score, LEAD(score) OVER (ORDER BY score DESC, name) AS neighbor_score | |
| FROM (VALUES ('Ann', 90), ('Bob', 85), ('Cid', 85), ('Dee', 70)) AS t(name, score) | |
| ORDER BY score DESC, name; | |
| result: | | |
| | name | score | neighbor_score | | |
| |------|-------|----------------| | |
| | Ann | 90 | 85 | | |
| | Bob | 85 | 85 | | |
| | Cid | 85 | 70 | | |
| | Dee | 70 | NULL | | |
| - title: An offset and a default | |
| description: Look two rows after, and return `0` where there is no such row. | |
| sql: | | |
| SELECT name, score, LEAD(score, 2, 0) OVER (ORDER BY score DESC, name) AS two_away | |
| FROM (VALUES ('Ann', 90), ('Bob', 85), ('Cid', 85), ('Dee', 70)) AS t(name, score) | |
| ORDER BY score DESC, name; | |
| result: | | |
| | name | score | two_away | | |
| |------|-------|----------| | |
| | Ann | 90 | 85 | | |
| | Bob | 85 | 70 | | |
| | Cid | 85 | 0 | | |
| | Dee | 70 | 0 | | |
| - title: Within each partition | |
| description: The rows of one team don't see the rows of another. | |
| sql: | | |
| SELECT team, name, score, LEAD(name) OVER (PARTITION BY team ORDER BY score DESC) AS neighbor | |
| FROM (VALUES ('red', 'Ann', 90), ('red', 'Bob', 85), ('red', 'Cid', 70), ('blue', 'Eve', 95), ('blue', 'Fay', 80)) AS t(team, name, score) | |
| ORDER BY team, score DESC; | |
| result: | | |
| | team | name | score | neighbor | | |
| |------|------|-------|----------| | |
| | blue | Eve | 95 | Fay | | |
| | blue | Fay | 80 | NULL | | |
| | red | Ann | 90 | Bob | | |
| | red | Bob | 85 | Cid | | |
| | red | Cid | 70 | NULL | | |
| - title: A NULL value with a NOT NULL default | |
| description: Row 1 reads the NULL in row 2, but gets `-1`, because `default` is NOT NULL. | |
| sql: | | |
| SELECT id, LEAD(v, 1, 0) OVER (ORDER BY id) AS next_v | |
| FROM (VALUES (1, 10), (2, CAST(NULL AS INT)), (3, 30)) AS t(id, v); | |
| result: | | |
| | id | next_v | | |
| |----|--------| | |
| | 1 | -1 | | |
| | 2 | 30 | | |
| | 3 | 0 | |