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