Menu de navegação

[faccParticipante]

Boa tarde!

Venho mais uma vez pedir socorro para vocês. É o seguinte, me pediram para montar uma Procedure/Select que traga alguns dados aleatórios e salve em um arquivo texto, mas ele possui algumas particularidades.

1 – Preciso saber qual o contagem e a percentagem em relação ao total de dados gravados que possuam o CPF com o digito Verificador entre 01 e 99.

2 – Depois de feito isso, irei informar para me retornar 50.000 registros, obedecendo a porcentagem obtida na questão 1

Ex.
Total de Registros : 600.000

Cpfs com o DV 01 equivalem a 25% do total
Cpfs com o DV 02 equivaelm a 15% do total
…
Cpfs com o DV 99 equivalem a 05% do total

e com base dessa percentagem, retornar a quantidade de registros.

25% de 600.000 = 150.000
15% de 600.000 = 90.000
05% de 600.000 = 30.000

Espero ter sido claro.

Desde já muito obrigado.

[faccParticipante]

Retificando… a percentagem não é dos 600.000 e sim do informado, neste caso, os 50.000.

[fsitjaParticipante]

Então Facc, esse select na verdade só é aleatório dentro dos grupos, mas como os percentuais não dão uma divisão inteira pelo tamanho da amostragem não dá para retornar o número exato de linhas que você pedir. Dentro de cada grupo o percentual vai converter numa quantidade decimal de linhas.

Suponha que você tem 100 linhas na tabela de pessoa, com 100 CPFs diferentes. Se, por exemplo, 3 linhas possuem DV=00 dessas 100 dá 3%.

Agora suponhamos que você peça uma amostra de 40 linhas:
Logo 3% de 40 dá 1,2 que é um número decimal. Sua amostra truncará para baixo isso e terá apenas uma única linha com DV=00.

Somando todas essas frações e arredondamentos vai acontecer que sua amostragem vai acabar com menos de 40 linhas como consequência.

No exemplo abaixo eu criei uma tabela de teste de 10.000 linhas e peço uma amostragem de 5.000 linhas. No meu caso de teste o Random acabou gerando uma amostragem de 4.974 linhas, então 26 delas se perdem por conta da truncagem para baixo no percentual. Como você pode ver, para amostragens grandes o impacto não é muito significativo, mas fique com isso em mente.

Segue o create table para testar, e em seguida o SQL. Substitua o 5000 pelo seu tamanho de amostragem.


CREATE TABLE PESSOA AS
SELECT to_char((power(10, 12)) * val_random, 'FM000000000000') AS cpf
FROM (SELECT dbms_random.VALUE val_random FROM dual CONNECT BY LEVEL <= 10000);

SELECT p.cpf,
p.dv
FROM (SELECT cpf,
dv,
cont,
cont_distintos,
val_random,
row_number() OVER(PARTITION BY dv ORDER BY val_random) num_linha
FROM (SELECT substr(cpf, 11, 2) dv,
cpf,
COUNT() OVER(PARTITION BY substr(cpf, 11, 2) ORDER BY 0) cont,
COUNT() OVER(ORDER BY 0) cont_distintos,
dbms_random.VALUE val_random
FROM pessoa)
ORDER BY cont, row_number() OVER(PARTITION BY dv ORDER BY val_random)) p
WHERE num_linha <= (cont * (5000 / cont_distintos))
ORDER BY p.dv;


[faccParticipante]

Podemos dizer que foi resolvido 80%, pois alguns CPFs, depois de importados, ficam com menos de 11 digitos (geralmente os que possuem 0 no inicio), como poderia resolver?

Alterando o tipo do campo para String? Atualmente está como Numérico.

[faccParticipante]

Resolvido.

Alterei o tipo do campo para String.

Agora irei fazer mais testes e postar o resultado.

[faccParticipante]

Analisando mais profundamente, não é bem isso que eu queria, atualmente ele está me retornando os primeiros 5000 registros, necessitava que ele me mostrasse os 5000 registros aleatoriamente… por isso queria calcular a percentagem dos DVs dos CPFs.

Para pegar o percentual de DVs final 00 e dentre esses 5000 trazer o proporcional a percentagem do total geral.

Vamos supor que o DV 01 corresponda a 10% do total (20.000) e o DV 02 corresponda a 20% do Total, quero que retorne o equivalente a 5.000, sendo

DV 01 = 10% de 5.000 = 500 registros
DV 02 = 20% de 5.000 = 1000 registros
…
DV 99 = 5% de 5.000 = 50 registros

assim até atingir os 5.000 registros.

[faccParticipante]

Com essa SQL consigo saber a quantidade que a base possui para cada DV

select substr(secgccpf, 10, 2) DV,
count(*)
from tabela
group by substr(secgccpf, 10, 2)
order by substr(secgccpf, 10, 2)

Mas preciso que me retorne em Porcentagem para que, posteriormente, pegue a mesma porcentagem de uma quantidade definida pelo usuário, conforme o tópico anterior.

[fsitjaParticipante]

Pois é isso que está fazendo, repara que está sendo atribuído um valor aleatório a cada linha (DBMS_RANDOM.VALUE) e está sendo ordenado por aquele campo. Está sendo retornado os primeiros CPFs ordenados pelo valor aleatório, limitado ao tamanho da amostragem que você passar.

Se você executar várias vezes o SQL vai notar que cada execução retorna linhas diferentes, ficando claro que é aleatório. Vamos reduzir o tamanho da amostra e filtrar apenas pelo DV = 00 para ficar fácil de ver. Por exemplo:


SQL> CREATE TABLE PESSOA AS
2 SELECT to_char((power(10, 12)) * val_random, 'FM000000000000') AS cpf
3 FROM (SELECT dbms_random.VALUE val_random FROM dual CONNECT BY LEVEL
SQL> select * from
2 (SELECT p.cpf,
3 p.dv
4 FROM (SELECT cpf,
5 dv,
6 cont,
7 cont_distintos,
8 val_random,
9 row_number() OVER(PARTITION BY dv ORDER BY val_random) num_linha
10 FROM (SELECT substr(cpf, 11, 2) dv,
11 cpf,
12 COUNT() OVER(PARTITION BY substr(cpf, 11, 2) ORDER BY 0) cont,
13 COUNT() OVER(ORDER BY 0) cont_distintos,
14 dbms_random.VALUE val_random
15 FROM pessoa)
16 ORDER BY cont, row_number() OVER(PARTITION BY dv ORDER BY val_random)) p
17 WHERE num_linha
SQL> select * from
2 (SELECT p.cpf,
3 p.dv
4 FROM (SELECT cpf,
5 dv,
6 cont,
7 cont_distintos,
8 val_random,
9 row_number() OVER(PARTITION BY dv ORDER BY val_random) num_linha
10 FROM (SELECT substr(cpf, 11, 2) dv,
11 cpf,
12 COUNT() OVER(PARTITION BY substr(cpf, 11, 2) ORDER BY 0) cont,
13 COUNT() OVER(ORDER BY 0) cont_distintos,
14 dbms_random.VALUE val_random
15 FROM pessoa)
16 ORDER BY cont, row_number() OVER(PARTITION BY dv ORDER BY val_random)) p
17 WHERE num_linha


Só note que não há como chegar nos 5.000 certinho, é mais fácil ganhar na mega-sena. A porcentagem do total não vai converter numa quantidade inteira de linhas aplicando essa mesma porcentagem sobre o tamanho da amostragem.

[faccParticipante]

Sei que não serão uma conta perfeita, conversei com o responsavel e ele disse que não tem problema trazer todos os 5.000 registros.

Eu realmente precisava trazer, por ex. 10% correspondente ao DV 00, assim
Se o DV 00 corresponde a 500 registros, retornar os primeiros 500 registros correspondentes ao DV 00

Pq estou falando isso? É o seguinte a empresa que trabalho possui 71 lojas e com essa SQL que me passou, me retorna apenas as lojas de 1 a 9

[fsitjaParticipante]

Ocorre isso porque é aleatório por DV do CPF, não tem nada de loja ali… Ou aumenta a amostragem e roda várias vezes até sair uma amostragem arrumada ou tem que colocar loja como critério. Mas aí fica tão direcionado, por DV e Loja, que já deixa de ser algo aleatório na minha humilde opinião.

Tem motivo para ter a separação por DV? Não seria melhor particionar só por loja?

[faccParticipante]

resolvido… o responsavel me enviou arquivo errado… agora que me enviou o correto pude ver o funcionamento…

Muito obrigado mesmo.

[fsitjaParticipante]

Falou camarada, disponha 8)

[]’s
Francisco

[faccParticipante]

Pode me xingar…

Estou usando esse critério que em mostrou para rodar em 2 tabelas diferentes, sei que eles irão retornar dados sortidos toda vez que é executado, mas me falaram que o arquivo a ser exportado deve conter os mesmos registros, ou seja se CPF 123456789-00 está em um arquivo, o mesmo CPF deve estar no segundo arquivo,

E tem outra coisa… não posso contar os CPFs repitidos, caso haja algum repetido, ele deve somar mais a quantidade de outros (não posso eliminar os contratos repitidos)

Assim, se dentre todos, 3 CPFs se repetem, devo manter esses 3 e pegar outros 3 para a amostragem

[fsitjaParticipante]

Sobre a parte dos arquivos iguais, acho que o jeito seria resolver isso na “arquitetura”, digamos assim.
Como você está gerando o arquivo? Exporta um arquivo a partir de cada tabela? Se for isso então faz junto da gravação uma cópia de uma tabela igualzinho para a outra, pode ser um comando só até:


insert into tab2 (col1, col2, col3, etc)
select col1, col2, col3, etc from tab1

Assumo que as duas têm a mesma estrutura de colunas.

Fiquei confuso agora quanto à parada dos CPFs repetidos 😕

[faccParticipante]

Vc pode me dar uma luz? Pois fiz algo parecido com o que vc comentou, mas acaba demorando muito a execução.

create or replace procedure CYBELAR_EXP_TXT_ACSP is
/****************************************************************
* PROCEDURE : CYBELAR_EXP_TXT_ACSP *
* OBJETIVO : Exporta dados de uma tabela para um arquivo texto *
* CRIACAO : 07/01/2010 *
* VERSAO : 1.0 *
* AUTOR : FABIO A. CAMPOS DA CRUZ - *
****************************************************************/

---

-- DECLARACAO DE VARIAVEIS P/ CONTROLE DO PROCESSO --

---

ARQ_NRSORTE UTL_FILE.file_type;
VC_LINHA VARCHAR2(8000) := NULL;
VC_ARQ_NOME VARCHAR2(100) := NULL;
VC_DIR_LOG VARCHAR2(50) := NULL;
VC_DIR_OUT VARCHAR2(50) := NULL;
VG_PROCESSO VARCHAR2(100) := 'CYBELAR_EXP_TXT_ACSP';

---

-- CURSOR PARA GERACAO DO ARQUIVO --

---

CURSOR CUR_ACSP IS
SELECT p.ACSPCcpf,
P.ACSPDTAB,
P.ACSPINDP,
P.ACSPP06M,
P.ACSPP18M,
P.ACSPNRO,
P.ACSPMDIA,
P.ACSPDVM,
P.ACSPDPM,
P.ACSPNPM,
P.ACSPNROL,
P.ACSPNRC
FROM (SELECT ACSPCCPF,
dv,
cont,
cont_distintos,
val_random,
row_number() OVER(PARTITION BY dv ORDER BY val_random) num_linha,
ACSPDTAB,
ACSPINDP,
ACSPP06M,
ACSPP18M,
ACSPNRO,
ACSPMDIA,
ACSPDVM,
ACSPDPM,
ACSPNPM,
ACSPNROL,
ACSPNRC
FROM (SELECT substr(ACSPCcpf, 7, 2) dv,
ACSPCCPF,
COUNT() OVER(PARTITION BY substr(ACSPCcpf, 7, 2) ORDER BY 0) cont,
COUNT() OVER(ORDER BY 0) cont_distintos,
dbms_random.VALUE val_random,
ACSPDTAB,
ACSPINDP,
ACSPP06M,
ACSPP18M,
ACSPNRO,
ACSPMDIA,
ACSPDVM,
ACSPDPM,
ACSPNPM,
ACSPNROL,
ACSPNRC
FROM CYBELAR_ACSP)
ORDER BY cont,
row_number() OVER(PARTITION BY dv ORDER BY val_random)) p
WHERE num_linha <= (cont * (5000 / cont_distintos));

begin

---

-- BUSCA DIRETORIO --

---

BEGIN
SELECT PINT_NM_DIRETORIO_LOG, PINT_NM_DIRETORIO_OUT
INTO VC_DIR_LOG, VC_DIR_OUT
FROM GEMCO_PARAMETRO_INTERFACE PINT, GEMCO_SISTEMA SIST
WHERE PINT.PINT_CD_SISTEMA = SIST.SIST_CD_SISTEMA
AND SIST.SIST_DS_SISTEMA = VG_PROCESSO;
EXCEPTION
-- SE NAO EXISTIR INFORMAR O DIRETORIO ONDE DEVERA SER
-- GERADO O LOG DE OCORRENCIAS
WHEN NO_DATA_FOUND THEN
VC_DIR_LOG := 'homegemcointegraACSPLog';
VC_DIR_OUT := 'homegemcointegraACSP';
WHEN TOO_MANY_ROWS THEN
VC_DIR_LOG := 'homegemcointegraACSPLog';
VC_DIR_OUT := 'homegemcointegraACSP';
WHEN OTHERS THEN
VC_DIR_LOG := 'homegemcointegraACSPLog';
VC_DIR_OUT := 'homegemcointegraACSP';
END;

-- SETA NOME DO ARQUIVO
VC_ARQ_NOME := 'BACK_TEST_ACSP.txt';
-- INICIA GRAVACAO
ARQ_NRSORTE := UTL_FILE.fopen(VC_DIR_OUT, VC_ARQ_NOME, 'W');
UTL_FILE.fclose(ARQ_NRSORTE);

---

-- ABRE CURSOR PARA LEITURA E MONTAGEM --

---

trunctab('CYBELAR_AUX_ACSP');
FOR CUR IN CUR_ACSP LOOP

-- GRAVA O RESULTADO EM UMA TABELA AUXILIAR PARA DEPOIS SER USADA NA EXPORTAÇÃO 
-- PARA O SERASA.  

/*
INSERT INTO CYBELAR_AUX_SERASA
(SECGCCPF,
SEDTAPRO,
SEPERFOR,
SENRCON,
SEVLRCON,
SEVLRENT,
SEQTPARC,
SEDTNASC,
SESEXOCL,
SERENDA,
SEENDERE,
SECIDADE,
SEESTCIV,
SEESCOLA,
SENRDEPE,
SENATOCU,
SEPROFIS,
SEFONERE)
SELECT SECGCCPF,
SEDTAPRO,
SEPERFOR,
SENRCON,
SEVLRCON,
SEVLRENT,
SEQTPARC,
SEDTNASC,
SESEXOCL,
SERENDA,
SEENDERE,
SECIDADE,
SEESTCIV,
SEESCOLA,
SENRDEPE,
SENATOCU,
SEPROFIS,
SEFONERE
FROM CYBELAR_SERASA
WHERE SECGCCPF = CUR.ACSPCCPF;

COMMIT;*/

VC_LINHA := LPAD(CUR.ACSPCCPF, 11, 0) || ';' ||
            LPAD(CUR.ACSPDTAB, 8, 0) || ';' || CUR.ACSPINDP || ';' ||
            CUR.ACSPP06M || ';' || CUR.ACSPP18M || ';' ||
            LPAD(CUR.ACSPNRO, 2, 0) || ';' || LPAD(CUR.ACSPMDIA, 4, 0) || ';' ||
            LPAD(CUR.ACSPDVM, 8, 0) || ';' || LPAD(CUR.ACSPDPM, 8, 0) || ';' ||
            LPAD(CUR.ACSPNPM, 2, 0) || ';' || LPAD(CUR.ACSPNROL, 3, 0) || ';' ||
            LPAD(CUR.ACSPNRC, 11, 0);

-- GRAVA O REGISTRO NO ARQUIVO
ARQ_NRSORTE := UTL_FILE.fopen(VC_DIR_OUT, VC_ARQ_NOME, 'A');
UTL_FILE.put_line(ARQ_NRSORTE, VC_LINHA);
UTL_FILE.fclose(ARQ_NRSORTE);

END LOOP;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
dbms_output.put_line('ERRO: INVALID PATH - ' || SQLCODE || ' ' ||
SQLERRM);
WHEN UTL_FILE.INVALID_MODE THEN
dbms_output.put_line('ERRO: FILE ' || VC_DIR_LOG || ' / ' ||
VC_DIR_OUT || ' INVALID MODE - ' || SQLCODE || ' ' ||
SQLERRM);
WHEN UTL_FILE.INVALID_OPERATION THEN
dbms_output.put_line('ERRO: FILE ' || VC_DIR_LOG || ' / ' ||
VC_DIR_OUT || ' INVALID OPERATION - ' || SQLCODE || ' ' ||
SQLERRM);
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20902, SQLCODE || ' ' || SQLERRM);
WHEN UTL_FILE.INTERNAL_ERROR THEN
dbms_output.put_line('ERRO: INTERNAL ERROR - ' || SQLCODE || ' ' ||
SQLERRM);
WHEN UTL_FILE.READ_ERROR THEN
dbms_output.put_line('ERRO: READ ERROR - ' || SQLCODE || ' ' ||
SQLERRM);
WHEN UTL_FILE.WRITE_ERROR THEN
dbms_output.put_line('ERRO: WRITE ERROR - ' || SQLCODE || ' ' ||
SQLERRM);
WHEN VALUE_ERROR THEN
dbms_output.put_line('ERRO: VALUE ERROR - ' || SQLCODE || ' ' ||
SQLERRM);
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);

end CYBELAR_EXP_TXT_ACSP;


Creio que o trecho comentado que está ocasionando a demora, pois eliminando ele roda normalmente.

[Autor]

Posts