Para mostrar un reporte de la cantidad de ventas por canal y pais habia que hacer el siguiente query:
SQL> conn sh/sh@rop111
Connected.
SQL> select co.country_name,
2 sum(decode(ch.channel_desc,'Direct Sales',1,0)) "Direct Sales",
3 sum(decode(ch.channel_desc,'Tele Sales',1,0)) "Tele Sales",
4 sum(decode(ch.channel_desc,'Catalog',1,0)) "Catalog",
5 sum(decode(ch.channel_desc,'Internet',1,0)) "Internet",
6 sum(decode(ch.channel_desc,'Partners',1,0)) "Partners"
7 from sales sa,
8 customers cu,
9 countries co,
10 channels ch
11 where sa.channel_id = ch.channel_id
12 and sa.cust_id = cu.cust_id
13 and cu.country_id = co.country_id
14 group by co.country_name
15 order by co.country_name
16 ;
COUNTRY_NAME Direct Sales Tele Sales Catalog Internet Partners
---------------------------------------- ------------ ---------- ---------- ---------- ----------
Argentina 126 51 0 23 2
Australia 20169 5 0 4364 9147
Brazil 75 46 0 27 32
Canada 11474 6 0 3601 7777
China 2 9 0 7 1
Denmark 9870 10 0 2084 4687
France 21807 5 0 3577 7689
Germany 53864 4 0 9426 18684
Italy 26810 69 0 5193 10498
Japan 31895 1 0 8715 19572
New Zealand 0 7 0 0 0
Poland 4 3 0 9 2
Saudi Arabia 1 0 0 6 0
Singapore 11553 8 0 4198 9494
Spain 10320 0 0 2207 4609
Turkey 154 0 0 3 11
United Kingdom 37786 5 0 6390 14457
United States of America 304418 1845 0 68586 151363
18 rows selected.
La consulta es poco clara, con pivot es mucho mas fácil:
SQL> select * from
2 (select co.country_name,
3 ch.channel_desc
4 from sales sa,
5 customers cu,
6 countries co,
7 channels ch
8 where sa.channel_id = ch.channel_id
9 and sa.cust_id = cu.cust_id
10 and cu.country_id = co.country_id )
11 pivot
12 ( count(1)
13 for channel_desc in ('Direct Sales','Tele Sales','Catalog','Internet','Partners')
14 )
15 order by country_name;
COUNTRY_NAME 'Direct Sales' 'Tele Sales' 'Catalog' 'Internet' 'Partners'
---------------------------------------- -------------- ------------ ---------- ---------- ---------
Argentina 126 51 0 23 2
Australia 20169 5 0 4364 9147
Brazil 75 46 0 27 32
Canada 11474 6 0 3601 7777
China 2 9 0 7 1
Denmark 9870 10 0 2084 4687
France 21807 5 0 3577 7689
Germany 53864 4 0 9426 18684
Italy 26810 69 0 5193 10498
Japan 31895 1 0 8715 19572
New Zealand 0 7 0 0 0
Poland 4 3 0 9 2
Saudi Arabia 1 0 0 6 0
Singapore 11553 8 0 4198 9494
Spain 10320 0 0 2207 4609
Turkey 154 0 0 3 11
United Kingdom 37786 5 0 6390 14457
United States of America 304418 1845 0 68586 151363
18 rows selected.
El resultado es el mismo pero la consulta es mas intuitiva y mas sencilla de construir.
Ahora, si quisiera pasar de formato matriz a tabular uso la función opuesta UNPIVOT:
SQLPLUS>
create table t_matriz
as
select * from
(select co.country_name,
ch.channel_desc
from sales sa,
customers cu,
countries co,
channels ch
where sa.channel_id = ch.channel_id
and sa.cust_id = cu.cust_id
and cu.country_id = co.country_id )
pivot
( count(1)
for channel_desc in ('Direct Sales','Tele Sales','Catalog','Internet','Partners')
)
order by country_name
Table created.
SQL> desc t_matriz
Name Null? Type
----------------------------------------------------------------- -------- ------------------------
COUNTRY_NAME NOT NULL VARCHAR2(40)
'Direct Sales' NUMBER
'Tele Sales' NUMBER
'Catalog' NUMBER
'Internet' NUMBER
'Partners' NUMBER
1 select * from t_matriz
2 unpivot
3 ( cant_ventas for pais in ("'Direct Sales'","'Tele Sales'","'Catalog'","'Internet'","'Partners'
4* )
SQL> /
COUNTRY_NAME PAIS CANT_VENTAS
---------------------------------------- -------------- -----------
Argentina 'Direct Sales' 126
Argentina 'Tele Sales' 51
Argentina 'Catalog' 0
Argentina 'Internet' 23
Argentina 'Partners' 2
Australia 'Direct Sales' 20169
Australia 'Tele Sales' 5
Australia 'Catalog' 0
Australia 'Internet' 4364
Australia 'Partners' 9147
Brazil 'Direct Sales' 75
Brazil 'Tele Sales' 46
Brazil 'Catalog' 0
Brazil 'Internet' 27
Brazil 'Partners' 32
Canada 'Direct Sales' 11474
Canada 'Tele Sales' 6
Canada 'Catalog' 0
Canada 'Internet' 3601
Canada 'Partners' 7777
China 'Direct Sales' 2
China 'Tele Sales' 9
China 'Catalog' 0
China 'Internet' 7
China 'Partners' 1
Denmark 'Direct Sales' 9870
Denmark 'Tele Sales' 10
Denmark 'Catalog' 0
Denmark 'Internet' 2084
Denmark 'Partners' 4687
France 'Direct Sales' 21807
France 'Tele Sales' 5
France 'Catalog' 0
France 'Internet' 3577
France 'Partners' 7689
Germany 'Direct Sales' 53864
Germany 'Tele Sales' 4
Germany 'Catalog' 0
Germany 'Internet' 9426
Germany 'Partners' 18684
Italy 'Direct Sales' 26810
Italy 'Tele Sales' 69
Italy 'Catalog' 0
Italy 'Internet' 5193
Italy 'Partners' 10498
Japan 'Direct Sales' 31895
Japan 'Tele Sales' 1
Japan 'Catalog' 0
Japan 'Internet' 8715
Japan 'Partners' 19572
New Zealand 'Direct Sales' 0
New Zealand 'Tele Sales' 7
New Zealand 'Catalog' 0
New Zealand 'Internet' 0
New Zealand 'Partners' 0
Poland 'Direct Sales' 4
Poland 'Tele Sales' 3
Poland 'Catalog' 0
Poland 'Internet' 9
Poland 'Partners' 2
Saudi Arabia 'Direct Sales' 1
Saudi Arabia 'Tele Sales' 0
Saudi Arabia 'Catalog' 0
Saudi Arabia 'Internet' 6
Saudi Arabia 'Partners' 0
Singapore 'Direct Sales' 11553
Singapore 'Tele Sales' 8
Singapore 'Catalog' 0
Singapore 'Internet' 4198
Singapore 'Partners' 9494
Spain 'Direct Sales' 10320
Spain 'Tele Sales' 0
Spain 'Catalog' 0
Spain 'Internet' 2207
Spain 'Partners' 4609
Turkey 'Direct Sales' 154
Turkey 'Tele Sales' 0
Turkey 'Catalog' 0
Turkey 'Internet' 3
Turkey 'Partners' 11
United Kingdom 'Direct Sales' 37786
United Kingdom 'Tele Sales' 5
United Kingdom 'Catalog' 0
United Kingdom 'Internet' 6390
United Kingdom 'Partners' 14457
United States of America 'Direct Sales' 304418
United States of America 'Tele Sales' 1845
United States of America 'Catalog' 0
United States of America 'Internet' 68586
United States of America 'Partners' 151363
SQL> ed
Wrote file afiedt.buf
1 create table t_tabular
2 as
3 select * from t_matriz
4 unpivot
5 ( cant_ventas for pais in ("'Direct Sales'","'Tele Sales'","'Catalog'","'Internet'","'Partners'
6* )
SQL> /
Table created.
SQL> desc t_tabular
Name Null? Type
----------------------------------------------------------------- -------- ------------------------
COUNTRY_NAME VARCHAR2(40)
PAIS VARCHAR2(14)
CANT_VENTAS NUMBER
SQL>
Como mostré en el ejemplo la creación de sentencias para mostrar la información en formato matricial (muy utilizada en reportes de DataWarehouse) ahora es muy simple y sobre todo mas entendible
.JPG)
No hay comentarios:
Publicar un comentario