select main.*, tmp_first.v as `first`, tmp_last.v as `last`
from (
SELECT DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00') AS `t`, MIN(tt.v) AS `min`, MAX(tt.v) AS `max`
FROM test_time tt
GROUP BY DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00')
) main
left join (
select t as first_t, v from test_time where t in (select min(`t`) from test_time group by DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00'))
) as tmp_first on DATE_FORMAT(main.t, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(tmp_first.first_t, '%Y-%m-%d %H:%i:00')
left join (
select t as last_t, v from test_time where t in (select max(`t`) from test_time group by DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00'))
) as tmp_last on DATE_FORMAT(main.t, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(tmp_last.last_t, '%Y-%m-%d %H:%i:00')set @current_price = null, @period = 0, @prev_start_date = null;
select min(t.start_date) as start_date, max(t.end_date) as end_date, t.price
from (
select
@prev_start_date,
start_date,
(@prev_start_date - interval 1 day) as end_date,
@prev_start_date := start_date,
@current_price,
price,
if (@current_price <=> price, @period, if(@current_price := price, @period := @period + 1, null)) as period
from test_test
where true
order by start_date desc
) t
group by t.price, t.period
order by start_date desc;