Re: [sqlite] 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 <
jean-luc.hain...@unamur.be> 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
> 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

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

Re: [sqlite] 2 consecutive rises in value

[Bart Smissaert]

> 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
> andT1.Id = T2.Id and T3.Id + T1.Id   -- same Id
> andT1.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 <
jean-luc.hain...@unamur.be> 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
> andT1.Id = T2.Id and T3.Id + T1.Id   -- same Id
> andT1.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
> 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]

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
5 rows.

RBS

On Thu, Oct 20, 2016 at 4:55 PM, Jean-Luc Hainaut <
jean-luc.hain...@unamur.be> 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
> andT1.Id = T2.Id and T3.Id + T1.Id   -- same Id
> andT1.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
> 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

[Jean-Luc Hainaut]

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
andT1.Id = T2.Id and T3.Id + T1.Id   -- same Id
andT1.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
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] 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

[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]

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]

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 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]

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 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

[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

[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]

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

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