> On Oct 20, 2016, at 1:58 AM, Bart Smissaert <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
