CODEWITHSUNDEEP
mysqlsql
astrakid932
astrakid932

Reputation: 3405

Find pattern amongst Mysql entries

I need to find the number of records that are greater than a specific float and find the group of data that repeat the most.

For example, I have the data below and I need to find how many entries have values > 4. 

    1.5
    1.7
    4.5
    4.7
    4.8
    1.4
    4.5
    4.9

In the above data the longest continuous repetition of values greater than 4 is 4.5,4.7,4.8.

Therefore the total I would like returned should be 3.

As you can see the pattern breaks after 4.8 since the number is 1.4 above.

Is there a way to identify this pattern?

Upvotes: 1

Views: 37

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

did you mean some thing like this ?

SELECT count(*) AS nr, IF(num >4,1,0) AS inRange,
@grpnr := IF (@lastval = @lastval:= IF(num >4,1,0),@grpnr,@grpnr+1 )  AS grpnr,
n.* , GROUP_CONCAT(n.num) AS numbers
FROM mynum n
CROSS JOIN ( SELECT @grpnr:=0, @lastval:=0) AS parameter  
GROUP BY grpnr
ORDER BY id;

Sample

MariaDB [who]> SELECT * from mynum;
+----+------+
| id | num  |
+----+------+
|  1 |  1.5 |
|  2 |  1.7 |
|  3 |  4.5 |
|  4 |  4.7 |
|  5 |  4.8 |
|  6 |  1.4 |
|  7 |  4.5 |
|  8 |  4.9 |
+----+------+
8 rows in set (0.00 sec)

MariaDB []> SELECT count(*) AS nr, IF(num >4,1,0) AS inRange,
    -> @grpnr := IF (@lastval = @lastval:= IF(num >4,1,0),@grpnr,@grpnr+1 )  AS grpnr,
    -> n.* , GROUP_CONCAT(n.num) AS numbers
    -> FROM mynum n
    -> CROSS JOIN ( SELECT @grpnr:=0, @lastval:=0) AS parameter
    -> GROUP BY grpnr
    -> ORDER BY id;
+----+---------+-------+----+------+-------------+
| nr | inRange | grpnr | id | num  | numbers     |
+----+---------+-------+----+------+-------------+
|  2 |       0 | 0     |  1 |  1.5 | 1.5,1.7     |
|  3 |       1 | 1     |  3 |  4.5 | 4.5,4.7,4.8 |
|  1 |       0 | 2     |  6 |  1.4 | 1.4         |
|  2 |       1 | 3     |  7 |  4.5 | 4.5,4.9     |
+----+---------+-------+----+------+-------------+
4 rows in set (0.01 sec)

MariaDB []>

Upvotes: 1

Related Questions