[RESOLVIDO] - SQL Server - Sumarizar query com rollup/cube/grouping

Assuntos Gerais
#1

Olá pessoal.

Tenho a tabela de origem conforme abaixo (p/ facilitar, fiz uma breve descrição sobre os dados no final desta mensagem):

customer_ID      product_ID    product_model  product_offer    subProduct_description    subProduct_balance    subProduct_tax   product_observation    userSession_ID    date_register
1	               a1               V	        offer A        desc_a                    5                     1,25             some desc_1            user_1             19/06/2013
2                  b1               V           offer A        desc_a                    10,5                  1,25             NULL                   NULL               NULL
2                  b1               V	        offer A        desc_b                    11,5                  1,5              some desc_1            user_1             15/05/2013
2                  b1               V	        offer A        desc_c                    12,5                  2                NULL                   NULL               NULL
2                  b1               V	        offer A        desc_d                    13,5                  2,5              some desc_1            user_1             05/04/2013
2                  c1               P           offer B        desc_a                    6                     1,25             some desc_1            user_1             19/06/2013
2                  c2               V	        offer B        desc_b                    7                     1,5              some desc_1            user_1             19/06/2013
2                  c3               P           offer A        desc_b                    5                     1,5              NULL                   NULL               NULL
2                  c3               P           offer A        desc_c                    6                     2                some desc_1            user_1             19/06/2013
3                  d1               V           offer B        desc_c                    4                     2                some desc_1            user_1             22/01/2013
3                  d1               V           offer B        desc_d                    6                     2,5              NULL                   NULL               NULL
3                  d2               V           offer C        desc_a                    8                     1,25             NULL                   NULL               NULL
3                  d2               V           offer C        desc_b                    4                     1,5              some desc_1            user_1             15/05/2013

e quero sumarizá-la assim (desconsidere as cores e formatação):

CUSTOMER_ID	PRODUCT_ID	PRODUCT_MODEL	PRODUCT_OFFER	SUBPRODUCT_DESCRIPTION				SUBPRODUCT_BALANCE	SUBPRODUCT_TAX	PRODUCT_OBSERVATION	USERSESSION_ID	DATE_REGISTER
1			a1			V				offer A			desc_a								5					1,25			some desc_1			user_1			19/06/2013
-			-			-				-				Total of products by ID				5					-				-					-				-
-			-			-				-				Total of all products by customer	5					-				-					-				-
2			b1			V				offer A			desc_a								10,5				1,25			NULL				NULL			NULL
2			b1			V				offer A			desc_b								11,5				1,5				some desc_1			user_1			15/05/2013
2			b1			V				offer A			desc_c								12,5				2				NULL				NULL			NULL
2			b1			V				offer A			desc_d								13,5				2,5				some desc_1			user_1			05/04/2013
-			-			-				-				Total of products by ID				48					-				-					-				-
2			c1			P				offer B			desc_a								6					1,25			some desc_1			user_1			19/06/2013
-			-			-				-				Total of products by ID				6					-				-					-				-
2			c2			V				offer B			desc_b								7					1,5				some desc_1			user_1			19/06/2013
-			-			-				-				Total of products by ID				7					-				-					-				-
2			c3			P				offer A			desc_b								5					1,5				NULL				NULL			NULL
2			c3			P				offer A			desc_c								6					2				some desc_1			user_1			19/06/2013
-			-			-				-				Total of products by ID				11					-				-					-				-
-			-			-				-				Total of all products by customer	72					-				-					-				-
3			d1			V				offer B			desc_c								4					2				some desc_1			user_1			22/01/2013
3			d1			V				offer B			desc_d								6					2,5				NULL				NULL			NULL
-			-			-				-				Total of products by ID				10					-				-					-				-
3			d2			V				offer C			desc_a								8					1,25			NULL				NULL			NULL
3			d2			V				offer C			desc_b								4					1,5				some desc_1			user_1			15/05/2013
-			-			-				-				Total of products by ID				12					-				-					-				-
-			-			-				-				Total of all products by customer	22					-				-					-				-
-			-			-				-				Grand Total							99					-				-					-				-

Tentei resolver com rollup, cube, grouping, mas não consegui.

Alguém poderia me ajudar?

  • Descrição da tabela de origem:

  • 1 cliente (customer_id) pode adquirir 1 ou mais produtos (product_id).

  • todos os produtos tem os atributos: modelo (product_model) e nome da oferta (product_offer).

  • cada produto é composto por, no mínimo, 1 OU mais subitens (product_description).

  • cada subitem tem apenas 1 valor (subProduct_balance), 1 taxa (subProduct_tax), 1 observação (subproduct_observation), 1 usuário que gravou o registro (userSession_ID) e 1 data de registro (date_register).

Agradeço pelo apoio.

#2

Talvez exista soluções melhores para este problema, mas procurei focar nas opções que citou.

Primeiro podemos dividir em dois passos:

  • calcular os totais e subtotais que precisa
  • combinar valores com os detalhes.

A primeira parte, usando rollup é relativamente simples:

select       
	GROUPING(customer_ID),
	customer_ID, 
	GROUPING(product_ID),
	product_ID, 
	sum(subProduct_balance ) balance
from 
	products
group by 
	rollup(customer_ID, product_ID)

Essa query traz todas informações que precisa para gerar os subtotais e totais do seu relatório.

A segunda parte é combinar o resultado da query anterior com os dados detalhados.
O maior desafio aqui é ordernar de forma correta, para mostrar conforme mostrou.
O que consegui fazer aqui foi:


select 
	0 order_customer,
	customer_ID,
	0 order_product,
	product_ID, 
	0 tipo_linha,
	subProduct_description,
	subProduct_balance

from
	products
union all	

select 
	GROUPING(customer_ID) order_customer,
	customer_ID,
	GROUPING(product_ID) order_product,
	product_ID, 
	1 tipo_linha,
	CASE
		WHEN (GROUPING(customer_ID) = 1) THEN 'Grand Total'           
		WHEN (GROUPING(product_ID) = 1) THEN 'Total of products by customer ' + cast(customer_ID as varchar)
		ELSE 'Total by product ' + cast(product_ID as varchar)
	END  msg,	
	sum(subProduct_balance ) balance	
from 
	products 
group by rollup(customer_ID, product_ID)
order by order_customer, customer_ID, order_product,  product_ID, tipo_linha

É preciso alguns truques com a função Grouping, porque valores nulos vão sempre para o topo da lista com Order by (pelo menos até a versão 2012).

Agora para ter o relatório como quer, basta incluir as colunas que precisa e remover as desnecessárias.

#3

[quote=AbelBueno]Talvez exista soluções melhores para este problema, mas procurei focar nas opções que citou.

Primeiro podemos dividir em dois passos:

  • calcular os totais e subtotais que precisa
  • combinar valores com os detalhes.

A primeira parte, usando rollup é relativamente simples:

select       
	GROUPING(customer_ID),
	customer_ID, 
	GROUPING(product_ID),
	product_ID, 
	sum(subProduct_balance ) balance
from 
	products
group by 
	rollup(customer_ID, product_ID)

Essa query traz todas informações que precisa para gerar os subtotais e totais do seu relatório.

A segunda parte é combinar o resultado da query anterior com os dados detalhados.
O maior desafio aqui é ordernar de forma correta, para mostrar conforme mostrou.
O que consegui fazer aqui foi:


select 
	0 order_customer,
	customer_ID,
	0 order_product,
	product_ID, 
	0 tipo_linha,
	subProduct_description,
	subProduct_balance

from
	products
union all	

select 
	GROUPING(customer_ID) order_customer,
	customer_ID,
	GROUPING(product_ID) order_product,
	product_ID, 
	1 tipo_linha,
	CASE
		WHEN (GROUPING(customer_ID) = 1) THEN 'Grand Total'           
		WHEN (GROUPING(product_ID) = 1) THEN 'Total of products by customer ' + cast(customer_ID as varchar)
		ELSE 'Total by product ' + cast(product_ID as varchar)
	END  msg,	
	sum(subProduct_balance ) balance	
from 
	products 
group by rollup(customer_ID, product_ID)
order by order_customer, customer_ID, order_product,  product_ID, tipo_linha

É preciso alguns truques com a função Grouping, porque valores nulos vão sempre para o topo da lista com Order by (pelo menos até a versão 2012).

Agora para ter o relatório como quer, basta incluir as colunas que precisa e remover as desnecessárias.

[/quote]

Abel,

arrebentou!

Parabéns e muito obrigado mesmo!

#4