[Kevin Martin] > For the quick and dirty solution, I think you can use something like this to > create your view. You would need to index time, and even with the index, I'm > not too sure about speed. > > select x1.time, x1.value, x2.time from x as x1 left join x as x2 on > x2.time=(select max(time) from x where time<x1.time);
Thanks, that works! However, it's *extremely* slow. That's mainly caused by the use of max() even though "time" is my primary key (shouldn't that create a sorted index, so max() doesn't have to scan through the whole table?). I get up to a 100,000x speedup when I query for the weighted average in my actual table if I stick with my "order by" construct, so this is better: select x1.time, x1.value, x2.time from x as x1 left join x as x2 on x2.time=(select time from x where time<x1.time order by time desc limit 1); It's ten times faster to do an avg() on the original table, but speedwise the above is quite acceptable. It takes 1.5 seconds to get the weighted average this way from a table with nearly a million rows. For simplicity, I'll store the time elapsed since the last entry instead: create view my_view as select x1.time, x1.value as value, x1.time-x2.time as interval from x as x1 left join x as x2 on x2.time=(select time from x where time<x1.time order by time desc limit 1); Then I can get the weighted average this way: select sum(value * interval) / sum(interval) from my_view; The second sum() seems silly. I tried to divide by ((select max(time) from my_view) - (select min(time) from my_view)) instead, but it actually slowed it down. However, if I select max and min from the original table instead, I get a slight speedup. I need to refine this a bit, so rather than this kind of weighting, I should sum the average of the value and the value of the previous row. > I would favor the virtual table approach, as I think the other > solutions require more complicated queries to account for the fact > that interpolation is going on. Yes, but if creating views does the queries fast enough for me, I'll be pragmatic about this. :) -- Steinar _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
