download_by_point_year.sql 1.12 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
-- Download by point, year, proj

COPY(
WITH weekly_fnu AS (
    SELECT
        id_point
        , id_city
        , 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"
        , 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_point
        , id_city
        , year
), wk_fnu_join_point AS (
    SELECT
        f.id_point
        , f.year
        , f.id_city
        , f.down_bytes_total
        , f.up_bytes_total
        , f.down_bytes_avg
        , f.up_bytes_avg
        , f.down_bytes_max
        , f.up_bytes_max
        , p.is_gesac
        , p.is_telecenter
        , p.is_digital_city
    FROM
        weekly_fnu f
        INNER JOIN point p ON p.id = f.id_point
)

SELECT * FROM wk_fnu_join_point ORDER BY year, id_city

) TO STDOUT WITH CSV HEADER DELIMITER ';';