sql - better way of querying required data

Question

I have this table snapshot

A | B   | C  | D
o1| 100 | 20 | i1
o2| 100 | 30 | i1

For a given number, I need rows until sum(B-C) >= given_number

ex:

• request (i1, 80) = o1 (since 100 - 20 = 80 >= 80)

• request (i1, 100) = o1, o2 { (100 - 20) + (100 - 30) >= 100 }

I'm using informix db. Please suggest mysql specific solutions as well, I will try informix equivalent.

Answer 1

Using Informix 12.10.FC8DE maybe the following is what you want.

CREATE TABLE test_data
(
    a CHAR(2)
    , b INTEGER
    , c INTEGER
    , d CHAR(2)
);

INSERT INTO test_data VALUES('o1', 100,  20, 'i1');
INSERT INTO test_data VALUES('o2', 100,  30, 'i1');
INSERT INTO test_data VALUES('o3', 100,  15, 'i1');
INSERT INTO test_data VALUES('o4', 100,  45, 'i1');
INSERT INTO test_data VALUES('o5', 100,  20, 'i2');
INSERT INTO test_data VALUES('o6', 100, 100, 'i1');
INSERT INTO test_data VALUES('o7', 100,  30, 'i2');
INSERT INTO test_data VALUES('o8', 100,   5, 'i2');
INSERT INTO test_data VALUES('o9', 100,  60, 'i1');

---

SELECT * FROM test_data;
a            b           c d    
o1         100          20 i1
o2         100          30 i1
o3         100          15 i1
o4         100          45 i1
o5         100          20 i2
o6         100         100 i1
o7         100          30 i2
o8         100           5 i2
o9         100          60 i1
 9 row(s) retrieved.

With this sample data we can use (I left the cumulative sums for better visualization) for request (i1, 80):

SELECT
    *
FROM
(
SELECT
    *
    , SUM (t.b - t.c) 
        OVER 
        (
            ORDER BY t.a
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
        ) AS r_sum 
    , SUM (t.b - t.c) 
        OVER 
        (
            ORDER BY t.a
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND 1 PRECEDING
        ) AS previous_r_sum

FROM
    test_data AS t
WHERE
    t.d = 'i1'
) AS vt1
WHERE
    previous_r_sum < 80 
    OR previous_r_sum IS NULL;

---

a            b           c d             r_sum   previous_r_sum    
o1         100          20 i1               80
 1 row(s) retrieved.

With this sample data we can use (I left the cumulative sums for better visualization) for request (i1, 100):

SELECT
    *
FROM
(
SELECT
    *
    , SUM (t.b - t.c) 
        OVER 
        (
            ORDER BY t.a
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
        ) AS r_sum 
    , SUM (t.b - t.c) 
        OVER 
        (
            ORDER BY t.a
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND 1 PRECEDING
        ) AS previous_r_sum

FROM
    test_data AS t
WHERE
    t.d = 'i1'
) AS vt1
WHERE
    previous_r_sum < 100
    OR previous_r_sum IS NULL;

---

a            b           c d             r_sum   previous_r_sum
o1         100          20 i1               80
o2         100          30 i1              150               80
 2 row(s) retrieved.

I have not done any performance or corner cases testing.