Dados Aleatórios

[facc]

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.

[facc]

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

[Trevisolli]

Falai brother, beleza?

Cara, uma Dica, acho que você resolve esse teu problema com duas contagens na base.

1) Contar os CPF's dessa tabela agrupando por DV, ou, selecionando um DV específico (acho que no teu caso, se agrupar por DV, tem todas as quantidades).

2) Contar o total de linhas dessa tabela.

Com esses dois valores brother, você mata tua charada, resolve todas as outras questões que postou aqui.

Espero ter ajudado.

qualquer coisa, manda pra gente.

[facc]

Valeu.

Para agrupar por DV posso utilizar normalmente o

Selecionar tudo

Substring(CPF, 9, 2)

?

[Trevisolli]

Isso brother,
To sem o Oracle nessa máquina agora pra fazer o teste, mas, agrupa pelo substring desse campo, posto que o mesmo (DV), num está em campo separado correto???
Só analisa a performance dessa tua query. Talvez se você inserisse esses dois dígitos numa outra coluna, ficaria melhor..mas, dá uma analisada ai e, qualquer coisa, manda pra gente.

[fsitja]

Olá, notei que a pergunta foi postada em dois forums, aqui e no ProfissionaisOracle, então vou postar minha resposta aqui também:

-----------

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.

Selecionar tudo

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; 

[facc]

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.

[facc]

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

Selecionar tudo

select substr(secgccpf, 10, 2) DV,
       count(*)
  from cybelar_SERASA
 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.

[fsitja]

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:

Selecionar tudo

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 <= 10000);
 
Table created
 
SQL> 
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 <= (cont * (100 / cont_distintos))
 18   ORDER BY p.dv
 19   ) where dv = 00;
 
CPF           DV
------------- --
948605629900  00
 
SQL> 
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 <= (cont * (100 / cont_distintos))
 18   ORDER BY p.dv
 19   ) where dv = 00;
 
CPF           DV
------------- --
150227561600  00
 
SQL> 

[fsitja]

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.

[facc]

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

[facc]

Olha eu aqui novamente...

Cada dia que passa eles inventam moda...

Mas vamos ao que interessa, na verdade preciso trazer 5000 CPFs diferentes e não 5000 registros...

Como posso resolver esse impasse??

PS.: Não posso colocar select distinct, pois preciso trazer os CPFs repitidos, quando houver.

[diegolenhardt]

Distinct se tiver 2 vezes, vai trazer uma só, não quer dizer que não trará nenhuma vez.

[facc]

Distinct se tiver 2 vezes, vai trazer uma só, não quer dizer que não trará nenhuma vez.

Não entendi o que você quiz dizer

[diegolenhardt]

Se você tem 2 linhas repetidas, e usar o distinct, somente uma será retornada,

pelo que entendi, você achou que nenhuma seria retornada.. certo?

[facc]

Consegui resolver com a ajuda de outro forum e do usuário fsitja
Para quem precisar, está aí o SELECT que precisava.

Selecionar tudo

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, 
               dense_rank() over(PARTITION BY dv ORDER BY val_random) num_linha, 
               acspdtab, 
               acspindp, 
               acspp06m, 
               acspp18m, 
               acspnro, 
               acspmdia, 
               acspdvm, 
               acspdpm, 
               acspnpm, 
               acspnrol, 
               acspnrc 
          FROM (SELECT substr(to_char(acspccpf, '00000000000'), 11, 2) dv, 
                       to_char(acspccpf, '00000000000') cpf, 
                       COUNT(DISTINCT acspccpf) over(PARTITION BY substr(to_char(acspccpf, '00000000000'), 11, 2)) cont, 
                       COUNT(DISTINCT acspccpf) over() cont_distintos, 
                       AVG(dbms_random.value) over(PARTITION BY acspccpf) val_random, 
                       acspdtab, 
                       acspindp, 
                       acspp06m, 
                       acspp18m, 
                       acspnro, 
                       acspmdia, 
                       acspdvm, 
                       acspdpm, 
                       acspnpm, 
                       acspnrol, 
                       acspnrc 
                  FROM cybelar_acsp) 
         ORDER BY cont, dense_rank() over(PARTITION BY dv ORDER BY val_random)) p 
 WHERE num_linha <= (cont * (5000 / cont_distintos));

[rmarques]

Caro amigo, não sei se entendi direito, mas vamos lá

Você disse que precisa calcular os nº de CPF fazendo este cálculo pelo digito verificador correto? e além disto saber os percentuais de cada calculo.

Bem você também informou que o total de registros é de 900.000 ok.

Bem com estes dados em mente eu aconselho fazer o seguinte:

1 - Faça uma view com um count conforme exemplo abaixo:

Selecionar tudo

create view CPFView as
select substr(cpf,10,2) as CPF,
          count(substr(cpf,10,2)) as Total
from cpf
group by CPF

OBS: Desta forma trará todos os CPFs 00 à 99 com os seus respectivos totais

Após ter feito isto faça o seguinte:

2.

Selecionar tudo

Select CPF,Total, Total/900.000 as Perc
     from CPFView

Isto trara todos os dados a que se refere.

PS: Estes dados não se deve repetir, ou seja você não terá dados como 01,35,35% - 01,25,15%, se isto acontecer existe erro de cadastro.

Sem mais um abraço

Ricardo