On 9/12/15, Aurel Wisse <aurel.wisse at gmail.com> wrote:
>
> In your example, there are 1000 rows of indexed dates.
>
It's easy enough to change it to 6300000 or whatever number is closer
to your test case.
I get:
Run Time: real 2.311 user 2.177056 sys 0.131887
Run Time: real 0.164 user 0.159917 sys 0.003997
The first time is for CREATE INDEX and the second is for the CREATE
TABLE offsets.
Test script:
-- Create a table "securities(calc_date)" and fill it with lots
-- with lots of dates in a crazy order.
--
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;
.timer on
CREATE INDEX securities_calcdate ON securities(calc_date);
-- Compute the "offsets(calc_date, tmw)" which has one entry for each
-- calc_date in securities and for which tmw is the first non-Sunday date
-- in securities that comes after calc_date.
--
CREATE TABLE offsets AS
WITH RECURSIVE dtoff(calc_date, tmw) AS (
SELECT NULL, (SELECT min(calc_date) FROM securities)
UNION
SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
WHERE securities.calc_date>dtoff.tmw
AND strftime('%w',securities.calc_date)<>'0')
FROM dtoff
)
SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
--
D. Richard Hipp
drh at sqlite.org
