Re: [sqlite] 2 consecutive rises in value

[Petite Abeille]

> On Oct 19, 2016, at 11:40 PM, Bart Smissaert  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 consecutive rises in value

[R Smith]

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_dateINT_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 -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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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_dateINT_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 -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
>
>  

Re: [sqlite] 2 consecutive rises in value

[Petite Abeille]

> On Oct 20, 2016, at 12:23 AM, Bart Smissaert  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.*,
  (
selectSelf.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
  fromDataSet

  union all
  select  NextSet.id as id,
  NextSet.key as key,
  NextSet.date as date,
  NextSet.value as value,
  (
selectSelf.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
  fromNextSet
  where   exists
  (
select  1
fromDataSet
where   DataSet.key = NextSet.key
and DataSet.date = NextSet.next_date
and DataSet.value > NextSet.value
  )
)
selectNextSet.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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 consecutive rises in value

[Bart Smissaert]

Thanks, will try that as well.

RBS

On Thu, Oct 20, 2016 at 12:16 AM, Petite Abeille 
wrote:

>
> > On Oct 20, 2016, at 12:23 AM, Bart Smissaert 
> 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.*,
>   (
> selectSelf.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
>   fromDataSet
>
>   union all
>   select  NextSet.id as id,
>   NextSet.key as key,
>   NextSet.date as date,
>   NextSet.value as value,
>   (
> selectSelf.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
>   fromNextSet
>   where   exists
>   (
> select  1
> fromDataSet
> where   DataSet.key = NextSet.key
> and DataSet.date = NextSet.next_date
> and DataSet.value > NextSet.value
>   )
> )
> selectNextSet.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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
wrote:

>
> > On Oct 20, 2016, at 1:58 AM, Bart Smissaert 
> 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.*,
>   (
> selectSelf.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
>   fromDataSet
>
>   union all
>   select  NextSet.key as key,
>   NextSet.date as date,
>   NextSet.value as value,
>   (
> selectSelf.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
>   fromNextSet
>   where   exists
>   (
> select  1
> fromDataSet
> where   DataSet.key = NextSet.key
> and DataSet.date = NextSet.next_date
> and DataSet.value < NextSet.value
>   )
> )
> selectNextSet.key
> from  NextSet
> where NextSet.count = 3
>
> group by  NextSet.key
>
> order by  1;
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
wrote:

>
> > On Oct 19, 2016, at 11:40 PM, Bart Smissaert 
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 consecutive rises in value

[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 
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  > wrote:
>
>>
>> > On Oct 19, 2016, at 11:40 PM, Bart Smissaert 
>> 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
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 consecutive rises in value

[Petite Abeille]

> On Oct 20, 2016, at 1:58 AM, Bart Smissaert  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.*,
  (
selectSelf.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
  fromDataSet

  union all
  select  NextSet.key as key,
  NextSet.date as date,
  NextSet.value as value,
  (
selectSelf.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
  fromNextSet
  where   exists
  (
select  1
fromDataSet
where   DataSet.key = NextSet.key
and DataSet.date = NextSet.next_date
and DataSet.value < NextSet.value
  )
)
selectNextSet.key
from  NextSet
where NextSet.count = 3

group by  NextSet.key

order by  1;

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 consecutive rises in value

[Cezary H. Noweta]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Introducing Bedrock: SQLite for the Enterprise

[Stephen Chrzanowski]

So am I to understand you just "server-ized" SQLite with Bedrock?

Looks rather interesting.  I was just talking to my IT manager about how I
can take something like a SQLite backup and put it somewhere else so it'll
eventually get to tape, but, if I run a node on my local machine, run one
on the 'primary' and another on the 'backup' of the primary, it'd satisfy
quorum, and I'd be thinking less of backups.  (But its still a thought)

Is there a way to disable the check for quorum and either let the split
brain happen, or at least make the executive decision to which is the
primary data source at all times?  In the tool I'll be writing, it COULD
happen that only one node would be available.  We typically run our servers
here at work in primary/secondary fashion, no tertiary, so if the primary
goes away, and its only the secondary, then my software would go down,
which is something I obviously want to avoid.  We also do typically one-way
replication.

Is there a mechanism that will allow me to run the Backup API to dump the
database on a particular node?


On Tue, Oct 18, 2016 at 8:45 PM, David Barrett 
wrote:

> Love SQLite?  Wish you could use it to power your enterprise SaaS or web
> service? Now you can!  Check out Expensify Bedrock, our distributed
> transaction layer built atop SQLite, powering Expensify's millions of
> users.  More information is here:
>
> http://bedrockdb.com
>
>
> Keep all the power and simplicity of SQLite, but wrapped in a package that
> provides network accessibility, WAN-optimized replication, and distributed
> ACID transactions.  Under continuous development and operation for the past
> 8 years, now it's open sourced and ready for your production use.
>
> Thank you to the SQLite team for not only producing such an incredible
> database, but helping with our countless questions and demanding
> requirements.  I'm ecstatic to share this with you, and I hope you enjoy it
> too!
>
> -david
> Founder and CEO of Expensify
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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_dateINT_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 -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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bart Smissaert
Gesendet: Mittwoch, 19. Oktober 2016 16:53
An: General Discussion of SQLite Database 
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_dateINT_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 -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
sqlite-users@mailinglists.sqlite.org
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: h...@scigames.at

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 10 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  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:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Bart Smissaert
> Gesendet: Mittwoch, 19. Oktober 2016 16:53
> An: General Discussion of SQLite Database  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_dateINT_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 -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
> sqlite-users@mailinglists.sqlite.org
> 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: h...@scigames.at
>
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users