daily pastebin goal
5%
SHARE
TWEET

School Traff Better

genegreen Jul 15th, 2015 (edited) 1,235 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  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
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