daily pastebin goal
5%
SHARE
TWEET

School Traff Bad

genegreen Jul 15th, 2015 (edited) 1,266 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top