Here is still a follow up of last weeks discussion "Worked in 3.8.9
but no longer in .." which is about the following case:
a large table includes a datetime field. From the points in time one
wants to report time intervals (from - to).
The datetime field is indexed.
The fastest solution that was proposed involves the creation of a temp
table.
Na?vely I searched a faster solution that should just do a single scan
of the existing index on datetime. And to report intervals this
somehow needs to access the result of two rows at the time, say the
current row and the previous row.
Assuming the execution plan is as expected, this can be achieved with
a sort of global variable. That is to be set at each row and hold the
point in time and recalled at the next row to report it as the from
date.
An SQL function is required then to set and get the value. I had a
look at sqlite3_mprintf and this comes close if it would write the
string in some global variable.
Based on that I created function mprint() as limited version of
mprintf. It accepts no format, only a value parameter. Furthermore, if
called with no parameter at all, it returns the last printed value.
The query is now:
.load mprint
CREATE TABLE securities(calc_date);
WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE
x<6300000)
INSERT INTO securities(calc_date)
SELECT datetime(2440587.5 + ((x*123456789012345)%16700000)/1000.0)
FROM c;
CREATE INDEX securities_calcdate ON securities(calc_date);
select mprint() as calc_date0, --previous value
mprint(calc_date) as tmw --new value
from (select mprint(''))--initial value
join securities
group by calc_date
having strftime('%w',calc_date)<>'0'
;
and the result is as expected and comes almost twice as fast.
Possibly I am a bit flattered by this result,
Is a function like this of any gene ral use?
Should I mail the extension source?
~
