2 consecutive rises in value

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

2 consecutive rises in value

Bart Smissaert
Have a table like this:

create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE] INTEGER)
with data like this:

ID     ISO8601_date    INT_VALUE
----------------------------------------------------
1       2016-01-01         10
1       2016-01-28          9
1       2016-03-05          12
1       2016-05-12          11
2       2016-01-01          12
2       2016-02-02          10
2       2016-03-05          12
2       2016-04-07          14

The date column is in the format yyyy-mm-dd.

Now I want to select the unique ID values that have 2 consecutive rises in
INT_VALUE.
A rise will need to be a higher value on the next date, but not on the same
date.
So in the above data the result would be 2 only as that has 2 rises on
consecutive dates.

Any suggestions how this can be done?

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Hick Gunter
Write a procedural program... ;)

In SQL it requires a triple self join similar to:

Select t1.id from table1 t1, table1 t2, table1 t3 where t2.id=t1.d and t1.value < t2.value and t2.date = (select min(date) from table1 where id =t.id and date > t1.date) and t3.id=t2.id and t2.value < t3.value and t3.date = (select min(date) from t3 where id=t2.id and date > t2.date);

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bart Smissaert
Gesendet: Mittwoch, 19. Oktober 2016 16:53
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [sqlite] 2 consecutive rises in value

Have a table like this:

create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE] INTEGER) with data like this:

ID     ISO8601_date    INT_VALUE
----------------------------------------------------
1       2016-01-01         10
1       2016-01-28          9
1       2016-03-05          12
1       2016-05-12          11
2       2016-01-01          12
2       2016-02-02          10
2       2016-03-05          12
2       2016-04-07          14

The date column is in the format yyyy-mm-dd.

Now I want to select the unique ID values that have 2 consecutive rises in INT_VALUE.
A rise will need to be a higher value on the next date, but not on the same date.
So in the above data the result would be 2 only as that has 2 rises on consecutive dates.

Any suggestions how this can be done?

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
Thanks for that, trying this out now. Must have done something wrong as for
now I get too many ID's. My actual fields are somewhat different. It is
indeed slow, something like 150 secs to process a table of 100000 rows.
This is with select distinct ID etc.
To do this in code will be indeed a lot faster and easier as well, but want
to give SQL a try.

RBS

On Wed, Oct 19, 2016 at 4:34 PM, Hick Gunter <[hidden email]> wrote:

> Write a procedural program... ;)
>
> In SQL it requires a triple self join similar to:
>
> Select t1.id from table1 t1, table1 t2, table1 t3 where t2.id=t1.d and
> t1.value < t2.value and t2.date = (select min(date) from table1 where id =
> t.id and date > t1.date) and t3.id=t2.id and t2.value < t3.value and
> t3.date = (select min(date) from t3 where id=t2.id and date > t2.date);
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Bart Smissaert
> Gesendet: Mittwoch, 19. Oktober 2016 16:53
> An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> sqlite.org>
> Betreff: [sqlite] 2 consecutive rises in value
>
> Have a table like this:
>
> create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE]
> INTEGER) with data like this:
>
> ID     ISO8601_date    INT_VALUE
> ----------------------------------------------------
> 1       2016-01-01         10
> 1       2016-01-28          9
> 1       2016-03-05          12
> 1       2016-05-12          11
> 2       2016-01-01          12
> 2       2016-02-02          10
> 2       2016-03-05          12
> 2       2016-04-07          14
>
> The date column is in the format yyyy-mm-dd.
>
> Now I want to select the unique ID values that have 2 consecutive rises in
> INT_VALUE.
> A rise will need to be a higher value on the next date, but not on the
> same date.
> So in the above data the result would be 2 only as that has 2 rises on
> consecutive dates.
>
> Any suggestions how this can be done?
>
> RBS
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

R Smith
In reply to this post by Bart Smissaert


On 2016/10/19 4:53 PM, Bart Smissaert wrote:

> Have a table like this:
>
> create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE] INTEGER)
> with data like this:
>
> ID     ISO8601_date    INT_VALUE
> ----------------------------------------------------
> 1       2016-01-01         10
> 1       2016-01-28          9
> 1       2016-03-05          12
> 1       2016-05-12          11
> 2       2016-01-01          12
> 2       2016-02-02          10
> 2       2016-03-05          12
> 2       2016-04-07          14
>
> The date column is in the format yyyy-mm-dd.
>
> Now I want to select the unique ID values that have 2 consecutive rises in
> INT_VALUE.
> A rise will need to be a higher value on the next date, but not on the same
> date.
> So in the above data the result would be 2 only as that has 2 rises on
> consecutive dates.
>
> Any suggestions how this can be done?

First get the dates to be unique so a single date isn't considered
twice, then match next higher and next higher dates to get 2 consecutive
items, filter on values going consecutively up.

Like so:

       -- SQLite version 3.9.2  [ Release: 2015-11-02 ] on  SQLitespeed
    version 2.0.2.4.

       -- Script Items: 5          Parameter Count: 0
       -- 2016-10-19 22:40:25.512  |  [Info]       Script Initialized,
    Started executing...
       --
    ================================================================================================

    create table table1([ID] INTEGER, [ISO8601_date] TEXT, [INT_value]
    INTEGER)

    INSERT INTO table1 VALUES
      (1       ,'2016-01-01',         10)
    ,(1       ,'2016-01-28',          9)
    ,(1       ,'2016-03-05',         12)
    ,(1       ,'2016-05-12',         11)
    ,(2       ,'2016-01-01',         12)
    ,(2       ,'2016-02-02',         10)
    ,(2       ,'2016-03-05',         12)
    ,(2       ,'2016-04-07',         14)
    ;

    SELECT * FROM table1;


       --  ID | ISO8601_date | INT_value
       -- --- | ------------ | ---------
       --  1  | 2016-01-01   |     10
       --  1  | 2016-01-28   |     9
       --  1  | 2016-03-05   |     12
       --  1  | 2016-05-12   |     11
       --  2  | 2016-01-01   |     12
       --  2  | 2016-02-02   |     10
       --  2  | 2016-03-05   |     12
       --  2  | 2016-04-07   |     14


    -- This query just to show what's going on

    WITH C1(ID,DT,V) AS (
          SELECT ID, ISO8601_date, MAX(INT_value) FROM table1 GROUP BY
    ID, ISO8601_date
    )
    SELECT A.ID, A.DT, B.DT, C.DT, A.V, B.V, C.V
       FROM C1 AS A
       JOIN C1 AS B ON B.ID=A.ID AND B.DT = (SELECT MIN(X.DT) FROM C1 AS
    X WHERE X.ID=A.ID AND X.DT>A.DT)
       JOIN C1 AS C ON C.ID=A.ID AND C.DT = (SELECT MIN(X.DT) FROM C1 AS
    X WHERE X.ID=A.ID AND X.DT>B.DT)
      WHERE B.V > A.V AND C.V > B.V
    ;

       --      ID      | DT           | DT           | DT |       V    
    |       V      |       V
       -- ------------ | ------------ | ------------ | ------------ |
    ------------ | ------------ | ------------
       --       2      | 2016-02-02   | 2016-03-05   | 2016-04-07 |    
    10      |      12      |      14


    -- This is the actual result query showing just the ID's

    WITH C1(ID,DT,V) AS (
          SELECT ID, ISO8601_date, MAX(INT_value) FROM table1 GROUP BY
    ID, ISO8601_date
    )
    SELECT A.ID
       FROM C1 AS A
       JOIN C1 AS B ON B.ID=A.ID AND B.DT = (SELECT MIN(X.DT) FROM C1 AS
    X WHERE X.ID=A.ID AND X.DT>A.DT)
       JOIN C1 AS C ON C.ID=A.ID AND C.DT = (SELECT MIN(X.DT) FROM C1 AS
    X WHERE X.ID=A.ID AND X.DT>B.DT)
      WHERE B.V > A.V AND C.V > B.V
    ;

       --      ID
       -- ------------
       --       2


       --   Script Stats: Total Script Execution Time:     0d 00h 00m
    and 00.049s
       --                 Total Script Query Time:         0d 00h 00m
    and 00.025s
       --                 Total Database Rows Changed:     8
       --                 Total Virtual-Machine Steps:     4482
       --                 Last executed Item Index:        5
       --                 Last Script Error:
       --
    ------------------------------------------------------------------------------------------------



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
Looks very nice and a lot faster, but I get more ID's than I should.
With this data for example:

ID ISO8601_DATE INT_VALUE
--------------------------------------------
2 2004-06-23 42
2 2006-12-28 39
2 2007-10-09 42
2 2007-10-24 43
2 2009-06-17 45
2 2015-09-09 36
3 2004-05-05 46
3 2005-05-24 43
3 2007-05-14 43
3 2010-04-16 45
3 2010-11-22 43
3 2014-09-15 37
8 2004-06-10 45
10 2005-06-10 46
12 2004-09-07 49
12 2005-02-09 50
12 2006-09-04 46
12 2007-02-13 49
12 2007-04-24 46
12 2007-05-15 47
12 2007-06-05 46
12 2007-07-17 46
12 2007-10-23 49
12 2008-09-29 46
12 2009-01-27 47
12 2009-04-23 45
12 2010-03-26 48
12 2010-06-09 47
12 2011-06-23 43
12 2013-03-19 47
12 2014-03-12 38
12 2014-12-19 40
12 2016-02-01 39
13 2005-01-25 43
13 2005-09-15 45
13 2006-09-26 45
13 2006-10-23 42
13 2008-11-03 44
13 2009-05-18 45
13 2010-03-08 44
13 2010-10-12 45
13 2011-11-07 45
13 2013-03-06 44
13 2013-11-20 36
13 2014-09-18 34
13 2014-09-26 34
13 2015-06-16 36
13 2015-08-06 35
13 2015-10-23 34
13 2016-08-08 35
14 2016-04-01 38
21 2006-04-05 46
21 2008-08-07 46
22 2005-06-29 45
22 2005-07-08 42
22 2005-07-15 42
22 2005-07-29 43
22 2007-01-29 44
22 2007-04-25 44
22 2012-06-01 44
22 2015-04-01 39
24 2010-05-28 49
24 2014-07-04 40
26 2004-09-24 46
26 2004-10-15 46
26 2004-11-23 45
26 2005-09-27 45
26 2006-12-04 46
26 2007-10-08 46
26 2008-11-10 46
26 2009-11-13 46
26 2010-10-13 39
26 2010-10-22 42
26 2010-11-03 43
26 2010-12-02 45
26 2011-11-02 47
26 2012-10-22 46
26 2013-11-04 39
26 2014-06-06 38

I get these ID's:

ID
----------------------------
2
13
22
26

ID 2 for example shouldn't be selected.


RBS



On Wed, Oct 19, 2016 at 9:48 PM, R Smith <[hidden email]> wrote:

>
>
> On 2016/10/19 4:53 PM, Bart Smissaert wrote:
>
>> Have a table like this:
>>
>> create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE]
>> INTEGER)
>> with data like this:
>>
>> ID     ISO8601_date    INT_VALUE
>> ----------------------------------------------------
>> 1       2016-01-01         10
>> 1       2016-01-28          9
>> 1       2016-03-05          12
>> 1       2016-05-12          11
>> 2       2016-01-01          12
>> 2       2016-02-02          10
>> 2       2016-03-05          12
>> 2       2016-04-07          14
>>
>> The date column is in the format yyyy-mm-dd.
>>
>> Now I want to select the unique ID values that have 2 consecutive rises in
>> INT_VALUE.
>> A rise will need to be a higher value on the next date, but not on the
>> same
>> date.
>> So in the above data the result would be 2 only as that has 2 rises on
>> consecutive dates.
>>
>> Any suggestions how this can be done?
>>
>
> First get the dates to be unique so a single date isn't considered twice,
> then match next higher and next higher dates to get 2 consecutive items,
> filter on values going consecutively up.
>
> Like so:
>
>       -- SQLite version 3.9.2  [ Release: 2015-11-02 ] on  SQLitespeed
>    version 2.0.2.4.
>
>       -- Script Items: 5          Parameter Count: 0
>       -- 2016-10-19 22:40:25.512  |  [Info]       Script Initialized,
>    Started executing...
>       --
>    ===========================================================
> =====================================
>
>    create table table1([ID] INTEGER, [ISO8601_date] TEXT, [INT_value]
>    INTEGER)
>
>    INSERT INTO table1 VALUES
>      (1       ,'2016-01-01',         10)
>    ,(1       ,'2016-01-28',          9)
>    ,(1       ,'2016-03-05',         12)
>    ,(1       ,'2016-05-12',         11)
>    ,(2       ,'2016-01-01',         12)
>    ,(2       ,'2016-02-02',         10)
>    ,(2       ,'2016-03-05',         12)
>    ,(2       ,'2016-04-07',         14)
>    ;
>
>    SELECT * FROM table1;
>
>
>       --  ID | ISO8601_date | INT_value
>       -- --- | ------------ | ---------
>       --  1  | 2016-01-01   |     10
>       --  1  | 2016-01-28   |     9
>       --  1  | 2016-03-05   |     12
>       --  1  | 2016-05-12   |     11
>       --  2  | 2016-01-01   |     12
>       --  2  | 2016-02-02   |     10
>       --  2  | 2016-03-05   |     12
>       --  2  | 2016-04-07   |     14
>
>
>    -- This query just to show what's going on
>
>    WITH C1(ID,DT,V) AS (
>          SELECT ID, ISO8601_date, MAX(INT_value) FROM table1 GROUP BY
>    ID, ISO8601_date
>    )
>    SELECT A.ID, A.DT, B.DT, C.DT, A.V, B.V, C.V
>       FROM C1 AS A
>       JOIN C1 AS B ON B.ID=A.ID AND B.DT = (SELECT MIN(X.DT) FROM C1 AS
>    X WHERE X.ID=A.ID AND X.DT>A.DT)
>       JOIN C1 AS C ON C.ID=A.ID AND C.DT = (SELECT MIN(X.DT) FROM C1 AS
>    X WHERE X.ID=A.ID AND X.DT>B.DT)
>      WHERE B.V > A.V AND C.V > B.V
>    ;
>
>       --      ID      | DT           | DT           | DT |       V
> |       V      |       V
>       -- ------------ | ------------ | ------------ | ------------ |
>    ------------ | ------------ | ------------
>       --       2      | 2016-02-02   | 2016-03-05   | 2016-04-07 |
> 10      |      12      |      14
>
>
>    -- This is the actual result query showing just the ID's
>
>    WITH C1(ID,DT,V) AS (
>          SELECT ID, ISO8601_date, MAX(INT_value) FROM table1 GROUP BY
>    ID, ISO8601_date
>    )
>    SELECT A.ID
>       FROM C1 AS A
>       JOIN C1 AS B ON B.ID=A.ID AND B.DT = (SELECT MIN(X.DT) FROM C1 AS
>    X WHERE X.ID=A.ID AND X.DT>A.DT)
>       JOIN C1 AS C ON C.ID=A.ID AND C.DT = (SELECT MIN(X.DT) FROM C1 AS
>    X WHERE X.ID=A.ID AND X.DT>B.DT)
>      WHERE B.V > A.V AND C.V > B.V
>    ;
>
>       --      ID
>       -- ------------
>       --       2
>
>
>       --   Script Stats: Total Script Execution Time:     0d 00h 00m
>    and 00.049s
>       --                 Total Script Query Time:         0d 00h 00m
>    and 00.025s
>       --                 Total Database Rows Changed:     8
>       --                 Total Virtual-Machine Steps:     4482
>       --                 Last executed Item Index:        5
>       --                 Last Script Error:
>       --
>    -----------------------------------------------------------
> -------------------------------------
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Petite Abeille-2

> On Oct 19, 2016, at 11:40 PM, Bart Smissaert <[hidden email]> wrote:
>
> ID 2 for example shouldn't be selected.

Hmmm? ID 2 has two ‘raises' as per your definition: 39 > 42 > 43 and 42 > 43 > 45

2 2004-06-23 42
2 2006-12-28 39
2 2007-10-09 42
2 2007-10-24 43
2 2009-06-17 45
2 2015-09-09 36

Or?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
Ah, yes, sorry, I needed actually drops and was looking at that.
Will work it round.

RBS


On Wed, Oct 19, 2016 at 11:06 PM, Petite Abeille <[hidden email]>
wrote:

>
> > On Oct 19, 2016, at 11:40 PM, Bart Smissaert <[hidden email]>
> wrote:
> >
> > ID 2 for example shouldn't be selected.
>
> Hmmm? ID 2 has two ‘raises' as per your definition: 39 > 42 > 43 and 42 >
> 43 > 45
>
> 2 2004-06-23 42
> 2 2006-12-28 39
> 2 2007-10-09 42
> 2 2007-10-24 43
> 2 2009-06-17 45
> 2 2015-09-09 36
>
> Or?
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Petite Abeille-2

> On Oct 20, 2016, at 12:23 AM, Bart Smissaert <[hidden email]> wrote:
>
> Ah, yes, sorry, I needed actually drops and was looking at that.

Just for fun, here is one using recursion:

with
DataSet
as
(
  select 1 as id, 2 as key, '2004-06-23' as date, 42 as value union all
  select 2 as id, 2 as key, '2006-12-28' as date, 39 as value union all
  select 3 as id, 2 as key, '2007-10-09' as date, 42 as value union all
  select 4 as id, 2 as key, '2007-10-24' as date, 43 as value union all
  select 5 as id, 2 as key, '2009-06-17' as date, 45 as value union all
  select 6 as id, 2 as key, '2015-09-09' as date, 36 as value
),
NextSet
(
  id,
  key,
  date,
  value,
  next_date,
  count
)
as
(
  select  DataSet.*,
          (
            select    Self.date
            from      DataSet as Self
            where     Self.key = DataSet.key
            and       Self.date > DataSet.date
            order by  Self.date
            limit     1
          ) as next_date,
          0 as count
  from    DataSet

  union all
  select  NextSet.id as id,
          NextSet.key as key,
          NextSet.date as date,
          NextSet.value as value,
          (
            select    Self.date
            from      DataSet as Self
            where     Self.key = NextSet.key
            and       Self.date > NextSet.next_date
            order by  Self.date
            limit     1
          ) as next_date,
          NextSet.count + 1 as count
  from    NextSet
  where   exists
          (
            select  1
            from    DataSet
            where   DataSet.key = NextSet.key
            and     DataSet.date = NextSet.next_date
            and     DataSet.value > NextSet.value
          )
)
select    NextSet.key,
          min( NextSet.id ) as min_id,
          max( NextSet.id ) as max_id
from      NextSet
where     NextSet.count = 2

group by  NextSet.key

order by  1;

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
In reply to this post by Bart Smissaert
I worked it round to get 3 consecutive drops:

WITH C1(ID, DT, V) AS (
          SELECT ID, ISO8601_DATE, MIN(INT_VALUE) FROM TABLE1
GROUP BY   ID, ISO8601_DATE
    )
    SELECT DISTINCT A.ID
       FROM C1 AS A
       JOIN C1 AS B ON B.ID = A.ID AND B.DT = (SELECT MAX(X.DT) FROM C1 AS X
       WHERE X.ID = A.ID AND X.DT > A.DT)
       JOIN C1 AS C ON C.ID = A.ID AND C.DT = (SELECT MAX(X.DT) FROM C1 AS X
       WHERE X.ID = A.ID AND X.DT < B.DT)
       JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MAX(X.DT) FROM C1 AS X
       WHERE X.ID = A.ID AND X.DT < C.DT)
     WHERE B.V < A.V AND C.V < B.V AND D.V < C.V

but I must have done something wrong as it picked 62 up from this:

62 2005-01-07 44
62 2006-02-01 47
62 2006-05-22 45
62 2007-04-05 45
62 2007-08-14 45
62 2008-05-21 46
62 2009-08-24 46
62 2010-10-08 45
62 2011-12-07 47
62 2013-01-17 46
62 2014-02-25 37
62 2015-03-30 39
62 2016-09-02 40

Any idea what I did wrong?

RBS



On Wed, Oct 19, 2016 at 11:23 PM, Bart Smissaert <[hidden email]>
wrote:

> Ah, yes, sorry, I needed actually drops and was looking at that.
> Will work it round.
>
> RBS
>
>
> On Wed, Oct 19, 2016 at 11:06 PM, Petite Abeille <[hidden email]
> > wrote:
>
>>
>> > On Oct 19, 2016, at 11:40 PM, Bart Smissaert <[hidden email]>
>> wrote:
>> >
>> > ID 2 for example shouldn't be selected.
>>
>> Hmmm? ID 2 has two ‘raises' as per your definition: 39 > 42 > 43 and 42 >
>> 43 > 45
>>
>> 2 2004-06-23 42
>> 2 2006-12-28 39
>> 2 2007-10-09 42
>> 2 2007-10-24 43
>> 2 2009-06-17 45
>> 2 2015-09-09 36
>>
>> Or?
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
In reply to this post by Petite Abeille-2
Thanks, will try that as well.

RBS

On Thu, Oct 20, 2016 at 12:16 AM, Petite Abeille <[hidden email]>
wrote:

>
> > On Oct 20, 2016, at 12:23 AM, Bart Smissaert <[hidden email]>
> wrote:
> >
> > Ah, yes, sorry, I needed actually drops and was looking at that.
>
> Just for fun, here is one using recursion:
>
> with
> DataSet
> as
> (
>   select 1 as id, 2 as key, '2004-06-23' as date, 42 as value union all
>   select 2 as id, 2 as key, '2006-12-28' as date, 39 as value union all
>   select 3 as id, 2 as key, '2007-10-09' as date, 42 as value union all
>   select 4 as id, 2 as key, '2007-10-24' as date, 43 as value union all
>   select 5 as id, 2 as key, '2009-06-17' as date, 45 as value union all
>   select 6 as id, 2 as key, '2015-09-09' as date, 36 as value
> ),
> NextSet
> (
>   id,
>   key,
>   date,
>   value,
>   next_date,
>   count
> )
> as
> (
>   select  DataSet.*,
>           (
>             select    Self.date
>             from      DataSet as Self
>             where     Self.key = DataSet.key
>             and       Self.date > DataSet.date
>             order by  Self.date
>             limit     1
>           ) as next_date,
>           0 as count
>   from    DataSet
>
>   union all
>   select  NextSet.id as id,
>           NextSet.key as key,
>           NextSet.date as date,
>           NextSet.value as value,
>           (
>             select    Self.date
>             from      DataSet as Self
>             where     Self.key = NextSet.key
>             and       Self.date > NextSet.next_date
>             order by  Self.date
>             limit     1
>           ) as next_date,
>           NextSet.count + 1 as count
>   from    NextSet
>   where   exists
>           (
>             select  1
>             from    DataSet
>             where   DataSet.key = NextSet.key
>             and     DataSet.date = NextSet.next_date
>             and     DataSet.value > NextSet.value
>           )
> )
> select    NextSet.key,
>           min( NextSet.id ) as min_id,
>           max( NextSet.id ) as max_id
> from      NextSet
> where     NextSet.count = 2
>
> group by  NextSet.key
>
> order by  1;
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Petite Abeille-2
In reply to this post by Bart Smissaert

> On Oct 20, 2016, at 1:58 AM, Bart Smissaert <[hidden email]> wrote:
>
> I worked it round to get 3 consecutive drops:
>
> but I must have done something wrong as it picked 62 up from this:

It has three consecutive drops, no? E.g.  47 < 46 < 37


Example:

with
DataSet
as
(
  select 62 as key, '2005-01-07' as date, 44 as value union all
  select 62 as key, '2006-02-01' as date, 47 as value union all
  select 62 as key, '2006-05-22' as date, 45 as value union all
  select 62 as key, '2007-04-05' as date, 45 as value union all
  select 62 as key, '2007-08-14' as date, 45 as value union all
  select 62 as key, '2008-05-21' as date, 46 as value union all
  select 62 as key, '2009-08-24' as date, 46 as value union all
  select 62 as key, '2010-10-08' as date, 45 as value union all
  select 62 as key, '2011-12-07' as date, 47 as value union all
  select 62 as key, '2013-01-17' as date, 46 as value union all
  select 62 as key, '2014-02-25' as date, 37 as value union all
  select 62 as key, '2015-03-30' as date, 39 as value union all
  select 62 as key, '2016-09-02' as date, 40 as value
),
NextSet
(
  key,
  date,
  value,
  next_date,
  count
)
as
(
  select  DataSet.*,
          (
            select    Self.date
            from      DataSet as Self
            where     Self.key = DataSet.key
            and       Self.date > DataSet.date
            order by  Self.date
            limit     1
          ) as next_date,
          0 as count
  from    DataSet

  union all
  select  NextSet.key as key,
          NextSet.date as date,
          NextSet.value as value,
          (
            select    Self.date
            from      DataSet as Self
            where     Self.key = NextSet.key
            and       Self.date > NextSet.next_date
            order by  Self.date
            limit     1
          ) as next_date,
          NextSet.count + 1 as count
  from    NextSet
  where   exists
          (
            select  1
            from    DataSet
            where   DataSet.key = NextSet.key
            and     DataSet.date = NextSet.next_date
            and     DataSet.value < NextSet.value
          )
)
select    NextSet.key
from      NextSet
where     NextSet.count = 3

group by  NextSet.key

order by  1;

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
> It has three consecutive drops, no? E.g.  47 < 46 < 37

That is 3 values, but only  2 drops.

RBS

On Thu, Oct 20, 2016 at 1:07 AM, Petite Abeille <[hidden email]>
wrote:

>
> > On Oct 20, 2016, at 1:58 AM, Bart Smissaert <[hidden email]>
> wrote:
> >
> > I worked it round to get 3 consecutive drops:
> >
> > but I must have done something wrong as it picked 62 up from this:
>
> It has three consecutive drops, no? E.g.  47 < 46 < 37
>
>
> Example:
>
> with
> DataSet
> as
> (
>   select 62 as key, '2005-01-07' as date, 44 as value union all
>   select 62 as key, '2006-02-01' as date, 47 as value union all
>   select 62 as key, '2006-05-22' as date, 45 as value union all
>   select 62 as key, '2007-04-05' as date, 45 as value union all
>   select 62 as key, '2007-08-14' as date, 45 as value union all
>   select 62 as key, '2008-05-21' as date, 46 as value union all
>   select 62 as key, '2009-08-24' as date, 46 as value union all
>   select 62 as key, '2010-10-08' as date, 45 as value union all
>   select 62 as key, '2011-12-07' as date, 47 as value union all
>   select 62 as key, '2013-01-17' as date, 46 as value union all
>   select 62 as key, '2014-02-25' as date, 37 as value union all
>   select 62 as key, '2015-03-30' as date, 39 as value union all
>   select 62 as key, '2016-09-02' as date, 40 as value
> ),
> NextSet
> (
>   key,
>   date,
>   value,
>   next_date,
>   count
> )
> as
> (
>   select  DataSet.*,
>           (
>             select    Self.date
>             from      DataSet as Self
>             where     Self.key = DataSet.key
>             and       Self.date > DataSet.date
>             order by  Self.date
>             limit     1
>           ) as next_date,
>           0 as count
>   from    DataSet
>
>   union all
>   select  NextSet.key as key,
>           NextSet.date as date,
>           NextSet.value as value,
>           (
>             select    Self.date
>             from      DataSet as Self
>             where     Self.key = NextSet.key
>             and       Self.date > NextSet.next_date
>             order by  Self.date
>             limit     1
>           ) as next_date,
>           NextSet.count + 1 as count
>   from    NextSet
>   where   exists
>           (
>             select  1
>             from    DataSet
>             where   DataSet.key = NextSet.key
>             and     DataSet.date = NextSet.next_date
>             and     DataSet.value < NextSet.value
>           )
> )
> select    NextSet.key
> from      NextSet
> where     NextSet.count = 3
>
> group by  NextSet.key
>
> order by  1;
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Cezary H. Noweta
In reply to this post by Bart Smissaert
Hello,

On 2016-10-20 01:58, Bart Smissaert wrote:
> I worked it round to get 3 consecutive drops:

[...]

>        JOIN C1 AS B ON B.ID = A.ID AND B.DT = (SELECT MAX(X.DT) FROM C1 AS X
>        WHERE X.ID = A.ID AND X.DT > A.DT)
>        JOIN C1 AS C ON C.ID = A.ID AND C.DT = (SELECT MAX(X.DT) FROM C1 AS X
>        WHERE X.ID = A.ID AND X.DT < B.DT)
>        JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MAX(X.DT) FROM C1 AS X
>        WHERE X.ID = A.ID AND X.DT < C.DT)

Probably you have forgotten to change a direction of the first comparison.

> but I must have done something wrong as it picked 62 up from this:
>
> 62 2005-01-07 44
> 62 2006-02-01 47
> 62 2006-05-22 45
> 62 2007-04-05 45
> 62 2007-08-14 45
> 62 2008-05-21 46
> 62 2009-08-24 46
> 62 2010-10-08 45
> 62 2011-12-07 47
> 62 2013-01-17 46
> 62 2014-02-25 37
> 62 2015-03-30 39
> 62 2016-09-02 40
>
> Any idea what I did wrong?

The above mentioned JOIN was originally used to extract consecutive
ISO8601_DATE triplets, so you should not change MIN to MAX, nor reverse
comparison operators. If you want to have a quadruplets you should
append fourth JOIN without any changes besides C to D:

        JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MIN(X.DT) FROM C1
AS X
        WHERE X.ID = A.ID AND X.DT > C.DT)

Optionally you can change MIN => MAX, ``>'' => ``<'' in JOIN (which
results in reversed quadruplets) and do NOT reverse comparison operators
in the final WHERE clause. You cannot both reverse tuplets and
comparison operators in the final WHERE clause because one operation
cancels an effect of other.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
> and do NOT reverse comparison operators in the final WHERE clause.

Yes, thank, that was it.

RBS

On Thu, Oct 20, 2016 at 4:40 AM, Cezary H. Noweta <[hidden email]>
wrote:

> Hello,
>
> On 2016-10-20 01:58, Bart Smissaert wrote:
>
>> I worked it round to get 3 consecutive drops:
>>
>
> [...]
>
>        JOIN C1 AS B ON B.ID = A.ID AND B.DT = (SELECT MAX(X.DT) FROM C1
>> AS X
>>        WHERE X.ID = A.ID AND X.DT > A.DT)
>>        JOIN C1 AS C ON C.ID = A.ID AND C.DT = (SELECT MAX(X.DT) FROM C1
>> AS X
>>        WHERE X.ID = A.ID AND X.DT < B.DT)
>>        JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MAX(X.DT) FROM C1
>> AS X
>>        WHERE X.ID = A.ID AND X.DT < C.DT)
>>
>
> Probably you have forgotten to change a direction of the first comparison.
>
> but I must have done something wrong as it picked 62 up from this:
>>
>> 62 2005-01-07 44
>> 62 2006-02-01 47
>> 62 2006-05-22 45
>> 62 2007-04-05 45
>> 62 2007-08-14 45
>> 62 2008-05-21 46
>> 62 2009-08-24 46
>> 62 2010-10-08 45
>> 62 2011-12-07 47
>> 62 2013-01-17 46
>> 62 2014-02-25 37
>> 62 2015-03-30 39
>> 62 2016-09-02 40
>>
>> Any idea what I did wrong?
>>
>
> The above mentioned JOIN was originally used to extract consecutive
> ISO8601_DATE triplets, so you should not change MIN to MAX, nor reverse
> comparison operators. If you want to have a quadruplets you should append
> fourth JOIN without any changes besides C to D:
>
>        JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MIN(X.DT) FROM C1
> AS X
>        WHERE X.ID = A.ID AND X.DT > C.DT)
>
> Optionally you can change MIN => MAX, ``>'' => ``<'' in JOIN (which
> results in reversed quadruplets) and do NOT reverse comparison operators in
> the final WHERE clause. You cannot both reverse tuplets and comparison
> operators in the final WHERE clause because one operation cancels an effect
> of other.
>
> -- best regards
>
> Cezary H. Noweta
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Jean-Luc Hainaut
In reply to this post by Bart Smissaert

What about this one?

create table TABLE1(Seq integer primary key autoincrement,
                     Id integer, Date date, Value integer);
insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...;

select distinct T1.Id                                -- only one per Id
from   TABLE1 T1, TABLE1 T2, TABLE1 T3               -- very fast join
on indexed rowid
where  T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2       -- consecutive triples
and    T1.Id = T2.Id and T3.Id + T1.Id               -- same Id
and    T1.Value < T2.Value and T2.Value < T3.Value;  -- ascending Values

Works if:
- the rows are inserted in ascending values of (Id,Date) (if they don't,
just create and load a temp table with the sorted rows of TABLE1)
- and if the rows are unique on (Id,Date).

If rowid column Seq cannot be added to source TABLE1, load data in temp
table TABLE2 with columns (Seq,Id,Date,Value).

Probably not as fast as a purely procedural algorithm and less elegant
than previous proposals,
but probably faster than previous proposals and more elegant than a
purely procedural one!

J-L Hainaut

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
Thanks, will give that a try as well, but I am happy with the solution form
R Smith.
Only one relatively simple SQL and quite fast. Took about 1.5 secs to
process
50000 rows.

RBS

On Thu, Oct 20, 2016 at 4:55 PM, Jean-Luc Hainaut <
[hidden email]> wrote:

>
> What about this one?
>
> create table TABLE1(Seq integer primary key autoincrement,
>                     Id integer, Date date, Value integer);
> insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...;
>
> select distinct T1.Id                                -- only one per Id
> from   TABLE1 T1, TABLE1 T2, TABLE1 T3               -- very fast join on
> indexed rowid
> where  T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2       -- consecutive triples
> and    T1.Id = T2.Id and T3.Id + T1.Id               -- same Id
> and    T1.Value < T2.Value and T2.Value < T3.Value;  -- ascending Values
>
> Works if:
> - the rows are inserted in ascending values of (Id,Date) (if they don't,
> just create and load a temp table with the sorted rows of TABLE1)
> - and if the rows are unique on (Id,Date).
>
> If rowid column Seq cannot be added to source TABLE1, load data in temp
> table TABLE2 with columns (Seq,Id,Date,Value).
>
> Probably not as fast as a purely procedural algorithm and less elegant
> than previous proposals,
> but probably faster than previous proposals and more elegant than a purely
> procedural one!
>
> J-L Hainaut
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
In reply to this post by Jean-Luc Hainaut
> select distinct T1.Id                                -- only one per Id
> from   TABLE1 T1, TABLE1 T2, TABLE1 T3               -- very fast join on
indexed rowid
> where  T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2       -- consecutive
triples
> and    T1.Id = T2.Id and T3.Id + T1.Id               -- same Id
> and    T1.Value < T2.Value and T2.Value < T3.Value;  -- ascending Values

I had a look at this and tried it without using the extra auto-increment
field, using the table ROWID instead to check for consecutiveness.
It would work great and a lot faster indeed if it wasn't for the multiple
values on the same date. Problem with that is that if you group by date
it may skip a ROWID, so it won't pick up that triple. The answer is just to
clean that table up and clear these extra values on the same day.
This will always need to be done in any case, so it might as well be done
as a one off rather than in every select SQL.
Thanks for that idea, it looks to me the fastest.

RBS



On Thu, Oct 20, 2016 at 4:55 PM, Jean-Luc Hainaut <
[hidden email]> wrote:

>
> What about this one?
>
> create table TABLE1(Seq integer primary key autoincrement,
>                     Id integer, Date date, Value integer);
> insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...;
>
> select distinct T1.Id                                -- only one per Id
> from   TABLE1 T1, TABLE1 T2, TABLE1 T3               -- very fast join on
> indexed rowid
> where  T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2       -- consecutive triples
> and    T1.Id = T2.Id and T3.Id + T1.Id               -- same Id
> and    T1.Value < T2.Value and T2.Value < T3.Value;  -- ascending Values
>
> Works if:
> - the rows are inserted in ascending values of (Id,Date) (if they don't,
> just create and load a temp table with the sorted rows of TABLE1)
> - and if the rows are unique on (Id,Date).
>
> If rowid column Seq cannot be added to source TABLE1, load data in temp
> table TABLE2 with columns (Seq,Id,Date,Value).
>
> Probably not as fast as a purely procedural algorithm and less elegant
> than previous proposals,
> but probably faster than previous proposals and more elegant than a purely
> procedural one!
>
> J-L Hainaut
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Jean-Luc Hainaut
 > I had a look at this and tried it without using the extra
auto-increment field, using the table ROWID
 > instead to check for consecutiveness. It would work great and a lot
faster indeed if it wasn't for
 > the multiple values on the same date. Problem with that is that if
you group by date it may
 > skip a ROWID, so it won't pick up that triple. The answer is just to
clean that table up and
 > clear these extra values on the same day. This will always need to be
done in any case, so
 > it might as well be done as a one off rather than in every select
SQL. Thanks for that idea,
 > it looks to me the fastest. RBS

With a complexity of O(N.logN) this algorithm is the second best to a
pure sequential scan that compares the successive triples, which is in
O(N). Practically, considering the locality of accesses for the join
(the 3 rows to join most often are in the same page) and the small size
of the primary index, the performance should be close to that of a
sequential scan.

When you write "if it wasn't for the multiple values on the same date",
do you mean that the values of (Id,Date) are not unique among the rows
of TABLE1, so that one can find more than one row for the same values of
(Id,Date)? In this case, how do you compute the unique Value of this
couple (Id,Date)? Average, last one, largest one?

JLH

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2 consecutive rises in value

Bart Smissaert
> so that one can find more than one row for the same values of (Id,Date)?
Yes and that messes up your idea. Solution is to tidy that table up first
before running any select SQL.

> Average, last one, largest one?
For now I have taken the largest one as in R Smith's SQL. Probably it is
better to keep the last value, so the one with the highest ROWID.

RBS

On Fri, Oct 21, 2016 at 7:59 PM, Jean-Luc Hainaut <
[hidden email]> wrote:

> > I had a look at this and tried it without using the extra auto-increment
> field, using the table ROWID
> > instead to check for consecutiveness. It would work great and a lot
> faster indeed if it wasn't for
> > the multiple values on the same date. Problem with that is that if you
> group by date it may
> > skip a ROWID, so it won't pick up that triple. The answer is just to
> clean that table up and
> > clear these extra values on the same day. This will always need to be
> done in any case, so
> > it might as well be done as a one off rather than in every select SQL.
> Thanks for that idea,
> > it looks to me the fastest. RBS
>
> With a complexity of O(N.logN) this algorithm is the second best to a pure
> sequential scan that compares the successive triples, which is in O(N).
> Practically, considering the locality of accesses for the join (the 3 rows
> to join most often are in the same page) and the small size of the primary
> index, the performance should be close to that of a sequential scan.
>
> When you write "if it wasn't for the multiple values on the same date", do
> you mean that the values of (Id,Date) are not unique among the rows of
> TABLE1, so that one can find more than one row for the same values of
> (Id,Date)? In this case, how do you compute the unique Value of this couple
> (Id,Date)? Average, last one, largest one?
>
> JLH
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users