CODEWITHSUNDEEP
mysqltrendforex
user1974442
user1974442

Reputation: 49

select range when value is higher than in previous value

How to get all records where value is higher than previous value. For example, first range in the table below start on id 1 and ends on id 6, next range is from 7 to 10, etc...

id  Open
1   1.30077
2   1.30088
3   1.30115
4   1.30132
5   1.30135
6   1.30144
7   1.30132
8   1.30137
9   1.30152
10  1.30158
11  1.30149
12  ...

Upvotes: 5

Views: 2303

Answers (3)

Wrikken
Wrikken

Reputation: 70500

SELECT startid,MAX(id) FROM (
    SELECT 
       @currentid := IF(@previous <= open,@currentid,id) as startid,
       @previous := open,
       id
    FROM ranges 
    JOIN (SELECT @currentid := MIN(id), @previous := MIN(open) FROM ranges) as variables
    ORDER BY id) runningscan
GROUP BY startid ORDER BY startid + 0;

See this on SQLFiddle: http://sqlfiddle.com/#!2/e3cea/3

What this does is: in the runningscan subquery, it runs through the table once, keeping taps if open is bigger or smaller then the previous open (stored in the @previous variable. This gets you a list with all ids, and the id's which started an interrupted higher (or equal) 'run'. From that, we only need to find the highest id for a starting id, so we put it in a subquery for an easy max construct. Gaps in the id column are no problem. If you a single row can't be a range (i.e: open lowers twice or more in succession), add a WHERE startid < id clause in the outer query. If you need ranges of a minimum number of rows to qualify is a range of more then 1, add a HAVING COUNT(*) > your_desired_minimum.

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

Ranges can be numbered using this query:

SELECT id, open, range_number
FROM( 
  SELECT *,
         if(@lastopen<open,@grp,@grp:=@grp+1) range_number,
         @lastopen:=open
  FROM table1,
  (select @lastopen:=null,@grp:=0) qqq
  ORDER BY id
) qqq;

Demo: http://www.sqlfiddle.com/#!2/b1bb8/9

| ID |           OPEN | RANGE_NUMBER |
|----|----------------|--------------|
|  1 | 1.300770044327 |            1 |
|  2 | 1.300879955292 |            1 |
|  3 | 1.301149964333 |            1 |
|  4 | 1.301319956779 |            1 |
|  5 |  1.30134999752 |            1 |
|  6 | 1.301440000534 |            1 |
|  7 | 1.301319956779 |            2 |
|  8 | 1.301370024681 |            2 |
|  9 | 1.301519989967 |            2 |
| 10 |  1.30157995224 |            2 |
| 11 | 1.301489949226 |            3 |

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

YOUR SAMPLE DATA

USE test
DROP TABLE IF EXISTS rangedata;
CREATE TABLE rangedata
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  open FLOAT
) ENGINE=MyISAM;
INSERT INTO rangedata (open) VALUES
(1.30077),(1.30088),(1.30115),(1.30132),
(1.30135),(1.30144),(1.30132),(1.30137),
(1.30152),(1.30158),(1.30149),
(1.30077),(1.30088),(1.30115),(1.30132),
(1.30135),(1.30144),(1.30132),(1.30137),
(1.30152),(1.30158),(1.30149),
(1.30077),(1.30088),(1.30115),(1.30132),
(1.30135),(1.30144),(1.30132),(1.30137),
(1.30152),(1.30158),(1.30149);

YOUR SAMPLE DATA LOADED

mysql>     USE test
Database changed
mysql>     DROP TABLE IF EXISTS rangedata;
Query OK, 0 rows affected (0.01 sec)

mysql>     CREATE TABLE rangedata
    ->     (
    ->       id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->       open FLOAT
    ->     ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.09 sec)

mysql>     INSERT INTO rangedata (open) VALUES
    ->     (1.30077),(1.30088),(1.30115),(1.30132),
    ->     (1.30135),(1.30144),(1.30132),(1.30137),
    ->     (1.30152),(1.30158),(1.30149),
    ->     (1.30077),(1.30088),(1.30115),(1.30132),
    ->     (1.30135),(1.30144),(1.30132),(1.30137),
    ->     (1.30152),(1.30158),(1.30149),
    ->     (1.30077),(1.30088),(1.30115),(1.30132),
    ->     (1.30135),(1.30144),(1.30132),(1.30137),
    ->     (1.30152),(1.30158),(1.30149);
Query OK, 33 rows affected (0.00 sec)
Records: 33  Duplicates: 0  Warnings: 0

mysql>

QUERY USING JOINS

Here is the LEFT JOIN query

SET @grp = 1;
SELECT A.open prev,(@grp:=@grp+IF(A.open<B.open,1,0)) group_number
FROM rangedata A LEFT JOIN rangedata B ON A.id= B.id+1;

Here is its output

mysql> SELECT A.open prev,(@grp:=@grp+IF(A.open<B.open,1,0)) group_number
    -> FROM rangedata A LEFT JOIN rangedata B ON A.id= B.id+1;
+---------+--------------+
| prev    | group_number |
+---------+--------------+
| 1.30088 |            1 |
| 1.30115 |            1 |
| 1.30132 |            1 |
| 1.30135 |            1 |
| 1.30144 |            1 |
| 1.30132 |            2 |
| 1.30137 |            2 |
| 1.30152 |            2 |
| 1.30158 |            2 |
| 1.30149 |            3 |
| 1.30077 |            4 |
| 1.30088 |            4 |
| 1.30115 |            4 |
| 1.30132 |            4 |
| 1.30135 |            4 |
| 1.30144 |            4 |
| 1.30132 |            5 |
| 1.30137 |            5 |
| 1.30152 |            5 |
| 1.30158 |            5 |
| 1.30149 |            6 |
| 1.30077 |            7 |
| 1.30088 |            7 |
| 1.30115 |            7 |
| 1.30132 |            7 |
| 1.30135 |            7 |
| 1.30144 |            7 |
| 1.30132 |            8 |
| 1.30137 |            8 |
| 1.30152 |            8 |
| 1.30158 |            8 |
| 1.30149 |            9 |
| 1.30077 |            9 |
+---------+--------------+
33 rows in set (0.01 sec)

QUERY WITHOUT JOINS

Using user-defined variables, you simply monitor each row and see when the previous value is greater. Ready for the query? Here it is:

SET @prev = '0.00000';
SET @grp = 1;
SELECT id,open,(@grp:=@grp+increasing) group_number FROM
(SELECT id,open,IF(@prev<=open,0,1) increasing,(@prev:=open) FROM rangedata) A;

Here is your sample data tripled:

Here is the query's execution:

mysql> SET @prev = '0.00000';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @grp = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id,open,(@grp:=@grp+increasing) group_number FROM
    -> (SELECT id,open,IF(@prev<=open,0,1) increasing,(@prev:=open) FROM rangedata) A;
+----+---------+--------------+
| id | open    | group_number |
+----+---------+--------------+
|  1 | 1.30077 |            1 |
|  2 | 1.30088 |            1 |
|  3 | 1.30115 |            1 |
|  4 | 1.30132 |            1 |
|  5 | 1.30135 |            1 |
|  6 | 1.30144 |            1 |
|  7 | 1.30132 |            2 |
|  8 | 1.30137 |            2 |
|  9 | 1.30152 |            2 |
| 10 | 1.30158 |            2 |
| 11 | 1.30149 |            3 |
| 12 | 1.30077 |            4 |
| 13 | 1.30088 |            4 |
| 14 | 1.30115 |            4 |
| 15 | 1.30132 |            4 |
| 16 | 1.30135 |            4 |
| 17 | 1.30144 |            4 |
| 18 | 1.30132 |            5 |
| 19 | 1.30137 |            5 |
| 20 | 1.30152 |            5 |
| 21 | 1.30158 |            5 |
| 22 | 1.30149 |            6 |
| 23 | 1.30077 |            7 |
| 24 | 1.30088 |            7 |
| 25 | 1.30115 |            7 |
| 26 | 1.30132 |            7 |
| 27 | 1.30135 |            7 |
| 28 | 1.30144 |            7 |
| 29 | 1.30132 |            8 |
| 30 | 1.30137 |            8 |
| 31 | 1.30152 |            8 |
| 32 | 1.30158 |            8 |
| 33 | 1.30149 |            9 |
+----+---------+--------------+
33 rows in set (0.00 sec)

The key point is this: Every time a new group number appears, that tells you the the next value dropped.

PLEASE NOTE THE OUTPUT IS IDENTICAL FOR BOTH QUERIES

CAVEAT: The second query is not a perfect solution in case there is some floating point issues between prev and open. If these are ridiculously close to each other, it may not be right. This was best try outside of writing a stored procedure.

Upvotes: 2

Related Questions