分组依据已分组和聚合的数据 - Postgresql 9.3 及更高版本

Question

表 "A" 内容：

IP       | Port
----------------+-------
10.211.240.300 | 10
10.211.240.300 | 10
10.211.240.300 | 20
10.211.240.300 | 20
10.211.240.300 | 20
10.211.240.300 | 20
10.211.240.300 | 30
10.211.240.300 | 30
10.211.240.300 | 30
10.211.240.200 | 10
10.211.240.200 | 10
10.211.240.200 | 10
10.211.240.200 | 20
10.211.240.200 | 20
10.211.240.200 | 20
10.211.240.200 | 20
10.211.240.200 | 30
10.211.240.100 | 10
10.211.240.100 | 10
10.211.240.100 | 10
10.211.240.100 | 10
10.211.240.100 | 10
10.211.240.100 | 10
10.211.240.100 | 10
10.211.240.100 | 10
10.211.240.100 | 20
10.211.240.100 | 20
10.211.240.100 | 20
10.211.240.100 | 30
10.211.240.100 | 30
10.211.240.100 | 30
10.211.240.100 | 30
10.211.240.100 | 30

我需要对其计数对 IP 地址进行排序，并且还需要对其端口进行排序计数，因此上述数据的预期结果是：

IP      | Port    | PortCount  | TotalCount
----------------------------------------------
10.211.240.100 | 10      |  08        | 16
10.211.240.100 | 30      |  05        | 16
10.211.240.100 | 20      |  03        | 16
10.211.240.300 | 30      |  04        | 09
10.211.240.300 | 20      |  03        | 09
10.211.240.300 | 10      |  02        | 09
10.211.240.200 | 20      |  04        | 08
10.211.240.200 | 10      |  03        | 08
10.211.240.200 | 30      |  01        | 08

有人可以帮我进行相同的 postgres 关系查询吗？

Answer 1

SELECT A.IP, A.PORT, T2.PORT_COUNT, COUNT(*) 
FROM A JOIN (
SELECT T1.IP, COUNT(T1.PORT) AS PORT_COUNT
FROM A AS T1
GROUP BY T1.IP  
) AS T2
ON A.IP =  T2.IP
GROUP BY A.IP, A.PORT

我测试的在线数据库：http://sqlfiddle.com/#!9/23a78/6

Answer 2

如果您使用的是postgresql，请使用查询：-

SELECT a.IP, a.PORT,total_count.total,COUNT(*)
FROM a
JOIN (
SELECT b.IP, COUNT(b.PORT) AS total
FROM a AS b
GROUP BY b.IP  
) AS total_count
ON a.IP =  total_count.IP
GROUP BY a.IP, a.PORT,total_count.total
ORDER BY a.ip