Performing a prefix computation using SQL without defined procedures

Question

I have a table with a column of integers - I need a way to generate the "prefix" of this column in another table.

For e.g.

I have 1, 0, 0, 0, 1, 0, 1, 0, 0 as the input
I need 1, 1, 1, 1, 2, 2, 3, 3, 3 as the output

This needs to be done in SQLite's SQL dialect , no user defined functions or stored procedures are possible.

Answer 1

try something like this:

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id ) as accumulated
from table t1

from: http://stackoverflow.com/questions/3785995/sqlite-accumulator-sum-column-in-a-select-statement/3786711#3786711

Answer 2

So to insert from input table to output table you need following query:

INSERT INTO output
SELECT id,
(SELECT sum(i1.value) FROM input AS i1 WHERE i1.rowid  <= i2.rowid) as VALUE
FROM input AS i2