[Solved] Split float between list of numbers
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Split float between list of numbers

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?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.