I have problem with splitting 0.00xxx float values between numbers. Here is example of input data 0 is sum of 1-3 float numbers. As result I want to see rounded numbers without loosing sum of 1-3:
IN:
0 313.726
1 216.412
2 48.659
3 48.655
OUT:
0 313.73
1 216.41
2 48.66
3 48.66
How it should work: Idea is to split the lowest rest(in our example it's 0.002 from value 216.412) between highest. 0.001 to 48.659 = 48.66 and 0.001 to 48.655 = 48.656 after this we can round numbers without loosing data.
After sitting on this problem yesterday I found the solution. The query as I think should look like this.
select test.*,
sum(value - trunc(value, 2)) over (partition by case when id = 0 then 0 else 1 end) part,
row_number() over(partition by case when id = 0 then 0 else 1 end order by value - trunc(value, 2) desc) rn,
case when row_number() over(partition by case when id = 0 then 0 else 1 end order by value - trunc(value, 2) desc) / 100 <=
round(sum(value - trunc(value, 2)) over (partition by case when id = 0 then 0 else 1 end), 2) then trunc(value, 2) + 0.01 else trunc(value, 2) end result
from test;
But still for me it's strange to add const value "0.01" while getting the result.
Any ideas to improve this query?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT
test.*,
sum(test.value - trunc(test.value,
2)) OVER (PARTITION
BY
CASE
WHEN id = 0 THEN 0
ELSE 1 END ) part,
row_number() OVER (PARTITION
BY
CASE
WHEN id = 0 THEN 0
ELSE 1 END
ORDER BY
test.value - trunc(test.value,
2) DESC) rn,
CASE
WHEN row_number() OVER (PARTITION
BY
CASE
WHEN id = 0 THEN 0
ELSE 1 END
ORDER BY
test.value - trunc(test.value,
2) DESC) / 100 <= round(sum(test.value - trunc(test.value,
2)) OVER (PARTITION
BY
CASE
WHEN id = 0 THEN 0
ELSE 1 END ),
2) THEN trunc(test.value,
2) + 0.01
ELSE trunc(test.value,
2) END result
FROM
test