net_usage_percentage.sql 3.17 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
-- Query that count the number of contacts in a interval and
-- divides by the total of contacts

COPY(
WITH filtered_fnu AS (
    SELECT
        id_city
        , EXTRACT(HOUR FROM collect_time) AS "hour"
        , COUNT(1) AS "contacts"
    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
    GROUP BY
        id_city
        , hour
), total_by_city AS (
    SELECT
        id_city
        , SUM(contacts) AS "total_contacts"
    FROM
        filtered_fnu
    GROUP BY
        id_city
), percentage_by_city AS (
    SELECT
        f.id_city
        , f.hour
        , (f.contacts/t.total_contacts) AS "percent_contacts"
    FROM
        filtered_fnu f
        INNER JOIN  total_by_city t ON f.id_city = t.id_city

), horizontal_percentage_by_city AS (
    SELECT
        id_city
        , CASE
            WHEN hour = 8 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_8
        , CASE
            WHEN hour = 9 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_9
        , CASE
            WHEN hour = 10 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_10
        , CASE
            WHEN hour = 11 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_11
        , CASE
            WHEN hour = 12 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_12
        , CASE
            WHEN hour = 13 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_13
        , CASE
            WHEN hour = 14 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_14
        , CASE
            WHEN hour = 15 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_15
        , CASE
            WHEN hour = 16 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_16
        , CASE
            WHEN hour = 17 THEN percent_contacts
            ELSE 0
        END AS percent_contacts_17
    FROM
        percentage_by_city
), sumarize_horizontal_percentage_by_city AS (
    SELECT
        id_city AS "COD IBGE 7"
        , SUM(percent_contacts_8) AS "Porcentagem de contatos (8)"
        , SUM(percent_contacts_9) AS "Porcentagem de contatos (9)"
        , SUM(percent_contacts_10) AS "Porcentagem de contatos (10)"
        , SUM(percent_contacts_11) AS "Porcentagem de contatos (11)"
        , SUM(percent_contacts_12) AS "Porcentagem de contatos (12)"
        , SUM(percent_contacts_13) AS "Porcentagem de contatos (13)"
        , SUM(percent_contacts_14) AS "Porcentagem de contatos (14)"
        , SUM(percent_contacts_15) AS "Porcentagem de contatos (15)"
        , SUM(percent_contacts_16) AS "Porcentagem de contatos (16)"
        , SUM(percent_contacts_17) AS "Porcentagem de contatos (17)"
    FROM
        horizontal_percentage_by_city
    GROUP BY
        id_city
)
SELECT * FROM sumarize_horizontal_percentage_by_city

) TO STDOUT WITH CSV HEADER DELIMITER ';';