如何选择一天中每小时的最新记录 [英] How to select the latest record of each hour in a day
本文介绍了如何选择一天中每小时的最新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想基于datetime列"reading_on"选择给定日期中每个小时的最新记录.我已经执行了以下查询
I want to select the latest record of each hour in a given date, based on the datetime column 'reading_on'. I have executed the below query
hourly_max = InverterReading
.where("DATE(reading_on) = ? AND imei = ?", Date.today, "770000000000126")
.group("HOUR(reading_on)")
.having("max(HOUR(reading_on))")
hourly_max.group_by(&:id).each { |k,v| puts v.last.reading_on }
在上面的查询中,我没有得到所需的结果.选择一天中每个小时的最新记录的正确方法是什么?下面是表结构
In the above query I am not getting the required result. What is the proper way to select the latest record of each hour in a day. Below is the table structure
推荐答案
SELECT
HOUR(a.reading_on) As hr, max(a.id),a.reading_on,
date_format(a.reading_on,'%j-%Y-%k')
FROM
InverterReadings a
LEFT JOIN
InverterReadings b
ON
date_format(a.reading_on,'%j-%Y-%k') = date_format(b.reading_on,'%j-%Y-%k')
AND
a.reading_on < b.reading_on
WHERE
b.reading_on is null
group by a.reading_on;
这篇关于如何选择一天中每小时的最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文