Performing a prefix computation using SQL without defined procedures

I have a table with a column of integers - I need a way to 开发者_StackOverflow社区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.

try something like this:

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

from: SQLite: accumulator (sum) column in a SELECT statement

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