# School Traff Better

Jul 15th, 2015
2,076
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
1. WITH
2. params AS
3. (
4.     SELECT date_part('month', CURRENT_DATE - INTERVAL '1 MONTH') AS "month", date_part('year', CURRENT_DATE - INTERVAL '1 MONTH') AS "year"
5. ),
6. cteYearStats AS
7. (
8. SELECT
9.     ip_num_to_str(ip)::inet AS ip,
10.     m AS m,
11.     CASE WHEN h BETWEEN 8 AND 14 THEN '8-14' ELSE '14-8' END AS period,
12.     sum(CASE WHEN is_local = true THEN bytes ELSE 0 END) AS local_traf,
13.     sum(CASE WHEN is_local = false THEN bytes ELSE 0 END) AS notlocal_traf
14. FROM
15.     nf_statistic n
16. WHERE
17.     y = (SELECT "year" FROM params)
18. GROUP BY
19.     period, ip, m
20.
21. ),
22. cteTrunStat as
23. (
24. SELECT
25.     ip,
26.     period,
27.     sum(CASE WHEN m = (SELECT "month" FROM params) THEN rs.local_traf ELSE 0 END) / 2 ^ 20 AS t1,
28.     sum(CASE WHEN m = (SELECT "month" FROM params) THEN rs.notlocal_traf ELSE 0 END)  / 2 ^ 20 AS t2,
29.     sum(CASE WHEN m = (SELECT "month" FROM params) THEN rs.notlocal_traf + rs.local_traf ELSE 0 END) / 2 ^ 20 AS t3,
30.     sum(CASE WHEN m <=(SELECT "month" FROM params) THEN rs.local_traf+rs.notlocal_traf ELSE 0 END)  / 2 ^ 20 AS t4
31. FROM
32.     cteYearStats AS rs
33. GROUP BY
34.     ip, period
35. )
36. SELECT
37.     rs.ip, s.rtcom_id, s.region, s.name, s.finance,
38.     rs.period, rs.t1, rs.t2, rs.t3, rs.t4
39. from
40.     cteTrunStat AS rs
41. JOIN
42.     schools s ON s.inetsubnet = rs.ip
43. WHERE
44.     s.deleted = false
45. ORDER BY
46.     s.name ASC, period DESC;
RAW Paste Data