Dúvidas SELECT ( COUNT + CASE ) por Faixa Etária e Sexo

vitellozzi · Sex, 12 Nov 2010 4:06 pm

Olá Pessoal, Boa Tarde.

Preciso mais uma ajuda de vocês ... veja que elaborei esse SELECT para obter obter os usuários cadastrados no periodo de 2 anos e conforme a faixa etária 0 - 18 depois 19 - 23 ... 24 a 28 ... 29 a 33 e acima de 59, a soma de M e F. Mas, conforme select abaixo só consegui chegar nessa solução e não consegui elaborar. Podem me ajudar:

Selecionar tudo

SELECT SUBSTR(U.DTINICIO,7,4) DT_INICIO, IDADE(V.DTNASCIMENTO, SYSDATE) IDADE, V.CDSEXO, U.NRCONTRATO,
       DECODE(V.CDINTERCAMBIO,'P','Pre Pagamento','C','Custo Operacional', 'N','Sem Intercambio', 'M','Múltiplo') INTERCAMBIO,
       (SELECT COUNT(CASE VU.CDSEXO WHEN 'M' THEN 1 END) FROM V_USUARIO VU) AS MASC
       FROM V_USUARIO_PESQUISA V , V_USUARIO VU, USUARIO U , TIPO_DE_USUARIO T
       WHERE U.DTINICIO BETWEEN '01/10/2008' AND '31/10/2010'
       AND V.NRREGISTRO_USUARIO = U.NRREGISTRO_USUARIO AND VU.NRREGISTRO_USUARIO = U.NRREGISTRO_USUARIO
       AND V.TPUSUARIO = T.TPUSUARIO AND U.DTEXCLUSAO IS NULL

Resultado ficou:

Selecionar tudo

  DT_INICIO   IDADE   SEXO   CONTRATO   INTERCAMBIO     MASC
1	   2010	      40	    M	     4001	  Sem Intercambio	  15226
2	   2008	      28	    M	     4006	  Sem Intercambio	  15226
3	   2008	      28	    M	     4006	  Sem Intercambio	  15226
4	   2008	      28	    M	     4006	  Sem Intercambio	  15226
5	   2008  	    28	    M	     4006   Sem Intercambio	  15226

Para conseguir a soma de M e F da tabela elaborei esse :

Selecionar tudo

SELECT DISTINCT (SELECT COUNT(CDSEXO) FROM V_USUARIO WHERE CDSEXO = 'M' ) AS MASC,
       (SELECT COUNT(CDSEXO) FROM V_USUARIO WHERE CDSEXO = 'F') AS FEM
FROM V_USUARIO

Mas, pelo que analisaram não consigo separar pela faixa etária ...

Desde já agradeço ...

vitellozzi · Sex, 12 Nov 2010 4:13 pm

Conclusão

Preciso de algo assim :

Que informa o período ... depois se o sexo é M ou F, totalizando o sexo dentro da faixa etária.

Ficando assim exemplo :

Selecionar tudo

PERIODO SEXO_M TOTAL_M SEXO_F TOTAL_F IDADE TOTAL
2010 M  50 0-18 F 100 0-18 150

Agradeço muita a ajuda ... [ ]'s

SergioLBJr · Sex, 12 Nov 2010 4:47 pm

Faz um teste aí cara

Selecionar tudo

SELECT DISTINCT CD_SEXO, 
                                COUNT(VU.CDSEXO) OVER (PARTITION BY CDSEXO, CASE 
                                                                                                                          WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 0 AND 18 THEN 1
                                                                                                                          WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 19 AND 23 THEN 2
                                                                                                                          WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 24 AND 28 THEN 3
                                                                                                                          WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 29 AND 33 THEN 4
                                                                                                                          WHEN IDADE(V.DTNASCIMENTO,SYSDATE) > 59 THEN 5
                                                                                                                          END) AS TOTAL_POR_SEXO_E_IDADE,
                               COUNT (VU.CDSEXO) OVER(PARTITION BY CASE 
                                                                                                         WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 0 AND 18 THEN 1
                                                                                                         WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 19 AND 23 THEN 2
                                                                                                         WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 24 AND 28 THEN 3
                                                                                                         WHEN IDADE(V.DTNASCIMENTO,SYSDATE) BETWEEN 29 AND 33 THEN 4
                                                                                                         WHEN IDADE(V.DTNASCIMENTO,SYSDATE) > 59 THEN 5
                                                                                                         END) AS TOTAL_POR_IDADE
FROM V_USUARIO_PESQUISA V,
            V_USUARIO VU,
            USUARIO U
WHERE V.NRREGISTRO_USUARIO = VU.NRREGISTRO_USUARIO
    AND VU.NRREGISTRO = U.NRREGISTRO
    AND U.DTEXCLUSAO IS NULL

Retorna dpoiz aí o que deu.

----- > Só um toque, procura utiliza a tag code pra postar código, fica bem ruim de ler sql quando o código da no meio do texto.

vitellozzi · Ter, 16 Nov 2010 7:22 am

Ao testar ... ocorreu o seguinte erro :

Selecionar tudo

ORA_00918: column ambiguously defined

Devido ao o campo CD_SEXO não existir, o correto é VU.CDSEXO. Também tive que apontar a tabela e campo depois do PARTITION BY ... ficando VU.CDSEXO. Rodei mas, fica processando por vários minutos sem obter êxito.

Vou tentar acrescentar sua idéia no meu SELECT anterior e se obter sucesso vou postar no forúm.

Desde já agradeço pela ajuda ...

SergioLBJr · Ter, 16 Nov 2010 10:27 am

Fica complicado montar o select certinho sem ter as tabelas.

Mas a ideia é que ele monte um agrupamento pelo campo cd_sexo para saber quantos por sexo que existe. E um agrupamento por faxa etária que é discriminada pelo case, ele vai fazer agrupamento por 1 os que estiverem entre 0 e 18, vai agrupar por 2 os que estiverem entre 19 e 23 e assim por diante.

alexandervinson · Qua, 17 Nov 2010 9:11 am

Para uma tabela contento dois campos um data e outro sexo a seguinte consulta também pode ser usada

Selecionar tudo

select t.sexo,
       CASE
         when trunc((sysdate - t.data) / 365) between 0 and 18 then 1
         when trunc((sysdate - t.data) / 365) between 19 and 23 then 2
         when trunc((sysdate - t.data) / 365) between 24 and 28 then 3
         when trunc((sysdate - t.data) / 365) between 29 and 33 then 4
         when trunc((sysdate - t.data) / 365) between 24 and 59 then 5
         when trunc((sysdate - t.data) / 365) > 59 then 6
       end Faixa,
       count(1)
  from teste_data t
 group by t.sexo,
          CASE
         when trunc((sysdate - t.data) / 365) between 0 and 18 then 1
         when trunc((sysdate - t.data) / 365) between 19 and 23 then 2
         when trunc((sysdate - t.data) / 365) between 24 and 28 then 3
         when trunc((sysdate - t.data) / 365) between 29 and 33 then 4
         when trunc((sysdate - t.data) / 365) between 24 and 59 then 5
         when trunc((sysdate - t.data) / 365) > 59 then 6
       end

vitellozzi · Qua, 17 Nov 2010 12:38 pm

Olá Pessoal,

Deu certo ... ficou assim :

Selecionar tudo

SELECT P.CDSEXO, 
       CASE 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 0 AND 18 THEN 1  -- F 3695 M 3833
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) > 59 THEN 6 
       END FAIXA, 
       COUNT(1) 
  FROM USUARIO U, PESSOA P
 WHERE U.NRREGISTRO_USUARIO = P.NRREGISTRO AND P.CDSEXO IS NOT NULL
 GROUP BY P.CDSEXO, 
       CASE 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 0 AND 18 THEN 1 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5 
         WHEN IDADE(P.DTNASCIMENTO, SYSDATE) > 59 THEN 6 
       END

Agradeço pelas dicas ...

Abraços

alexandervinson · Qua, 17 Nov 2010 2:10 pm

Oi vitellozzi,
Que bom que a idéia tenha funcionado.

Só para confirmar... esta função IDADE que tu usas foi uma que tu implementaste né.....?

Alex

vitellozzi · Qui, 18 Nov 2010 7:11 am

Olá Alex, Bom Dia.

Existe uma FUNCTION em meu sistema :

Selecionar tudo

create or replace function idade
(PdtNasc in date, PdtRefer in date) return number
as
   vnrDiar varchar2(2);
   vnrMesr varchar2(2);
   vnrAnor varchar2(4);
   vnrDian varchar2(2);
   vnrMesn varchar2(2);
   vnrAnon varchar2(4);
   Vlresult number (04) ;
begin
   vnrDian := to_char(PdtNasc, 'dd');
   vnrMesn := to_char(PdtNasc, 'mm');
   vnrAnon := to_char(PdtNasc, 'yyyy');
   vnrDiar := to_char(PdtRefer, 'dd');
   vnrMesr := to_char(PdtRefer, 'mm');
   vnrAnor := to_char(PdtRefer, 'yyyy');

       if vnrMesr||vnrDiar >= vnrMesn||vnrDian then
          VlResult := to_number(vnrAnor) - to_number(vnrAnon);
       else
          VlResult := to_number(vnrAnor) - to_number(vnrAnon) - 1;
       end if;

   if VlResult > 0 then
      Return VlResult ;
   else
      Return 0 ;
end if ;

end;

Abraços

fsitja · Qui, 18 Nov 2010 10:41 am

Olá Vitellozi,

Eu sugiro você substituir a function por um cálculo direto no SQL, por questão de performance. No SQL postado acima há 12 chamadas para a função, e isso acrescenta um peso enorme no SQL fazendo chaveamento de contexto entre a engine SQL e a PL/SQL, para algo que dá para resolver apenas no SQL da forma abaixo:

Selecionar tudo

  SELECT CDSEXO,
         CASE
         WHEN IDADE BETWEEN 0 AND 18 THEN 1 -- F 3695 M 3833
         WHEN IDADE BETWEEN 19 AND 23 THEN 2
         WHEN IDADE BETWEEN 24 AND 28 THEN 3
         WHEN IDADE BETWEEN 29 AND 33 THEN 4
         WHEN IDADE BETWEEN 24 AND 59 THEN 5
         WHEN IDADE > 59 THEN 6
         END FAIXA,
         COUNT(*)
  FROM (SELECT u.*,
               trunc(months_between(sysdate, P.DTNASCIMENTO) / 12) as idade,
               p.cdsexo,
               p.dtnascimento
          FROM USUARIO U
          JOIN PESSOA P on U.NRREGISTRO_USUARIO = P.NRREGISTRO AND P.CDSEXO IS NOT NULL)  u
  GROUP BY CDSEXO,
           CASE
           WHEN IDADE BETWEEN 0 AND 18 THEN 1
           WHEN IDADE BETWEEN 19 AND 23 THEN 2
           WHEN IDADE BETWEEN 24 AND 28 THEN 3
           WHEN IDADE BETWEEN 29 AND 33 THEN 4
           WHEN IDADE BETWEEN 24 AND 59 THEN 5
           WHEN IDADE > 59 THEN 6 END

Abaixo faço um teste de desempenho comparando:

Selecionar tudo

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 
Connected as FSITJA
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION idade(pdtnasc IN DATE, pdtrefer IN DATE) RETURN NUMBER AS
  2    vnrdiar  VARCHAR2(2);
  3    vnrmesr  VARCHAR2(2);
  4    vnranor  VARCHAR2(4);
  5    vnrdian  VARCHAR2(2);
  6    vnrmesn  VARCHAR2(2);
  7    vnranon  VARCHAR2(4);
  8    vlresult NUMBER(04);
  9  BEGIN
 10    vnrdian := to_char(pdtnasc, 'dd');
 11    vnrmesn := to_char(pdtnasc, 'mm');
 12    vnranon := to_char(pdtnasc, 'yyyy');
 13    vnrdiar := to_char(pdtrefer, 'dd');
 14    vnrmesr := to_char(pdtrefer, 'mm');
 15    vnranor := to_char(pdtrefer, 'yyyy');
 16    IF vnrmesr || vnrdiar >= vnrmesn || vnrdian
 17    THEN
 18      vlresult := to_number(vnranor) - to_number(vnranon);
 19    ELSE
 20      vlresult := to_number(vnranor) - to_number(vnranon) - 1;
 21    END IF;
 22    IF vlresult > 0
 23    THEN
 24      RETURN vlresult;
 25    ELSE
 26      RETURN 0;
 27    END IF;
 28  END;
 29  /
 
Function created
SQL> create table usuario as
  2  select level as nrregistro_usuario
  3    from dual connect by level <= 1000000;
 
Table created
SQL> create table pessoa as
  2  select level as nrregistro,
  3         trunc(sysdate - level) as dtnascimento,
  4         decode(trunc(dbms_random.value*2), 0, 'M', 'F') as cdsexo
  5    from dual connect by level <= 1000000;
 
Table created
SQL> set serveroutput on
SQL> DECLARE
  2    t1   NUMBER;
  3    t2   NUMBER;
  4    qtty NUMBER;
  5  BEGIN
  6    -- executa para carregar no buffer e realizar parse
  7    select count(*) into qtty from (
  8    SELECT P.CDSEXO,
  9    CASE
 10    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 0 AND 18 THEN 1 -- F 3695 M 3833
 11    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2
 12    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3
 13    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4
 14    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5
 15    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) > 59 THEN 6
 16    END FAIXA,
 17    COUNT(1)
 18    FROM USUARIO U, PESSOA P
 19    WHERE U.NRREGISTRO_USUARIO = P.NRREGISTRO AND P.CDSEXO IS NOT NULL
 20    GROUP BY P.CDSEXO,
 21    CASE
 22    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 0 AND 18 THEN 1
 23    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2
 24    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3
 25    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4
 26    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5
 27    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) > 59 THEN 6 END);
 28  
 29    t1 := dbms_utility.get_time;
 30    select count(*) into qtty from (
 31    SELECT P.CDSEXO,
 32    CASE
 33    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 0 AND 18 THEN 1 -- F 3695 M 3833
 34    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2
 35    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3
 36    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4
 37    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5
 38    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) > 59 THEN 6
 39    END FAIXA,
 40    COUNT(1)
 41    FROM USUARIO U, PESSOA P
 42    WHERE U.NRREGISTRO_USUARIO = P.NRREGISTRO AND P.CDSEXO IS NOT NULL
 43    GROUP BY P.CDSEXO,
 44    CASE
 45    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 0 AND 18 THEN 1
 46    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2
 47    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3
 48    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4
 49    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5
 50    WHEN IDADE(P.DTNASCIMENTO, SYSDATE) > 59 THEN 6 END);
 51    t2 := dbms_utility.get_time;
 52    dbms_output.put_line(qtty || ' registros em ' || to_char((t2 - t1) / 100, '990.000') || ' segundos');
 53    -- executa para carregar no buffer e realizar parse
 54    select count(*) into qtty from (
 55    SELECT CDSEXO,
 56           CASE
 57           WHEN IDADE BETWEEN 0 AND 18 THEN 1 -- F 3695 M 3833
 58           WHEN IDADE BETWEEN 19 AND 23 THEN 2
 59           WHEN IDADE BETWEEN 24 AND 28 THEN 3
 60           WHEN IDADE BETWEEN 29 AND 33 THEN 4
 61           WHEN IDADE BETWEEN 24 AND 59 THEN 5
 62           WHEN IDADE > 59 THEN 6
 63           END FAIXA,
 64           COUNT(*)
 65    FROM (SELECT u.*,
 66                 trunc(months_between(sysdate, P.DTNASCIMENTO) / 12) as idade,
 67                 p.cdsexo,
 68                 p.dtnascimento
 69            FROM USUARIO U
 70            JOIN PESSOA P on U.NRREGISTRO_USUARIO = P.NRREGISTRO AND P.CDSEXO IS NOT NULL)  u
 71    GROUP BY CDSEXO,
 72             CASE
 73             WHEN IDADE BETWEEN 0 AND 18 THEN 1
 74             WHEN IDADE BETWEEN 19 AND 23 THEN 2
 75             WHEN IDADE BETWEEN 24 AND 28 THEN 3
 76             WHEN IDADE BETWEEN 29 AND 33 THEN 4
 77             WHEN IDADE BETWEEN 24 AND 59 THEN 5
 78             WHEN IDADE > 59 THEN 6 END);
 79    t1 := dbms_utility.get_time;
 80    select count(*) into qtty from (
 81    SELECT CDSEXO,
 82           CASE
 83           WHEN IDADE BETWEEN 0 AND 18 THEN 1 -- F 3695 M 3833
 84           WHEN IDADE BETWEEN 19 AND 23 THEN 2
 85           WHEN IDADE BETWEEN 24 AND 28 THEN 3
 86           WHEN IDADE BETWEEN 29 AND 33 THEN 4
 87           WHEN IDADE BETWEEN 24 AND 59 THEN 5
 88           WHEN IDADE > 59 THEN 6
 89           END FAIXA,
 90           COUNT(*)
 91    FROM (SELECT u.*,
 92                 trunc(months_between(sysdate, P.DTNASCIMENTO) / 12) as idade,
 93                 p.cdsexo,
 94                 p.dtnascimento
 95            FROM USUARIO U
 96            JOIN PESSOA P on U.NRREGISTRO_USUARIO = P.NRREGISTRO AND P.CDSEXO IS NOT NULL)  u
 97    GROUP BY CDSEXO,
 98             CASE
 99             WHEN IDADE BETWEEN 0 AND 18 THEN 1
100             WHEN IDADE BETWEEN 19 AND 23 THEN 2
101             WHEN IDADE BETWEEN 24 AND 28 THEN 3
102             WHEN IDADE BETWEEN 29 AND 33 THEN 4
103             WHEN IDADE BETWEEN 24 AND 59 THEN 5
104             WHEN IDADE > 59 THEN 6 END);
105    t2 := dbms_utility.get_time;
106    dbms_output.put_line(qtty || ' registros em ' || to_char((t2 - t1) / 100, '990.000') || ' segundos');
107  end;
108  /
 
12 registros em  154.380 segundos
12 registros em   31.960 segundos
 
PL/SQL procedure successfully completed
SQL> drop table usuario;
 
Table dropped
SQL> drop table pessoa;
 
Table dropped

Como você pode ver, para 1 milhão de registros de usuário/pessoa, o desempenho fica 5 vezes melhor:
154.380 segundos com functions

x

31.960 segundos sem functions .

Abraço,
Francisco.

vitellozzi · Qui, 18 Nov 2010 12:58 pm

Agradeço a dica ...

No meu caso, como vou executar uma vez a cada um ano essa informação gerou em 1 min 45 seg ( 3170 rows selected in 105,453 seconds )

Abraços

Dúvidas SELECT ( COUNT + CASE ) por Faixa Etária e Sexo

Quem está online