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 2d0258ed91 [doc] update floor/ceil/round/round_bankers function (#620)
2d0258ed91 is described below
commit 2d0258ed91f8c9bc3f7ed178ac9ae2d59c4f11e5
Author: Chester <[email protected]>
AuthorDate: Mon May 13 19:10:38 2024 +0800
[doc] update floor/ceil/round/round_bankers function (#620)
---
.../sql-functions/numeric-functions/ceil.md | 30 ++++++++++++++++++++--
.../sql-functions/numeric-functions/floor.md | 30 ++++++++++++++++++++--
.../numeric-functions/round-bankers.md | 14 ++++++++++
.../sql-functions/numeric-functions/round.md | 14 ++++++++++
.../sql-functions/numeric-functions/ceil.md | 28 +++++++++++++++++++-
.../sql-functions/numeric-functions/floor.md | 28 +++++++++++++++++++-
.../numeric-functions/round-bankers.md | 14 ++++++++++
.../sql-functions/numeric-functions/round.md | 14 ++++++++++
.../sql-functions/numeric-functions/ceil.md | 28 +++++++++++++++++++-
.../sql-functions/numeric-functions/floor.md | 28 +++++++++++++++++++-
.../numeric-functions/round-bankers.md | 14 ++++++++++
.../sql-functions/numeric-functions/round.md | 14 ++++++++++
.../sql-functions/numeric-functions/ceil.md | 30 ++++++++++++++++++++--
.../sql-functions/numeric-functions/floor.md | 30 ++++++++++++++++++++--
.../numeric-functions/round-bankers.md | 14 ++++++++++
.../sql-functions/numeric-functions/round.md | 14 ++++++++++
16 files changed, 332 insertions(+), 12 deletions(-)
diff --git a/docs/sql-manual/sql-functions/numeric-functions/ceil.md
b/docs/sql-manual/sql-functions/numeric-functions/ceil.md
index a222dcde8b..c91a10e91d 100644
--- a/docs/sql-manual/sql-functions/numeric-functions/ceil.md
+++ b/docs/sql-manual/sql-functions/numeric-functions/ceil.md
@@ -27,8 +27,16 @@ under the License.
### description
#### Syntax
-`BIGINT ceil(DOUBLE x)`
-Returns the smallest integer value greater than or equal to `x`.
+`T ceil(T x[, d])`
+
+If not specified `d`: returns the smallest integer value less than or equal to
`x`, which is **the most common usage**.
+Otherwise, returns the smallest round number that is less than or equal to `x`
and flowing the rules:
+
+If `d` is specified as literal:
+`d` = 0: just like without `d`
+`d` > 0 or `d` < 0: the round number would be a multiple of `1/(10^d)`, or the
nearest number of the appropriate data type if `1/(10^d)` isn't exact.
+
+Else if `d` is a column, and `x` has Decimal type, scale of result Decimal
will always be same with input Decimal.
:::tip
The other alias for this function are `dceil` and `ceiling`.
@@ -55,6 +63,24 @@ mysql> select ceil(-10.3);
+-------------+
| -10 |
+-------------+
+mysql> select ceil(123.45, 1), ceil(123.45), ceil(123.45, 0), ceil(123.45, -1);
++-----------------+--------------+-----------------+------------------+
+| ceil(123.45, 1) | ceil(123.45) | ceil(123.45, 0) | ceil(123.45, -1) |
++-----------------+--------------+-----------------+------------------+
+| 123.5 | 124 | 124 | 130 |
++-----------------+--------------+-----------------+------------------+
+mysql> SELECT number
+ -> , ceil(number * 2.5, number - 1) AS c_decimal_column
+ -> , ceil(number * 2.5, 0) AS c_decimal_literal
+ -> , ceil(cast(number * 2.5 AS DOUBLE), number - 1) AS c_double_column
+ -> , ceil(cast(number * 2.5 AS DOUBLE), 0) AS c_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | c_decimal_column | c_decimal_literal | c_double_column |
c_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git a/docs/sql-manual/sql-functions/numeric-functions/floor.md
b/docs/sql-manual/sql-functions/numeric-functions/floor.md
index 824f142934..dad824eaaa 100644
--- a/docs/sql-manual/sql-functions/numeric-functions/floor.md
+++ b/docs/sql-manual/sql-functions/numeric-functions/floor.md
@@ -27,8 +27,16 @@ under the License.
### description
#### Syntax
-`BIGINT floor(DOUBLE x)`
-Returns the largest integer value less than or equal to `x`.
+`T floor(T x[, d])`
+
+If not specified `d`: returns the largest integer value less than or equal to
`x`, which is **the most common usage**.
+Otherwise, returns the largest round number that is less than or equal to `x`
and flowing the rules:
+
+If `d` is specified as literal:
+`d` = 0: just like without `d`
+`d` > 0 or `d` < 0: the round number would be a multiple of `1/(10^d)`, or the
nearest number of the appropriate data type if `1/(10^d)` isn't exact.
+
+Else if `d` is a column, and `x` has Decimal type, scale of result Decimal
will always be same with input Decimal.
:::tip
Another alias for this function is `dfloor`.
@@ -55,6 +63,24 @@ mysql> select floor(-10.3);
+--------------+
| -11 |
+--------------+
+mysql> select floor(123.45, 1), floor(123.45), floor(123.45, 0), floor(123.45,
-1);
++------------------+---------------+------------------+-------------------+
+| floor(123.45, 1) | floor(123.45) | floor(123.45, 0) | floor(123.45, -1) |
++------------------+---------------+------------------+-------------------+
+| 123.4 | 123 | 123 | 120 |
++------------------+---------------+------------------+-------------------+
+mysql> SELECT number
+ -> , floor(number * 2.5, number - 1) AS f_decimal_column
+ -> , floor(number * 2.5, 0) AS f_decimal_literal
+ -> , floor(cast(number * 2.5 AS DOUBLE), number - 1) AS f_double_column
+ -> , floor(cast(number * 2.5 AS DOUBLE), 0) AS f_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | f_decimal_column | f_decimal_literal | f_double_column |
f_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git a/docs/sql-manual/sql-functions/numeric-functions/round-bankers.md
b/docs/sql-manual/sql-functions/numeric-functions/round-bankers.md
index 3115cbf9be..454cf67bb0 100644
--- a/docs/sql-manual/sql-functions/numeric-functions/round-bankers.md
+++ b/docs/sql-manual/sql-functions/numeric-functions/round-bankers.md
@@ -30,6 +30,8 @@ under the License.
`T round_bankers(T x[, d])`
Rounds the argument `x` to `d` specified decimal places. `d` defaults to 0 if
not specified. If d is negative, the left d digits of the decimal point are 0.
If x or d is null, null is returned.
+If `d` is a column, and `x` has Decimal type, scale of result Decimal will
always be same with input Decimal.
+
+ If the rounding number is halfway between two numbers, the function uses
banker’s rounding.
+ In other cases, the function rounds numbers to the nearest integer.
@@ -74,6 +76,18 @@ mysql> select round_bankers(1667.2725, -2);
+------------------------------+
| 1700 |
+------------------------------+
+mysql> SELECT number
+ -> , round_bankers(number * 2.5, number - 1) AS rb_decimal_column
+ -> , round_bankers(number * 2.5, 0) AS rb_decimal_literal
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), number - 1) AS
rb_double_column
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), 0) AS rb_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+-------------------+--------------------+------------------+-------------------+
+| number | rb_decimal_column | rb_decimal_literal | rb_double_column |
rb_double_literal |
++--------+-------------------+--------------------+------------------+-------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+-------------------+--------------------+------------------+-------------------+
```
### keywords
diff --git a/docs/sql-manual/sql-functions/numeric-functions/round.md
b/docs/sql-manual/sql-functions/numeric-functions/round.md
index edfb50a22f..06a19dae9e 100644
--- a/docs/sql-manual/sql-functions/numeric-functions/round.md
+++ b/docs/sql-manual/sql-functions/numeric-functions/round.md
@@ -31,6 +31,8 @@ under the License.
Rounds the argument `x` to `d` decimal places. `d` defaults to 0 if not
specified. If d is negative, the left d digits of the decimal point are 0. If x
or d is null, null is returned.
2.5 will round up to 3. If you want to round down to 2, please use the
round_bankers function.
+If `d` is a column, and `x` has Decimal type, scale of result Decimal will
always be same with input Decimal.
+
:::tip
Another alias for this function is `dround`.
:::
@@ -74,6 +76,18 @@ mysql> select round(1667.2725, -2);
+----------------------+
| 1700 |
+----------------------+
+mysql> SELECT number
+ -> , round(number * 2.5, number - 1) AS r_decimal_column
+ -> , round(number * 2.5, 0) AS r_decimal_literal
+ -> , round(cast(number * 2.5 AS DOUBLE), number - 1) AS r_double_column
+ -> , round(cast(number * 2.5 AS DOUBLE), 0) AS r_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | r_decimal_column | r_decimal_literal | r_double_column |
r_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/ceil.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/ceil.md
index 861f207b76..e831d8bfe1 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/ceil.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/ceil.md
@@ -28,7 +28,15 @@ under the License.
#### Syntax
`BIGINT ceil(DOUBLE x)`
-返回大于或等于`x`的最小整数值.
+
+如果不指定`d`: 返回大于或等于`x`的最大整数值, 这也是**最常见的用法**.
+否则, 按照下面规则返回最小的大于或者等于`x`的舍入数字:
+
+如 `d` 是字面量（不是列）:
+`d` = 0: 等同于没有 `d`
+`d` > 0 or `d` < 0: 舍入数是 `1/(10^d)` 的倍数，如果 `1/(10^d)` 不精确，则为相应数据类型的最接近的数字。
+
+如果 `d` 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
:::tip
该函数的其他别名为 `dceil` 和 `ceiling`。
@@ -55,6 +63,24 @@ mysql> select ceil(-10.3);
+-------------+
| -10 |
+-------------+
+mysql> select ceil(123.45, 1), ceil(123.45), ceil(123.45, 0), ceil(123.45, -1);
++-----------------+--------------+-----------------+------------------+
+| ceil(123.45, 1) | ceil(123.45) | ceil(123.45, 0) | ceil(123.45, -1) |
++-----------------+--------------+-----------------+------------------+
+| 123.5 | 124 | 124 | 130 |
++-----------------+--------------+-----------------+------------------+
+mysql> SELECT number
+ -> , ceil(number * 2.5, number - 1) AS c_decimal_column
+ -> , ceil(number * 2.5, 0) AS c_decimal_literal
+ -> , ceil(cast(number * 2.5 AS DOUBLE), number - 1) AS c_double_column
+ -> , ceil(cast(number * 2.5 AS DOUBLE), 0) AS c_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | c_decimal_column | c_decimal_literal | c_double_column |
c_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/floor.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/floor.md
index 693f55c709..f829770b14 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/floor.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/floor.md
@@ -28,7 +28,15 @@ under the License.
#### Syntax
`BIGINT floor(DOUBLE x)`
-返回小于或等于`x`的最大整数值.
+
+如果不指定`d`: 返回小于或等于`x`的最大整数值, 这也是**最常见的用法**.
+否则, 按照下面规则返回最大的小于或者等于`x`的舍入数字:
+
+如 `d` 是字面量（不是列）:
+`d` = 0: 等同于没有 `d`
+`d` > 0 or `d` < 0: 舍入数是 `1/(10^d)` 的倍数，如果 `1/(10^d)` 不精确，则为相应数据类型的最接近的数字。
+
+如果 `d` 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
:::tip
该函数的另一个别名为 `dfloor`。
@@ -55,6 +63,24 @@ mysql> select floor(-10.3);
+--------------+
| -11 |
+--------------+
+mysql> select floor(123.45, 1), floor(123.45), floor(123.45, 0), floor(123.45,
-1);
++------------------+---------------+------------------+-------------------+
+| floor(123.45, 1) | floor(123.45) | floor(123.45, 0) | floor(123.45, -1) |
++------------------+---------------+------------------+-------------------+
+| 123.4 | 123 | 123 | 120 |
++------------------+---------------+------------------+-------------------+
+mysql> SELECT number
+ -> , floor(number * 2.5, number - 1) AS f_decimal_column
+ -> , floor(number * 2.5, 0) AS f_decimal_literal
+ -> , floor(cast(number * 2.5 AS DOUBLE), number - 1) AS f_double_column
+ -> , floor(cast(number * 2.5 AS DOUBLE), 0) AS f_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | f_decimal_column | f_decimal_literal | f_double_column |
f_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round-bankers.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round-bankers.md
index 2b2d05b165..e92b94c353 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round-bankers.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round-bankers.md
@@ -30,6 +30,8 @@ under the License.
`T round_bankers(T x[, d])`
将`x`使用银行家舍入法后，保留d位小数，`d`默认为0。如果`d`为负数，则小数点左边`d`位为0。如果`x`或`d`为null，返回null。
+如果 d 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
+
+ 如果舍入数介于两个数字之间，则该函数使用银行家的舍入
+ 在其他情况下，该函数将数字四舍五入到最接近的整数。
@@ -73,6 +75,18 @@ mysql> select round_bankers(1667.2725, -2);
+------------------------------+
| 1700 |
+------------------------------+
+mysql> SELECT number
+ -> , round_bankers(number * 2.5, number - 1) AS rb_decimal_column
+ -> , round_bankers(number * 2.5, 0) AS rb_decimal_literal
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), number - 1) AS
rb_double_column
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), 0) AS rb_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+-------------------+--------------------+------------------+-------------------+
+| number | rb_decimal_column | rb_decimal_literal | rb_double_column |
rb_double_literal |
++--------+-------------------+--------------------+------------------+-------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+-------------------+--------------------+------------------+-------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round.md
index 8fc2e167c3..1ca8be2300 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/numeric-functions/round.md
@@ -31,6 +31,8 @@ under the License.
将`x`四舍五入后保留d位小数，d默认为0。如果d为负数，则小数点左边d位为0。如果x或d为null，返回null。
2.5会舍入到3，如果想要舍入到2的算法，请使用round_bankers函数。
+如果 d 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
+
:::tip
该函数的另一个别名为 `dround`。
:::
@@ -74,6 +76,18 @@ mysql> select round(1667.2725, -2);
+----------------------+
| 1700 |
+----------------------+
+mysql> SELECT number
+ -> , round(number * 2.5, number - 1) AS r_decimal_column
+ -> , round(number * 2.5, 0) AS r_decimal_literal
+ -> , round(cast(number * 2.5 AS DOUBLE), number - 1) AS r_double_column
+ -> , round(cast(number * 2.5 AS DOUBLE), 0) AS r_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | r_decimal_column | r_decimal_literal | r_double_column |
r_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
index 861f207b76..e831d8bfe1 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
@@ -28,7 +28,15 @@ under the License.
#### Syntax
`BIGINT ceil(DOUBLE x)`
-返回大于或等于`x`的最小整数值.
+
+如果不指定`d`: 返回大于或等于`x`的最大整数值, 这也是**最常见的用法**.
+否则, 按照下面规则返回最小的大于或者等于`x`的舍入数字:
+
+如 `d` 是字面量（不是列）:
+`d` = 0: 等同于没有 `d`
+`d` > 0 or `d` < 0: 舍入数是 `1/(10^d)` 的倍数，如果 `1/(10^d)` 不精确，则为相应数据类型的最接近的数字。
+
+如果 `d` 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
:::tip
该函数的其他别名为 `dceil` 和 `ceiling`。
@@ -55,6 +63,24 @@ mysql> select ceil(-10.3);
+-------------+
| -10 |
+-------------+
+mysql> select ceil(123.45, 1), ceil(123.45), ceil(123.45, 0), ceil(123.45, -1);
++-----------------+--------------+-----------------+------------------+
+| ceil(123.45, 1) | ceil(123.45) | ceil(123.45, 0) | ceil(123.45, -1) |
++-----------------+--------------+-----------------+------------------+
+| 123.5 | 124 | 124 | 130 |
++-----------------+--------------+-----------------+------------------+
+mysql> SELECT number
+ -> , ceil(number * 2.5, number - 1) AS c_decimal_column
+ -> , ceil(number * 2.5, 0) AS c_decimal_literal
+ -> , ceil(cast(number * 2.5 AS DOUBLE), number - 1) AS c_double_column
+ -> , ceil(cast(number * 2.5 AS DOUBLE), 0) AS c_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | c_decimal_column | c_decimal_literal | c_double_column |
c_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
index 693f55c709..f829770b14 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
@@ -28,7 +28,15 @@ under the License.
#### Syntax
`BIGINT floor(DOUBLE x)`
-返回小于或等于`x`的最大整数值.
+
+如果不指定`d`: 返回小于或等于`x`的最大整数值, 这也是**最常见的用法**.
+否则, 按照下面规则返回最大的小于或者等于`x`的舍入数字:
+
+如 `d` 是字面量（不是列）:
+`d` = 0: 等同于没有 `d`
+`d` > 0 or `d` < 0: 舍入数是 `1/(10^d)` 的倍数，如果 `1/(10^d)` 不精确，则为相应数据类型的最接近的数字。
+
+如果 `d` 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
:::tip
该函数的另一个别名为 `dfloor`。
@@ -55,6 +63,24 @@ mysql> select floor(-10.3);
+--------------+
| -11 |
+--------------+
+mysql> select floor(123.45, 1), floor(123.45), floor(123.45, 0), floor(123.45,
-1);
++------------------+---------------+------------------+-------------------+
+| floor(123.45, 1) | floor(123.45) | floor(123.45, 0) | floor(123.45, -1) |
++------------------+---------------+------------------+-------------------+
+| 123.4 | 123 | 123 | 120 |
++------------------+---------------+------------------+-------------------+
+mysql> SELECT number
+ -> , floor(number * 2.5, number - 1) AS f_decimal_column
+ -> , floor(number * 2.5, 0) AS f_decimal_literal
+ -> , floor(cast(number * 2.5 AS DOUBLE), number - 1) AS f_double_column
+ -> , floor(cast(number * 2.5 AS DOUBLE), 0) AS f_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | f_decimal_column | f_decimal_literal | f_double_column |
f_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
index 2b2d05b165..e92b94c353 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
@@ -30,6 +30,8 @@ under the License.
`T round_bankers(T x[, d])`
将`x`使用银行家舍入法后，保留d位小数，`d`默认为0。如果`d`为负数，则小数点左边`d`位为0。如果`x`或`d`为null，返回null。
+如果 d 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
+
+ 如果舍入数介于两个数字之间，则该函数使用银行家的舍入
+ 在其他情况下，该函数将数字四舍五入到最接近的整数。
@@ -73,6 +75,18 @@ mysql> select round_bankers(1667.2725, -2);
+------------------------------+
| 1700 |
+------------------------------+
+mysql> SELECT number
+ -> , round_bankers(number * 2.5, number - 1) AS rb_decimal_column
+ -> , round_bankers(number * 2.5, 0) AS rb_decimal_literal
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), number - 1) AS
rb_double_column
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), 0) AS rb_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+-------------------+--------------------+------------------+-------------------+
+| number | rb_decimal_column | rb_decimal_literal | rb_double_column |
rb_double_literal |
++--------+-------------------+--------------------+------------------+-------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+-------------------+--------------------+------------------+-------------------+
```
### keywords
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
index 8fc2e167c3..1ca8be2300 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
@@ -31,6 +31,8 @@ under the License.
将`x`四舍五入后保留d位小数，d默认为0。如果d为负数，则小数点左边d位为0。如果x或d为null，返回null。
2.5会舍入到3，如果想要舍入到2的算法，请使用round_bankers函数。
+如果 d 为一个列，并且第一个参数为 Decimal 类型，那么结果 Decimal 会跟入参 Decimal 具有相同的小数部分长度。
+
:::tip
该函数的另一个别名为 `dround`。
:::
@@ -74,6 +76,18 @@ mysql> select round(1667.2725, -2);
+----------------------+
| 1700 |
+----------------------+
+mysql> SELECT number
+ -> , round(number * 2.5, number - 1) AS r_decimal_column
+ -> , round(number * 2.5, 0) AS r_decimal_literal
+ -> , round(cast(number * 2.5 AS DOUBLE), number - 1) AS r_double_column
+ -> , round(cast(number * 2.5 AS DOUBLE), 0) AS r_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | r_decimal_column | r_decimal_literal | r_double_column |
r_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
index a222dcde8b..c91a10e91d 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/ceil.md
@@ -27,8 +27,16 @@ under the License.
### description
#### Syntax
-`BIGINT ceil(DOUBLE x)`
-Returns the smallest integer value greater than or equal to `x`.
+`T ceil(T x[, d])`
+
+If not specified `d`: returns the smallest integer value less than or equal to
`x`, which is **the most common usage**.
+Otherwise, returns the smallest round number that is less than or equal to `x`
and flowing the rules:
+
+If `d` is specified as literal:
+`d` = 0: just like without `d`
+`d` > 0 or `d` < 0: the round number would be a multiple of `1/(10^d)`, or the
nearest number of the appropriate data type if `1/(10^d)` isn't exact.
+
+Else if `d` is a column, and `x` has Decimal type, scale of result Decimal
will always be same with input Decimal.
:::tip
The other alias for this function are `dceil` and `ceiling`.
@@ -55,6 +63,24 @@ mysql> select ceil(-10.3);
+-------------+
| -10 |
+-------------+
+mysql> select ceil(123.45, 1), ceil(123.45), ceil(123.45, 0), ceil(123.45, -1);
++-----------------+--------------+-----------------+------------------+
+| ceil(123.45, 1) | ceil(123.45) | ceil(123.45, 0) | ceil(123.45, -1) |
++-----------------+--------------+-----------------+------------------+
+| 123.5 | 124 | 124 | 130 |
++-----------------+--------------+-----------------+------------------+
+mysql> SELECT number
+ -> , ceil(number * 2.5, number - 1) AS c_decimal_column
+ -> , ceil(number * 2.5, 0) AS c_decimal_literal
+ -> , ceil(cast(number * 2.5 AS DOUBLE), number - 1) AS c_double_column
+ -> , ceil(cast(number * 2.5 AS DOUBLE), 0) AS c_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | c_decimal_column | c_decimal_literal | c_double_column |
c_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
index 824f142934..dad824eaaa 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/floor.md
@@ -27,8 +27,16 @@ under the License.
### description
#### Syntax
-`BIGINT floor(DOUBLE x)`
-Returns the largest integer value less than or equal to `x`.
+`T floor(T x[, d])`
+
+If not specified `d`: returns the largest integer value less than or equal to
`x`, which is **the most common usage**.
+Otherwise, returns the largest round number that is less than or equal to `x`
and flowing the rules:
+
+If `d` is specified as literal:
+`d` = 0: just like without `d`
+`d` > 0 or `d` < 0: the round number would be a multiple of `1/(10^d)`, or the
nearest number of the appropriate data type if `1/(10^d)` isn't exact.
+
+Else if `d` is a column, and `x` has Decimal type, scale of result Decimal
will always be same with input Decimal.
:::tip
Another alias for this function is `dfloor`.
@@ -55,6 +63,24 @@ mysql> select floor(-10.3);
+--------------+
| -11 |
+--------------+
+mysql> select floor(123.45, 1), floor(123.45), floor(123.45, 0), floor(123.45,
-1);
++------------------+---------------+------------------+-------------------+
+| floor(123.45, 1) | floor(123.45) | floor(123.45, 0) | floor(123.45, -1) |
++------------------+---------------+------------------+-------------------+
+| 123.4 | 123 | 123 | 120 |
++------------------+---------------+------------------+-------------------+
+mysql> SELECT number
+ -> , floor(number * 2.5, number - 1) AS f_decimal_column
+ -> , floor(number * 2.5, 0) AS f_decimal_literal
+ -> , floor(cast(number * 2.5 AS DOUBLE), number - 1) AS f_double_column
+ -> , floor(cast(number * 2.5 AS DOUBLE), 0) AS f_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | f_decimal_column | f_decimal_literal | f_double_column |
f_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
index 3115cbf9be..454cf67bb0 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round-bankers.md
@@ -30,6 +30,8 @@ under the License.
`T round_bankers(T x[, d])`
Rounds the argument `x` to `d` specified decimal places. `d` defaults to 0 if
not specified. If d is negative, the left d digits of the decimal point are 0.
If x or d is null, null is returned.
+If `d` is a column, and `x` has Decimal type, scale of result Decimal will
always be same with input Decimal.
+
+ If the rounding number is halfway between two numbers, the function uses
banker’s rounding.
+ In other cases, the function rounds numbers to the nearest integer.
@@ -74,6 +76,18 @@ mysql> select round_bankers(1667.2725, -2);
+------------------------------+
| 1700 |
+------------------------------+
+mysql> SELECT number
+ -> , round_bankers(number * 2.5, number - 1) AS rb_decimal_column
+ -> , round_bankers(number * 2.5, 0) AS rb_decimal_literal
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), number - 1) AS
rb_double_column
+ -> , round_bankers(cast(number * 2.5 AS DOUBLE), 0) AS rb_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+-------------------+--------------------+------------------+-------------------+
+| number | rb_decimal_column | rb_decimal_literal | rb_double_column |
rb_double_literal |
++--------+-------------------+--------------------+------------------+-------------------+
+| 1 | 2.0 | 2 | 2 |
2 |
++--------+-------------------+--------------------+------------------+-------------------+
```
### keywords
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
index edfb50a22f..06a19dae9e 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-functions/numeric-functions/round.md
@@ -31,6 +31,8 @@ under the License.
Rounds the argument `x` to `d` decimal places. `d` defaults to 0 if not
specified. If d is negative, the left d digits of the decimal point are 0. If x
or d is null, null is returned.
2.5 will round up to 3. If you want to round down to 2, please use the
round_bankers function.
+If `d` is a column, and `x` has Decimal type, scale of result Decimal will
always be same with input Decimal.
+
:::tip
Another alias for this function is `dround`.
:::
@@ -74,6 +76,18 @@ mysql> select round(1667.2725, -2);
+----------------------+
| 1700 |
+----------------------+
+mysql> SELECT number
+ -> , round(number * 2.5, number - 1) AS r_decimal_column
+ -> , round(number * 2.5, 0) AS r_decimal_literal
+ -> , round(cast(number * 2.5 AS DOUBLE), number - 1) AS r_double_column
+ -> , round(cast(number * 2.5 AS DOUBLE), 0) AS r_double_literal
+ -> FROM test_enhanced_round
+ -> WHERE rid = 1;
++--------+------------------+-------------------+-----------------+------------------+
+| number | r_decimal_column | r_decimal_literal | r_double_column |
r_double_literal |
++--------+------------------+-------------------+-----------------+------------------+
+| 1 | 3.0 | 3 | 3 |
3 |
++--------+------------------+-------------------+-----------------+------------------+
```
### keywords
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
