genegreen

School Traff Bad

Jul 15th, 2015
2,099
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with in_data as (select 6 as month, 2015 as year)
  2. select s.rtcom_id, s.etap, s.region, s.name, s.finance, period,
  3. sum(case when m = (select month from in_data) then rs.local_traf else 0 end) / 2 ^ 20,
  4. sum(case when m = (select month from in_data) then rs.notlocal_traf else 0 end)  / 2 ^ 20,
  5. sum(case when m = (select month from in_data) then rs.notlocal_traf + rs.local_traf else 0 end) / 2 ^ 20,
  6. sum(case when m <=(select month from in_data) then rs.local_traf+rs.notlocal_traf else 0 end)  / 2 ^ 20, ip
  7. from (select '8-14' as period, y as y, m as m, ip_num_to_str(ip)::inet as ip,
  8. sum(case when is_local = true then bytes else 0 end) as local_traf,
  9. sum(case when is_local = false then bytes else 0 end) as notlocal_traf
  10. from nf_statistic n
  11. where h between 8 and 14
  12. group by ip, y, m
  13. union select '14-8', y, m, ip_num_to_str(ip)::inet,
  14. sum(case when is_local = true then bytes else 0 end),
  15. sum(case when is_local = false then bytes else 0 end)
  16. from nf_statistic n
  17. where not h between 8 and 14
  18. group by ip, y, m) as rs
  19. inner join schools s on s.inetsubnet = rs.ip
  20. where y = (select year from in_data) and s.deleted = false
  21. group by ip, y, period, s.rtcom_id, s.name, s.region, s.etap, s.finance
  22. order by s.name asc, period desc;
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×