
-- giorni del mese
SELECT tag, is_holiday(tag) as holiday, site_id, afr.node_it as from_it, afr.node_de as from_de, a.apr_it, a.apr_de, ato.node_it as to_it, ato.node_de as to_de, total, kind_1 as bikes, kind_2 as cars, kind_3 as deliverytrucks, kind_4 as trucks, kind_5 as bus, kind_6 as nc
FROM traffic_day t 
     JOIN aprs a ON a.apr_id = t.site_id
     JOIN apr_nodes afr ON afr.node_id = a.node_from_id
     JOIN apr_nodes ato ON ato.node_id = a.node_to_id
WHERE 
     t.tag >= '2023-07-01' and t.tag < '2023-08-01'
     AND site_id between 811 AND 882
ORDER BY site_id, tag;

-- medie orarie
SELECT date_part('hour', tag), site_id, afr.node_it as from_it, afr.node_de as from_de, a.apr_it, a.apr_de, ato.node_it as to_it, ato.node_de as to_de, 
     round(avg(CASE WHEN is_holiday(tag) = 0 THEN total ELSE NULL END)) as total_fer,
     round(avg(CASE WHEN is_holiday(tag) = 0 THEN kind_1 ELSE NULL END)) as bikes_fer,
     round(avg(CASE WHEN is_holiday(tag) = 0 THEN kind_2 ELSE NULL END)) as cars_fer,
     round(avg(CASE WHEN is_holiday(tag) = 0 THEN kind_3 ELSE NULL END)) as delivery_fer,
     round(avg(CASE WHEN is_holiday(tag) = 0 THEN kind_4 ELSE NULL END)) as trucks_fer,
     round(avg(CASE WHEN is_holiday(tag) = 0 THEN kind_5 ELSE NULL END)) as bus_fer,
     round(avg(CASE WHEN is_holiday(tag) = 0 THEN kind_6 ELSE NULL END)) as nc_fer,

     round(avg(CASE WHEN is_holiday(tag) > 0 THEN total ELSE NULL END)) as total_fes,
     round(avg(CASE WHEN is_holiday(tag) > 0 THEN kind_1 ELSE NULL END)) as bikes_fes,
     round(avg(CASE WHEN is_holiday(tag) > 0 THEN kind_2 ELSE NULL END)) as cars_fes,
     round(avg(CASE WHEN is_holiday(tag) > 0 THEN kind_3 ELSE NULL END)) as delivery_fes,
     round(avg(CASE WHEN is_holiday(tag) > 0 THEN kind_4 ELSE NULL END)) as trucks_fes,
     round(avg(CASE WHEN is_holiday(tag) > 0 THEN kind_5 ELSE NULL END)) as bus_fes,
     round(avg(CASE WHEN is_holiday(tag) > 0 THEN kind_6 ELSE NULL END)) as nc_fes

FROM traffic_hour t 
     JOIN aprs a ON a.apr_id = t.site_id
     JOIN apr_nodes afr ON afr.node_id = a.node_from_id
     JOIN apr_nodes ato ON ato.node_id = a.node_to_id
WHERE 
     t.tag >= '2023-07-01' and t.tag < '2023-08-01'
     AND site_id between 811 AND 882
GROUP BY date_part('hour', tag), site_id, afr.node_it, afr.node_de, a.apr_it, a.apr_de, ato.node_it, ato.node_de 
ORDER BY site_id, date_part('hour', tag);


-- medie giorni settimana
SELECT site_id, afr.node_it as from_it, afr.node_de as from_de, a.apr_it, a.apr_de, ato.node_it as to_it, ato.node_de as to_de,

     round(avg(CASE WHEN extract(dow from tag) = 1 THEN total ELSE NULL END)) as monday,
     round(avg(CASE WHEN extract(dow from tag) = 2 THEN total ELSE NULL END)) as tuesday,
     round(avg(CASE WHEN extract(dow from tag) = 3 THEN total ELSE NULL END)) as wednesday,
     round(avg(CASE WHEN extract(dow from tag) = 4 THEN total ELSE NULL END)) as thursday,
     round(avg(CASE WHEN extract(dow from tag) = 5 THEN total ELSE NULL END)) as friday,
     round(avg(CASE WHEN extract(dow from tag) = 6 THEN total ELSE NULL END)) as saturday,
     round(avg(CASE WHEN extract(dow from tag) = 0 THEN total ELSE NULL END)) as sunday
    
FROM traffic_hour t 
     JOIN aprs a ON a.apr_id = t.site_id
     JOIN apr_nodes afr ON afr.node_id = a.node_from_id
     JOIN apr_nodes ato ON ato.node_id = a.node_to_id
WHERE 
     t.tag >= '2023-07-01' and t.tag < '2023-08-01'
     AND site_id between 811 AND 882
GROUP BY site_id, afr.node_it, afr.node_de, a.apr_it, a.apr_de, ato.node_it, ato.node_de 
ORDER BY site_id


    