Post Views 481 views
Hey guys!
In this post I would like to share with you a SQL challenge I received involving hierarchies:
The biggest difficulty here is to group the data using hierarchies, so that the parent level (level 1) adds up the sale value of all children (level 2+). The level below (level 2) must add up the sales of all their children (level 3+) and so on.
And of course I accepted the challenge and tried to solve it, right?
Creating the test base
To facilitate your tests, I will provide the script to create this same 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) |
Data preview:
how i solved this challenge
To solve this challenge, I used recursive CTE, WHILE and subquery, in order to present a solution that is simple to understand the logic and quick to develop.
|
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) |
Final result
Option 1: New column with team sale
Option 2: New lines by selling the team
EDIT - Option 3: Solution sent by 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]; |
Result:
And you? How would you solve this challenge? Could you get a simpler or faster query than the one presented in the article?
Post your solution here in the comments.
I look forward to seeing what you guys did 🙂
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