Gastos Abertos/Analise Planilha Execucao
Pelo levantamento preliminar realizado temos os seguintes campos comuns a todas as tabelas: cd_anoexecucao
, cd_despesa
, cd_exercicio
, cd_orgao
, cd_unidade
. Um campo string livre (mantindo em NULL) pode ser adicionado para eventuais desambiguações para casos não previstos, cd_auxiliar
.
A solução pode ser expressa em "dois sabores" diferentes: array de textos (complementadas pela array dos cabeçalhos) e JSON (ou ainda array-JSON para permitir tipagem hibrida numa array).
usando TEXT
editarHá que se armazenar o "header" da planilha (tabela de dados brutos).
CREATE TABLE gastos_head ( -- headers das planilhas de referência
id serial PRIMARY KEY,
reftab varchar(40) NOT NULL, -- nome de uma tabela-referência (planilha) de origem
reftab_vers integer NOT NULL, -- versao dos dados, id da tabela-referência (ex. ano)
repotab varchar(40) NOT NULL DEFAULT 'gastos_pmsp', -- nome da tabela-destino neste repositório
info_head text[], -- nome dos campos, variam de ano a ano (versao a versao)
UNIQUE(reftab,reftab_vers)
);
CREATE TABLE tmp_carga ( -- tabela (isolada) temp. para análise data scraping
id serial PRIMARY KEY, -- opcional
reftab_id integer NOT NULL REFERENCES gastos_head(id), -- opcional
info text[], -- todos os dados aqui
); -- o primeiro insert da planilha (CSV) pode ser feito nesta tabela. Testando chave-candidata:
-- SELECT DISTINCT info[1], infor[3], ..., info[N] FROM tmp_carga
CREATE TABLE gastos_pmsp ( -- tabela definitiva, otimizada e relacionada
id serial PRIMARY KEY, -- chave de uso interno (joins e permalinks)
-- campos utilizados em joins, buscas e micro-services:
reftab_id integer NOT NULL REFERENCES gastos_head(id),
cd_anoexecucao integer NOT NULL,
cd_despesa integer NOT NULL,
cd_exercicio integer NOT NULL,
cd_orgao integer NOT NULL,
cd_unidade integer NOT NULL,
cd_auxiliar varchar(255),
info text[], -- demais campos, variam de ano para ano
UNIQUE (cd_anoexecucao, cd_despesa, cd_exercicio, cd_orgao, cd_unidade, cd_auxiliar)
);
-- --
-- função para caso de desejar popular gastos_pmsp sem redundância
CREATE FUNCTION array_splicebyindex(anyarray,integer[]) RETURNS anyarray AS
$BODY$
WITH t AS (
SELECT unnest($1) AS x, generate_subscripts($1, 1) AS i
) SELECT array_agg(x) FROM t WHERE not( i=ANY($2));
$BODY$ LANGUAGE 'sql' IMMUTABLE;
-- ex. SELECT array_splicebyindex(array['a','b','c','d'], array[1,2] ) -- retorna c,d
usando JSON
editarCREATE TABLE gastos_pmsp (
id serial PRIMARY KEY, -- chave de uso interno (joins e permalinks)
-- campos utilizados em joins, buscas e micro-services:
cd_anoexecucao integer NOT NULL,
cd_despesa integer NOT NULL,
cd_exercicio integer NOT NULL,
cd_orgao integer NOT NULL,
cd_unidade integer NOT NULL,
cd_auxiliar varchar(255),
info JSON, -- demais campos, variam de ano para ano
UNIQUE (cd_anoexecucao, cd_despesa, cd_exercicio, cd_orgao, cd_unidade, cd_auxiliar)
);
CREATE VIEW gastos_pmsp_formated AS -- apenas exemplo!
WITH t AS (
SELECT *, DATE info->>'datainicial' AS datainicial , DATE info->>'datafinal' AS datafinal
FROM gastos_pmsp
) SELECT *, to_char(datainicial, 'DD/MM/YYYY') AS datainicial_fmt,
to_char(datafinal, 'DD/MM/YYYY') AS datafinal_fmt
FROM t;
Essa proposta ainda precisa ser confirmada depois de uma avaliação com carga completa dos dados. É importante preservar os tipos de dados no JSON (ex. string ou number) e também criar uma VIEW SQL para "helper" de formatação de dados, como exemplificado com datas.
NOTA: não confundir o levantamento de modelagem de dados, que pressupõe dados originais com certa coerência, com o levantamento dos dados em si, como deste exemplo.