CODEWITHSUNDEEP
sqlpostgresql
Foad Tahmasebi
Foad Tahmasebi

Reputation: 1352

How to calculate bandwidth by SQL query

I have a table like this:

-----------+------------+-------
 first     | last       | bytes
-----------+------------+-------
1441013602 | 1441013602 | 10
-----------+------------+-------
1441013602 | 1441013603 | 20
-----------+------------+-------
1441013603 | 1441013605 | 30
-----------+------------+-------
1441013610 | 1441013612 | 30

which

How can I calculate bandwidth usage for each second from 1441013602 to 1441013612 ?

I want this:

1441013602   20 B/s
1441013603   20 B/s
1441013604   10 B/s
1441013605   10 B/s
1441013606    0 B/s
1441013607    0 B/s
1441013608    0 B/s
1441013609    0 B/s
1441013610   10 B/s
1441013611   10 B/s
1441013612   10 B/s

Upvotes: 4

Views: 1690

Answers (2)

dani herrera
dani herrera

Reputation: 51705

Here an approach at SQL Fiddle

PostgreSQL 9.3 Schema Setup:

create table t ( first int, last int, bytes int );

insert into t values 
(1441013602 , 1441013602 , 10 ),
(1441013602 , 1441013603 , 20 ),
(1441013603 , 1441013605 , 30 ),
(1441013610 , 1441013612 , 30 );

The query:

with

bytes as
       ( select first, last, 
              ( last - first ) as calc_time,
              bytes
         from t
         where ( last - first )>0 ),

bytes_per_second as 
       ( select first, last, bytes / calc_time as Bs
         from bytes ),

calc_interval as 
       ( SELECT * FROM generate_series(1441013602,1441013612) )

select 
       i.generate_series, bps.Bs
  from 
       calc_interval i 
  left outer join
       bytes_per_second bps 
          on i.generate_series between bps.first and bps.last - 1
 order by 
       i.generate_series

Results:

| generate_series |     bs |
|-----------------|--------|
|      1441013602 |     20 |
|      1441013603 |     15 |
|      1441013604 |     15 |
|      1441013605 | (null) |
|      1441013606 | (null) |
|      1441013607 | (null) |
|      1441013608 | (null) |
|      1441013609 | (null) |
|      1441013610 |     15 |
|      1441013611 |     15 |
|      1441013612 | (null) |

Explanation:

  • bytes and bytes_per_second are to cleaning data, perhaps is more accurate to do an average.
  • calc_interval a generator for your seconds.
  • last select is the final calculation. Also joining generated seconds with bandwidth.

Upvotes: 2

Craig Ringer
Craig Ringer

Reputation: 324751

You can use PostgreSQL's generate_series function for this. Generate a series of rows, one for each second, since that's what you want. Then left join on the table of info, so that you get one row for each second for each data flow. GROUP BY seconds, and sum the data flow bytes.

e.g:

SELECT seconds.second, coalesce(sum(t.bytes::float8 / (t.last::float8-t.first::float8+1)),0)
FROM generate_series(
  (SELECT min(t1.first) FROM Table1 t1),
  (SELECT max(t1.last) FROM Table1 t1)
) seconds(second)
LEFT JOIN table1 t
  ON (seconds.second BETWEEN t.first and t.last)
GROUP BY seconds.second
ORDER BY seconds.second;

http://sqlfiddle.com/#!15/b3b07/7

Note that we calculate the bytes per second of the flow, then sum that over the seconds of the flow across all flows. This only gives an estimate, since we don't know if the flow rate was steady over the flow duration.

For formatting the bytes, use the format function and/or pg_size_pretty.

Upvotes: 3

Related Questions