英文:
Snowflake CEIL Function - round up to next 0.1 kilometer
问题
我有一个包含以米为单位的测量值的列。
我想要将它们四舍五入到下一个100米并返回为公里值。
特殊的情况是：如果原始值是“圆整”数（每100米递增），它应该向上四舍五入到下一个100米的递增（请参见下面示例中的第3行）。
示例：
meter_value kilometer_value
1111 1.2
111 0.2
1000 1.1
我认为我可以通过以下方式获得前两行：
ceil(meter_value/1000,1) as kilometer_value
我考虑的解决方案来处理第三行的特殊情况是始终添加1米：
ceil((meter_value+1)/1000,1) as kilometer_value
这似乎有点笨拙，是否有更好的方法/替代函数来实现这一目标？
英文:
I have a column containing measurement values in meters.
I want to round them up (ceil) them to the next 100m and return it as a km value.
Special thing is: if the original value is a "round" number (100m increment) it should be ceiled up to the next 100m increment (see line 3 in the example below).
Example:
meter_value kilometer_value
1111 1.2
111 0.2
1000 1.1
I think I can get the first two lines by doing:
ceil(meter_value/1000,1) as kilometer_value
The solution I thought of to fix the edge case in line three is to just add 1 meter always:
ceil((meter_value+1)/1000,1) as kilometer_value
It seems a bit clumsy, is there a better way/alternative function to archive this?
答案1
得分: 1
你可以检查是否可以被100整除，只有在可以整除时才加1：
ceil(((meter_value + iff(meter_value % 100 = 0, 1, 0))/1000), 1)
这样可以避免在允许小数部分的情况下，如果每次都加1，将导致在值为999.5时不准确。
英文:
You can check to see if it's divisible by 100 and only add one if it is:
ceil(((meter_value + iff(meter_value % 100 = 0, 1, 0))/1000), 1)
This will prevent situations where (if decimal parts are allowed) adding 1 to a value of 999.5 would not be accurate if adding one all the time.
答案2
得分: 1
Greg的答案很好，对我来说更容易阅读的方式是：
- 除以100
- 向下取整
- 加1
- 向上取整
- 除以10
select
column1 as METER_VALUE
,ceil(((METER_VALUE + iff(METER_VALUE % 100 = 0, 1, 0))/1000), 1) as GREG
,ceil(floor(METER_VALUE/100)+1)/10 as SIMEON
from values
(1111)
,(111)
,(1000)
,(1)
,(0)
;
| METER_VALUE | GREG | SIMEON |
|---|---|---|
| 1,111 | 1.2 | 1.2 |
| 111 | 0.2 | 0.2 |
| 1,000 | 1.1 | 1.1 |
| 1 | 0.1 | 0.1 |
| 0 | 0.1 | 0.1 |
我们是否需要提到负值？我是说它是距离，所以是一个无方向的大小，对吗？
无论如何，对于负值，我们两种方法中的+1都会使边界情况出错。
实际上：
一旦你对添加1或0.1进行向下取整，如果先除以1000而不是100，就不需要再向上取整
因此，两种缩写形式可以是：
,ceil(floor(METER_VALUE/100)+1)/10 as VERSION_A
,(floor(METER_VALUE/100)+1)/10 as VERSION_B
,floor(METER_VALUE/1000,1)+0.1 as VERSION_C
英文:
Greg's answer is good, simpler to read to me would be to
- divide by 100
- floor
- add 1
- ceil
- divide by 10
select
column1 as meter_value
,ceil(((meter_value + iff(meter_value % 100 = 0, 1, 0))/1000), 1) as greg
,ceil(floor(meter_value/100)+1)/10 as simeon
from values
(1111)
,(111)
,(1000)
,(1)
,(0)
;
| METER_VALUE | GREG | SIMEON |
|---|---|---|
| 1,111 | 1.2 | 1.2 |
| 111 | 0.2 | 0.2 |
| 1,000 | 1.1 | 1.1 |
| 1 | 0.1 | 0.1 |
| 0 | 0.1 | 0.1 |
do we want to mention negative values? I mean it distance, so it's a directionless magnitude, right?
anyway with negative value, both our methods the +1 forces the boundary case to be wrong.
Actually:
Once you have floored adding the 1 or 0.1 if you divide by 1000 vs 100 first, you don't need to ceil at all
thus two short forms can be:
,ceil(floor(meter_value/100)+1)/10 as version_a
,(floor(meter_value/100)+1)/10 as version_b
,floor(meter_value/1000,1)+0.1 as version_c
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论