Question 275544 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL – Group By values across columns

I have something like a netflow table and would like to group it in such a way that it groups it by (src_ip, src_port, dst_ip, dst_port) where the values could be swapped between the src and dst fields.

src_ip src_port dst_ip dst_port bytes_sent
192.168.1.1 123 192.168.10.5 321 111
192.168.10.5 321 192.168.1.1 123 222
10.0.0.5 50 172.0.0.5 55 500
172.0.0.5 55 10.0.0.5 50 300
192.168.1.1 123 192.168.10.5 321 1000

I would like to have the following result from this table:

src_ip src_port dst_ip dst_port bytes_sent bytes_recv
192.168.1.1 123 192.168.10.5 321 1111 222
10.0.0.5 50 172.0.0.5 55 500 800

Basically, trying to capture the traffic going both ways in a single row. So something like grouping by (src_ip, src_port) and (dst_ip, dst_port) where those values could be inverted. What would be the best way to achieve this?

Tags:

2

Answers


  1. 0

    Assuming the smallest IP is the source IP, and the largest is the destination IP.

    You can use LEAST and GREATEST functions to ensures that for each combination of the smallest and largest IP addresses, one entry will be selected :

    select src.*, dst.bytes_sent as bytes_recv
from mytable src
inner join mytable dst on src.src_ip = dst.dst_ip and src.src_port = dst.dst_port
inner join (
  select least(src_ip, dst_ip) src_ip, greatest(src_ip, dst_ip) dst_ip
  from mytable src
  group by least(src_ip, dst_ip), greatest(src_ip, dst_ip)
) as s on s.src_ip = src.src_ip and s.dst_ip = src.dst_ip

    Demo here

    Login or Signup to reply.
  2. 0

    You can use a self-join:

    select n.src_ip, n.src_port, n1.src_ip, n1.src_port, n.bytes_sent, n.bytes_sent + n1.bytes_sent
from netflow n join netflow n1 on n1.src_ip = n.dst_ip where n.src_port < n.dst_port

    See fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search