Round amount up to 2 decimal places in druid

Question

I have a column Amount with data type as string. I want to round these to 2 decimal places in Apache Druid.

For example:

• 0.0 should be 0.00
• 3.0 should be 3.00
• 5.6666 should be 5.67

Answer 1

Until now you can't do that kind of rounding in Druid. I used this query without success:

--  0.0 should be 0.00 
--  3.0 should be 3.00 
--  5.6666 should be 5.67 
SELECT ROUND(0.01, 2) as test_value_1, --OUTPUT: 0.01
       ROUND(3.1231, 2) as test_value_2, --OUTPUT: 3.12
       ROUND(5.6666, 2) as test_value_3, --OUTPUT: 5.67
       ROUND(5.00, 2) as test_value_4, --OUTPUT: 5
       CAST(0 as DECIMAL) as value_5 --OUTPUT: 0

I have reviewed these documents Expression and in it we can read that:

round(x, y) returns the value of the x rounded to the y decimal places. While x can be an integer or floating-point number, y must be an integer. The type of the return value is specified by that of x. y defaults to 0 if omitted. When y is negative, x is rounded on the left side of the y decimal points. If x is NaN, x returns 0. If x is infinity, x will be converted to the nearest finite double.

So, I think you will be able to round but not add the zeros.

If you want to force decimals even, you can use STRING_FORMAT:

SELECT STRING_FORMAT('%.2f', 0.0) as test_value_1, --OUTPUT: 0.00
       STRING_FORMAT('%.2f', 3.0) as test_value_2, --OUTPUT: 3.00
       STRING_FORMAT('%.2f', 5.6666) as test_value_3, --OUTPUT: 5.66
       STRING_FORMAT('%.2f', 1.0 * 0) as value_4 --OUTPUT: 0.00