Visualizações: 481 views
Fala pessoal!
Nesse post eu gostaria compartilhar com vocês um desafio de SQL que recebi envolvendo hierarquias:
A maior dificuldade aqui, é agrupar os dados utilizando hierarquias, de modo que o nível pai (nível 1) some o valor da venda de todos os filhos (nível 2+). O nível abaixo (nível 2), deve somar as vendas de todos os filhos deles (nível 3+) e assim por diante.
E é claro que topei o desafio e tentei resolvê-lo, né?
Criando a base de testes
Para facilitar os seus testes, vou disponibilizar o script para criar essa mesma base:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
------------------------------------- -- Cria a tabela com dados de teste ------------------------------------- DROP TABLE IF EXISTS #Hierarquia_Vendas CREATE TABLE #Hierarquia_Vendas ( Cd_Vendedor INT NOT NULL, Cd_Superior INT NULL ) INSERT INTO #Hierarquia_Vendas VALUES ( 301, NULL ), ( 209, 301 ), ( 576, 301 ), ( 111, 209 ), ( 112, 209 ), ( 123, 576 ), ( 444, 576 ) DROP TABLE IF EXISTS #Vendas CREATE TABLE #Vendas ( Cd_Vendedor INT NOT NULL, Venda NUMERIC(18, 2) NOT NULL ) INSERT INTO #Vendas VALUES (301, 25), (209, 30), (111, 80), (112, 70), (576, 50), (123, 100), (444, 120) |
Prévia dos dados:
Como eu resolvi esse desafio
Para resolver esse desafio, eu utilizei CTE recursiva, WHILE e subquery, de modo a apresentar uma solução simples de entender a lógica e rápida para desenvolver.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 |
-------------------------------------------------------- -- Cria a tabela com dados de teste -------------------------------------------------------- DROP TABLE IF EXISTS #Hierarquia_Vendas CREATE TABLE #Hierarquia_Vendas ( Cd_Vendedor INT NOT NULL, Cd_Superior INT NULL ) INSERT INTO #Hierarquia_Vendas VALUES ( 301, NULL ), ( 209, 301 ), ( 576, 301 ), ( 111, 209 ), ( 112, 209 ), ( 123, 576 ), ( 444, 576 ) DROP TABLE IF EXISTS #Vendas CREATE TABLE #Vendas ( Cd_Vendedor INT NOT NULL, Venda NUMERIC(18, 2) NOT NULL ) INSERT INTO #Vendas VALUES (301, 25), (209, 30), (111, 80), (112, 70), (576, 50), (123, 100), (444, 120) -------------------------------------------------------- -- Cria o nível de profundidade e a string de hierarquia -------------------------------------------------------- DROP TABLE IF EXISTS #Base; -- Vou utilizar essa CTE resursiva para gerar o nível de profundidade e a string da hierarquia ;WITH cte AS ( -- Nivel 1 SELECT 1 AS Nivel, CONVERT(VARCHAR(MAX), CONCAT('', [Cd_Vendedor])) AS Hierarquia, [Cd_Superior], [Cd_Vendedor] FROM [#Hierarquia_Vendas] WHERE [Cd_Superior] IS NULL UNION ALL -- Nivel 2->N SELECT B.[Nivel] + 1 AS Nivel, CONVERT(VARCHAR(MAX), CONCAT(B.[Hierarquia], '-', A.[Cd_Vendedor])) AS Hierarquia, A.[Cd_Superior], A.[Cd_Vendedor] FROM [#Hierarquia_Vendas] A JOIN [cte] B ON [A].[Cd_Superior] = [B].[Cd_Vendedor] ) SELECT A.[Nivel], A.[Cd_Vendedor], A.[Cd_Superior], A.[Hierarquia], ISNULL([B].[Venda], 0) AS Venda, -- Coluna que vai guardar a venda da equipe CAST(NULL AS NUMERIC(18, 2)) AS Venda_Equipe, -- Coluna utilizada para ordenar os resultados ROW_NUMBER() OVER(ORDER BY ISNULL(A.[Hierarquia], -1)) AS Ordem INTO #Base FROM cte AS A LEFT JOIN [#Vendas] B ON [A].[Cd_Vendedor] = [B].[Cd_Vendedor] -------------------------------------------------------- -- Calcula a venda por equipe de forma agregada -------------------------------------------------------- DECLARE @MenorNivel INT = 1, @NivelAtual INT = (SELECT MAX(Nivel) FROM [#Base]) -- Calcula do maior do nível para o menor WHILE(@NivelAtual >= @MenorNivel) BEGIN -- Atualiza a coluna "Venda_Equipe" com a soma das vendas do nível acima para o mesmo superior UPDATE A SET A.[Venda_Equipe] = ISNULL(A.[Venda], 0) + ISNULL(B.[Venda_Equipe], 0) FROM [#Base] A LEFT JOIN ( SELECT ISNULL([Cd_Superior], -1) AS Cd_Superior, SUM(ISNULL([Venda_Equipe], 0)) AS Venda_Equipe FROM [#Base] WHERE [Nivel] = @NivelAtual + 1 GROUP BY ISNULL([Cd_Superior], -1) ) B ON A.[Cd_Vendedor] = B.[Cd_Superior] WHERE A.[Nivel] = @NivelAtual SET @NivelAtual -= 1 -- Vai reduzindo o nível até acabar END -------------------------------------------------------- -- Executa a consulta final -------------------------------------------------------- -- Opção 1: Nova coluna com a venda da equipe SELECT * FROM [#Base] -- Opção 2: Novas linhas com a venda da equipe ;WITH cteFinal AS ( SELECT [Nivel], [Hierarquia], [Venda], 'Venda Direta' AS Agrupador, [Ordem] FROM [#Base] UNION ALL SELECT [Nivel], [Hierarquia], [Venda_Equipe], 'Total Venda Equipe' AS Agrupador, [Ordem] FROM [#Base] ) SELECT [Nivel], [Hierarquia], [Venda], [Agrupador] FROM [cteFinal] ORDER BY [Ordem], IIF(Agrupador = 'Venda Direta', 1, 2) |
Resultado Final
Opção 1: Nova coluna com a venda da equipe
Opção 2: Novas linhas com a venda da equipe
EDIT – Opção 3: Solução enviada pelo Cassio Junior
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
;WITH ListHierarquia_Vendas AS ( SELECT [a].[Cd_Superior], [a].[Cd_Vendedor], CONVERT(DEC(10, 2), [Vendas].[Venda]) AS [Venda], 1 AS [LevelId] FROM [#Hierarquia_Vendas] AS [a] INNER JOIN [#Vendas] AS [Vendas] ON [a].[Cd_Vendedor] = [Vendas].[Cd_Vendedor] WHERE [a].[Cd_Superior] IS NULL UNION ALL SELECT [Hierarquia_Vendas].[Cd_Superior], [Hierarquia_Vendas].[Cd_Vendedor], CONVERT(DEC(10, 2), [Vendas].[Venda]) AS [Venda], [ListHierarquia_Vendas].[LevelId] + 1 AS [LevelId] FROM [ListHierarquia_Vendas] AS [ListHierarquia_Vendas] INNER JOIN ( [#Hierarquia_Vendas] AS [Hierarquia_Vendas] INNER JOIN [#Vendas] AS [Vendas] ON [Hierarquia_Vendas].[Cd_Vendedor] = [Vendas].[Cd_Vendedor] ) ON [ListHierarquia_Vendas].[Cd_Vendedor] = [Hierarquia_Vendas].[Cd_Superior] ), ValoresVendasFilhos AS ( SELECT Cd_Superior, [ListHierarquia_Vendas].[Venda] FROM ListHierarquia_Vendas WHERE [ListHierarquia_Vendas].[LevelId] > 1 ), SomaVendaPai AS ( SELECT [b].[Cd_Superior], [b].[Cd_Vendedor], [b].[Venda], [b].[LevelId], IIF([b].[LevelId] = 1, SUM([b].[Venda]) OVER (PARTITION BY NULL), NULL) AS [SumLevel1] FROM [ListHierarquia_Vendas] AS [b] ) ,SomaVendasFilhos as ( SELECT [Cd_Superior], SUM([ValoresVendasFilhos].[Venda]) AS [Venda] FROM [ValoresVendasFilhos] GROUP BY [Cd_Superior] ) SELECT [SomaVendaPai].[Cd_Vendedor], [SomaVendaPai].[Cd_Superior], ISNULL( IIF([SomaVendaPai].[LevelId] = 1, [SomaVendaPai].[SumLevel1], ([SomaVendaPai].[Venda] + [SomaVendasFilhos].[Venda])), [SomaVendaPai].[Venda] ) AS [Venda] FROM [SomaVendaPai] AS [SomaVendaPai] LEFT JOIN [SomaVendasFilhos] AS [SomaVendasFilhos] ON [SomaVendaPai].[Cd_Vendedor] = [SomaVendasFilhos].[Cd_Superior]; |
Resultado:
E você? Como você resolveria esse desafio? Conseguiria uma consulta mais simples ou mais rápida do que essa apresentada no artigo?
Posta aqui nos comentários a sua solução.
Estou ansioso para ver o que vocês fizeram 🙂
Portuguese 
English
Opa, Pensei dessa maneira.
;with ListHierarquia_Vendas as
(
select
a.Cd_Superior
,a.Cd_Vendedor
,Venda = convert(dec(10,2),Vendas.Venda)
,LevelId = 1
from #Hierarquia_Vendas a
inner join #Vendas Vendas
on a.Cd_Vendedor = Vendas.Cd_Vendedor
where Cd_Superior is null
union all
Select
Hierarquia_Vendas.Cd_Superior
,Hierarquia_Vendas.Cd_Vendedor
,Venda = convert(dec(10,2),Vendas.Venda)
,LevelId = ListHierarquia_Vendas.LevelId + 1
from ListHierarquia_Vendas ListHierarquia_Vendas
inner join (#Hierarquia_Vendas Hierarquia_Vendas
inner join #Vendas Vendas
on Hierarquia_Vendas.Cd_Vendedor = Vendas.Cd_Vendedor)
on ListHierarquia_Vendas.Cd_Vendedor = Hierarquia_Vendas.Cd_Superior
)
,ValoresVendasFilhos as
(
Select
Cd_Superior
,Venda
from ListHierarquia_Vendas
where LevelId >1
)
,SomaVendaPai as
(
Select
b.Cd_Superior
,b.Cd_Vendedor
,b.Venda
,b.LevelId
,SumLevel1 = iif(b.LevelId = 1, sum(b.Venda) over(partition by null),null)
from ListHierarquia_Vendas b
)
,SomaVendasFilhos as
(
Select
Cd_Superior
,Venda = sum(Venda)
from ValoresVendasFilhos
group by Cd_Superior
)
Select
SomaVendaPai.Cd_Vendedor
,SomaVendaPai.Cd_Superior
,Venda = isnull(Iif(SomaVendaPai.LevelId = 1,SomaVendaPai.SumLevel1,(SomaVendaPai.Venda+SomaVendasFilhos.Venda)),SomaVendaPai.Venda)
from SomaVendaPai SomaVendaPai
left join SomaVendasFilhos SomaVendasFilhos
on SomaVendaPai.Cd_Vendedor = SomaVendasFilhos.Cd_Superior