fnu_by_year_city.sql 1.2 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
-- Export fnu by city,year - renamed

COPY(
WITH fnu_year_city AS (
    SELECT
        id_city
        , EXTRACT (YEAR FROM collect_date) AS "year"
        , SUM(down_bytes) AS "down_bytes_total"
        , SUM(up_bytes) AS "up_bytes_total"
        , MAX(down_bytes) AS "down_bytes_max"
        , MAX(up_bytes) AS "up_bytes_max"
    FROM
        fact_net_usage
    WHERE
        collect_date >= '${DATE_FILTER_INIT}'::date
        AND collect_date < '${DATE_FILTER_END}'::date
    GROUP BY
        id_city
        , year
), fnu_yc_with_city_info AS (
    SELECT
        f.id_city
        , f.year
        , f.down_bytes_total
        , f.up_bytes_total
        , f.down_bytes_max
        , f.up_bytes_max
        , c.name
        , c.state
    FROM
        fnu_year_city f
        INNER JOIN city c ON c.id = f.id_city
), renamed AS (
    SELECT
        id_city AS "COD IBGE 7"
        , name AS "Cidade"
        , state AS "Estado"
        , down_bytes_total AS "Tráfego (download)"
        , up_bytes_total AS "Tráfego (upload)"
        , down_bytes_max AS "Pico (download)"
        , up_bytes_max AS "Pico (upload)"
    FROM
        fnu_yc_with_city_info
)
SELECT * FROM renamed

) TO STDOUT WITH CSV HEADER DELIMITER ';';