weekly_fnu_by_proj.sql 2.8 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
-- Creates a time series for each project

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"
    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
        , 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"
    FROM
        wk_fnu_join_point
    GROUP BY
        week,
        year
), renamed AS (
    SELECT
        year AS "Ano"
        , week AS "Semana"
        , dbt_gesac AS "Total-GESAC-download"
        , dbt_telecenter AS "Total-TLBR-download"
        , dbt_dc AS "Total-C.Digital-download"
        , ubt_gesac AS "Total-GESAC-upload"
        , ubt_telecenter AS "Total-TLBR-upload"
        , ubt_dc AS "Total-C.Digital-upload"
        , dba_gesac AS "Média-GESAC-download"
        , dba_telecenter AS "Média-TLBR-download"
        , dba_dc AS "Média-C.Digital-download"
        , uba_gesac AS "Média-GESAC-upload"
        , uba_telecenter AS "Média-TLBR-upload"
        , uba_dc AS "Média-C.Digital-upload"
    FROM
        wk_by_project
)
SELECT * FROM renamed

) TO STDOUT WITH CSV HEADER DELIMITER ';';