Possibly flawed example description for LEAD() and LAG() window functions
The MySQL docs entry for the [`LEAD` window function](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lead) includes the following example description:
> For example, if `N` is `3`, the return value is `default` for the last two rows.
Shouldn't it be the last *three* rows? If the current row is the third to last row in a partition, then it is not possible to look three rows ahead for a leading value, thus resulting in the `default` value being used for this third to last row (as well as the second to last and actual last rows as well ... hence three rows and not two).
The sample [sales](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html) data set provided in the docs can illustrate what I mean.
The query
```sql
SELECT
S.*,
LEAD(S.profit, 3, -1) OVER(PARTITION BY S.country ORDER BY S.profit DESC) AS 'lead'
FROM
sales S;
```
produces the following result set:
```
+------+---------+------------+--------+------+
| year | country | product | profit | lead |
+------+---------+------------+--------+------+
| 2000 | Finland | Computer | 1500 | -1 |
| 2000 | Finland | Phone | 100 | -1 |
| 2001 | Finland | Phone | 10 | -1 |
| 2000 | India | Computer | 1200 | -1 |
| 2000 | India | Calculator | 75 | -1 |
| 2000 | India | Calculator | 75 | -1 |
| 2001 | USA | Computer | 1500 | 150 |
| 2000 | USA | Computer | 1500 | 100 |
| 2001 | USA | Computer | 1200 | 75 |
| 2001 | USA | TV | 150 | 50 |
| 2001 | USA | TV | 100 | -1 |
| 2000 | USA | Calculator | 75 | -1 |
| 2001 | USA | Calculator | 50 | -1 |
+------+---------+------------+--------+------+
```
The `lead` entries for the `Finland` and `India` `country` partitions all have `-1`, the assigned `default` value, when `N` is `3`. *All* partitions have `-1` as the `lead` value for the last `3` rows of each partition.
The same "issue" appears in the documentation of the `LAG()` window function (i.e., if `N` is `3`, then the return value is `default` for the first *three* rows, not two).
Subject
Views
Written By
Posted
Possibly flawed example description for LEAD() and LAG() window functions
552
November 03, 2022 03:48AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.