Numeric

round

round(value, scale) rounds a value to the specified scale using the "half up" method.

Arguments

  • value is any numeric value.
  • scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Description

round(value, scale) returns the closest value in the specified scale. It uses the "half up" tie-breaking method when the value is exactly halfway between the round_up and round_down values.

Return value

Return value type is double.

Examples

SELECT
d,
round(d, -2),
round(d, -1),
round(d,0),
round(d,1),
round(d,2)
FROM dbl;
dround-2round-1round0round1round2
-0.81190540600-1-0.8-0.81
-5.0027685470-10-5-5-5
-64.75487334-100-60-65-64.8-64.75
-926.531695-900-930-927-926.5-926.53
0.0693614480000.10.07
4.00362705300444
86.91359825100908786.986.91
376.3807766400380376376.4376.38

round_down

round_down(value, scale) - rounds a value down to the specified scale

Arguments

  • value is any numeric value.
  • scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Description

round_up(value, scale) rounds a value down to the specified scale.

Return value

Return value type is double.

Examples

SELECT
d,
round_down(d, -2),
round_down(d, -1),
round_down(d,0),
round_down(d,1),
round_down(d,2)
FROM dbl;
dr_down-2r_down-1r_down0r_down1r_down2
-0.811905406000-0.8-0.81
-5.00276854700-5-5-5
-64.754873340-60-64-64.7-64.75
-926.531695-900-920-926-926.5-926.53
0.06936144800000.06
4.00362705300444
86.913598250808686.986.91
376.3807766400370376376.3376.38

round_up

round_up(value, scale) - rounds a value up to the specified scale

Arguments

  • value is any numeric value.
  • scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Description

round_up(value, scale) rounds a value up to the specified scale

Return value

Return value type is double.

Examples

SELECT
d,
round_up(d, -2),
round_up(d, -1),
round_up(d,0),
round_up(d,1),
round_up(d,2)
FROM dbl;
dr_up-2r_up-1r_up0r_up1r_up2
-0.811905406-100-10-1-0.9-0.82
-5.002768547-100-10-6-5.1-5.01
-64.75487334-100-70-65-64.8-64.76
-926.531695-1000-930-927-926.6-926.54
0.0693614481001010.10.07
4.0036270531001054.14.01
86.9135982510090878786.92
376.3807766400380377376.4376.39

round_half_even

round_half_even(value, scale) - returns the closest value in the specified scale using the "half to even" behaviour

Arguments

  • value is any numeric value.
  • scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Description

round_half_even(value, scale) returns the closest value in the specified scale. It uses the "half up" tie-breaking method when the value is exactly halfway between the round_up and round_down values.

Return value

Return value type is double.

Examples

Tie-breaker behavior
SELECT
round_half_even(5.55, 1),
round_half_even(5.65, 1)
FROM long_sequence(1);
round_half_evenround_half_even
5.65.6
More examples
SELECT
d,
round_half_even(d, -2),
round_half_even(d, -1),
round_half_even(d,0),
round_half_even(d,1),
round_half_even(d,2)
FROM dbl;
dr_h_e-2r_h_e-1r_h_e0r_h_e1r_h_e2
-0.81190540600-1-0.8-0.81
-5.00276854700-5-5-5
-64.75487334-100-60-65-64.8-64.75
-926.531695-900-930-927-926.5-926.53
0.0693614480000.10.07
4.00362705300444
86.91359825100908786.986.91
376.3807766400380376376.4376.38

abs

abs(value) return the absolute value.

Arguments

  • value is any numeric value.

Description

abs(value) behaves as follow

  • When the input value is positive, abs returns value
  • When the input value is negative, abs returns - value
  • When the input value is 0, abs returns 0

Return value

Return value type is double.

Examples

SELECT
x - 2 a,
abs(x -2)
FROM long_sequence(3);
aabs
-11
00
11