Phenomenon 1: Only the Integer Part Retained Without Number of Decimal Places
When a retrieval and analysis statement similar to the following is used, it is expected to retain two decimal places, but the result only has integer parts:
Search and analysis statement:
* | select count_if(status>=400)*100/count(*) as " error log percentage"
Result:
Even when using the round function to specify the desired number of decimal places, the result still only shows the integer part: Search and analysis statement:
* | select round(count_if(status>=400)*100/count(*),2) as "error log percentage"
Result:
Cause
In SQL, when integers (bigint) are divided, the result is still an integer, and it is invalid to use the round function for integers.
Solution
Method 1:
In four arithmetic operations, multiplication is used to convert one of the integers (bigint) into a decimal (double), for example:
* | select round(count_if(status>=400)*100.0/count(*),2) as "error log percentage"
Method 2:
* | select round(cast(count_if(status>=400)*100 as double)/count(*),2) as "error log percentage"
Phenomenon 2: incorrect number of decimal places
When a retrieval and analysis statement similar to the following is used, it is expected that the final 2 decimal places can be retained, but the result retains 14 places:
Search and analysis statement:
* | select round(count_if(status>=400)*1.0/count(*),5)*100 as "error log percentage"
Result:
Cause
The result of count_if(status>=400)*1.0/count(*) is of type double. Even after the round function is applied, the result remains a double. When further operations are performed, the correctness of the decimal places can no longer be guaranteed. Solution
After all calculations are completed, retain the desired number of decimal places at the end. For example:
* | select round(count_if(status>=400)*100.0/count(*),3) as "error log percentage"