Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb有四舍五入六成双的修约函数吗？或者有没有实现方式呢
To improve efficiency, please provide the following information. A clear problem description will help resolve the issue faster:
[Overview] Scenario + Problem Summary
I need to use the Banker’s rounding rule (round half to even), but TiDB does not support custom functions. How can I achieve this?
[Application Framework and Development Adaptation Business Logic]
[Background] Actions taken
[Phenomenon] Business and database phenomena
[Problem] Current issue encountered
[Business Impact]
[TiDB Version]
[Attachments] Relevant logs and monitoring
You are mistaken, it should be “round half to even.”
No, it’s about the rounding function (Banker’s rounding).
This functionality can be achieved through a User-Defined Function (UDF).
Yes, is there a good solution?
Custom functions are not supported… right?
It is estimated that it can only be implemented at the application layer.
In theory, multiple nested functions should be able to achieve it.
In EXCEL, by entering the composite formula “=IF((A1-INT(A1))<>0.5,ROUND(A1,0),IF(MOD(INT(A1),2)=0,INT(A1),ROUND(A1,0)))” in the cell, you can determine the positive or negative value of the input in cell A1 and perform rounding to the nearest even number using the “round half to even” rule.
There should be a common method for this rule in the code, right?
It should be possible to calculate it through nested built-in database functions, after all, the database can use case when.
I don’t think this should be implemented at the database layer.
https://dev.mysql.com/doc/refman/8.0/en/precision-math-rounding.html
Even MySQL has this note.
* For approximate-value numbers, the result depends on the C library. On many systems, this means that [ROUND()](https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_round) uses the “round to nearest even” rule: A value with a fractional part exactly half way between two integers is rounded to the nearest even integer.
The type of rounding depends on the calculation, and part of it relies on the C library. This may cause different behaviors on different platforms. There are examples in the documentation link.
If data types, C libraries, and other miscellaneous issues can all lead to unstable results, it seems better to implement this at the application layer.
It is better to implement special business logic in code, as databases are prone to issues.
Implement it within the business code.
The rounding mode of MySQL database: decimal uses round half up, while float and double use round half to even.
Round down: if the digit is less than or equal to 4, it is rounded down.
Round up: if the digit is greater than or equal to 6, it is rounded up.
Round half to even: if the digit is 5, check the previous digit. If it is even, it remains the same; if it is odd, it is rounded up. If the digit after 5 is greater than 0, it is rounded up; if it is 0, it is rounded down.
First time hearing about it, I’ve learned something new.
Implement it at the code level.
Although TiDB does not support custom functions, you can implement the Banker’s rounding rule (round half to even) using the following methods:
-
Using built-in functions: TiDB provides some built-in functions for common numerical operations and rounding. You can try using the ROUND() function for rounding. For example, ROUND(123.456, 2) will return 123.46.
-
Using expressions and conditional statements: If built-in functions do not meet your needs, you can use expressions and conditional statements to implement the Banker’s rounding rule. For example, by checking the decimal part and the value of the next digit, you can decide whether to round.
Here is an example SQL query that uses expressions and conditional statements to implement the Banker’s rounding rule:
SELECT
CASE
WHEN ABS(value * 10 - FLOOR(value * 10)) = 0.5 AND FLOOR(value) % 2 = 0 THEN FLOOR(value)
ELSE ROUND(value, 2)
END AS rounded_value
FROM your_table;
In this example, we check if the result of multiplying the decimal part by 10 is 0.5 and if the integer part is even to decide whether to round. If the conditions are met, the FLOOR() function is used to truncate; otherwise, the ROUND() function is used for rounding.
This piece of documentation has existed since a long time ago, back in v2.
|
username: TiDBer_NpMjpo3g
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 