weekly_fnu_by_proj.sql 4.17 KB
Newer Older
1
-- Creates a time series for each project (weekly)
2 3 4 5 6 7 8 9 10 11 12

COPY(
WITH weekly_fnu AS (
    SELECT
        id_point
        , EXTRACT (WEEK FROM collect_date) AS "week"
        , EXTRACT (YEAR FROM collect_date) AS "year"
        , SUM(down_bytes) AS "down_bytes_total"
        , SUM(up_bytes) AS "up_bytes_total"
        , AVG(down_bytes) AS "down_bytes_avg"
        , AVG(up_bytes) AS "up_bytes_avg"
13 14
        , MAX(down_bytes) AS "down_bytes_max"
        , MAX(up_bytes) AS "up_bytes_max"
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
    FROM
        fact_net_usage
    WHERE
        collect_date >= '${DATE_FILTER_INIT}'::date
        AND collect_date < '${DATE_FILTER_END}'::date
    GROUP BY
        id_point
        , year
        , week
), wk_fnu_join_point AS (
    SELECT
        f.id_point
        , f.year
        , f.week
        , f.down_bytes_total
        , f.up_bytes_total
        , f.down_bytes_avg
        , f.up_bytes_avg
33 34
        , f.down_bytes_max
        , f.up_bytes_max
35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
        , p.is_gesac
        , p.is_telecenter
        , p.is_digital_city
    FROM
        weekly_fnu f
        INNER JOIN point p ON p.id = f.id_point
), wk_by_project AS (
    SELECT
        week
        , year
        , SUM(CASE WHEN is_gesac THEN down_bytes_total ELSE NULL END) AS "dbt_gesac"
        , SUM(CASE WHEN is_telecenter THEN down_bytes_total ELSE NULL END) AS "dbt_telecenter"
        , SUM(CASE WHEN is_digital_city THEN down_bytes_total ELSE NULL END) AS "dbt_dc"
        , SUM(CASE WHEN is_gesac THEN up_bytes_total ELSE NULL END) AS "ubt_gesac"
        , SUM(CASE WHEN is_telecenter THEN up_bytes_total ELSE NULL END) AS "ubt_telecenter"
        , SUM(CASE WHEN is_digital_city THEN up_bytes_total ELSE NULL END) AS "ubt_dc"
        , AVG(CASE WHEN is_gesac THEN down_bytes_avg ELSE NULL END) AS "dba_gesac"
        , AVG(CASE WHEN is_telecenter THEN down_bytes_avg ELSE NULL END) AS "dba_telecenter"
        , AVG(CASE WHEN is_digital_city THEN down_bytes_avg ELSE NULL END) AS "dba_dc"
        , AVG(CASE WHEN is_gesac THEN up_bytes_avg ELSE NULL END) AS "uba_gesac"
        , AVG(CASE WHEN is_telecenter THEN up_bytes_avg ELSE NULL END) AS "uba_telecenter"
        , AVG(CASE WHEN is_digital_city THEN up_bytes_avg ELSE NULL END) AS "uba_dc"
57 58 59 60 61 62
        , MAX(CASE WHEN is_gesac THEN down_bytes_max ELSE NULL END) AS "dbm_gesac"
        , MAX(CASE WHEN is_telecenter THEN down_bytes_max ELSE NULL END) AS "dbm_telecenter"
        , MAX(CASE WHEN is_digital_city THEN down_bytes_max ELSE NULL END) AS "dbm_dc"
        , MAX(CASE WHEN is_gesac THEN up_bytes_max ELSE NULL END) AS "ubm_gesac"
        , MAX(CASE WHEN is_telecenter THEN up_bytes_max ELSE NULL END) AS "ubm_telecenter"
        , MAX(CASE WHEN is_digital_city THEN up_bytes_max ELSE NULL END) AS "ubm_dc"
63 64 65 66 67
    FROM
        wk_fnu_join_point
    GROUP BY
        week,
        year
68 69 70 71 72 73 74 75 76 77 78
), series AS (
    SELECT
        EXTRACT (WEEK FROM collect_date) AS "week"
        , EXTRACT (YEAR FROM collect_date) AS "year"
    FROM
        (
            SELECT
                '2014-01-02'::date + s.a AS "collect_date"
            FROM
                generate_series(0, 1825, 7) AS s(a)
        ) AS a
79 80
), renamed AS (
    SELECT
81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
        s.year AS "Ano"
        , s.week AS "Semana"
        , d.dbt_gesac AS "Total-GESAC-download"
        , d.dbt_telecenter AS "Total-TLBR-download"
        , d.dbt_dc AS "Total-C.Digital-download"
        , d.ubt_gesac AS "Total-GESAC-upload"
        , d.ubt_telecenter AS "Total-TLBR-upload"
        , d.ubt_dc AS "Total-C.Digital-upload"
        , d.dba_gesac AS "Média-GESAC-download"
        , d.dba_telecenter AS "Média-TLBR-download"
        , d.dba_dc AS "Média-C.Digital-download"
        , d.uba_gesac AS "Média-GESAC-upload"
        , d.uba_telecenter AS "Média-TLBR-upload"
        , d.uba_dc AS "Média-C.Digital-upload"
        , d.dbm_gesac AS "Pico-GESAC-download"
        , d.dbm_telecenter AS "Pico-TLBR-download"
        , d.dbm_dc AS "Pico-C.Digital-download"
        , d.ubm_gesac AS "Pico-GESAC-upload"
        , d.ubm_telecenter AS "Pico-TLBR-upload"
        , d.ubm_dc AS "Pico-C.Digital-upload"
101
    FROM
102 103
        series s
        LEFT OUTER JOIN wk_by_project d ON s.week = d.week AND s.year = d.year
104
)
105
SELECT * FROM renamed ORDER BY "Ano", "Semana"
106 107

) TO STDOUT WITH CSV HEADER DELIMITER ';';