Накопительная сумма в SQLite

Question

Я написал запрос в SQLite, который накапливает VALUE, и было бы хорошо, если бы я упорядочивал только по ID (ID - это автоинкремент int). Но если я хочу сделать заказ по ДАТЕ, а затем по ID, запрос покажет мне неверные результаты. Есть идеи?

Запрос:

select t1.ID, 
       t1.DATE, 
       t1.VALUE, 
       sum(t2.VALUE) as TOTAL     
from test t1
inner join test t2 on t1.DATE >= t2.DATE and t1.id >= t2.id 
group by t1.ID, t1.DATE
order by t1.DATE desc, t1.ID desc

Результаты:

ID  DATE        VALUE   TOTAL
9   2015-01-16  55      281 -- should be 226 + 55 = 281
6   2015-01-15  10      26  -- should be 216 + 10 = 226
5   2015-01-15  5       16  -- should be 211 +  5 = 216
8   2015-01-14  100     211
7   2015-01-14  100     111
4   2015-01-13  5       11
3   2015-01-12  3       6
2   2015-01-11  2       3
1   2015-01-10  1       1

Answer 1

По крайней мере, для небольших данных, меняя соединение на что-то вроде этого:

t1.DATE||substr('00000'||t1.id,-5) >= t2.DATE||substr('00000'||t2.id,-5)

работает нормально... Итак, мы создаем "ключ сортировки" на основе даты и идентификатора (дополненный нулями до 5 цифр) и используем его в соединении. Может потребоваться построить индекс для этого, чтобы иметь дело с производительностью, хотя