代码之家 › 专栏 › 技术社区 › martinarroyo

检索聚合和日期运算符上的其他列

greatest-n-per-group postgresql

martinarroyo · 技术社区 · 8 年前

我有以下PostgreSQL表结构,它每秒钟收集一次温度记录:

+----+--------+-------------------------------+---------+
| id | value  |             date              | station |
+----+--------+-------------------------------+---------+
|  1 |      0 | 2017-08-22 14:01:09.314625+02 |       1 |
|  2 |      0 | 2017-08-22 14:01:09.347758+02 |       1 |
|  3 | 25.187 | 2017-08-22 14:01:10.315413+02 |       1 |
|  4 | 24.937 | 2017-08-22 14:01:10.322528+02 |       1 |
|  5 | 25.187 | 2017-08-22 14:01:11.347271+02 |       1 |
|  6 | 24.937 | 2017-08-22 14:01:11.355005+02 |       1 |
| 18 | 24.875 | 2017-08-22 14:01:17.35265+02  |       1 |
| 19 | 25.187 | 2017-08-22 14:01:18.34673+02  |       1 |
| 20 | 24.875 | 2017-08-22 14:01:18.355082+02 |       1 |
| 21 | 25.187 | 2017-08-22 14:01:19.361491+02 |       1 |
| 22 | 24.875 | 2017-08-22 14:01:19.371154+02 |       1 |
| 23 | 25.187 | 2017-08-22 14:01:20.354576+02 |       1 |
| 30 | 24.937 | 2017-08-22 14:01:23.372612+02 |       1 |
| 31 |      0 | 2017-08-22 15:58:53.576238+02 |       1 |
| 32 |      0 | 2017-08-22 15:58:53.590872+02 |       1 |
| 33 | 26.625 | 2017-08-22 15:58:54.59986+02  |       1 |
| 38 | 26.375 | 2017-08-22 15:58:56.593205+02 |       1 |
| 39 |      0 | 2017-08-21 15:59:40.181317+02 |       1 |
| 40 |      0 | 2017-08-21 15:59:40.190221+02 |       1 |
| 41 | 26.562 | 2017-08-21 15:59:41.182622+02 |       1 |
| 42 | 26.375 | 2017-08-21 15:59:41.18905+02  |       1 |
+----+--------+-------------------------------+---------+

现在,我想检索每小时的最大值,以及与该条目相关的数据(id、日期)。因此,我尝试了以下方法:

select max(value) as m, (date_trunc('hour', date)) as d
from temperature
where station='1'
group by (date_trunc('hour', date));

这很好用( fiddle ),但结果只得到了m和d列。如果我现在尝试添加 date id SELECT 声明,我照常做 column "temperature.id" must appear in the GROUP BY clause or be used in an aggregate function

我已经尝试过上述方法 here ,不幸的是没有用,例如,我似乎无法在 date_trunc

我的目标是:

+----+--------+-------------------------------+---------+
| id | value  |             date              | station |
+----+--------+-------------------------------+---------+
|  3 | 25.187 | 2017-08-22 14:01:10.315413+02 |       1 |
| 33 | 26.625 | 2017-08-22 15:58:54.59986+02  |       1 |
| 41 | 26.562 | 2017-08-21 15:59:41.182622+02 |       1 |
+----+--------+-------------------------------+---------+

如果两个或多个条目具有相同的值,则检索哪个记录无关紧要。

1 回复 | 直到 8 年前

Clodoaldo Neto 8 年前

distinct on :

select distinct on (date_trunc('hour', date)) *
from temperature
where station = '1'
order by date_trunc('hour', date), value desc

Fiddle