如何在PostgreSQL中每个组（名称，col1）的最新collect_time上获得总和（值）？

奇异性

实际上，对于以下线程上的类似问题，我得到了很好的答案，但是我需要针对不同数据集的另一种解决方案。

数据集具有历史数据，我只想在最新的collect_time上获取该组的sum（value）。最终结果应如下：

 name  | col1 |     gather_time     | sum
-------+------+---------------------+-----
 first | 100  | 2016-01-01 23:12:49 |   6
 first | 200  | 2016-01-01 23:11:13 |   4

但是，我只能通过下面的查询看到一个组（前100个）的数据，这意味着第二组（前200个）没有数据。事情是，我需要每个小组一行。组的数量可以变化。

select name,col1,gather_time,sum(value) 
from testtable
group by name,col1,gather_time
order by gather_time desc
limit 2;

 name  | col1 |     gather_time     | sum
-------+------+---------------------+-----
 first | 100  | 2016-01-01 23:12:49 |   6
 first | 100  | 2016-01-01 23:11:19 |   6
(2 rows)

您能建议我完成这项要求吗？

资料集

create table testtable
(
name varchar(30),
col1 varchar(30),
col2 varchar(30),
gather_time timestamp,
value integer
);


insert into testtable values('first','100','q1','2016-01-01 23:11:19',2);
insert into testtable values('first','100','q2','2016-01-01 23:11:19',2);
insert into testtable values('first','100','q3','2016-01-01 23:11:19',2);
insert into testtable values('first','200','t1','2016-01-01 23:11:13',2);
insert into testtable values('first','200','t2','2016-01-01 23:11:13',2);
insert into testtable values('first','100','q1','2016-01-01 23:11:11',2);
insert into testtable values('first','100','q1','2016-01-01 23:12:49',2);
insert into testtable values('first','100','q2','2016-01-01 23:12:49',2);
insert into testtable values('first','100','q3','2016-01-01 23:12:49',2);

select * 
from testtable 
order by name,col1,gather_time;

 name  | col1 | col2 |     gather_time     | value
-------+------+------+---------------------+-------
 first | 100  | q1   | 2016-01-01 23:11:11 |     2
 first | 100  | q2   | 2016-01-01 23:11:19 |     2
 first | 100  | q3   | 2016-01-01 23:11:19 |     2
 first | 100  | q1   | 2016-01-01 23:11:19 |     2
 first | 100  | q3   | 2016-01-01 23:12:49 |     2
 first | 100  | q1   | 2016-01-01 23:12:49 |     2
 first | 100  | q2   | 2016-01-01 23:12:49 |     2
 first | 200  | t2   | 2016-01-01 23:11:13 |     2
 first | 200  | t1   | 2016-01-01 23:11:13 |     2

蒂姆·比格莱森（Tim Biegeleisen）

一种选择是你原来的表连接到只包含有最新的记录的表gather_time的每个name，col1组。然后，您可以取value每个组的列总和以获得所需的结果集。

SELECT t1.name, t1.col1, MAX(t1.gather_time) AS gather_time, SUM(t1.value) AS sum
FROM testtable t1 INNER JOIN
(
    SELECT name, col1, col2, MAX(gather_time) AS maxTime
    FROM testtable
    GROUP BY name, col1, col2
) t2
ON t1.name = t2.name AND t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND
    t1.gather_time = t2.maxTime
GROUP BY t1.name, t1.col1

如果要在WHERE子句中使用子查询（如在OP中尝试的那样）以将记录限制为仅包含最新记录，gather_time则可以尝试以下操作：

SELECT name, col1, gather_time, SUM(value) AS sum
FROM testtable t1
WHERE gather_time =
(
    SELECT MAX(gather_time) 
    FROM testtable t2
    WHERE t1.name = t2.name AND t1.col1 = t2.col1
)
GROUP BY name, col1

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。