simmc_index.sql 4.95 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 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163
-- Query to calculate the "SIMMC index".
-- This index calculates the potential of net usage.
-- It takes, for each point, the MAX net_usage -- in the five minutes interval
-- (estimative of connection BANDWITH) and multiplies by the number of intervals
-- of five min. This represents the MAXIMUM net_usage possible for that point.
-- Then it takes the total of net usage of the point in the same intervals.
-- The total is divided by the maximum, so is a value between 0 and 1 that
-- measures how much the point use of its potential.
-- The index of a city is the mean of the index of each point in the city.

COPY(
WITH filtered_fnu AS (
    SELECT
        id_point
        , id_city
        , macaddr
        , ip
        , collect_date
        , collect_time
        , up_bytes
        , down_bytes
        , EXTRACT(TIMEZONE_HOUR FROM collect_time) AS "timezone"
        , EXTRACT(HOUR FROM collect_time) AS "hour"
        , EXTRACT(DAY FROM collect_date) AS "day"
        , EXTRACT(MONTH FROM collect_date) AS "month"
        , EXTRACT(YEAR FROM collect_date) AS "year"
    FROM
        fact_net_usage
    WHERE
        collect_time >= '${TIME_FILTER_INIT}'::time
        AND collect_time < '${TIME_FILTER_END}'::time
        AND collect_date >= '${DATE_FILTER_INIT}'::date
        AND collect_date < '${DATE_FILTER_END}'::date
        AND EXTRACT(DOW FROM collect_date) != 0
        AND EXTRACT(DOW FROM collect_date) != 6
), ffnu_by_point_by_year AS (
    SELECT
        id_city
        , id_point
        , macaddr
        , ip
        , timezone
        , hour
        , day
        , month
        , year
        , MAX(up_bytes) AS "max_up_bytes"
        , MAX(down_bytes) AS "max_down_bytes"
        , SUM(up_bytes) AS "sum_up_bytes"
        , SUM(down_bytes) AS "sum_down_bytes"
    FROM
        filtered_fnu
    GROUP BY
        id_city
        , id_point
        , macaddr
        , ip
        , year
        , month
        , day
        , hour
        , timezone
), ffnu_with_index AS (
    SELECT
        id_city
        , id_point
        , macaddr
        , ip
        , hour
        , timezone
        , day
        , month
        , year
        , CASE
            WHEN max_down_bytes > 0 THEN
            (
                sum_down_bytes::double precision
                /(max_down_bytes*12)
            )
            ELSE NULL
        END AS "index_down_bytes"
        , CASE
            WHEN max_up_bytes> 0 THEN
            (
                sum_up_bytes::double precision
                /(max_up_bytes*12)
            )
            ELSE NULL
        END AS "index_up_bytes"
    FROM
        ffnu_by_point_by_year

), ffnu_index_by_city_year AS (
    SELECT
        id_city
        , year
        , AVG(index_down_bytes) AS "index_down_bytes"
        , AVG(index_up_bytes) AS "index_up_bytes"
        --, COUNT(1) AS "total_samples"
        --, COUNT(CASE index_down_bytes IS NULL 1 ELSE NULL) AS "down_samples_removed"
        --, COUNT(CASE index_up_bytes IS NULL 1 ELSE NULL) AS "up_samples_removed"
    FROM
        ffnu_with_index
    GROUP BY
        id_city
        , year
), ffnu_with_index_by_city AS (
    SELECT
        id_city
        , CASE
            WHEN year = 2014 THEN index_down_bytes
            ELSE 0
        END AS index_down_bytes_2014
        , CASE
            WHEN year = 2015 THEN index_down_bytes
            ELSE 0
        END AS index_down_bytes_2015
        , CASE
            WHEN year = 2016 THEN index_down_bytes
            ELSE 0
        END AS index_down_bytes_2016
        , CASE
            WHEN year = 2017 THEN index_down_bytes
            ELSE 0
        END AS index_down_bytes_2017
        , CASE
            WHEN year = 2014 THEN index_up_bytes
            ELSE 0
        END AS index_up_bytes_2014
        , CASE
            WHEN year = 2015 THEN index_up_bytes
            ELSE 0
        END AS index_up_bytes_2015
        , CASE
            WHEN year = 2016 THEN index_up_bytes
            ELSE 0
        END AS index_up_bytes_2016
        , CASE
            WHEN year = 2017 THEN index_up_bytes
            ELSE 0
        END AS index_up_bytes_2017
    FROM
        ffnu_index_by_city_year
), ffnu_with_index_by_city_rename AS (
    SELECT
        id_city AS "COD IBGE 7"
        , SUM(index_down_bytes_2014) AS "Índice SIMMC (download) (2014)"
        , SUM(index_down_bytes_2015) AS "Índice SIMMC (download) (2015)"
        , SUM(index_down_bytes_2016) AS "Índice SIMMC (download) (2016)"
        , SUM(index_down_bytes_2017) AS "Índice SIMMC (download) (2017)"
        , SUM(index_up_bytes_2014) AS "Índice SIMMC (upload) (2014)"
        , SUM(index_up_bytes_2015) AS "Índice SIMMC (upload) (2015)"
        , SUM(index_up_bytes_2016) AS "Índice SIMMC (upload) (2016)"
        , SUM(index_up_bytes_2017) AS "Índice SIMMC (upload) (2017)"
    FROM
        ffnu_with_index_by_city
    GROUP BY
        id_city
)
SELECT * FROM ffnu_with_index_by_city_rename
--SELECT * FROM ffnu_with_index WHERE index_down_bytes > 1 OR index_up_bytes > 1

) TO STDOUT WITH CSV HEADER DELIMITER ';';