dayly_fnu_by_proj.sql 3.45 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
-- Creates a time series for each project

COPY(
WITH dayly_fnu AS (
    SELECT
        id_point
        , EXTRACT (DAY FROM collect_date) AS "day"
        , EXTRACT (MONTH FROM collect_date) AS "month"
        , 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"
    FROM
        fact_net_usage
    WHERE
        collect_date >= '${DATE_FILTER_INIT}'::date
        AND collect_date < '${DATE_FILTER_END}'::date
    GROUP BY
        id_point
        , year
        , month
        , day
), dayly_fnu_join_point AS (
    SELECT
        f.id_point
        , f.year
        , f.month
        , f.day
        , f.down_bytes_total
        , f.up_bytes_total
        , f.down_bytes_avg
        , f.up_bytes_avg
        , p.is_gesac
        , p.is_telecenter
        , p.is_digital_city
    FROM
        dayly_fnu f
        INNER JOIN point p ON p.id = f.id_point
), dayly_by_project AS (
    SELECT
        day
        , month
        , 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"
    FROM
        dayly_fnu_join_point
    GROUP BY
        day,
        month,
        year
), series AS (
    SELECT
        EXTRACT (DAY FROM collect_date) AS "day"
        , EXTRACT (MONTH FROM collect_date) AS "month"
        , EXTRACT (YEAR FROM collect_date) AS "year"
    FROM
        (
            SELECT
                '2014-01-01'::date + s.a AS "collect_date"
            FROM
                generate_series(0, 1460) AS s(a)
        ) AS a
), renamed AS (
    SELECT
        s.year AS "Ano"
        , s.month AS "Mês"
        , s.day AS "Dia"
        , 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"
    FROM
        series s
        LEFT OUTER JOIN dayly_by_project d ON s.day = d.day AND s.month = d.month AND s.year = d.year
)
SELECT * FROM renamed ORDER BY "Ano", "Mês", "Dia"

) TO STDOUT WITH CSV HEADER DELIMITER ';';