turicas · June 10, 2020 09:30
diff --git a/README.md b/README.md
diff --git a/auxilio_por_estado.csv b/auxilio_por_estado.csv
diff --git a/auxilio_por_municipio.csv b/auxilio_por_municipio.csv
diff --git a/beneficiarios.sql b/beneficiarios.sql
 -- Cria view com o agrupamento por UF e município
 CREATE VIEW auxilio_agrupado AS
 	SELECT
 		uf,
 		municipio,
 		COUNT(*) AS total_pessoas,
 		SUM(valor) AS valor_total
 	FROM auxilio_emergencial
 	GROUP BY
 		uf,
 		municipio;

 -- Cria view com o cruzamento dos dados de auxílio por município com tabela
 -- de população e calcula índices
 CREATE VIEW auxilio_por_municipio AS
 	SELECT
 		p.state AS uf,
 		p.city AS municipio,
 		p.estimated_population AS populacao_estimada_2019,
 		v.total_pessoas AS total_beneficiarios,
 		v.valor_total AS valor_total,
 		ROUND(v.valor_total::numeric / v.total_pessoas, 2) AS media_valor,
 		ROUND(100 * (v.total_pessoas::numeric / p.estimated_population), 2) AS percentual_da_populacao
 	FROM populacao AS p
 		FULL OUTER JOIN auxilio_agrupado AS v
 			ON p.state = v.uf AND p.city = v.municipio
 	ORDER BY
 		uf, municipio;

 -- Cria view com dados consolidados por estado
 CREATE VIEW auxilio_por_estado AS
 	SELECT
 		uf,
 		SUM(populacao_estimada_2019) AS populacao_estimada_2019,
 		SUM(total_beneficiarios) AS total_beneficiarios,
 		SUM(valor_total) AS valor_total,
 		ROUND(SUM(valor_total)::numeric / SUM(total_pessoas), 2) AS media_valor,
 		ROUND(100 * (SUM(total_beneficiarios)::numeric / SUM(populacao_estimada_2019)), 2) AS percentual_da_populacao
 	FROM auxilio_por_municipio
 	GROUP BY uf
  ORDER BY uf;
uf	populacao_estimada_2019	total_beneficiarios	valor_total	media_valor	percentual_da_populacao
AC	881935	274545	192816000.00	702.31	31.13
AL	3337357	1065914	776407200.00	728.40	31.94
AM	4144597	1268420	919844400.00	725.19	30.60
AP	845731	264364	198905400.00	752.39	31.26
BA	14873064	5063679	3585577800.00	708.10	34.05
CE	9132078	3060250	2154010200.00	703.87	33.51
DF	3015268	563192	403462200.00	716.38	18.68
ES	4018650	989632	683398800.00	690.56	24.63
GO	7018354	1724620	1221031200.00	708.00	24.57
MA	7075181	2384724	1732808400.00	726.63	33.71
MG	21168791	4954629	3410505000.00	688.35	23.41
MS	2778986	658276	467767800.00	710.60	23.69
MT	3484466	867639	620540400.00	715.21	24.90
PA	8602865	2836789	2046029400.00	721.25	32.97
PB	4018127	1342407	947827800.00	706.07	33.41
PE	9557071	3136033	2267274600.00	722.98	32.81
PI	3273227	1161197	816932400.00	703.53	35.48
PR	11433957	2421280	1661698800.00	686.29	21.18
RJ	17264943	4304747	3086337600.00	716.96	24.93
RN	3506853	1102658	778848000.00	706.34	31.44
RO	1777225	470754	330130800.00	701.28	26.49
RR	605761	183974	133107600.00	723.51	30.37
RS	11377239	2024853	1436257800.00	709.31	17.80
SC	7164788	1167670	809134800.00	692.95	16.30
SE	2298696	758444	547864200.00	722.35	32.99
SP	45919049	9419128	6606288600.00	701.37	20.51
TO	1572866	449549	313901400.00	698.26	28.58
		273	170400.00	624.18
	-- Cria view com o agrupamento por UF e município
	CREATE VIEW auxilio_agrupado AS
	SELECT
	uf,
	municipio,
	COUNT(*) AS total_pessoas,
	SUM(valor) AS valor_total
	FROM auxilio_emergencial
	GROUP BY
	uf,
	municipio;

	-- Cria view com o cruzamento dos dados de auxílio por município com tabela
	-- de população e calcula índices
	CREATE VIEW auxilio_por_municipio AS
	SELECT
	p.state AS uf,
	p.city AS municipio,
	p.estimated_population AS populacao_estimada_2019,
	v.total_pessoas AS total_beneficiarios,
	v.valor_total AS valor_total,
	ROUND(v.valor_total::numeric / v.total_pessoas, 2) AS media_valor,
	ROUND(100 * (v.total_pessoas::numeric / p.estimated_population), 2) AS percentual_da_populacao
	FROM populacao AS p
	FULL OUTER JOIN auxilio_agrupado AS v
	ON p.state = v.uf AND p.city = v.municipio
	ORDER BY
	uf, municipio;

	-- Cria view com dados consolidados por estado
	CREATE VIEW auxilio_por_estado AS
	SELECT
	uf,
	SUM(populacao_estimada_2019) AS populacao_estimada_2019,
	SUM(total_beneficiarios) AS total_beneficiarios,
	SUM(valor_total) AS valor_total,
	ROUND(SUM(valor_total)::numeric / SUM(total_pessoas), 2) AS media_valor,
	ROUND(100 * (SUM(total_beneficiarios)::numeric / SUM(populacao_estimada_2019)), 2) AS percentual_da_populacao
	FROM auxilio_por_municipio
	GROUP BY uf
	ORDER BY uf;