Doubts SELECT (COUNT + CASE) by age group and sex

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 12 Nov 2010 4:06 pm

Hello Everybody, Good Afternoon.

I need one more help from you ... see that elaborated that SELECT to get obtain the registered users in the period of 2 years and as the age group 0-18 after 19-23 ... 24 to 28. 29 to 33 and 59 above, the sum of M and F, but select only managed to get below as this solution and could not elaborate. Can you help me: SELECT SUBSTR (u. DTINICIO .7 .4) DT_INICIO, AGE (v. DTNASCIMENTO, SYSDATE) age, v. CDSEXO, u. NRCONTRATO, DECODE (v. CDINTERCAMBIO, ' P ', ' Pre payment ', ' c ', ' operating costs ', 'n', ' Without Exchange ', 'm ', ' Multiple '), EXCHANGE (SELECT COUNT (CASE VU.CDSEXO WHEN'm ' THEN 1 END) FROM V_USUARIO VU) the MEN FROM V_USUARIO_PESQUISA V, V_USUARIO VU, USER U, TIPO_DE_USUARIO T WHERE DTINICIO BETWEEN 10/1/2008 ' U ' AND ' 10/31/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 result was: DT_INICIO AGE SEX MASC 2010 1 EXCHANGE CONTRACT 40 M 4001 Without Exchange 2008 28 M 2 15226 4006 Without Exchange 2008 3 28 M 4006 15226 Without Exchange 2008 28 M 4 15226 4006 Without Exchange 5 M 28 2008 15226 4006 Without 15226 Exchange to get the sum of M and F of table elaborated this : SELECT DISTINCT (SELECT COUNT (CDSEXO) FROM V_USUARIO WHERE CDSEXO = 'm ') the MEN, (SELECT COUNT (CDSEXO) FROM V_USUARIO WHERE CDSEXO = ' F ') the WOM FROM V_USUARIO But, by reviewing can't separate by age group ...

I thank you ...
vitellozzi
Location: Itatiba

Poston Fri, 12 Nov 2010 4:13 pm

Conclusion I need something like this: that States the period ... then if the sex is M or F, totaling sex within the age group.

Thus example: SEXO_M TOTAL_M TOTAL_F SEXO_F PERIOD AGE 2010 M 0 50 TOTAL-18 F 18 0-100 150 Appreciate much help ... [ ]'s
vitellozzi
Location: Itatiba

Poston Fri, 12 Nov 2010 4:47 pm

Does a test aí Guy
Code: Select all
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
Returns dpoiz aí what gave.



-----> Just a touch, search uses the tag code to post code, is pretty bad to read sql when the code in the middle of the text.
SergioLBJr
Location: Parobé - RS

Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

Poston Tue, 16 Nov 2010 7:22 am

To test the following error occurred: ORA_00918: column ambiguously defined due to the CD_SEXO field does not exist, it is wise to VU.CDSEXO. I also had to point out the table and field after PARTITION BY ... getting VU.CDSEXO. I ran but, is suing for several minutes without success.

I'll try to add your idea in my previous SELECT and obtain success I'll post in the Forum.

I thank you for your help ...
vitellozzi
Location: Itatiba

Poston Tue, 16 Nov 2010 10:27 am

It is complicated to assemble the select right without having the tables.

But the idea is that he mount a group by field cd_sexo to find out how many for sex there is. And a grouping by age group which is discriminated against by the corset case, he's going to do group by 1 those between 0 and 18, will group by 2 those who are between 19 and 23, and so on.
SergioLBJr
Location: Parobé - RS

Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

Poston Wed, 17 Nov 2010 9:11 am

For a table containing two fields a date and other sex the following query can also be used
Code: Select all
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
alexandervinson
Location: Belem - Para

Poston Wed, 17 Nov 2010 12:38 pm

Hello guys, it worked ... went like this: SELECT P. CDSEXO, CASE WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 0 AND THEN 18-1-F 3695 M 3833 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5 WHEN AGE (p. DTNASCIMENTO, SYSDATE) > 59 THEN 6 END track, COUNT (1) FROM USER U, PERSON P WHERE u. NRREGISTRO_USUARIO = p. NRREGISTRO AND p. CDSEXO IS NOT NULL GROUP BY p. CDSEXO, CASE WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 0 AND 18 THEN 1 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 19 AND 23 THEN 2 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 28 THEN 3 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 29 AND 33 THEN 4 WHEN AGE (p. DTNASCIMENTO, SYSDATE) BETWEEN 24 AND 59 THEN 5 WHEN AGE (p. DTNASCIMENTO, SYSDATE) > 59 THEN 6 END Thanks for the tips ...

Hugs
vitellozzi
Location: Itatiba

Poston Wed, 17 Nov 2010 2:10 pm

Hi vitellozzi, glad the idea worked.

Just to confirm ... This function AGE you wear was a implementaste you know ...?

Alex
alexandervinson
Location: Belem - Para

Poston Thu, 18 Nov 2010 7:11 am

Hello Alex, Hello.

There is a FUNCTION in my system: create or replace function age (PdtNasc in date, PdtRefer in date) return number the 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;

Hugs
vitellozzi
Location: Itatiba

Poston Thu, 18 Nov 2010 10:41 am

Hello Vitellozi, I suggest you replace the function by a direct calculation in SQL, for the sake of performance. In SQL posted above there are 12 calls to the function, and this adds a huge weight in SQL doing context switching between the engine SQL and PL/SQL, to something that can solve only in SQL as below:
Code: Select all
  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
Below do a performance test comparing:
Code: Select all
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
as you can see, for 1 million user records/person, the performance is 5 times better: 154,380 seconds with functions x 31,960 seconds without functions .

Hug, Francisco.
fsitja
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Poston Thu, 18 Nov 2010 12:58 pm

Thanks for the tip ...

In my case, as I will run once each year that information generated in 1 min 45 sec (3170 rows selected in 105.453 seconds) Hugs
vitellozzi
Location: Itatiba


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 9 guests