SQL Query performance to find middle point

user_2125671 0
Viewed 37

I have this table

CREATE TABLE base_table (id int, amount int)
INSERT INTO base_table VALUES (1, 1)
   ,(2,1)
   ,(3,1)
   ,(4,1)
   ,(5,1)
   ,(6,2)
   ,(7,3)

The sum of first 5 amounts is equal to the sum of last two, I need to find this id 5.

I manage to do it with following query :

SELECT b.id
FROM base_table b, (SELECT SUM(amount)/2 AS amount FROM base_table) s
WHERE (SELECT SUM(amount) FROM base_table b2 WHERE b2.id <= b.id    ) <= s.amount
  AND (SELECT SUM(amount) FROM base_table b3 WHERE b3.id <= b.id + 1) >  s.amount

Now this query will perform badly for big tables (I have not tried). What can we do to improve performance ?

sql performance
1 Answers

You can avoid all the subqueries with analytic functions:

with data as (
    select *,
        sum(amount) over () as s,
        sum(amount) over (order by id) as cs
    from base_table
)
select max(id)
from data
where cs <= s / 2.0;

You seem to be relying on the sequential order of the id values. This approach would not require that.

The difference in execution time is apparent here: https://www.db-fiddle.com/f/jFAYrE12H68KM7u9MeHDBM/2

user_13222680
Related