You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by al...@apache.org on 2025/04/05 11:07:55 UTC
(datafusion) branch main updated: Docs : Added Sql examples for window Functions : `nth_val` , etc (#15555)
This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 5167a89c67 Docs : Added Sql examples for window Functions : `nth_val` , etc (#15555)
5167a89c67 is described below
commit 5167a89c670c372f8f1b074e77ef871b499933d1
Author: aditya singh rathore <14...@users.noreply.github.com>
AuthorDate: Sat Apr 5 16:37:50 2025 +0530
Docs : Added Sql examples for window Functions : `nth_val` , etc (#15555)
* add example for cum_dist
* add nth_val examples
* updated doc
* updated nth_value
* Update Nth_value
* Update nth_value.rs
* Update window_functions.md
* Update window_functions.md
* Update nth_value.rs
* Updated nth_value.rs
* fmt
* Update via ./dev/update_function_docs.sh
---------
Co-authored-by: Andrew Lamb <an...@nerdnetworks.org>
---
datafusion/functions-window/src/cume_dist.rs | 16 +++++++-
datafusion/functions-window/src/nth_value.rs | 43 +++++++++++++++++---
docs/source/user-guide/sql/window_functions.md | 54 ++++++++++++++++++++++++--
3 files changed, 102 insertions(+), 11 deletions(-)
diff --git a/datafusion/functions-window/src/cume_dist.rs b/datafusion/functions-window/src/cume_dist.rs
index d777f7932b..171ca575a9 100644
--- a/datafusion/functions-window/src/cume_dist.rs
+++ b/datafusion/functions-window/src/cume_dist.rs
@@ -43,8 +43,20 @@ define_udwf_and_expr!(
/// CumeDist calculates the cume_dist in the window function with order by
#[user_doc(
doc_section(label = "Ranking Functions"),
- description = "Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).",
- syntax_example = "cume_dist()"
+ description = "Relative rank of the current row: (number of rows preceding or peer with the current row) / (total rows).",
+ syntax_example = "cume_dist()",
+ sql_example = r#"-- Example usage of the cume_dist window function:
+SELECT salary,
+ cume_dist() OVER (ORDER BY salary) AS cume_dist
+FROM employees;
++--------+-----------+
+| salary | cume_dist |
++--------+-----------+
+| 30000 | 0.33 |
+| 50000 | 0.67 |
+| 70000 | 1.00 |
++--------+-----------+
+"#
)]
#[derive(Debug)]
pub struct CumeDist {
diff --git a/datafusion/functions-window/src/nth_value.rs b/datafusion/functions-window/src/nth_value.rs
index 1c781bd8e5..36e6b83d61 100644
--- a/datafusion/functions-window/src/nth_value.rs
+++ b/datafusion/functions-window/src/nth_value.rs
@@ -160,16 +160,49 @@ fn get_last_value_doc() -> &'static Documentation {
static NTH_VALUE_DOCUMENTATION: LazyLock<Documentation> = LazyLock::new(|| {
Documentation::builder(
DOC_SECTION_ANALYTICAL,
- "Returns value evaluated at the row that is the nth row of the window \
- frame (counting from 1); null if no such row.",
+ "Returns the value evaluated at the nth row of the window frame \
+ (counting from 1). Returns NULL if no such row exists.",
"nth_value(expression, n)",
)
.with_argument(
"expression",
- "The name the column of which nth \
- value to retrieve",
+ "The column from which to retrieve the nth value.",
+ )
+ .with_argument(
+ "n",
+ "Integer. Specifies the row number (starting from 1) in the window frame.",
+ )
+ .with_sql_example(
+ r#"```sql
+-- Sample employees table:
+CREATE TABLE employees (id INT, salary INT);
+INSERT INTO employees (id, salary) VALUES
+(1, 30000),
+(2, 40000),
+(3, 50000),
+(4, 60000),
+(5, 70000);
+
+-- Example usage of nth_value:
+SELECT nth_value(salary, 2) OVER (
+ ORDER BY salary
+ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+) AS nth_value
+FROM employees;
+```
+
+```text
++-----------+
+| nth_value |
++-----------+
+| 40000 |
+| 40000 |
+| 40000 |
+| 40000 |
+| 40000 |
++-----------+
+```"#,
)
- .with_argument("n", "Integer. Specifies the n in nth")
.build()
});
diff --git a/docs/source/user-guide/sql/window_functions.md b/docs/source/user-guide/sql/window_functions.md
index 1c02804f0d..135c4d0e29 100644
--- a/docs/source/user-guide/sql/window_functions.md
+++ b/docs/source/user-guide/sql/window_functions.md
@@ -160,12 +160,26 @@ All [aggregate functions](aggregate_functions.md) can be used as window function
### `cume_dist`
-Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).
+Relative rank of the current row: (number of rows preceding or peer with the current row) / (total rows).
```sql
cume_dist()
```
+#### Example
+
+-- Example usage of the cume_dist window function:
+SELECT salary,
+cume_dist() OVER (ORDER BY salary) AS cume_dist
+FROM employees;
++--------+-----------+
+| salary | cume_dist |
++--------+-----------+
+| 30000 | 0.33 |
+| 50000 | 0.67 |
+| 70000 | 1.00 |
++--------+-----------+
+
### `dense_rank`
Returns the rank of the current row without gaps. This function ranks rows in a dense manner, meaning consecutive ranks are assigned even for identical values.
@@ -272,7 +286,7 @@ lead(expression, offset, default)
### `nth_value`
-Returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.
+Returns the value evaluated at the nth row of the window frame (counting from 1). Returns NULL if no such row exists.
```sql
nth_value(expression, n)
@@ -280,5 +294,37 @@ nth_value(expression, n)
#### Arguments
-- **expression**: The name the column of which nth value to retrieve
-- **n**: Integer. Specifies the n in nth
+- **expression**: The column from which to retrieve the nth value.
+- **n**: Integer. Specifies the row number (starting from 1) in the window frame.
+
+#### Example
+
+```sql
+-- Sample employees table:
+CREATE TABLE employees (id INT, salary INT);
+INSERT INTO employees (id, salary) VALUES
+(1, 30000),
+(2, 40000),
+(3, 50000),
+(4, 60000),
+(5, 70000);
+
+-- Example usage of nth_value:
+SELECT nth_value(salary, 2) OVER (
+ ORDER BY salary
+ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+) AS nth_value
+FROM employees;
+```
+
+```text
++-----------+
+| nth_value |
++-----------+
+| 40000 |
+| 40000 |
+| 40000 |
+| 40000 |
+| 40000 |
++-----------+
+```
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org