SQL Server e Azure SQL - Desafio para agrupar dados utilizando hierarquias

Visualizações: 330 views

Tempo de Leitura: 4 minutos

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:

-------------------------------------
-- 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 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.

--------------------------------------------------------
-- 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)

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

;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];

;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 🙂

Cassio Jr disse:

13 de agosto de 2023 às 06:51

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

Responder

Assinar blog por e-mail

Visualizações do Blog

Power Alerts

Curso Adm de Bancos no Azure

Curso Segurança de SQL Server

Minhas Certificações

Arquivo de Posts

Categorias

Posts recentes

SQL Server e Azure SQL – Desafio para agrupar dados utilizando hierarquias

Criando a base de testes

Como eu resolvi esse desafio

You may also like...

1 Response

Deixe uma respostaCancelar resposta

Criando a base de testes

Como eu resolvi esse desafio

You may also like...

Como recuperar o maior valor entre múltiplas colunas em uma tabela do SQL Server

SQL Server – Como gerar um histórico de deadlocks para análise de falhas em rotinas

Gerando engenharia reversa (DDL de Criação) de Usuários, Tablespaces, Roles, Jobs e Profiles no Oracle Database

1 Response

Deixe uma respostaCancelar resposta