根据最高时间戳选择每个主机名的利用率百分比

Question

我有一个cpu利用率数据表。每一行都有一个主机名、利用率百分比和时间戳。我想为特定主机选择最高时间戳的所有主机和cpu利用率。

下面是我的表格结构、插入和选择

CREATE TABLE cpu_utilization
(
  id integer NOT NULL,
  hostname character varying(255),
  "timestamp" bigint,
  cpuutilizationpercentage integer,
  CONSTRAINT cpu_utilization_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cpu_utilization
  OWNER TO postgres;


INSERT INTO cpu_utilization
VALUES (1,'host1','111111',10);
INSERT INTO cpu_utilization
VALUES (2,'host1','111112',12);
INSERT INTO cpu_utilization
VALUES (3,'host1','111113',15);

INSERT INTO cpu_utilization
VALUES (4,'host2','111111',20);
INSERT INTO cpu_utilization
VALUES (5,'host2','111112',22);
INSERT INTO cpu_utilization
VALUES (6,'host2','111113',25);

INSERT INTO cpu_utilization
VALUES (7,'host3','111111',10);

我正在执行这个select查询

select * from cpu_utilization where timestamp in ( select max(timestamp) from cpu_utilization group by hostname );

我得到的输出是-

id; hostname    timestamp   cpuutilizationpercentage
1;  "host1";    111111;     10
3;  "host1";    111113;     15
4;  "host2";    111111;     20
6;  "host2";    111113;     25
7;  "host3";    111111;     10

但我希望输出结果是-

id; hostname    timestamp   cpuutilizationpercentage
3;  "host1";    111113;     15
6;  "host2";    111113;     25
7;  "host3";    111111;     10

我的select查询出了什么问题？

Answer 1

谢谢@cha，我在id上做了错误的where子句，而应该是在hostname上。我用以下命令得到了想要的输出-

select * from cpu_utilization where timestamp = (select max(timestamp) from cpu_utilization cpuutil where cpuutil.hostname=cpu_utilization.hostname);