On 12-09-15 16:36, Aurel Wisse wrote:
> 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
>>
>
>
What about this one? i cannot test because i do not have (enough)
data..... ;)
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, (dates.cd) FROM dtoff, dates
WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
AND strftime('%%w', dates.cd)<>'0')
SELECT * from dtoff d1
WHERE calc_date NOTNULL
AND (SELECT cOUNT(*) FROM dtoff d2 WHERE d2.calc_date=d1.calc_date
and d2.tmw>d1.tmw)=0
ORDER BY calc_date;
