postgresql 在 Postgres 中将数字四舍五入到最接近的 10
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41210358/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rounding numbers to the nearest 10 in Postgres
提问by neuron
I'm trying to solve this particular problem from PGExercises.com:
我正在尝试从 PGExercises.com 解决这个特殊问题：
https://www.pgexercises.com/questions/aggregates/rankmembers.html
https://www.pgexercises.com/questions/aggregates/rankmembers.html
The gist of the question is that I'm given a table of club members and half hourtime slots that they have booked (getting the list is a simple INNER JOIN of two tables).
问题的要点是，我得到了一张俱乐部会员表和他们预订的半小时时段（获取列表是两个表的简单 INNER JOIN）。
I'm supposed to produce a descending ranking of members by total hoursbooked, rounded off to the nearest 10. I also need to produce a column with the rank, using the RANK()window function, and sort the result by the rank. (The result produces 30 records.)
我应该按预订的总小时数生成成员的降序排名，四舍五入到最接近的 10。我还需要使用RANK()窗口函数生成带有排名的列，并按排名对结果进行排序。（结果产生 30 条记录。）
The author's very elegant solution is this:
作者非常优雅的解决方案是这样的：
select firstname, surname, hours, rank() over (order by hours) from
(select firstname, surname,
((sum(bks.slots)+5)/20)*10 as hours
from cd.bookings bks
inner join cd.members mems
on bks.memid = mems.memid
group by mems.memid
) as subq
order by rank, surname, firstname;
Unfortunately, as a SQL newbie, my very unelegant solution is much more convoluted, using CASE WHENand converting numbers to text in order to look at the last digit for deciding on whether to round upor down:
不幸的是，作为 SQL 新手，我非常不优雅的解决方案更加复杂，使用CASE WHEN数字并将其转换为文本以查看最后一位数字来决定是向上还是向下舍入：
SELECT
firstname,
surname,
CASE
WHEN (SUBSTRING(ROUND(SUM(slots*0.5),0)::text from '.{1}$') IN ('5','6','7','8','9','0')) THEN CEIL(SUM(slots*0.5) /10) * 10
ELSE FLOOR(SUM(slots*0.5) /10) * 10
END AS hours,
RANK() OVER(ORDER BY CASE
WHEN (SUBSTRING(ROUND(SUM(slots*0.5),0)::text from '.{1}$') IN ('5','6','7','8','9','0')) THEN CEIL(SUM(slots*0.5) /10) * 10
ELSE FLOOR(SUM(slots*0.5) /10) * 10
END DESC) as rank
FROM cd.bookings JOIN cd.members
ON cd.bookings.memid = cd.members.memid
GROUP BY firstname, surname
ORDER BY rank, surname, firstname;
Still, I manage to almost get it just right - out of the 30 records, I get one edge case, whose firstnameis 'Ponder' and lastnameis 'Stephens'. His rounded number of hours is 124.5, but the solution insists that rounding it to the nearest 10 should produce a result of 120, whilst my solution produces 130.
尽管如此，我还是几乎做到了——在 30 条记录中，我得到了一个边缘案例，它的名字是“Ponder”，姓氏是“Stephens”。他的四舍五入小时数是124.5，但解决方案坚持将其四舍五入到最接近的 10 应产生结果120，而我的解决方案产生130.
(By the way, there are several other examples, such as 204.5rounding up to 210both in mine and the exercise author's solution.)
（顺便说一下，还有其他几个例子，例如在我的解决方案和练习作者的解决方案中进行204.5四舍五入210。）
What's wrong with my rounding logic?
我的舍入逻辑有什么问题？
回答by Gordon Linoff
If you want to round to the nearest 10, then use the built-in round()function:
如果要四舍五入到最接近的 10，请使用内置round()函数：
select round(<whatever>, -1)
The second argument can be negative, with -1 for tens, -2 for hundreds, and so on.
第二个参数可以是负数，-1 表示十位，-2 表示百位，依此类推。
回答by Bohemian
To round to the nearestmultiple of 10:
四舍五入到最接近的倍数10：
round(<value> / 10 - .5) * 10
which will round upfor values ending in 5-9, down otherwise.
这将圆了在5-9结束值，否则下降。
This has the general formula of:
这具有以下通用公式：
round(<value> / <range> - .5) * <range>
So you could round to the nearest 13if you wanted to.
因此，13如果您愿意，可以四舍五入到最近的位置。
回答by Gajus
I have struggled with an equivalent issue. I needed to round number to the nearest multiple of 50. Gordon's suggestion here does not work.
我一直在努力解决一个同等的问题。我需要将数字四舍五入到最接近的 50 倍数。 Gordon 的建议在这里不起作用。
My first attempt was SELECT round(120 / 50) * 50, which gives 100. However, SELECT round(130 / 50) * 50gave 100. This is wrong; the nearest multiple is 150.
我的第一次尝试是SELECT round(120 / 50) * 50，它给出了100. 然而，SELECT round(130 / 50) * 50给了100。这是错误的；最接近的倍数是150。
The trick is to divide using a float, e.g. SELECT round(130 / 50.0) * 50is going to give 150.
诀窍是使用浮点数进行除法，例如SELECT round(130 / 50.0) * 50将要给150。
Turns out that doing x/y, where xand yare integers, is equivalent to trunc(x/y). Where as float division correctly rounds to the nearest multiple.
事实证明，这样做x/y，其中x和y是整数，等价于trunc(x/y)。浮动除法正确四舍五入到最接近的倍数。
