sql - How to round to 0.5 or 1? - Stack Overflow

admin管理员组

文章数量:1295887

I'm looking to round values like

Input	Desired Output
0.01	0.5
2.3913	2.5
4.6667	4.5
2.11	2.5

I'm looking to round values like

Input	Desired Output
0.01	0.5
2.3913	2.5
4.6667	4.5
2.11	2.5

How can I manage this in BigQuery? I have a script below which gives me 2 instead of 2.5.

(round((2.11) * 2 ,0)/2) 

Can someone please help me to get rounded values which is the maximum one instead of rounding to the closest one. Like 2.11 should give me 2.5 not 2.

• sql
• google-bigquery

Share Improve this question Follow edited Feb 12 at 6:00 DarkBee 15.6k88 gold badges7272 silver badges116116 bronze badges asked Feb 12 at 0:14 balabala 111 bronze badge 5

• what you expect for let's say 2.9? – Mikhail Berlyant Commented Feb 12 at 0:19
• 3 Oh yes, shouldn't 4.66 -> 5? Since its the nearest maximum. – Dale K Commented Feb 12 at 0:21
• Do you have a requirement for negative values? – Guillaume Outters Commented Feb 12 at 6:08
• Add 0.0 and -0.01. – jarlh Commented Feb 12 at 7:01
• 2 what is the logic ? I first though you wanted to round to the nearest maximum but since you want 4.667 to be 4.5 there is another logic ? – GuidoG Commented Feb 12 at 7:11

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 0

Don't know much about BigQuery but I do know maths so I guess this should work

round(2.11 * 2 + 0.5) / 2

I don't know BigQuery but in SQL Server you could do this

select *,
    -- round to nearest max 
    round(round(number * 2 + 0.5, 0) / 2, 2) as nearest_max,
       
    -- round to nearest max unless the decimal is greater than 0.5
    case when (number % 1) < 0.5 then round(round(number * 2 + 0.5, 0) / 2, 2)
         else round(round((number - 0.5) * 2 + 0.5, 0) / 2, 2)
    end as nearest_max_unless
from numbers

See also this dbFiddle

The result is

number	nearest_max	nearest_max_unless
0,001	0,5	0,5
2,3913	2,5	2,5
4,6667	5	4,5
2,11	2,5	2,5

(due to the small inconsistence between your description and your example, two possibilities)

To the higher of the nearest halves

The "maximum of the range" function is ceil.

So you would write:

(ceil((2.11) * 2)/2))

Always align on a half value

If on the other hand you want to round to a value ending in .5,
you will add and substract:

round(4.6667 + 0.5) - 0.5

Summary

(/!\ In wait for your wishes about negative values; I added some to the example to see what it does as is)

You can try this on GBQ to compare the results:

select
  v,
  ceil(v * 2)/2 with_ceil,
  round(v * 2 + 0.5) / 2 plus_half, -- Leon's answer is equivalent to ceil, https://stackoverflow/a/79431614/1346819
  round(v + 0.5) - 0.5 to_nearest_half
from unnest([ -2.11, -0.2, 0.01, 2.11, 2.3913, 4.6667 ]) as v;

The simplest function that matches the examples that you provided is FLOOR(value) + 0.5.

Here's an example:

SELECT 
  value, FLOOR(value) + 0.5
FROM UNNEST([0.01, 2.3913, 4.6667, 2.11]) AS value

本文标签： sqlHow to round to 05 or 1Stack Overflow