检索每n行的最高值
16lz
2021-01-22
I have a table where each row contains a value and a datetime. It has hundreds of thousands of rows. I would like to select the highest (max) value every n rows.
我有一个表,其中每行包含一个值和一个日期时间。它有数十万行。我想每n行选择最高(最大)值。
I had previously used a query to get the highest value every hour, but this isn't quite what I am looking for:
我之前使用过查询来获得每小时最高的值,但这不是我想要的:
SELECT datetime, MAX(value)
FROM `table`
GROUP BY date_format(datetime, '%Y-%m-%d &h')
Any advice would be greatly appreciated!
任何建议将不胜感激!
1 个解决方案
#1
1
You can enumerate the rows and then aggregate to your heart's desire:
您可以枚举行然后聚合到您心中的愿望:
select min(rn), max(rn), min(datetime), max(datetime), max(value)
from (select t.*, (@rn := @rn + 1) rn
from `table` t cross join
(select @rn := 0) params
order by datetime
) t
group by floor((rn - 1) / @n)
order by min(rn);
更多相关文章
- 寻找解决方案,以每100速率1显示div随机而不创建其他div
- 等待执行所有ajax回调的最佳解决方案
- 【问题解决方案】ImportError: No module named 'pygal'
- Python 黏包及黏包解决方案
- 无法安装ndg-httpsclient或者我的解决方案错误
- Linux无法连接网络解决方案
- Media-S 简介(一个开源的DRM解决方案)
- AppScan安全问题解决方案
- PLSQL乱码解决方案