genegreen

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

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.

×