• Sign Up
• Login
• API
• FAQ
• Tools
• Archive
SHARE
TWEET # School Traff Bad genegreen   Jul 15th, 2015 (edited) 1,549 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
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.

Top