3.1.13. Pedidos de cobertura do Proagro (COP)
O Proagro é um programa do Governo Federal que garante o pagamento de financiamentos rurais de custeio agrícola quando a lavoura amparada tiver sua receita reduzida por causa de eventos climáticos ou pragas e doenças sem controle [27]. Ele é uma das formas de garantia do financiamento e quando da ocorrência de um evento adverso o produtor rural que optou por essa garantia pode solicitar seu pagamento.
No Sicor, os microdados sobre os pedidos de cobertura do Proagro (COP) estão disponíveis no formato CSV no arquivo SICOR_COP_BASICO.gz. Esses pedidos registram:
O evento adverso originador da solicitação, como geada, granizo, seca e enchentes, entre outros. A lista completa dos tipos de eventos adversos geradores de pedidos de cobertura encontra-se disponível no arquivo EventoProagro.csv, cuja estrutura é apresentada na Tabela 3.14.
Tabela 3.14 - Estrutura do arquivo com os possíveis eventos geradores de pedidos do Proagro. nome
tipo
descrição
PK
cd_evento
integer
Código do evento do proagro
nome_evento
text
Descrição do evento do proagro
O tipo de solo relacionado ao ZARC (Seção 3.2 - Zoneamento Agrícola de Risco Climático), que possibilita verificar se o empreendimento foi realizado em uma época propícia. A lista completa dos tipos de solos encontra-se disponível no arquivo TipoSoloProagro.csv, cuja estrutura é apresentada na Tabela 3.15.
Tabela 3.15 - Estrutura do arquivo com os tipos de solo. nome
tipo
descrição
PK
cd_tipo_solo
integer
Código do tipo de solo
descricao_tipo_solo
text
Descrição do tipo de solo
O ciclo do cultivar relacionado ao ZARC (Seção 3.2 - Zoneamento Agrícola de Risco Climático), que também é usado para verificar se o empreendimento foi realizado em uma época propícia, de acordo com o recomendado. A lista completa dos tipos de solos encontra-se disponível no arquivo CicloCultivarProagro.csv, cuja estrutura é apresentada na Tabela 3.16.
Tabela 3.16 - Estrutura do arquivo com o ciclo do cultivar relacionado ao ZARC. nome
tipo
descrição
PK
cd_ciclo_cultivar
integer
Código do ciclo do cultivar
descricao_ciclo
text
Descrição do ciclo do cultivar
Situação do pedido de cobertura:
em análise,deferida,indeferida,desistida,canceladaeinválida. A lista completa dos possíveis estados de um pedido de cobertura encontra-se disponível no arquivo StatusCOPProagro.csv, cuja estrutura é apresentada na Tabela 3.17.Tabela 3.17 - Estrutura do arquivo com os possíveis estados dos pedidos de cobertura do Proagro. nome
tipo
descrição
PK
cd_status
integer
Código do status da COP
descricao
text
Descrição do status da COP
A Tabela 3.18 apresenta a estrutura do arquivo contendo os registros de pedido de Proagro (SICOR_COP_BASICO.gz).
nome |
tipo |
descrição |
|
|---|---|---|---|
PK, FK1 |
ref_bacen |
integer |
Número mascarado de referência do contrato |
PK, FK1 |
nu_ordem |
integer |
Número da destinação/finalidade dentro do contrato |
dt_comunicacao |
date |
Data de comunicação da COP |
|
dt_fim_colheita |
date |
Fim de colheita |
|
dt_fim_plantio |
date |
Fim de plantio |
|
dt_inicio_colheita |
date |
Início de colheita |
|
dt_inicio_plantio |
date |
Início de plantio |
|
FK2 |
cd_status |
integer |
Código do status da COP |
FK3 |
cd_ciclo_cultivar |
integer |
Código do ciclo do cultivar |
FK4 |
cd_tipo_solo |
integer |
Código do tipo de solo |
PK, FK5 |
cd_evento |
integer |
Código do evento da COP |
Outro microdado complementar ao dos pedidos de Proagro é o registro das periciadoras e peritos, disponíveis no arquivo SICOR_COMPLEMENTO_COP.gz, cuja estrutura é apresentada na Tabela 3.19.
nome |
tipo |
descrição |
|
|---|---|---|---|
PK, FK |
ref_bacen |
integer |
Número mascarado de referência do contrato |
PK, FK |
nu_ordem |
integer |
Número da destinação/finalidade dentro do contrato |
PK, FK |
cd_evento |
integer |
Código do evento da COP |
cd_cpf_cnpj_periciadora |
text |
CPF do perito da empresa periciadora |
|
cd_cpf_perito |
text |
CPF/CNPJ do perito |
Os arquivos listados acima foram carregados nas tabelas sicor_cop_basico, eventoproagro, statuscopproagro, ciclocultivarproagro, tiposoloproagro e sicor_complemento_cop do banco de dados PostgreSQL. A Figura 3.17 apresenta os relacionamentos dessas tabelas.
Figura 3.17 - Diagrama Entidade-Relacionamento dos pedidos de cobertura do Proagro (simplificado).
Os microdados do relatório de comprovação de perdas do Proagro estão disponíveis no arquivo SICOR_RCP_BASICO.gz, que possui a estrutura mostrada na Tabela 3.20.
nome |
tipo |
descrição |
|
|---|---|---|---|
PK, FK1 |
ref_bacen |
integer |
Número mascarado de referência do contrato |
PK, FK1 |
nu_ordem |
integer |
Número da destinação/finalidade dentro do contrato |
dt_entrega |
date |
Data de entrega do RCP |
|
dt_fim_colheita |
date |
Fim de colheita |
|
dt_fim_evento |
date |
Fim de evento |
|
dt_fim_plantio |
date |
Fim de plantio |
|
dt_inicio_colheita |
date |
Início de colheita |
|
dt_inicio_evento |
date |
Início de evento |
|
dt_inicio_plantio |
date |
Início de plantio |
|
vl_area |
numeric |
Área |
|
vl_prev_prod |
numeric |
Previsão de produção |
|
vl_rec_prev |
numeric |
Valor da receita prevista correspondente |
|
FK2 |
cd_status |
integer |
Código do status do RCP |
FK3 |
cd_tipo |
integer |
Código do tipo de solo |
FK4 |
cd_evento |
integer |
Código do evento do RCP |
dt_visita |
date |
Data de visita do perito |
|
nu_dias_ciclo_cultivar |
integer |
Número de ciclos do cultivar |
Assim como os pedidos de cobertura, os relatórios de comprovação de perdas possuem um microdado complementar, com registro das periciadoras e peritos. Esta infromação encontra-se disponível no arquivo SICOR_COMPLEMENTO_RCP.gz, cuja estrutura é apresentada na Tabela 3.21.
nome |
tipo |
descrição |
|
|---|---|---|---|
PK, FK |
ref_bacen |
integer |
Número mascarado de referência do contrato |
PK, FK |
nu_ordem |
integer |
Número da destinação/finalidade dentro do contrato |
cd_cpf_cnpj_periciadora |
text |
CPF do perito da empresa periciadora |
|
cd_cpf_perito |
text |
CPF/CNPJ do perito |
Os relatórios de comprovação de perdas em operações contratadas podem apresentar glebas da comprovação. Esses microdados são disponibilizados em dois arquivos:
SICOR_RCP_GLEBAS_2015_2020.gz: Glebas da comprovação de perdas de operações contratadas entre os anos de 2015 e 2020.
SICOR_RCP_GLEBAS_2021.gz: Glebas da comprovação de perdas de operações contratadas a partir de 2021.
A Tabela 3.22 apresenta a estrutura desses arquivos. Conforme pode ser observado, é utilizada uma estrutura semelhante à apresentada na Seção 3.1.10 - Glebas de Operações Contratadas com Recursos Públicos, com as geometrias das glebas representadas textualmente no formato WKT.
nome |
tipo |
descrição |
|
|---|---|---|---|
PK, FK |
ref_bacen |
integer |
Número mascarado de referência do contrato |
PK, FK |
nu_ordem |
integer |
Número da destinação/finalidade dentro do contrato |
PK |
nu_indice |
integer |
Identificador da gleba dentro da operação |
gt_geometria |
text |
Geometria da gleba no formato Well-Know Text (WKT) |
Os arquivos SICOR_RCP_BASICO.gz, SICOR_COMPLEMENTO_RCP.gz e SICOR_RCP_GLEBAS_2021.gz foram carregados, respectivamente, para as tabelas sicor_rcp_basico, sicor_complemento_rcp e sicor_rcp_glebas_wkt do banco de dados PostgreSQL. A Figura 3.18 apresenta os relacionamentos dessas tabelas.
Figura 3.18 - Diagrama Entidade-Relacionamento dos relatórios de comprovação de perdas em operações contratadas (simplificado).
Nota
O diagrama mostrado na Figura 3.18 também inclui uma tabela denominada sicor_rcp_glebas com estrutura similiar à tabela sicor_rcp_glebas_wkt mas já com uma coluna geométrica para representação das glebas dos relatórios. O código SQL abaixo pode ser utilizado para criar essa tabela a partir da tabela sicor_rcp_glebas_wkt:
CREATE TABLE sicor_rcp_glebas
(
gid INTEGER NOT NULL,
ref_bacen INTEGER NOT NULL,
nu_ordem INTEGER NOT NULL,
nu_indice INTEGER NOT NULL,
data_entrega DATE NOT NULL,
area_gleba NUMERIC NOT NULL DEFAULT 0.0,
perimetro_gleba NUMERIC NOT NULL DEFAULT 0.0,
area_menor_retangulo_envolvente NUMERIC NOT NULL DEFAULT 0.0,
area_menor_circulo_envolvente NUMERIC NOT NULL DEFAULT 0.0,
geom GEOMETRY(GEOMETRY, 4674)
);
INSERT INTO sicor_rcp_glebas
SELECT ROW_NUMBER () OVER () AS gid,
glebas.ref_bacen AS ref_bacen,
glebas.nu_ordem AS nu_ordem,
glebas.nu_indice AS nu_indice,
rcp.dt_entrega AS data_entrega,
ST_Area(ST_Transform((ST_Force2D(GT_GEOMETRIA::geometry))::geometry(geometry, 4674), 100000)) AS area_gleba,
ST_Perimeter(ST_Transform((ST_Force2D(GT_GEOMETRIA::geometry))::geometry(geometry, 4674), 100000)) AS perimetro_gleba,
ST_Area(ST_Transform(ST_OrientedEnvelope((ST_Force2D(GT_GEOMETRIA::geometry))::geometry(geometry, 4674)), 100000)) AS area_menor_retangulo_envolvente,
ST_Area(ST_Transform(ST_MinimumBoundingCircle((ST_Force2D(GT_GEOMETRIA::geometry))::geometry(geometry, 4674)), 100000)) AS area_menor_circulo_envolvente,
(ST_Force2D(GT_GEOMETRIA::geometry))::geometry(geometry, 4674) AS geom
FROM sicor_rcp_glebas_wkt AS glebas,
sicor_rcp_basico AS rcp
WHERE rcp.ref_bacen = glebas.ref_bacen
AND rcp.nu_ordem = glebas.nu_ordem;
ALTER TABLE sicor_rcp_glebas ADD CONSTRAINT sicor_rcp_glebas_pkey PRIMARY KEY (gid);
ALTER TABLE sicor_rcp_glebas ADD CONSTRAINT sicor_rcp_glebas_ukey UNIQUE (ref_bacen, nu_ordem, nu_indice);
ALTER TABLE sicor_rcp_glebas ADD CONSTRAINT sicor_rcp_glebas_ref_bacen_fkey
FOREIGN KEY(ref_bacen, nu_ordem) REFERENCES sicor_rcp_basico(ref_bacen, nu_ordem)
ON UPDATE CASCADE
ON DELETE NO ACTION;
CREATE INDEX sicor_rcp_glebas_geom_idx ON sicor_rcp_glebas USING GiST(geom);
CREATE INDEX sicor_rcp_glebas_data_entrega_idx ON sicor_rcp_glebas(data_entrega);
CREATE INDEX sicor_rcp_glebas_ano_idx ON sicor_rcp_glebas(extract(YEAR FROM data_entrega));
As informações das súmulas do julgamento são disponibilizadas no arquivo SICOR_SUMULA_JULGAMENTO.gz, cuja estrutura é mostrada na Tabela 3.23.
nome |
tipo |
descrição |
|
|---|---|---|---|
PK, FK1 |
ref_bacen |
integer |
Número mascarado de referência do contrato |
PK, FK1 |
nu_ordem |
integer |
Número da destinação/finalidade dentro do contrato |
vl_cob_ant_parcela_invest_proagro_mais |
numeric |
Cobertura da parcela de investimento do Proagro Mais |
|
vl_remu_encarr_comprov_perdas |
numeric |
Remuneração do encarregado da comprovação de perdas |
|
cd_status |
integer |
Status da súmula de julgamento |
|
vl_receitas_consideradas |
numeric |
Receitas consideradas |
|
vl_cobertura_ant_rec_proprios |
numeric |
Cobertura anterior dos recursos próprios |
|
vl_demais_despesas_comprov_perd |
numeric |
Demais despesas da comprovação de perdas |
|
vl_cred_custeio_usado |
numeric |
Crédito de custeio utilizado |
|
vl_demais_desp_ant_comp_per |
numeric |
Demais despesas anteriores da comprovação de perdas |
|
vl_rec_prop_usado |
numeric |
Recursos próprios utilizados |
|
vl_cob_ant_garantia_renda_min |
numeric |
Cobertura da garantia de renda mínima do Proagro Mais |
|
FK2 |
cd_instancia |
integer |
Instância da súmula de julgamento |
vl_perdas_nao_amparadas |
numeric |
Perdas não amparadas |
|
vl_encargos_sob_credito |
numeric |
Encargos financeiros sobre o crédito utilizado |
|
dt_inclusao |
date |
Data de inclusão da súmula de julgamento |
|
vl_perc_redutor_cobertura |
numeric |
Percentual do redutor de cobertura |
|
vl_remu_ant_encarg_comp_perdas |
numeric |
Remuneração anterior do encarregado da comprovação de perdas |
|
cd_decisao |
integer |
Início de plantio |
|
dt_decisao |
date |
Data da decisão na súmula de julgamento |
|
dt_base |
date |
Data base da súmula de julgamento |
|
vl_cobertura_ant_credito_custeio |
numeric |
Cobertura anterior do crédito de custeio |
|
vl_bonus_pgaf |
numeric |
||
vl_deducoes_legais |
numeric |
||
vl_orcamento_enquadrado |
numeric |
||
nu_dias_uteis_atraso_perito |
integer |
||
ib_segunda_vistoria |
integer |
Os valores pagos pelo Proagro são disponibilizados no arquivo SICOR_PARCELAS_PROAGRO.gz, que possui a estrutura apresentada na Tabela 3.24.
nome |
tipo |
descrição |
|
|---|---|---|---|
PK, FK1 |
ref_bacen |
integer |
Número mascarado de referência do contrato |
PK, FK1 |
nu_ordem |
integer |
Número da destinação/finalidade dentro do contrato |
dt_base |
date |
Data base de parcela do Proagro |
|
dt_pagamento |
date |
Data de pagamento de parcela do Proagro |
|
FK2 |
cd_instancia |
integer |
Instância de parcela do Proagro |
FK3 |
cd_status |
integer |
Status de parcela do Proagro |
FK4 |
cd_natureza_parcela |
char(3) |
Natureza de parcela do Proagro |
dt_atualizacao |
date |
Data de atualização de parcela do Proagro |
|
vl_atual |
numeric |
Valor atual de parcela do Proagro |
|
vl_base |
numeric |
Valor base de parcela do Proagro |
|
vl_pago |
numeric |
Valor pago de parcela do Proagro |
|
vl_imposto |
numeric |
Valor do imposto de parcela do Proagro |
|
dt_remessa |
date |
Data de remessa de parcela do Proagro |
O arquivo SICOR_PARCELAS_PROAGRO.gz foi carregado para a tabela sicor_parcelas_proagro do banco de dados PostgreSQL. A Figura 3.19 apresenta os relacionamentos dessa tabela.
Figura 3.19 - Diagrama Entidade-Relacionamento dos valores pagos pelo Proagro nos contratos de financiamento (simplificado).
Nota
A tabela naturezaproagro foi estendida com a coluna denominada sinal. O comando SQL abaixo pode ser usado para criar essa coluna:
ALTER TABLE naturezaproagro ADD COLUMN sinal NUMERIC DEFAULT 1.0;
A idéia básica dessa coluna é auxiliar na definição de valores positivos ou negativos da coluna vl_pago nos dados da tabela sicor_parcelas_proagro. As naturezas de parcela cuja descrição contenha a cadeia de caracteres devolução (ou similar) deve ter o valor da coluna sinal com valor -1. Podemos usar o comando SQL abaixo para definir esses valores:
UPDATE naturezaproagro SET sinal = -1.0 WHERE descricao LIKE '%devolu%';
3.1.13.1. Consultas
Consulta 1. Existem contratos/destinaçõs com mais de um evento reportado no pedido de Proagro?
Solução:
A lista dos tipos de eventos que aparecem nos registros de pedidos de cobertura podem ser encontrados na tabela eventoproagro.
SELECT ref_bacen, nu_ordem, COUNT(*) AS num_eventos
FROM sicor_cop_basico
GROUP BY ref_bacen, nu_ordem
HAVING COUNT(*) > 1
ORDER BY num_eventos DESC;
Resultado:
ref_bacen | nu_ordem | num_eventos
-----------+----------+-------------
515729598 | 1 | 4
516068998 | 1 | 4
516533502 | 2 | 4
... | ... | ...
513259304 | 1 | 2
513511692 | 1 | 2
515290913 | 1 | 2
(5398 rows)
Consulta 2. Quais são os eventos geradores de pedido de Proagro?
Solução:
A lista dos tipos de eventos, que aparecem nos registros de pedidos de cobertura, pode ser encontrada na tabela eventoproagro.
SELECT * FROM eventoproagro ORDER BY nome_evento;
Resultado:
cd_evento | nome_evento
-----------+-------------------------------------
17 | Chuva excessiva
135 | Chuva na colheita
110 | Doença ou praga
127 | Enchentes
24 | Geada
31 | Granizo
103 | Outros fenômenos naturais fortuitos
93 | Raio
48 | Seca
55 | Tromba de água
86 | Variação excessiva de temperatura
61 | Vendaval
79 | Vento forte
62 | Vento frio
(14 rows)
Consulta 3. Quais são os eventos que geram o maior número de pedidos de Proagro?
Solução:
SELECT extract(YEAR FROM dt_comunicacao) AS ano,
eventoproagro.nome_evento,
COUNT(*) AS total_pedidos
FROM sicor_cop_basico AS cop
LEFT JOIN eventoproagro
ON (cop.cd_evento = eventoproagro.cd_evento)
GROUP BY extract(YEAR FROM dt_comunicacao), cop.cd_evento, eventoproagro.nome_evento
ORDER BY ano ASC, total_pedidos DESC;
Resultado:
ano | nome_evento | total_pedidos
------+-------------------------------------+---------------
2013 | Geada | 8211
2013 | Chuva excessiva | 3497
2013 | Seca | 2776
2013 | Granizo | 1405
2013 | Vento forte | 647
2013 | Doença ou praga | 245
2013 | Variação excessiva de temperatura | 179
2013 | Vento frio | 25
2014 | Chuva excessiva | 33198
2014 | Seca | 23049
2014 | Vento forte | 2269
2014 | Granizo | 2242
2014 | Geada | 1161
2014 | Doença ou praga | 952
2014 | Variação excessiva de temperatura | 308
2014 | Vento frio | 29
... | ... | ...
2024 | Seca | 38799
2024 | Chuva excessiva | 23723
2024 | Geada | 7430
2024 | Doença ou praga | 3627
2024 | Variação excessiva de temperatura | 3266
2024 | Vento forte | 1974
2024 | Granizo | 940
2024 | Chuva na colheita | 190
2024 | Vendaval | 125
2024 | Enchentes | 32
2024 | Tromba de água | 29
2024 | Vento frio | 22
2024 | Outros fenômenos naturais fortuitos | 11
(137 rows)
Consulta 4. Qual a proporção no ano de cada evento gerador de pedido de Proagro?
Solução:
WITH total_pedidos_ano AS (
SELECT extract(YEAR FROM dt_comunicacao) AS ano,
COUNT(*) AS pedidos
FROM sicor_cop_basico
GROUP BY extract(YEAR FROM dt_comunicacao)
)
SELECT extract(YEAR FROM dt_comunicacao) AS ano,
eventoproagro.nome_evento,
COUNT(*) AS total_pedidos,
COUNT(*)::numeric / total_pedidos_ano.pedidos * 100.0 AS percentagem_ano
FROM sicor_cop_basico AS cop
LEFT JOIN eventoproagro
ON (cop.cd_evento = eventoproagro.cd_evento)
LEFT JOIN total_pedidos_ano
ON extract(YEAR FROM dt_comunicacao) = total_pedidos_ano.ano
GROUP BY extract(YEAR FROM dt_comunicacao),
cop.cd_evento,
eventoproagro.nome_evento,
total_pedidos_ano.pedidos
ORDER BY ano ASC, percentagem_ano DESC;
Resultado:
ano | nome_evento | total_pedidos | percentagem_ano
------+-------------------------------------+---------------+-----------------------------
2013 | Geada | 8211 | 48.342655284074183103000
2013 | Chuva excessiva | 3497 | 20.588754783632617015000
2013 | Seca | 2776 | 16.343832793641448337000
2013 | Granizo | 1405 | 8.272004710038269061000
2013 | Vento forte | 647 | 3.809243450103032087000
2013 | Doença ou praga | 245 | 1.442449219899911687000
2013 | Variação excessiva de temperatura | 179 | 1.053871062702384457000
2013 | Vento frio | 25 | 0.147188695908154254000
2014 | Chuva excessiva | 33198 | 52.521832679407669915000
2014 | Seca | 23049 | 36.465320845462599671000
2014 | Vento forte | 2269 | 3.589735476521959246000
2014 | Granizo | 2242 | 3.547019364637387672000
2014 | Geada | 1161 | 1.836792811036577648000
2014 | Doença ou praga | 952 | 1.506138463485634730000
2014 | Variação excessiva de temperatura | 308 | 0.487280091127705354000
2014 | Vento frio | 29 | 0.045880268320465764000
... | ... | ... | ...
2024 | Seca | 38799 | 48.397116056281808203000
2024 | Chuva excessiva | 23723 | 29.591607623989621794000
2024 | Geada | 7430 | 9.268037122043708213000
2024 | Doença ou praga | 3627 | 4.524249076938429298000
2024 | Variação excessiva de temperatura | 3266 | 4.073944716096197984000
2024 | Vento forte | 1974 | 2.462329108871370123000
2024 | Granizo | 940 | 1.172537670891128630000
2024 | Chuva na colheita | 190 | 0.237002295180121744000
2024 | Vendaval | 125 | 0.155922562618501148000
2024 | Enchentes | 32 | 0.039916176030336294000
2024 | Tromba de água | 29 | 0.036174034527492266000
2024 | Vento frio | 22 | 0.027442371020856202000
2024 | Outros fenômenos naturais fortuitos | 11 | 0.013721185510428101000
(137 rows)
Consulta 5. Quais as culturas que mais solicitam Proagro?
Solução:
Os empreendimentos que serão avaliados consistem de financiamentos de custeio agrícola para lavoura.
SELECT *
FROM (
SELECT *,
dense_rank() OVER (PARTITION BY ano ORDER BY valor_total DESC) as rank_valor,
dense_rank() OVER (PARTITION BY ano ORDER BY num_pedidos_proagro DESC) as rank_pedidos,
dense_rank() OVER (PARTITION BY ano ORDER BY valor_medio_proagro DESC) as rank_valor_medio
FROM (
SELECT extract(YEAR FROM op.dt_emissao) AS ano,
produto,
COUNT(*) AS num_pedidos_proagro,
SUM(valor_pago_proagro) AS valor_total,
(SUM(valor_pago_proagro)/COUNT(*)) AS valor_medio_proagro
FROM
(
SELECT ref_bacen,
nu_ordem,
SUM(parcelas.vl_pago * natureza.sinal) AS valor_pago_proagro
FROM sicor_parcelas_proagro AS parcelas,
naturezaproagro AS natureza
WHERE parcelas.cd_natureza_parcela = natureza.cd_natureza_parcela
GROUP BY ref_bacen, nu_ordem
) AS pedidos_proagro,
sicor_operacao_basica_estado AS op,
empreendimento
WHERE pedidos_proagro.ref_bacen = op.ref_bacen
AND pedidos_proagro.nu_ordem = op.nu_ordem
AND op.cd_empreendimento = empreendimento.cd_empreendimento
AND empreendimento.finalidade = 'custeio'
AND empreendimento.atividade = 'agrícola'
AND empreendimento.modalidade = 'lavoura'
GROUP BY ano, produto
)
ORDER BY ano, rank_valor
)
WHERE rank_valor <= 10
OR rank_pedidos <= 10
OR rank_valor_medio <= 10;
Dica
Para exportar o resultado da consulta acima para um arquivo CSV, utilize o comando \copy:
\copy (consulta) to '/tmp/culturas-proagro.csv' WITH (FORMAT CSV, HEADER, DELIMITER ';', ENCODING 'UTF8');
Resultado:
ano | produto | num_pedidos_proagro | valor_total | valor_medio_proagro | rank_valor | rank_pedidos | rank_valor_medio
------+----------------------------------+---------------------+----------------+------------------------+------------+--------------+------------------
2013 | milho | 173309 | 269125887.990 | 1552.8673524744819946 | 1 | 1 | 16
2013 | trigo | 33561 | 199561956.860 | 5946.2458466672625965 | 2 | 4 | 3
2013 | soja | 117458 | 196530639.730 | 1673.1992689301707845 | 3 | 2 | 14
2013 | café | 55310 | 25000912.950 | 452.0143364671849575 | 4 | 3 | 57
2013 | feijão | 12127 | 20172009.850 | 1663.3965407767790880 | 5 | 6 | 15
2013 | mandioca (aipim, macaxeira) | 18008 | 17532771.480 | 973.6101443802754331 | 6 | 5 | 24
2013 | cebola | 5090 | 10884551.030 | 2138.4186699410609037 | 7 | 8 | 13
... | ... | ... | ... | ... | ... | ... | ...
2024 | abóbora-moranga | 178 | 1639524.420 | 9210.8113483146067416 | 17 | 25 | 9
2024 | pepino | 116 | 1105571.120 | 9530.7855172413793103 | 21 | 33 | 7
2024 | maçã | 70 | 707403.920 | 10105.7702857142857143 | 26 | 41 | 6
2024 | gergelim | 4 | 57170.670 | 14292.667500000000 | 68 | 71 | 3
2024 | oliva (azeitona) | 1 | 18881.860 | 18881.8600000000000000 | 84 | 74 | 2
2024 | girassol | 1 | 9210.640 | 9210.6400000000000000 | 97 | 74 | 10
(217 rows)
O resultado acima encontra-se disponível no formato CSV no seguinte arquivo: culturas-proagro.csv.
Consulta 6. Qual o tamanho médio das áreas associadas a pedidos de Proagro e os valores dos contratos?
Solução:
Nessa consulta iremos utilizar a tabela statuscopproagro, que possui o seguinte conteúdo:
SELECT * FROM statuscopproagro ORDER BY descricao;
Saída:
cd_status | descricao
-----------+------------
5 | cancelada
2 | deferida
4 | desistida
1 | em análise
3 | indeferida
6 | inválida
(6 rows)
Além, da tabela acima, iremos utilizar as tabelas sicor_cop_basico, sicor_operacao_basica_estado e empreendimento, como mostrado na consulta abaixo:
SELECT ano,
produto,
COUNT(*) AS num_pedidos_proagro,
MIN(valor) AS menor_valor_emprestado,
MAX(valor) AS maior_valor_emprestado,
AVG(valor) AS media_valor_emprestado,
MIN(area) AS menor_area,
MAX(area) AS maior_area,
AVG(area) AS media_area
FROM (
SELECT DISTINCT ON(sicor_operacao_basica_estado.ref_bacen, sicor_operacao_basica_estado.nu_ordem)
sicor_operacao_basica_estado.ref_bacen AS ref_bacen,
sicor_operacao_basica_estado.nu_ordem AS nu_ordem,
sicor_operacao_basica_estado.vl_parc_credito AS valor,
sicor_operacao_basica_estado.vl_area_informada As area,
extract(YEAR FROM sicor_operacao_basica_estado.dt_emissao) AS ano,
empreendimento.produto AS produto,
empreendimento.variedade AS variedade,
empreendimento.cesta AS cesta
FROM statuscopproagro,
sicor_cop_basico,
sicor_operacao_basica_estado,
empreendimento
WHERE statuscopproagro.descricao = 'deferida'
AND statuscopproagro.cd_status = sicor_cop_basico.cd_status
AND sicor_cop_basico.ref_bacen = sicor_operacao_basica_estado.ref_bacen
AND sicor_cop_basico.nu_ordem = sicor_operacao_basica_estado.nu_ordem
AND sicor_operacao_basica_estado.cd_empreendimento = empreendimento.cd_empreendimento
AND empreendimento.finalidade = 'custeio'
AND empreendimento.atividade = 'agrícola'
AND empreendimento.modalidade = 'lavoura'
)
GROUP BY ano, produto
ORDER BY ano, media_area DESC;
Resultado:
ano | produto | num_pedidos_proagro | menor_valor_emprestado | maior_valor_emprestado | media_valor_emprestado | menor_area | maior_area | media_area
------+-----------------------------------------------+---------------------+------------------------+------------------------+------------------------+------------+------------+------------------------
2013 | sorgo | 4 | 20000.00 | 151258.20 | 62567.300000000000 | 35.00 | 240.00 | 96.2500000000000000
2013 | amendoim | 2 | 74134.52 | 300000.00 | 187067.260000000000 | 24.00 | 91.00 | 57.5000000000000000
2013 | algodão | 1 | 100000.00 | 100000.00 | 100000.000000000000 | 55.00 | 55.00 | 55.0000000000000000
2013 | trigo | 6824 | 1445.00 | 300000.00 | 37778.650781066823 | 1.70 | 392.00 | 37.2841984173505275
2013 | aveia | 13 | 1982.88 | 102891.34 | 28646.108461538462 | 2.70 | 125.00 | 37.0000000000000000
2013 | girassol | 3 | 12596.18 | 78786.43 | 36477.413333333333 | 13.00 | 72.00 | 34.6333333333333333
2013 | canola | 92 | 3065.08 | 189288.00 | 28773.830326086957 | 4.00 | 220.00 | 32.7082608695652174
2013 | cacau | 1 | 35595.56 | 35595.56 | 35595.560000000000 | 28.60 | 28.60 | 28.6000000000000000
2013 | cana-de-açucar | 9 | 16113.68 | 152002.62 | 59680.486666666667 | 8.00 | 56.00 | 23.8055555555555556
2013 | arroz | 94 | 3200.00 | 175001.97 | 40832.886063829787 | 2.00 | 208.00 | 21.0656382978723404
2013 | soja | 8341 | 850.00 | 300000.00 | 22765.472341445870 | 1.00 | 1000.00 | 20.3837585421412301
2013 | cevada | 15 | 2490.00 | 63105.11 | 17998.629333333333 | 3.00 | 62.00 | 18.9666666666666667
2013 | milho | 12792 | 1000.00 | 300000.00 | 23248.158010475297 | 0.80 | 309.00 | 17.9366197623514697
2013 | mandioca (aipim, macaxeira) | 335 | 1845.96 | 300000.00 | 47686.844447761194 | 1.00 | 124.09 | 16.6392835820895522
2013 | feijão | 1313 | 418.23 | 300000.00 | 15721.824691546078 | 0.30 | 200.00 | 10.5115689261233816
2013 | manga | 2 | 31500.00 | 80000.00 | 55750.000000000000 | 6.00 | 15.00 | 10.5000000000000000
2013 | tangerina | 3 | 7920.00 | 12600.00 | 10573.3333333333333333 | 6.00 | 9.00 | 7.6666666666666667
2013 | banana | 43 | 5940.83 | 162277.71 | 33801.447209302326 | 1.50 | 26.00 | 6.1334883720930233
... | ... | ... | ... | ... | ... | ... | ... |
2024 | soja | 146 | 5000.00 | 321723.73 | 56505.768904109589 | 1.76 | 87.69 | 12.9967808219178082
2024 | milho | 548 | 3120.00 | 331679.97 | 35064.199343065693 | 0.73 | 84.70 | 7.8008211678832117
2024 | milho silagem | 9 | 9243.00 | 55788.11 | 29505.624444444444 | 1.58 | 8.50 | 4.9377777777777778
2024 | melancia | 1 | 36501.00 | 36501.00 | 36501.000000000000 | 4.60 | 4.60 | 4.6000000000000000
2024 | beterraba | 31 | 19000.00 | 248400.00 | 72732.137741935484 | 0.84 | 10.83 | 3.5812903225806452
2024 | café | 18 | 33782.91 | 180156.79 | 86608.560000000000 | 1.05 | 8.92 | 3.3883333333333333
2024 | abóbora-moranga | 28 | 6000.00 | 85800.00 | 34254.451785714286 | 1.00 | 8.00 | 3.3371428571428571
2024 | sorgo | 1 | 17580.00 | 17580.00 | 17580.0000000000000000 | 2.99 | 2.99 | 2.9900000000000000
2024 | couve-flor | 2 | 18000.00 | 78400.00 | 48200.000000000000 | 1.50 | 3.91 | 2.7050000000000000
2024 | caqui | 2 | 43048.23 | 55000.00 | 49024.115000000000 | 2.60 | 2.74 | 2.6700000000000000
2024 | repolho | 2 | 23867.35 | 33488.06 | 28677.705000000000 | 1.00 | 3.60 | 2.3000000000000000
2024 | cenoura | 2 | 68000.00 | 70525.00 | 69262.500000000000 | 2.00 | 2.18 | 2.0900000000000000
2024 | brócolos (brócolis) | 5 | 16000.00 | 32053.88 | 24039.116000000000 | 1.31 | 2.99 | 1.9600000000000000
2024 | tomate | 38 | 10194.87 | 247500.00 | 91838.797368421053 | 0.11 | 4.49 | 1.5828947368421053
2024 | batata-doce | 1 | 15000.00 | 15000.00 | 15000.0000000000000000 | 1.50 | 1.50 | 1.50000000000000000000
2024 | uva | 2 | 9819.52 | 21105.00 | 15462.260000000000 | 0.87 | 2.06 | 1.46500000000000000000
2024 | pimentão | 4 | 31383.33 | 71500.00 | 47432.587500000000 | 0.19 | 1.30 | 0.89500000000000000000
2024 | berinjela | 1 | 28428.47 | 28428.47 | 28428.470000000000 | 0.60 | 0.60 | 0.60000000000000000000
2024 | pepino | 10 | 6218.09 | 110000.00 | 48276.933000000000 | 0.15 | 1.00 | 0.49100000000000000000
(763 rows)
Consulta 7. Qual a localização das glebas associadas a pedidos de Proagro nos últimos três anos?
Solução:
A tabela sicor_complemento_operacao_basica contém uma coluna que possui o código do IBGE associado ao município da operação contratada. Dessa forma, vamos começar pela construção de uma consulta que calcule o número de pedidos de Proagro por municipio.
SELECT geocodigo,
COUNT(*) AS num_pedidos_proagro
FROM (
SELECT DISTINCT ON (cop.ref_bacen, cop.nu_ordem)
cd_ibge_municipio AS geocodigo
FROM sicor_cop_basico AS cop,
sicor_complemento_operacao_basica AS op_complemento
WHERE cop.ref_bacen = op_complemento.ref_bacen
AND cop.nu_ordem = op_complemento.nu_ordem
AND extract(YEAR FROM cop.dt_comunicacao) >= 2022
)
GROUP BY geocodigo
ORDER BY num_pedidos_proagro DESC;
Agora, vamos acrescentar quatro novas colunas à tabela municipios_2022:
num_pedidos_proagro: número total de pedidos de Proagro associado ao município.valor_financiamento: valor total associado aos contratos de finaciamento no município.num_pedidos_deferidos_proagro: número total de pedidos deferidos de Proagro no município.valor_financiamento_pedidos_deferidos: valor total dos contratos que tiveream pedidos deferidos de Proagro no município.valor_pago_proagro: valor total pago de Proagro no município.
ALTER TABLE municipios_2022
ADD COLUMN num_pedidos_proagro INTEGER NOT NULL DEFAULT 0,
ADD COLUMN valor_financiamento NUMERIC NOT NULL DEFAULT 0.0,
ADD COLUMN num_pedidos_deferidos_proagro INTEGER NOT NULL DEFAULT 0,
ADD COLUMN valor_financiamento_pedidos_deferidos NUMERIC NOT NULL DEFAULT 0.0,
ADD COLUMN valor_pago_proagro NUMERIC NOT NULL DEFAULT 0.0;
Agora podemos atualizar as colunas num_pedidos_proagro e valor_financiamento com o seguinte comando SQL:
UPDATE municipios_2022
SET num_pedidos_proagro = resultado.num_pedidos_proagro,
valor_financiamento = resultado.valor_financiamento
FROM (
SELECT geocodigo,
COUNT(*) AS num_pedidos_proagro,
SUM(valor_financiamento) AS valor_financiamento
FROM (
SELECT DISTINCT ON (cop.ref_bacen, cop.nu_ordem)
cd_ibge_municipio AS geocodigo,
op.vl_parc_credito AS valor_financiamento
FROM sicor_cop_basico AS cop,
sicor_complemento_operacao_basica AS op_complemento,
sicor_operacao_basica_estado AS op
WHERE cop.ref_bacen = op_complemento.ref_bacen
AND cop.nu_ordem = op_complemento.nu_ordem
AND extract(YEAR FROM cop.dt_comunicacao) >= 2022
AND cop.ref_bacen = op.ref_bacen
AND cop.nu_ordem = op.nu_ordem
)
GROUP BY geocodigo
) AS resultado
WHERE municipios_2022.cd_mun = resultado.geocodigo;
Em seguida, podemos atualizar as colunas num_pedidos_deferidos_proagro e valor_financiamento_pedidos_deferidos:
UPDATE municipios_2022
SET num_pedidos_deferidos_proagro = resultado.num_pedidos_deferidos_proagro,
valor_financiamento_pedidos_deferidos = resultado.valor_financiamento_pedidos_deferidos
FROM (
SELECT geocodigo,
COUNT(*) AS num_pedidos_deferidos_proagro,
SUM(valor_financiamento) AS valor_financiamento_pedidos_deferidos
FROM (
SELECT DISTINCT ON (cop.ref_bacen, cop.nu_ordem)
cd_ibge_municipio AS geocodigo,
op.vl_parc_credito AS valor_financiamento
FROM sicor_cop_basico AS cop,
statuscopproagro AS status,
sicor_complemento_operacao_basica AS op_complemento,
sicor_operacao_basica_estado AS op
WHERE cop.ref_bacen = op_complemento.ref_bacen
AND cop.nu_ordem = op_complemento.nu_ordem
AND extract(YEAR FROM cop.dt_comunicacao) >= 2022
AND cop.ref_bacen = op.ref_bacen
AND cop.nu_ordem = op.nu_ordem
AND cop.cd_status = status.cd_status
AND status.descricao = 'deferida'
)
GROUP BY geocodigo
) AS resultado
WHERE municipios_2022.cd_mun = resultado.geocodigo;
Por último, podemos calcular o valor pago de Proagro por município, atualizando a coluna valor_pago_proagro:
UPDATE municipios_2022
SET valor_pago_proagro = resultado.valor_pago_proagro
FROM (
SELECT cd_ibge_municipio AS geocodigo,
SUM(parcelas.vl_pago * natureza.sinal) AS valor_pago_proagro
FROM (
SELECT DISTINCT ON (ref_bacen, nu_ordem)
ref_bacen,
nu_ordem
FROM sicor_cop_basico
WHERE extract(YEAR FROM dt_comunicacao) >= 2022
) AS pedidos_proagro,
sicor_parcelas_proagro AS parcelas,
sicor_complemento_operacao_basica AS op_complemento,
naturezaproagro AS natureza
WHERE pedidos_proagro.ref_bacen = parcelas.ref_bacen
AND pedidos_proagro.nu_ordem = parcelas.nu_ordem
AND pedidos_proagro.ref_bacen = op_complemento.ref_bacen
AND pedidos_proagro.nu_ordem = op_complemento.nu_ordem
AND parcelas.cd_natureza_parcela = natureza.cd_natureza_parcela
GROUP BY geocodigo
) AS resultado
WHERE municipios_2022.cd_mun = resultado.geocodigo;
O resultado dos cálculos realizados pode ser visto nos mapas apresentados nas figuras da Tabela 3.25.
(a) Número de pedidos de Proagro por município |
(b) Valor pago pelo Proagro em cada município |
|
|
Dica
Sempre que construimos uma consulta, é importante fazer uma checagem do seu resultado. No caso acima, uma boa base é saber o número de municípios entre 2022 e 2024 que estão associados aos pedidos de Proagro. Para saber o número de municípios, podemos construir uma ocnsulta como:
SELECT DISTINCT cd_ibge_municipio
FROM sicor_cop_basico,
sicor_complemento_operacao_basica
WHERE sicor_cop_basico.ref_bacen = sicor_complemento_operacao_basica.ref_bacen
AND sicor_cop_basico.nu_ordem = sicor_complemento_operacao_basica.nu_ordem
AND extract(YEAR FROM dt_comunicacao) >= 2022;
Consulta 8. Qual o percentual de pedidos de Proagro por município em relação ao número de financiamentos?
Solução:
Vamos acrescentar uma coluna chamada num_contratos na tabela municipios_2022:
ALTER TABLE municipios_2022
ADD COLUMN num_contratos INTEGER NOT NULL DEFAULT 0;
Para preencher essa nova coluna podemos utilizar a seguinte sentença SQL:
UPDATE municipios_2022
SET num_contratos = resultado.num_contratos
FROM (
SELECT op_complemento.cd_ibge_municipio AS geocodigo,
COUNT(*) AS num_contratos
FROM sicor_operacao_basica_estado AS op,
sicor_complemento_operacao_basica AS op_complemento
WHERE op.ref_bacen = op_complemento.ref_bacen
AND op.nu_ordem = op_complemento.nu_ordem
AND extract(YEAR FROM op.dt_emissao) >= 2022
GROUP BY op_complemento.cd_ibge_municipio
) AS resultado
WHERE municipios_2022.cd_mun = resultado.geocodigo;
Agora, podemos fazer uma consulta envolvendo as colunas num_pedidos_proagro e num_contratos:
SELECT cd_mun, nm_mun, sigla_uf,
num_pedidos_proagro,
num_contratos,
num_pedidos_proagro::numeric / num_contratos AS proporcao_pedidos
FROM municipios_2022
WHERE num_contratos > 0
ORDER BY proporcao_pedidos DESC;
Resultado:
cd_mun | nm_mun | sigla_uf | num_pedidos_proagro | num_contratos | proporcao_pedidos
---------+----------------------------------+----------+---------------------+---------------+----------------------------
3106705 | Betim | MG | 2 | 2 | 1.00000000000000000000
4119004 | Pérola d'Oeste | PR | 2623 | 3639 | 0.72080241824677109096
4106571 | Cruzeiro do Iguaçu | PR | 930 | 1408 | 0.66051136363636363636
4124806 | São João | PR | 2459 | 3824 | 0.64304393305439330544
4128609 | Verê | PR | 2916 | 4592 | 0.63501742160278745645
4309753 | Ibarama | RS | 738 | 1164 | 0.63402061855670103093
4322152 | Tunas | RS | 802 | 1268 | 0.63249211356466876972
4103024 | Boa Esperança do Iguaçu | PR | 1168 | 1856 | 0.62931034482758620690
4102752 | Bela Vista da Caroba | PR | 944 | 1506 | 0.62682602921646746348
4103222 | Bom Sucesso do Sul | PR | 931 | 1502 | 0.61984021304926764314
4301206 | Arroio do Tigre | RS | 3007 | 4857 | 0.61910644430718550546
4125209 | São Jorge d'Oeste | PR | 2347 | 3792 | 0.61893459915611814346
4311254 | Lagoão | RS | 1064 | 1721 | 0.61824520627542126671
... | ... | ... | ... | ... | ...