The most efficient query to select min elements from table and date_format displayed

Question

I've one table with 1 million of rows and I want to get the rows with min date and formatted by day.

My table is:

Id  Created_at          Value
1   2019-04-08 10:35:32 254
1   2019-04-08 10:31:23 241
1   2019-04-08 11:47:32 258
2   2019-04-08 10:32:42 276
2   2019-04-08 10:34:23 280
2   2019-04-08 11:34:23 290

And I would like to get (the min created_at values for each hour and format by hour):

Id  Created_at          Value
1   2019-04-08 10:00:00 241
1   2019-04-08 11:00:00 258
2   2019-04-08 10:00:00 276
2   2019-04-08 11:00:00 290

I have mysql 5.7 so I can't build windowed queries. I'm researching the most efficient way to select this elements.

Why do you want `258`? That not the minimum value. – The Impaler Apr 08 '19 at 18:29 — The Impaler, Apr 08 '19 at 18:29
Oups, I want the most recent value for each hour – kassbonbek Apr 08 '19 at 18:30 — kassbonbek, Apr 08 '19 at 18:30

ScaisEdge · Answer 1 · 2019-04-08T18:49:50.253

1

In mysql 5.7

You can use a join on subquery for min result

select  m.id, date(m.created_at) , m.value
INNER JOIN (
select  min(created_at) min_date
from  my_tbale  
group by date(created_at),hour(created_at)

) t on t.min_date  = m.created_at

be sure you have a composite index on my_table columns (created_at, id, value)

edited Apr 08 '19 at 18:49

answered Apr 08 '19 at 18:29

ScaisEdge

131,976
10
91
107

score 1 · Answer 2 · answered Apr 08 '19 at 18:37

I would do something like:

select
  t.id, m.h, t.value
from my_table t
join (
  select
    id,
    from_unixtime(floor(unix_timestamp(created_at) / 3600) * 3600) as h,
    min(created_at) as min_created_at
  from my_table
  group by id, from_unixtime(floor(unix_timestamp(created_at) / 3600) * 3600)
) m on m.id = t.id and m.min_created_at = t.created_at

The most efficient query to select min elements from table and date_format displayed

2 Answers2