Menu de navegação

[Willian VidalParticipante]

Bom dia, tenho dúvidas pra fazer um select onde tenho uma tabela itens_vendas que relaciona os itens de uma venda, preciso saber quais itens foram vendidos juntos e quantas vezes, onde me retorne um resultado do tipo:

Produto_1 | Produto_2 | Qtd_Vendas

Sendo que Produto1 e Produto2 estão relacionados pelo código da venda.

Alguém poderia me ajudar?
Obrigado.

[José Laurindo ChiappaModerador]

Bom, ** primeiro ** de tudo vc TEM que nos mostrar a Estrutura dos seus dados : ‘código da venda’ é alguma coisa meio genérica, e que (IMAGINO) pode se repetir…O ponto é, eu IMAGINO que vc tem um produto em cada linha dessa tabela , COMO é que a gente vai saber quais linhas devem ser “juntadas” pois representam uma única venda ?? Vc tem uma coluna NotaFiscal, aí vc quer juntar as linhas com mesma NF ? Ou é por data, vc tem uma coluna DATA DA VENDA e quer juntar linhas com produtos na mesma DATA DE VENDA ?? Enfim, vc TEM que nos dizer como está sua estrutura de dados, senão a gente simplesmente NÂO TEM COMO ADIVINHAR…. A questão é que , já que para identificar uma venda vc, por questões legais e/ou operacionais, TEM QUE TER NF, data da venda e outros campos únicos do tipo, não faz muito sentido vc ter um atributo artificial como imagino ser esse tal “código da venda”, mas Plz nos explique como está a sua modelagem… E se der pra EXEMPLIFICAR teus dados, nos mostrando uns casinhos-exemplos, melhor ainda…

Isso esclarecido, para que vc junte na mesma linha valores vindos de múltiplos registros, nós usamos a operação chamada PIVOT : pra podermos te exemplificar, precisamos saber a ** VERSÃO ** do seu database Oracle (outra coisa IMPORTANTE que vc pra variar não nos diz), pois cfrme http://www.oracle.com/technetwork/pt/articles/sql/principais-caracteristicas-database-2108383-ptb.html mostra até antes do 11g não tínhamos comando nativo pra fazer a operação…

[]s

Chiappa

[Willian VidalParticipante]

Então jlchiappa, imagine um cenário bem simples, tabelas VENDA{COD_VENDA, …}, ITEM{COD_ITEM, DESCRICAO…} e ITEM_VENDA{COD_VENDA, COD_ITEM}, ITEM_VENDA é a relação, então se tivéssemos em ITEM_VENDA:

COD_VENDA | COD_ITEM | QTD
1 | 100 | 2
1 | 101 | 3
1 | 102 | 4
2 | 100 | 1
2 | 101 | 1

Logo, estaríamos falando de duas vendas (1, 2) e três produtos diferentes (100, 101, 102), o que se quer saber é produtos que aparecem juntos na mesma venda, para este exemplo:

PRODUTO_1 | PRODUTO_2
100 | 101
100 | 102
101 | 102
100 | 101

Para saber a quantidade pode-se usar um group by, ficando:

PRODUTO_1 | PRODUTO_2 | QTD
100 | 101 | 2
100 | 102 | 1
101 | 102 | 1

É exatamente essa última query que preciso, a versão do Oracle é a 11g.
Att,

[José Laurindo ChiappaModerador]

Então : como eu disse, é MEGA-ESTRANHO vc não ter usado os identificadores naturais (como a data, NF/Docto da venda, etc) e ter preferido uma chave artificial como é esse CODVENDA, mas ok , é o que é está mais claro…
Sim, para se termos a quantidade por venda agruparíamos pelo CODVENDA , mas (e isso é BÁSICO em qualquer RDBMS relacional) quando vc faz um GROUP BY vc ** PERDE ** os detalhes, não sabe quem foram os elementos componentes do Grupo – vc pode pensar no GROUP BY como um “ensanduichador”, um Agregador que FUNDE n registros físicos num só registro lógico, e para esse registro lógico vc pode usar um group function… Mais ou menos o GROUP BY transforma :

COD_VENDA | COD_ITEM | QTD
1 | 100 | 2
1 | 101 | 3
1 | 102 | 4

nisto :

COD_VENDA | SOMA
1 | 9

ok ? Para vc agrupar SEM perder os detalhes (que aoq eu entendo é seu Objetivo) vc não pode usar o GROUP BY padrão … No RDBMS Oracle a alternativa Recomendada para isso são as FUNÇÕES ANALÍTICAS, elas permitem que vc Agrupe n registros físicos num grupo lógico, sem perde os detalhes, OU vc pode usar o complemento do ROLLUP : vou exemplificar com o ROLLUP e com analytics…

SYSTEM@11GR2:SQL>create table ITEM_VENDA (
2 COD_VENDA number,
3 COD_ITEM number,
4 QTD number );

SYSTEM@11GR2:SQL>INSERT INTO ITEM_VENDA values(1,101,3);
SYSTEM@11GR2:SQL>INSERT INTO ITEM_VENDA values(1,100,2);
SYSTEM@11GR2:SQL>INSERT INTO ITEM_VENDA values(1,101,3);
SYSTEM@11GR2:SQL>INSERT INTO ITEM_VENDA values(1,102,4);
SYSTEM@11GR2:SQL>INSERT INTO ITEM_VENDA values(2,100,1);
SYSTEM@11GR2:SQL>INSERT INTO ITEM_VENDA values(2,101,1);

SYSTEM@11GR2:SQL>commit;

SYSTEM@11GR2:SQL>select * from ITEM_VENDA;

COD_VENDA COD_ITEM QTD
———- ———- ———-
1 100 3
1 101 3
1 102 4
2 100 1
2 101 1

SYSTEM@11GR2:SQL>select COD_VENDA, SUM(QTD) FROM ITEM_VENDA GROUP BY COD_VENDA;

COD_VENDA SUM(QTD)
———- ———-
1 10
2 2

SYSTEM@11GR2:SQL>

==> veja que eu perco os detalhes… Vou aplicara primeiro ROLLUP :

SYSTEM@11GR2:SQL>select COD_VENDA, COD_ITEM, SUM(QTD) FROM ITEM_VENDA GROUP BY ROLLUP(COD_VENDA, COD_ITEM);

COD_VENDA COD_ITEM SUM(QTD)
———- ———- ———-
1 100 3
1 101 3
1 102 4
1 10
2 100 1
2 101 1
2 2
12

==> veja que o ROLLUP te dá um registro ** EXTRA **, com o resultado do gupo, no caso com soma… Podemos chegar lá, nesse SUM agrupado com detalhes, via analytics :

SYSTEM@11GR2:SQL>select COD_VENDA, COD_ITEM, QTD,
SUM(QTD) OVER (PARTITION BY COD_VENDA ORDER BY COD_ITEM) SOMA_QTDs
FROM ITEM_VENDA;

COD_VENDA COD_ITEM QTD SOMA_QTDS
———- ———- ———- ———-
1 100 3 3
1 101 3 6
1 102 4 10
2 100 1 1
2 101 1 2

==> vou tirar o QTD individual, pra melhor visualização :

SYSTEM@11GR2:SQL>select COD_VENDA, COD_ITEM,
FIRST_VALUE(SUM(QTD)) OVER (PARTITION BY COD_VENDA ORDER BY COD_ITEM) SOMA_QTDs
FROM ITEM_VENDA;

COD_VENDA COD_ITEM SOMA_QTDS
———- ———- ———-
1 100 3
1 101 6
1 102 10
2 100 1
2 101 2

SYSTEM@11GR2:SQL>

==> ok ? veja que o que obtemos é um RUNNING TOTAL, portanto a última linha do grupo é o total do grupo…

Em cima dessas duas técnicas, fica como lição de casa pra ti aplicar o PIVOT cfrme as refs que te dei antes – não vou te tirar totalmente o prazer de pesquisar te dando a solução final e completa… 🙂

[]s

Chiappa

[Autor]

Posts