结果集行平均数

Question

我有一张带宽利用率数据表。每一行都有一个主机名、尼卡名、利用率和时间戳。在每个主机的最大时间戳上，可以有不同的网卡。因此，对于每个主机，我希望在最大时间戳时平均使用不同的NIC卡。

下面是我的表结构，插入和查询-

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


INSERT INTO bandwith_utilization
VALUES (1,'host1','111111','nic1',40);
INSERT INTO bandwith_utilization
VALUES (2,'host1','111112','nic1',50);
INSERT INTO bandwith_utilization
VALUES (3,'host1','111113','nic1',50);
INSERT INTO bandwith_utilization
VALUES (4,'host1','111113','nic2',70);

INSERT INTO bandwith_utilization
VALUES (5,'host2','111111','nic1',80);
INSERT INTO bandwith_utilization
VALUES (6,'host2','111112','nic1',20);
INSERT INTO bandwith_utilization
VALUES (7,'host2','111112','nic2',30);

INSERT INTO bandwith_utilization
VALUES (8,'host3','111115','nic1',10);

这是我插入后的桌子-

id  hostname    timestamp   niccardname     percentageutilization
------------------------------------------------------------------
1;  "host1";    111111;     "nic1";         40
2;  "host1";    111112;     "nic1";         50
3;  "host1";    111113;     "nic1";         50
4;  "host1";    111113;     "nic2";         70

5;  "host2";    111111;     "nic1";         80
6;  "host2";    111112;     "nic1";         20
7;  "host2";    111112;     "nic2";         30

8;  "host3";    111115;     "nic1";         10

我有一个查询，它给出了最大时间戳的主机名输出-

select hostname, timestamp, niccardname, percentageutilization
from report.bandwith_utilization
 where timestamp = (select max(timestamp)
                    from report.bandwith_utilization nwUtil
                    where nwUtil.hostname = report.bandwith_utilization.hostname
                   ) ;  

上述查询的输出如下：

"host1";  111113; "nic1"; 50
"host1";  111113; "nic2"; 70

"host2";  111112; "nic1"; 20
"host2";  111112; "nic2"; 30

"host3";  111115; "nic1"; 10

所以现在我的预期输出是每台主机平均使用不同网卡的百分比。即

"host1";  111113; "nic1"; 60
"host2";  111112; "nic1"; 25
"host3";  111115; "nic1"; 10

如何在前面提到的相同查询中找到最终平均输出？

Answer 1

应该是一个AVG()和组

select hostname,timestamp,min(niccardname), avg(percentageutilization )
from report.bandwith_utilization
where (timestamp,hostname, niccardname)   in (select max(timestamp) ,hostname, niccardname
from report.bandwith_utilization nwUtil 
where nwUtil.hostname= report.bandwith_utilization.hostname
group by  hostname, niccardname
) 
group by  hostname,timestamp
order by  hostname,timestamp

Answer 2

以下是获取最大时间戳行的更好方法：

select u.*
from (select u.*,
             rank() over (partition by hostname order by timestamp desc) as seqnum
      from report.bandwith_utilization u
     ) u
where seqnum = 1;

现在，你可以得到你想要的：

select u.hostname, u.timestamp, avg(percentageutilization)
from (select u.*,
             rank() over (partition by hostname order by timestamp desc) as seqnum
      from report.bandwith_utilization u
     ) u
where seqnum = 1
group by u.hostname, u.timestamp;

(对我来说)在结果集中包括niccardname是没有意义的。如果您想要一个值，则可以使用min(niccardname)；如果要在数组中使用所有值，则可以使用array_agg(niccardname)。