@Luuk : I tried your query and I cancelled after 9 minutes when it wasn't
finished.
@Richard : The query isn't done after 15 minutes. I added a small
modification, reinserting the WITH clause inside the recursive WITH (and
using dates instead of securities in the remainder of the query) :
CREATE TEMP TABLE offsets AS
WITH RECURSIVE dtoff(calc_date, tmw) AS (
WITH dates AS (SELECT DISTINCT calc_date FROM securities)
SELECT NULL, (SELECT min(calc_date) FROM dates)
...
This is inevitable as "securities" is a table with 6.2 million rows. This
is the schema:
CREATE TABLE security (calc_date TEXT, security_code INT, mx_full REAL,
mx_adj REAL, mx_std REAL, fu_full REAL, fu_adj REAL, few_full REAL, few_adj
REAL);
There are about 4000 distinct calc_date in securities. I want to avoid an
intermediate table with distinct dates. If I were to use an intermediate
table, the offset would be trivial to create on an ordered version of
dates, self joined with the JOIN clause d1.rowid+1=d2.rowid.
The expected output is (first five rows):
2000-11-30|2000-12-01
2000-12-01|2000-12-04
2000-12-04|2000-12-05
2000-12-05|2000-12-06
2000-12-06|2000-12-07
I have the impression that nontrivial SELECT queries in result columns are
quite expensive.
The fact of the matter is that the original recursive query, up to this
point, is the most efficient solution with the non recursive query second.
Richard: Any chance aggregate recursive queries can be reactivated ?
Thanks,
Aurel
On Sat, Sep 12, 2015 at 12:25 PM, Richard Hipp <drh at sqlite.org> wrote:
> On 9/12/15, Aurel Wisse <aurel.wisse at gmail.com> wrote:
> > On 9/11/15, Richard Hipp <drh at sqlite.org> wrote:
> >
> > Here is the example.
>
> Have you considered using code like the following instead:
>
> -- 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<1000)
> INSERT INTO securities(calc_date)
> SELECT datetime(2440587.5 + ((x*123456789012345)%16700000)/1000.0) FROM
> c;
> 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;
> SELECT * FROM offsets;
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> _______________________________________________
> 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
