CODEWITHSUNDEEP
sqlsubtraction
arturas
arturas

Reputation: 1136

Subtract. SQL daily value (today value - yesterday value)

Friends, I have a database, where i keep all users traffic. It updates each day. I want to count a traffic per day. It means: traffic today - traffic yesterday?

There are three tables (down, up, remoteid). There are the results:

select rem.remoteid, down.mazgas, down.portas, down.down, up.up, down.date
from dbo.remoteid as rem
inner join dbo.down as down on down.mazgas=rem.mazgas and down.portas = rem.portas
inner join dbo.up as up on up.mazgas=down.mazgas  and up.portas = down.portas    
where down.mazgas=up.mazgas and down.portas=up.portas and down.date= up.date

result

remoteid         mazgas             portas           down    up       date
10156529    10.199.100.27   gpon-onu_1/12/5:1   2678.0  69963.9 2014-06-01
10156529    10.199.100.27   gpon-onu_1/12/5:1   2643.8  68912.3 2014-05-31
29546232    10.203.100.1    gpon-onu_1/16/1:4   927.8   39273.6 2014-06-01
29546232    10.203.100.1    gpon-onu_1/16/1:4   923.1   39126.7 2014-05-31

I would like to get an answer:

remoteid, mazgas, portas, down, up  where down = (down(today) - down(yesterday)) and up= (up(today) - up(yesterday))

Thank You a lot.

Upvotes: 2

Views: 1399

Answers (1)

Frazz
Frazz

Reputation: 3043

Since you did not specify the RDBMS you are using I will try to use basic SQL. Something like this would give you the desired results:

SELECT
  r.remoteid,
  r.mazgas,
  r.portas,
  COALESCE(d2.down,0)-COALESCE(d1.down,0) AS down,
  COALESCE(u2.up  ,0)-COALESCE(u1.up  ,0) AS up,
  d2.date
FROM      dbo.remoteid AS r
JOIN      dbo.down     AS d2 ON d2.mazgas=r.mazgas AND d2.portas=r.portas
LEFT JOIN dbo.up       AS u2 ON u2.mazgas=r.mazgas AND u2.portas=r.portas AND u2.date=d2.date
LEFT JOIN dbo.down     AS d1 ON d1.mazgas=r.mazgas AND d1.portas=r.portas AND d1.date=(
   SELECT MAX(date)
   FROM dbo.down
   WHERE mazgas=r.mazgas AND portas=r.portas AND date<d2.date)
LEFT JOIN dbo.up       AS u1 ON u1.mazgas=r.mazgas AND u1.portas=r.portas AND u1.date=(
   SELECT MAX(date)
   FROM dbo.up
   WHERE mazgas=r.mazgas AND portas=r.portas AND date<u2.date)

Things to consider:

  1. COALESCE is not supported on all RDBMS. But there should be something to substitute it with (IFNULL or IF or IIF). As a last resort, you can fallback on a CASE WHEN which is supported by most. But the COALESCE or equivalent is important. You do have to consider NULLs in this join. You are joining download, uploads on 2 different dates... ignoring NULLs is asking for trouble.
  2. I opted for referring to the remoteid table for mazgas and portas. This seems more solid than using the down table, as I suppose the remoteid master record should always exist, and does not depend on dates.

This will not give you the difference with yesterday's value, but the difference with the last date that has values (same mazgas and portas). If all days have values, then the result is the same.

What you want can be done with some date algebra, like this simple -1 syntax:

SELECT
  r.remoteid,
  r.mazgas,
  r.portas,
  COALESCE(d2.down,0)-COALESCE(d1.down,0) AS down,
  COALESCE(u2.up  ,0)-COALESCE(u1.up  ,0) AS up,
  d2.date
FROM      dbo.remoteid AS r
JOIN      dbo.down     AS d2 ON d2.mazgas=r.mazgas AND d2.portas=r.portas
LEFT JOIN dbo.up       AS u2 ON u2.mazgas=r.mazgas AND u2.portas=r.portas AND u2.date=d2.date
LEFT JOIN dbo.down     AS d1 ON d1.mazgas=r.mazgas AND d1.portas=r.portas AND d1.date=d2.date-1
LEFT JOIN dbo.up       AS u1 ON u1.mazgas=r.mazgas AND u1.portas=r.portas AND u1.date=u2.date-1

... but different RDBMS may handle date algebra in other ways (they all have some function to add, subtract days though).

As far as efficiency goes... well you have better have indexes on mazgas, portas, date on both the down table and the up table ;)

Upvotes: 1

Related Questions