我正在使用PostgreSQL,这是我的表measurement_archive
:
+-----------+------------------------+------+-------+
| sensor_id | time | type | value |
+-----------+------------------------+------+-------+
| 123 | 2017-11-26 01:53:11+00 | PM25 | 34.32 |
+-----------+------------------------+------+-------+
| 123 | 2017-11-26 02:15:11+00 | PM25 | 32.1 |
+-----------+------------------------+------+-------+
| 123 | 2017-11-26 04:32:11+00 | PM25 | 75.3 |
+-----------+------------------------+------+-------+
我需要一个查询,该查询将从指定的时间范围内获取记录(例如,从2017-01-01-01 00:00:00到2017-12-01 23:59:59),然后检查是否每小时在至少有1个记录 - 如果有,则将1添加到结果。
因此,如果我从2017-11-26 01:00到2017-11-26 04:59:59:59 00的查询,对于sensor_id
== 123
上表上表,则结果应为3
。<<<<<<<<<<<</p>
select count(*)
from (
select date_trunc('hour', time) as time
from measurement_archive
where
time >= '2017-11-26 01:00:00' and time < '2017-11-26 05:00:00'
and
sensor_id = 123
group by 1
) s
替代解决方案将使用不同的解决方案,
select count(*) from (select distinct a, extract(hour from time) from t where time >'2017-11-26 01:00:11' and time <'2017-11-26 05:00:00' and sensor_id=123)t;