Acumulado de compras por cliente

pexels-photo-1005638.jpeg
Foto por Oleg Magni (Pexels)

Há poucos dias, em um grupo de SQL Server no whatsapp uma das participantes solicitou auxílio em “preciso pegar um histórico de uma base de 2 meses”. Como o enunciado estava vago foi solicitado que explicasse o que seria o “histórico”, sendo que ela forneceu a seguinte resposta: “… tem uma base que tem as compras dos clientes dos últimos 5 anos. Quando gente vier comprar conosco eu preciso saber quanto ele já comprou conosco nos últimos 2 anos, 1 ano, 6 meses”. E então ficamos sabendo que o que ela necessitava não era de um histórico de compras mas sim o acumulado de compras por cliente, em 3 períodos diferentes.

Foram então propostas algumas soluções.

Embora no enunciado do problema seja mencionado “compras”, ao longo deste artigo será considerado o ponto de vista da loja, que é “vendas”.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.

Solução 1

A primeira, e mais simples, é fazer os cálculos de “compras nos últimos x” no momento em que a informação é necessária, sendo proposto o seguinte modelo:

-- código #1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @codCliente integer;
set @codCliente= 999;

declare @Hoje date, @2anos date, @1ano date, @6meses date;
set @Hoje= cast (current_timestamp as date);
set @2anos= dateadd (year, -2, @Hoje);
set @1ano= dateadd (year, -1, @Hoje);
set @6meses= dateadd (month, -6, @Hoje);

SELECT V.codCliente, 
       sum (V.valorVenda) as [2 anos],
       sum (case when V.dataVenda >= @1ano then V.valorVenda end) as [1 ano],
       sum (case when V.dataVenda >= @6meses then V.valorVenda end) as [6 meses]
  from tbVenda as V
  where V.codCliente = @codCliente
        and V.dataVenda >= @2anos
        and V.dataVenda < @Hoje
  group by V.codCliente;

No modelo foi utilizada tabela fictícia tbVenda, tendo colunas para identificação do cliente (codCliente), qual foi o valor da venda e quando ocorreu a venda.

O código sql sugerido considerou a existência de índice pelas colunas CodCliente e dataVenda e que o índice ou é clustered ou é índice de cobertura para o código sql em questão. Caso contrário, dependendo da quantidade de linhas da tabela o processo pode ser bem lento.

Adaptei o modelo anterior para o banco de dados Adventure Works, utilizando a tabela que contém as informações gerais da venda, Sales.SalesOrderHeader, ficando assim:

-- código #2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @codCliente integer;
set @codCliente= 29669;

declare @Hoje datetime, 
        @2anos datetime, @1ano datetime, @6meses datetime;

set @Hoje= cast (current_timestamp as date);
set @2anos= dateadd (year, -2, @Hoje);
set @1ano= dateadd (year, -1, @Hoje);
set @6meses= dateadd (month, -6, @Hoje);

SELECT V.CustomerID,
       sum (V.SubTotal) as [2 anos],
       sum (case when V.OrderDate >= @1ano then V.SubTotal end) as [1 ano],
       sum (case when V.OrderDate >= @6meses then V.SubTotal end) as [6 meses]
  from Sales.SalesOrderHeader as V
  where V.CustomerID = @codCliente
        and V.OrderDate >= @2anos
        and V.OrderDate < @Hoje
  group by V.CustomerID;

O plano de execução foi o seguinte:

p054_codigo #2 - plano

A leitura ocorreu através do índice IX_SalesOrderHeader_CustomerID, cuja chave é a coluna CustomerdID mas não é de cobertura. Assim, foram lidas 11 linhas e filtrando somente pelo código do cliente. O operador Key Lookup está presente no plano de execução, para coletar a data do pedido (OrderDate) e o total da venda (SubTotal). Com isto foi possível aplicar os filtros pela coluna OrderDate, o que reduziu para 8 linhas a serem processadas pelas acumulações, realizadas no operador Stream Aggregate.

p054_codigo #2 - prop rowsp054_codigo #2 - prop predicate

Fiz mais três simulações para comparar os planos de execução: a primeira, sem a presença do índice IX_SalesOrderHeader_CustomerID; a seguinte, criando índice pela coluna CustomerID e contendo as colunas OrderDate e SubTotal; e a última, criando índice pelas colunas CustomerID e OrderDate e contendo a coluna SubTotal.

Simulação 1

ALTER INDEX IX_SalesOrderHeader_CustomerID 
            on Sales.SalesOrderHeader disable;

Sem o índice IX_SalesOrderHeader_CustomerID o plano de execução passou a ser o seguinte:

p054_codigo #2 - plano t2

A leitura ocorreu através do índice PK_SalesOrderHeader_SalesOrderID, cuja chave é a coluna SalesOrderID que é o índice clustered. A leitura foi sequencial, isto é, toda a tabela foi lida com os filtros sendo aplicados a cada linha.

p054_codigo #2 - prop t2 rowsp054_codigo #2 - prop t2 predicate

Simulação 2

A segunda simulação, criar índice pela coluna CustomerID e contendo as colunas OrderDate e SubTotal,

CREATE nonclustered INDEX IX_SalesOrderHeader_CustomerID_2
       on Sales.SalesOrderHeader (CustomerID) 
       include (OrderDate, SubTotal);

gerou o seguinte plano de execução:

p054_codigo #2 - plano t3

A leitura ocorreu através do índice IX_SalesOrderHeader_CustomerID_2, cuja chave é a coluna CustomerdID e não é de cobertura. Assim, foram lidas 11 linhas, utilizando o código do cliente no predicado de busca, e a seguir aplicados os filtros adicionais como predicado, resultando em 8 linhas enviadas para a acumulação.

p054_codigo #2 - prop t3 rowsp054_codigo #2 - prop t3 seek predicatep054_codigo #2 - prop t3 predicate

Simulação 3

A terceira e última simulação, criar índice pelas colunas CustomerID e OrderDate e contendo a coluna SubTotal,

DROP INDEX IX_SalesOrderHeader_CustomerID_2 on Sales.SalesOrderHeader;
CREATE nonclustered INDEX IX_SalesOrderHeader_CustomerID_3
       on Sales.SalesOrderHeader (CustomerID, OrderDate) 
       include (SubTotal);

gerou o seguinte plano de execução:

p054_codigo #2 - plano t4

A leitura ocorreu através do índice IX_SalesOrderHeader_CustomerID_3, que é de cobertura e cuja chave é composta pelas colunas CustomerdID e OrderDate. Assim, foram lidas 8 linhas utilizando o predicado de busca.

p054_codigo #2 - prop t4 rowsp054_codigo #2 - prop t4 seek predicate

Ou seja, três situações diferentes e com resultados ligeiramente diferentes. A terceira simulação foi a mais eficiente, às custas de um índice de cobertura e com chave primária composta por 2 colunas.

Ao final, a situação original de índices deve ser restaurada:

DROP INDEX IX_SalesOrderHeader_CustomerID_3 
     on Sales.SalesOrderHeader;
ALTER INDEX IX_SalesOrderHeader_CustomerID 
     on Sales.SalesOrderHeader rebuild;

Solução 2

Outra solução é manter acumulado por cliente de suas compras nos últimos 2 anos, 1 ano e 6 meses. Assim, a informação seria obtida sem necessidade de cálculos no momento da consulta. Essa informação pode ser mantida através do acréscimo de 3 colunas na tabela de clientes ou, se a implementação física da modelagem de dados tiver que seguir as formas normais com rigor, então criar tabela adicional contendo as 3 colunas e a identificação do cliente.

Essa acumulação deve ser atualizada todo início de dia.

Para demonstração será utilizado o banco de dados Adventure Works e para não alterar a estrutura das tabelas dele optei por criar uma tabela específica para a acumulação. Embora isso signifique posterior necessidade de junção, nesse caso em específico vejo algumas vantagens.

A estrutura da nova tabela é a seguinte:

-- código #3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE Sales.VendaAcumulada (
   CustomerID int not null
              references Sales.Customer (CustomerID),
   Venda_2anos money not null default 0,
   Venda_1ano money not null default 0,
   Venda_6meses money not null default 0,
   constraint I1_VendaAcumulada primary key (CustomerID)
);

com o seguinte código sql de carga da tabela com as vendas acumuladas:

-- código #4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @Hoje datetime,
        @2anos datetime, @1ano datetime, @6meses datetime;

set @Hoje= cast (current_timestamp as date);
set @2anos= dateadd (year, -2, @Hoje);
set @1ano= dateadd (year, -1, @Hoje);
set @6meses= dateadd (month, -6, @Hoje);

with Acumula as (
SELECT V.CustomerID,
       sum (V.SubTotal) as V_2anos,
       sum (case when V.OrderDate >= @1ano then V.SubTotal end) as V_1ano,
       sum (case when V.OrderDate >= @6meses then V.SubTotal end) as V_6meses
  from Sales.SalesOrderHeader as V
  where V.OrderDate >= @2anos
        and V.OrderDate < @Hoje
  group by V.CustomerID
)
INSERT into Sales.VendaAcumulada
   SELECT C.CustomerID, coalesce (A.V_2anos, 0), 
          coalesce (A.V_1ano, 0), coalesce (A.V_6meses, 0)
     from Sales.Customer as C
          left join Acumula as A on A.CustomerID = C.CustomerID;

Observe que são incluídas linhas para todos os clientes, inclusive aqueles que não tenham adquirido algo nos últimos 2 anos. Isto foi necessário por causa da implementação 1:1 entre as tabelas Customer e VendaAcumulada.

O conteúdo da tabela VendaAcumulada deve ser apagado antes de processar o código #4.

Solução 3

A solução 2 pode ser otimizada, de modo a ler somente as linhas afetadas a cada novo dia. Isto envolve:

  1. fazer a acumulação completa uma única vez;
  2. diariamente, subtrair o dia que saiu do período e adicionar as vendas do dia anterior.

Esta solução envolve processamento inteligente dos dados, de modo a reduzir a leitura na tabela de vendas. Para facilitar a compreensão vamos imaginar que hoje é dia 15/1/2012 e que desejamos manter o acumulado dos 4 últimos dias, e que temos os seguintes dados:

p054_quadro 3.1 marcado

No dia 15/1/2012, conforme o item B.1 temos que a acumulação inicial dos 4 últimos dias para o cliente 29669 é de

120,00 + 280,00 + 50,00 + 85,00 = 535,00

No dia seguinte temos a seguinte situação:

p054_quadro 3.2 marcado

Como o acumulado deve ser dos últimos 4 dias então o período envolve agora dos dias 12/1/2012 a 15/1/2012. Para atender à regra B.2 devemos subtrair a venda do dia que saiu do período de “4 últimos dias”, 11/1/2012, e somar a venda do dia que entrou no período de “4 últimos dias”, 15/1/2012.

535,00 – 120,00 + 210,00 = 625,00

No dia seguinte, 17/1/2012, temos a seguinte situação:

p054_quadro 3.3 marcado

Como o acumulado deve ser dos últimos 4 dias então o período envolve agora dos dias 13/1/2012 a 16/1/2012. Para atender à regra B.2 devemos subtrair a venda do dia que saiu do período de “4 últimos dias”, 12/1/2012, e somar a venda do dia que entrou no período de “4 últimos dias”, 16/1/2012.

625,00 – 280,00 + 25,00 = 370,00

E assim em diante, a cada novo dia: subtrair o valor do dia que “saiu” e somar o valor do dia que “entrou” no período. Isto significa que somente são lidos os dados do dia que “saiu” e do dia que “entrou”.

A implementação desta solução envolve criar a tabela VendaAcumulada (código #3) e realizar a carga inicial dela (código #4). Quanto ao processamento diário, que lê o mínimo necessário de linhas, pode ser feito com o código sql a seguir:

-- código #5 v2
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @Hoje datetime, @Ontem datetime,
        @2anos datetime, @1ano datetime, @6meses datetime;

set @Hoje= cast (current_timestamp as date);
set @Ontem= dateadd (day, -1, @Hoje);
set @2anos= dateadd (year, -2, @Ontem);
set @1ano= dateadd (year, -1, @Ontem);
set @6meses= dateadd (month, -6, @Ontem);

CREATE TABLE #FiltroData (Data datetime primary key);
INSERT into #FiltroData
     values (@Ontem), (@2anos), (@1ano), (@6meses);

with Ajustes as (
SELECT V.CustomerID,
       coalesce (sum (case when V.OrderDate = @Ontem then V.SubTotal end), 0) as dia_Ontem,
       coalesce (sum (case when V.OrderDate = @2anos then V.SubTotal end), 0) as dia_2anos,
       coalesce (sum (case when V.OrderDate = @1ano then V.SubTotal end), 0) as dia_1ano,
       coalesce (sum (case when V.OrderDate = @6meses then V.SubTotal end), 0) as dia_6meses
  from Sales.SalesOrderHeader as V
  where V.OrderDate >= @2anos
        and V.OrderDate in (SELECT Data 
                            from #FiltroData as D
                            where D.Data = V.OrderDate)
  group by V.CustomerID
)
UPDATE VA
  set Venda_2anos += (dia_Ontem - dia_2anos),
      Venda_1ano += (dia_Ontem - dia_1ano),
      Venda_6meses += (dia_Ontem - dia_6meses)
  from Sales.VendaAcumulada as VA
       inner join Ajustes As A on VA.CustomerID = A.CustomerID;

Como são 3 períodos acumulativos então existem 3 dias “saindo” (dia_2anos, dia_1ano, dia_6meses) e um dia “entrando” (dia_Ontem). Para cada um dos acumuladores são somadas as vendas de dia_Ontem e subtraídas as vendas do dia que “saiu”.

É preciso ficar atento que novos clientes são incluídos na ou retirados da tabela Sales.Customer. Para manter o relacionamento 1:1 com a tabela Sales.VendaAcumulada é necessário criar procedimento de gatilho associado à tabela Sales.Customer:

-- código #6
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TRIGGER Sales.paridade_VendaAcumulada 
     on Sales.Customer
     after INSERT, DELETE
as
begin
declare @NI integer, @ND integer;
set @ND= (SELECT count(*) from (SELECT top(2) CustomerID from DELETED) as D);
set @NI= (SELECT count(*) from (SELECT top(2) CustomerID from INSERTED) as I);


IF (@ND = 0 and @NI > 0)
  -- inclusão de clientes
  begin
  INSERT into Sales.VendaAcumulada 
          (CustomerID, Venda_2anos, Venda_1ano, Venda_6meses)
    SELECT CustomerID, 0, 0, 0
      from INSERTED;
  end
else
  begin
  -- remoção de clientes
  DELETE Sales.VendaAcumulada
    where CustomerID in (SELECT CustomerID from DELETED);
  end;
end;
go

Caso não se queira criar os procedimentos de gatilho é possível fazer a inclusão automática de clientes na tabela VendaAcumulada, ao substituir a instrução UPDATE do código #5 para MERGE, tratando os casos “when matched” e “when not matched by target”. Entretanto, tal alternativa não possibilita detectar clientes que tenham sido excluídos da tabela Customer.

Observações

A decisão de qual estratégia utilizar depende do volume de linhas das vendas nos últimos 2 anos e também da quantidade de vendas diárias. A primeira solução é a mais simples de se implementar, mas ela pode se tornar ineficiente caso o volume de vendas diárias for elevado.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s