Yes it works, but it takes forever. By comparison:
Original recursive query: 7-8 seconds (SQLite 3.8.10.1)
Non recursive query (see below): 18-19 seconds. (SQLite 3.8.10.1)
Proposed query: Not finished after 10 minutes. I am cancelling.
Non recursive query with same result:
CREATE TEMP TABLE dateoff AS
WITH dates AS (SELECT DISTINCT calc_date FROM wts_next_day
WHERE strftime('%w', calc_date)<>'0')
SELECT dt1.calc_date AS calc_date, min(dt2.calc_date) AS tmw
FROM dates AS dt1, dates AS dt2
ON dt2.calc_date > dt1.calc_date
GROUP BY dt1.calc_date;
So the recursive option is still way ahead.
On Sat, Sep 12, 2015 at 10:01 AM, E.Pasma <pasma10 at concepts.nl> wrote:
>
> Op 12 sep 2015, om 13:48 heeft Aurel Wisse het volgende geschreven:
>
>>
>>
>> Here is the example. It doesn't use an actual aggregation (sum, count),
>> but
>> the
>>
>> "min" aggregate function.
>>
>>
>> From a list of dates, create a lookup table with two
>>
>> columns: the original date (calc_date) and the date after calc_date (tmw).
>>
>>
>> CREATE TABLE offsets AS
>>
>> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>>
>> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>>
>> SELECT NULL, min(cd) FROM dates
>>
>> UNION
>>
>> SELECT dtoff.tmw, min(dates.cd) FROM dtoff, dates
>>
>> WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
>>
>> AND strftime('%%w', dates.cd)<>'0')
>>
>> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>>
>>
>
> The query below should work.
> I hope that that is not a matter of good luck?
>
> CREATE TABLE securities(calc_date)
> ;
> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>
> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>
> SELECT NULL, min(cd) FROM dates
>
> UNION
>
> SELECT dtoff.tmw,
> -- begin change 150912
> (
> SELECT min(dates.cd)
> FROM dates
> WHERE dates.cd > dtoff.tmw
> AND strftime('%%w', dates.cd)<>'0'
> )
> -- end change 150912
> FROM dtoff
> WHERE dtoff.tmw NOTNULL
> )
> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>
> ~
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Aurel Wisse
(514) 814-5421
