CODEWITHSUNDEEP
mysqlsqldatetimesubquery
Andres J
Andres J

Reputation: 23

MySQL: Getting the most counted same-value entry (statistical mode) per hour within a datetime range

I have a table like this:

+--------+---------+----------------------+--------------+----------+
| idadata | value_r  | date_r               | idparameter  | idnode  |
+--------+-----------+-----------------------+--------------+--------+
| 54620   | 66.6627  | 2014-10-16 12:01:09  |    46         |   9    |
| 54621   | 19.4953  |2014-10-16 12:01:09   |    40         |   9    |
| 54622   | 19.9384  |2014-10-16 12:01:09   |    47         |   9    |
| 54623   | 163.849  | 2014-10-16 12:01:09  |    43         |   9    |
| 54624   | 67.9257  | 2014-10-16 12:02:09  |    44         |   9    |
| 54625   | 315      | 2014-10-16 12:02:09  |    42         |   9    |
| 54626   | 0.699    | 2014-10-16 12:02:09  |    41         |   9    |
| 54627   | 67.9257  | 2014-10-16 12:03:09  |    46         |   9    |
| 54628   | 19.2308  | 2014-10-16 12:03:09  |    40         |   9    |
| 54629   | 11.207   | 2014-10-16 12:03:09  |    47         |   9    |
| 54630   | 118.743  | 2014-10-16 12:03:09  |    43         |   9    |
| 54631   | 292.5    | 2014-10-16 12:03:09  |    42         |   9    |
+---------+----------+----------------------+---------------+-------+

I need to get the statistical mode or the value_r that repeats the most for a given idparameter and idnode in a given datime interval each hour. I have managed to get the mode when I set the datetime difference for 1 hour manually. However, when I try to group by hour or time difference it doesn't work and I end up with mode of the whole Start-End datetime and not group by hours.

So far this is my code:

select    value_r , date_r , max(counter_v) from
(SELECT   iddata,  value_r,date_r ,count( value_r ) counter_v 
FROM wsnca.data  dat
where dat.idnode=9 and dat.idparameter=42 and 
( dat.date_r between ('2014-10-16 12:00:00') and ('2014-10-16 13:00:00') )
group by value_r  
 order by counter_v  DESC) T;

Result:

+----------+----------------------+---------------+
| value_r   | date_r               | max(counter_v)|
+-----------+----------------------+--------------+
| 270       | 2014-10-16 12:03:09  |    7          |
+-----------+-----------------------+--------------+

However, the result I'm looking for would be like this:

+----------+----------------------+---------------+
| value_r   | date_r               | max(counter_v)|
+-----------+----------------------+--------------+
| 270       | 2014-10-16 12:00:00  |    7          |
+-----------+-----------------------+--------------+
| 90        | 2014-10-16 13:00:00  |    4          |
+-----------+-----------------------+--------------+
| 45        | 2014-10-16 14:00:00  |    9          |
+-----------+-----------------------+--------------+
| 180       | 2014-10-16 15:00:00  |    8          |
+-----------+-----------------------+--------------+

As I said before, I don't know how to group that by one hour time interval and reading from the query round at the hour datetime as in the desired table.

I know I could do it in the PHP doing several queries but would prefer to do it in the one query.

Upvotes: 2

Views: 76

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32402

You can number the count for each value_r per hour starting with #1 for the highest count, #2 for the 2nd highest and so on and then only keep #1 rows, which will be the modes for each hour.

select date_hour, value_r, cnt from (
    select * , 
        @rowNum := IF(date_hour = @prevDateHour,@rowNum+1,1) rowNum,
        @prevDateHour := date_hour
    from (
        select value_r, hour(date_r) date_hour, count(*) cnt
        from wsnca.data dat
        where dat.idnode=9 and dat.idparameter=42
        group by value_r, hour(date_r)
    ) t1 order by date_hour, cnt desc
) t1 where rowNum = 1

Upvotes: 1

sergio0983
sergio0983

Reputation: 1288

change group by value_r into group by value_r, date_r I think that should make it

EDIT Better Response for what you want to achieve

select    value_r , DATE_FORMAT(date_r, '%Y-%m-%d %H') as formatted_date, max(counter_v) from
(SELECT   iddata,  value_r,date_r ,count( value_r ) counter_v 
FROM wsnca.data  dat
where dat.idnode=9 and dat.idparameter=42 and 
( dat.date_r between ('2014-10-16 12:00:00') and ('2014-10-16 13:00:00') )
group by value_r, formatted_date
 order by counter_v  DESC) T

Upvotes: 0

Related Questions