This is an automated email from the ASF dual-hosted git repository.
luzhijing pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 80ac74bce9 [docs](fix)Update first_last_value doc (#550)
80ac74bce9 is described below
commit 80ac74bce9b54845d24443c80ec203022962d6e1
Author: Uniqueyou <[email protected]>
AuthorDate: Mon Apr 15 14:25:33 2024 +0800
[docs](fix)Update first_last_value doc (#550)
---
.../window-function-first-value.md | 62 ++++++++++++--------
.../window-functions/window-function-last-value.md | 37 +++++++-----
.../window-function-first-value.md | 62 ++++++++++++--------
.../window-functions/window-function-last-value.md | 37 +++++++-----
.../WINDOW-FUNCTION-FIRST-VALUE.md | 66 ++++++++++++++--------
.../window-functions/WINDOW-FUNCTION-LAST-VALUE.md | 41 +++++++++-----
.../window-function-first-value.md | 62 ++++++++++++--------
.../window-functions/window-function-last-value.md | 37 +++++++-----
.../WINDOW-FUNCTION-FIRST-VALUE.md | 66 ++++++++++++++--------
.../window-functions/WINDOW-FUNCTION-LAST-VALUE.md | 41 +++++++++-----
.../window-function-first-value.md | 62 ++++++++++++--------
.../window-functions/window-function-last-value.md | 37 +++++++-----
12 files changed, 376 insertions(+), 234 deletions(-)
diff --git
a/docs/sql-manual/sql-functions/window-functions/window-function-first-value.md
b/docs/sql-manual/sql-functions/window-functions/window-function-first-value.md
index a8e3f88e15..6aa17c26f2 100644
---
a/docs/sql-manual/sql-functions/window-functions/window-function-first-value.md
+++
b/docs/sql-manual/sql-functions/window-functions/window-function-first-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION FIRST_VALUE
### description
-FIRST_VALUE() returns the first value in the window's range.
+FIRST_VALUE() returns the first value in the window's range , ignore_null
determines whether to ignore null values , the ignore_null of default value is
false .
```sql
-FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+FIRST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -26,33 +26,47 @@ FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
We have the following data
```sql
- select name, country, greeting from mail_merge;
+ select id, myday, time_col, state from t;
- | name | country | greeting |
- |---------|---------|--------------|
- | Pete | USA | Hello |
- | John | USA | Hi |
- | Boris | Germany | Guten tag |
- | Michael | Germany | Guten morgen |
- | Bjorn | Sweden | Hej |
- | Mats | Sweden | Tja |
+ | id | myday | time_col | state |
+ |------|-------|-------------|-------|
+ | 3 | 21 | 04-21-13 | 3 |
+ | 7 | 22 | 04-22-10-24 | NULL |
+ | 8 | 22 | 04-22-10-25 | 9 |
+ | 10 | 23 | 04-23-12 | 10 |
+ | 4 | 22 | 04-22-10-21 | NULL |
+ | 9 | 23 | 04-23-11 | NULL |
+ | 1 | 21 | 04-21-11 | NULL |
+ | 5 | 22 | 04-22-10-22 | NULL |
+ | 12 | 24 | 02-24-10-21 | NULL |
+ | 2 | 21 | 04-21-12 | 2 |
+ | 6 | 22 | 04-22-10-23 | 5 |
+ | 11 | 23 | 04-23-13 | NULL |
```
-Use FIRST_VALUE() to group by country and return the value of the first
greeting in each group:
+Use FIRST_VALUE() to group by myday and return the value of the first state in
each group:
```sql
-select country, name,
-first_value(greeting)
-over (partition by country order by name, greeting) as greeting from
mail_merge;
-
-| country | name | greeting |
-|---------|---------|-----------|
-| Germany | Boris | Guten tag |
-| Germany | Michael | Guten tag |
-| Sweden | Bjorn | Hej |
-| Sweden | Mats | Hej |
-| USA | John | Hi |
-| USA | Pete | Hi |
+select * ,
+first_value(`state`, 1) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null,
+first_value(`state`, 0) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as not_ignore_null,
+first_value(`state`) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/docs/sql-manual/sql-functions/window-functions/window-function-last-value.md
b/docs/sql-manual/sql-functions/window-functions/window-function-last-value.md
index f2cf379c78..e1f4289829 100644
---
a/docs/sql-manual/sql-functions/window-functions/window-function-last-value.md
+++
b/docs/sql-manual/sql-functions/window-functions/window-function-last-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION LAST_VALUE
### description
-LAST_VALUE() returns the last value in the window range. Opposite of
FIRST_VALUE() .
+LAST_VALUE() returns the last value in the window range . Opposite of
FIRST_VALUE() . ignore_null determines whether to ignore null values , the
ignore_null of default value is false .
```sql
-LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+LAST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -25,19 +25,26 @@ LAST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
Using the data from the FIRST_VALUE() example:
```sql
-select country, name,
-last_value(greeting)
-over (partition by country order by name, greeting) as greeting
-from mail_merge;
-
-| country | name | greeting |
-|---------|---------|--------------|
-| Germany | Boris | Guten morgen |
-| Germany | Michael | Guten morgen |
-| Sweden | Bjorn | Tja |
-| Sweden | Mats | Tja |
-| USA | John | Hello |
-| USA | Pete | Hello |
+select * ,
+last_value(`state`, 1) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null,
+last_value(`state`, 0) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as not_ignore_null,
+last_value(`state`) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-first-value.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-first-value.md
index f828db3090..dba06df46d 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-first-value.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-first-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION FIRST_VALUE
### description
-FIRST_VALUE() 返回窗口范围内的第一个值。
+FIRST_VALUE() 返回窗口范围内的第一个值， ignore_null 决定是否忽略 null 值，参数 ignore_null 默认值为
false 。
```sql
-FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+FIRST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -26,33 +26,47 @@ FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
我们有如下数据
```sql
- select name, country, greeting from mail_merge;
+ select id, myday, time_col, state from t;
- | name | country | greeting |
- |---------|---------|--------------|
- | Pete | USA | Hello |
- | John | USA | Hi |
- | Boris | Germany | Guten tag |
- | Michael | Germany | Guten morgen |
- | Bjorn | Sweden | Hej |
- | Mats | Sweden | Tja |
+ | id | myday | time_col | state |
+ |------|-------|-------------|-------|
+ | 3 | 21 | 04-21-13 | 3 |
+ | 7 | 22 | 04-22-10-24 | NULL |
+ | 8 | 22 | 04-22-10-25 | 9 |
+ | 10 | 23 | 04-23-12 | 10 |
+ | 4 | 22 | 04-22-10-21 | NULL |
+ | 9 | 23 | 04-23-11 | NULL |
+ | 1 | 21 | 04-21-11 | NULL |
+ | 5 | 22 | 04-22-10-22 | NULL |
+ | 12 | 24 | 02-24-10-21 | NULL |
+ | 2 | 21 | 04-21-12 | 2 |
+ | 6 | 22 | 04-22-10-23 | 5 |
+ | 11 | 23 | 04-23-13 | NULL |
```
-使用 FIRST_VALUE()，根据 country 分组，返回每个分组中第一个 greeting 的值：
+使用 FIRST_VALUE()，根据 myday 分组，返回每个分组中第一个 state 的值：
```sql
-select country, name,
-first_value(greeting)
-over (partition by country order by name, greeting) as greeting from
mail_merge;
-
-| country | name | greeting |
-|---------|---------|-----------|
-| Germany | Boris | Guten tag |
-| Germany | Michael | Guten tag |
-| Sweden | Bjorn | Hej |
-| Sweden | Mats | Hej |
-| USA | John | Hi |
-| USA | Pete | Hi |
+select * ,
+first_value(`state`, 1) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null,
+first_value(`state`, 0) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as not_ignore_null,
+first_value(`state`) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-last-value.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-last-value.md
index 759b3d2817..a1b3c839be 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-last-value.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/window-functions/window-function-last-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION LAST_VALUE
### description
-LAST_VALUE() 返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。
+LAST_VALUE() 返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。ignore_null 决定是否忽略 null 值，参数
ignore_null 默认值为 false 。
```sql
-LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+LAST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -25,19 +25,26 @@ LAST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
使用FIRST_VALUE()举例中的数据：
```sql
-select country, name,
-last_value(greeting)
-over (partition by country order by name, greeting) as greeting
-from mail_merge;
-
-| country | name | greeting |
-|---------|---------|--------------|
-| Germany | Boris | Guten morgen |
-| Germany | Michael | Guten morgen |
-| Sweden | Bjorn | Tja |
-| Sweden | Mats | Tja |
-| USA | John | Hello |
-| USA | Pete | Hello |
+select * ,
+last_value(`state`, 1) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null,
+last_value(`state`, 0) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as not_ignore_null,
+last_value(`state`) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
index f828db3090..a751f63e13 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
@@ -14,10 +14,14 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION FIRST_VALUE
### description
-FIRST_VALUE() 返回窗口范围内的第一个值。
+:::info 备注
+自 2.0.9 开始支持 ignore_null 用法
+:::
+
+FIRST_VALUE() 返回窗口范围内的第一个值， ignore_null 决定是否忽略 null 值，参数 ignore_null 默认值为
false 。
```sql
-FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+FIRST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -26,33 +30,47 @@ FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
我们有如下数据
```sql
- select name, country, greeting from mail_merge;
+ select id, myday, time_col, state from t;
- | name | country | greeting |
- |---------|---------|--------------|
- | Pete | USA | Hello |
- | John | USA | Hi |
- | Boris | Germany | Guten tag |
- | Michael | Germany | Guten morgen |
- | Bjorn | Sweden | Hej |
- | Mats | Sweden | Tja |
+ | id | myday | time_col | state |
+ |------|-------|-------------|-------|
+ | 3 | 21 | 04-21-13 | 3 |
+ | 7 | 22 | 04-22-10-24 | NULL |
+ | 8 | 22 | 04-22-10-25 | 9 |
+ | 10 | 23 | 04-23-12 | 10 |
+ | 4 | 22 | 04-22-10-21 | NULL |
+ | 9 | 23 | 04-23-11 | NULL |
+ | 1 | 21 | 04-21-11 | NULL |
+ | 5 | 22 | 04-22-10-22 | NULL |
+ | 12 | 24 | 02-24-10-21 | NULL |
+ | 2 | 21 | 04-21-12 | 2 |
+ | 6 | 22 | 04-22-10-23 | 5 |
+ | 11 | 23 | 04-23-13 | NULL |
```
-使用 FIRST_VALUE()，根据 country 分组，返回每个分组中第一个 greeting 的值：
+使用 FIRST_VALUE()，根据 myday 分组，返回每个分组中第一个 state 的值：
```sql
-select country, name,
-first_value(greeting)
-over (partition by country order by name, greeting) as greeting from
mail_merge;
-
-| country | name | greeting |
-|---------|---------|-----------|
-| Germany | Boris | Guten tag |
-| Germany | Michael | Guten tag |
-| Sweden | Bjorn | Hej |
-| Sweden | Mats | Hej |
-| USA | John | Hi |
-| USA | Pete | Hi |
+select * ,
+first_value(`state`, 1) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null,
+first_value(`state`, 0) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as not_ignore_null,
+first_value(`state`) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
index 759b3d2817..a6afb4a5d0 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
@@ -14,10 +14,14 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION LAST_VALUE
### description
-LAST_VALUE() 返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。
+:::info 备注
+自 2.0.9 开始支持 ignore_null 用法
+:::
+
+LAST_VALUE() 返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。ignore_null 决定是否忽略 null 值，参数
ignore_null 默认值为 false 。
```sql
-LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+LAST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -25,19 +29,26 @@ LAST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
使用FIRST_VALUE()举例中的数据：
```sql
-select country, name,
-last_value(greeting)
-over (partition by country order by name, greeting) as greeting
-from mail_merge;
-
-| country | name | greeting |
-|---------|---------|--------------|
-| Germany | Boris | Guten morgen |
-| Germany | Michael | Guten morgen |
-| Sweden | Bjorn | Tja |
-| Sweden | Mats | Tja |
-| USA | John | Hello |
-| USA | Pete | Hello |
+select * ,
+last_value(`state`, 1) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null,
+last_value(`state`, 0) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as not_ignore_null,
+last_value(`state`) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
index f828db3090..dba06df46d 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION FIRST_VALUE
### description
-FIRST_VALUE() 返回窗口范围内的第一个值。
+FIRST_VALUE() 返回窗口范围内的第一个值， ignore_null 决定是否忽略 null 值，参数 ignore_null 默认值为
false 。
```sql
-FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+FIRST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -26,33 +26,47 @@ FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
我们有如下数据
```sql
- select name, country, greeting from mail_merge;
+ select id, myday, time_col, state from t;
- | name | country | greeting |
- |---------|---------|--------------|
- | Pete | USA | Hello |
- | John | USA | Hi |
- | Boris | Germany | Guten tag |
- | Michael | Germany | Guten morgen |
- | Bjorn | Sweden | Hej |
- | Mats | Sweden | Tja |
+ | id | myday | time_col | state |
+ |------|-------|-------------|-------|
+ | 3 | 21 | 04-21-13 | 3 |
+ | 7 | 22 | 04-22-10-24 | NULL |
+ | 8 | 22 | 04-22-10-25 | 9 |
+ | 10 | 23 | 04-23-12 | 10 |
+ | 4 | 22 | 04-22-10-21 | NULL |
+ | 9 | 23 | 04-23-11 | NULL |
+ | 1 | 21 | 04-21-11 | NULL |
+ | 5 | 22 | 04-22-10-22 | NULL |
+ | 12 | 24 | 02-24-10-21 | NULL |
+ | 2 | 21 | 04-21-12 | 2 |
+ | 6 | 22 | 04-22-10-23 | 5 |
+ | 11 | 23 | 04-23-13 | NULL |
```
-使用 FIRST_VALUE()，根据 country 分组，返回每个分组中第一个 greeting 的值：
+使用 FIRST_VALUE()，根据 myday 分组，返回每个分组中第一个 state 的值：
```sql
-select country, name,
-first_value(greeting)
-over (partition by country order by name, greeting) as greeting from
mail_merge;
-
-| country | name | greeting |
-|---------|---------|-----------|
-| Germany | Boris | Guten tag |
-| Germany | Michael | Guten tag |
-| Sweden | Bjorn | Hej |
-| Sweden | Mats | Hej |
-| USA | John | Hi |
-| USA | Pete | Hi |
+select * ,
+first_value(`state`, 1) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null,
+first_value(`state`, 0) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as not_ignore_null,
+first_value(`state`) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
index 759b3d2817..a1b3c839be 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION LAST_VALUE
### description
-LAST_VALUE() 返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。
+LAST_VALUE() 返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。ignore_null 决定是否忽略 null 值，参数
ignore_null 默认值为 false 。
```sql
-LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+LAST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -25,19 +25,26 @@ LAST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
使用FIRST_VALUE()举例中的数据：
```sql
-select country, name,
-last_value(greeting)
-over (partition by country order by name, greeting) as greeting
-from mail_merge;
-
-| country | name | greeting |
-|---------|---------|--------------|
-| Germany | Boris | Guten morgen |
-| Germany | Michael | Guten morgen |
-| Sweden | Bjorn | Tja |
-| Sweden | Mats | Tja |
-| USA | John | Hello |
-| USA | Pete | Hello |
+select * ,
+last_value(`state`, 1) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null,
+last_value(`state`, 0) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as not_ignore_null,
+last_value(`state`) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
b/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
index a8e3f88e15..72d22dba1d 100644
---
a/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
+++
b/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-FIRST-VALUE.md
@@ -14,10 +14,14 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION FIRST_VALUE
### description
-FIRST_VALUE() returns the first value in the window's range.
+:::info Note
+Since 2.0.9 , the ignore_null usage is supported
+:::
+
+FIRST_VALUE() returns the first value in the window's range , ignore_null
determines whether to ignore null values , the ignore_null of default value is
false .
```sql
-FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+FIRST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -26,33 +30,47 @@ FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
We have the following data
```sql
- select name, country, greeting from mail_merge;
+ select id, myday, time_col, state from t;
- | name | country | greeting |
- |---------|---------|--------------|
- | Pete | USA | Hello |
- | John | USA | Hi |
- | Boris | Germany | Guten tag |
- | Michael | Germany | Guten morgen |
- | Bjorn | Sweden | Hej |
- | Mats | Sweden | Tja |
+ | id | myday | time_col | state |
+ |------|-------|-------------|-------|
+ | 3 | 21 | 04-21-13 | 3 |
+ | 7 | 22 | 04-22-10-24 | NULL |
+ | 8 | 22 | 04-22-10-25 | 9 |
+ | 10 | 23 | 04-23-12 | 10 |
+ | 4 | 22 | 04-22-10-21 | NULL |
+ | 9 | 23 | 04-23-11 | NULL |
+ | 1 | 21 | 04-21-11 | NULL |
+ | 5 | 22 | 04-22-10-22 | NULL |
+ | 12 | 24 | 02-24-10-21 | NULL |
+ | 2 | 21 | 04-21-12 | 2 |
+ | 6 | 22 | 04-22-10-23 | 5 |
+ | 11 | 23 | 04-23-13 | NULL |
```
-Use FIRST_VALUE() to group by country and return the value of the first
greeting in each group:
+Use FIRST_VALUE() to group by myday and return the value of the first state in
each group:
```sql
-select country, name,
-first_value(greeting)
-over (partition by country order by name, greeting) as greeting from
mail_merge;
-
-| country | name | greeting |
-|---------|---------|-----------|
-| Germany | Boris | Guten tag |
-| Germany | Michael | Guten tag |
-| Sweden | Bjorn | Hej |
-| Sweden | Mats | Hej |
-| USA | John | Hi |
-| USA | Pete | Hi |
+select * ,
+first_value(`state`, 1) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null,
+first_value(`state`, 0) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as not_ignore_null,
+first_value(`state`) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
b/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
index f2cf379c78..eddaf705a1 100644
---
a/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
+++
b/versioned_docs/version-2.0/sql-manual/sql-functions/window-functions/WINDOW-FUNCTION-LAST-VALUE.md
@@ -14,10 +14,14 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION LAST_VALUE
### description
-LAST_VALUE() returns the last value in the window range. Opposite of
FIRST_VALUE() .
+:::info Note
+Since 2.0.9 , the ignore_null usage is supported
+:::
+
+LAST_VALUE() returns the last value in the window range . Opposite of
FIRST_VALUE() . ignore_null determines whether to ignore null values , the
ignore_null of default value is false .
```sql
-LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+LAST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -25,19 +29,26 @@ LAST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
Using the data from the FIRST_VALUE() example:
```sql
-select country, name,
-last_value(greeting)
-over (partition by country order by name, greeting) as greeting
-from mail_merge;
-
-| country | name | greeting |
-|---------|---------|--------------|
-| Germany | Boris | Guten morgen |
-| Germany | Michael | Guten morgen |
-| Sweden | Bjorn | Tja |
-| Sweden | Mats | Tja |
-| USA | John | Hello |
-| USA | Pete | Hello |
+select * ,
+last_value(`state`, 1) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null,
+last_value(`state`, 0) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as not_ignore_null,
+last_value(`state`) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
b/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
index a8e3f88e15..6aa17c26f2 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-first-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION FIRST_VALUE
### description
-FIRST_VALUE() returns the first value in the window's range.
+FIRST_VALUE() returns the first value in the window's range , ignore_null
determines whether to ignore null values , the ignore_null of default value is
false .
```sql
-FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+FIRST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -26,33 +26,47 @@ FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
We have the following data
```sql
- select name, country, greeting from mail_merge;
+ select id, myday, time_col, state from t;
- | name | country | greeting |
- |---------|---------|--------------|
- | Pete | USA | Hello |
- | John | USA | Hi |
- | Boris | Germany | Guten tag |
- | Michael | Germany | Guten morgen |
- | Bjorn | Sweden | Hej |
- | Mats | Sweden | Tja |
+ | id | myday | time_col | state |
+ |------|-------|-------------|-------|
+ | 3 | 21 | 04-21-13 | 3 |
+ | 7 | 22 | 04-22-10-24 | NULL |
+ | 8 | 22 | 04-22-10-25 | 9 |
+ | 10 | 23 | 04-23-12 | 10 |
+ | 4 | 22 | 04-22-10-21 | NULL |
+ | 9 | 23 | 04-23-11 | NULL |
+ | 1 | 21 | 04-21-11 | NULL |
+ | 5 | 22 | 04-22-10-22 | NULL |
+ | 12 | 24 | 02-24-10-21 | NULL |
+ | 2 | 21 | 04-21-12 | 2 |
+ | 6 | 22 | 04-22-10-23 | 5 |
+ | 11 | 23 | 04-23-13 | NULL |
```
-Use FIRST_VALUE() to group by country and return the value of the first
greeting in each group:
+Use FIRST_VALUE() to group by myday and return the value of the first state in
each group:
```sql
-select country, name,
-first_value(greeting)
-over (partition by country order by name, greeting) as greeting from
mail_merge;
-
-| country | name | greeting |
-|---------|---------|-----------|
-| Germany | Boris | Guten tag |
-| Germany | Michael | Guten tag |
-| Sweden | Bjorn | Hej |
-| Sweden | Mats | Hej |
-| USA | John | Hi |
-| USA | Pete | Hi |
+select * ,
+first_value(`state`, 1) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null,
+first_value(`state`, 0) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as not_ignore_null,
+first_value(`state`) over(partition by `myday` order by `time_col` rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
b/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
index f2cf379c78..8b352ce1d1 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-functions/window-functions/window-function-last-value.md
@@ -14,10 +14,10 @@ Unless required by applicable law or agreed to in writing,
software distributed
## WINDOW FUNCTION LAST_VALUE
### description
-LAST_VALUE() returns the last value in the window range. Opposite of
FIRST_VALUE() .
+LAST_VALUE() returns the last value in the window range . Opposite of
FIRST_VALUE() , ignore_null determines whether to ignore null values , the
ignore_null of default value is false .
```sql
-LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])
+LAST_VALUE(expr[, ignore_null]) OVER(partition_by_clause order_by_clause
[window_clause])
```
### example
@@ -25,19 +25,26 @@ LAST_VALUE(expr) OVER(partition_by_clause order_by_clause
[window_clause])
Using the data from the FIRST_VALUE() example:
```sql
-select country, name,
-last_value(greeting)
-over (partition by country order by name, greeting) as greeting
-from mail_merge;
-
-| country | name | greeting |
-|---------|---------|--------------|
-| Germany | Boris | Guten morgen |
-| Germany | Michael | Guten morgen |
-| Sweden | Bjorn | Tja |
-| Sweden | Mats | Tja |
-| USA | John | Hello |
-| USA | Pete | Hello |
+select * ,
+last_value(`state`, 1) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null,
+last_value(`state`, 0) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as not_ignore_null,
+last_value(`state`) over(partition by `myday` order by `time_col` DESC rows
between 1 preceding and 1 following) as ignore_null_default
+from t order by `id`, `myday`, `time_col`;
+
+| id | myday | time_col | state | ignore_null | not_ignore_null |
ignore_null_default |
+|------|-------|-------------|-------|-------------|-----------------|---------------------|
+| 1 | 21 | 04-21-11 | NULL | 2 | NULL |
NULL |
+| 2 | 21 | 04-21-12 | 2 | 2 | NULL |
NULL |
+| 3 | 21 | 04-21-13 | 3 | 2 | 2 |
2 |
+| 4 | 22 | 04-22-10-21 | NULL | NULL | NULL |
NULL |
+| 5 | 22 | 04-22-10-22 | NULL | 5 | NULL |
NULL |
+| 6 | 22 | 04-22-10-23 | 5 | 5 | NULL |
NULL |
+| 7 | 22 | 04-22-10-24 | NULL | 5 | 5 |
5 |
+| 8 | 22 | 04-22-10-25 | 9 | 9 | NULL |
NULL |
+| 9 | 23 | 04-23-11 | NULL | 10 | NULL |
NULL |
+| 10 | 23 | 04-23-12 | 10 | 10 | NULL |
NULL |
+| 11 | 23 | 04-23-13 | NULL | 10 | 10 |
10 |
+| 12 | 24 | 02-24-10-21 | NULL | NULL | NULL |
NULL |
```
### keywords
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
