Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH
- params AS
- (
- SELECT date_part('month', CURRENT_DATE - INTERVAL '1 MONTH') AS "month", date_part('year', CURRENT_DATE - INTERVAL '1 MONTH') AS "year"
- ),
- cteYearStats AS
- (
- SELECT
- ip_num_to_str(ip)::inet AS ip,
- m AS m,
- CASE WHEN h BETWEEN 8 AND 14 THEN '8-14' ELSE '14-8' END AS period,
- sum(CASE WHEN is_local = true THEN bytes ELSE 0 END) AS local_traf,
- sum(CASE WHEN is_local = false THEN bytes ELSE 0 END) AS notlocal_traf
- FROM
- nf_statistic n
- WHERE
- y = (SELECT "year" FROM params)
- GROUP BY
- period, ip, m
- ),
- cteTrunStat as
- (
- SELECT
- ip,
- period,
- sum(CASE WHEN m = (SELECT "month" FROM params) THEN rs.local_traf ELSE 0 END) / 2 ^ 20 AS t1,
- sum(CASE WHEN m = (SELECT "month" FROM params) THEN rs.notlocal_traf ELSE 0 END) / 2 ^ 20 AS t2,
- sum(CASE WHEN m = (SELECT "month" FROM params) THEN rs.notlocal_traf + rs.local_traf ELSE 0 END) / 2 ^ 20 AS t3,
- sum(CASE WHEN m <=(SELECT "month" FROM params) THEN rs.local_traf+rs.notlocal_traf ELSE 0 END) / 2 ^ 20 AS t4
- FROM
- cteYearStats AS rs
- GROUP BY
- ip, period
- )
- SELECT
- rs.ip, s.rtcom_id, s.region, s.name, s.finance,
- rs.period, rs.t1, rs.t2, rs.t3, rs.t4
- from
- cteTrunStat AS rs
- JOIN
- schools s ON s.inetsubnet = rs.ip
- WHERE
- s.deleted = false
- ORDER BY
- s.name ASC, period DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement