Como determinar que esta chegando a data de compra de um cli

[romulobonnadio]

Ola pessoal como faco para pegar em SQL FOXPRO a media de data das vendas para um determinado cliente e se ele esta proximo a sua data de compra novamente?
Alguem tem alguma sugestao? Pois nao consigo desenvolver um sql que me informe em qtos dias medios o cliente solicita sua compra e se provavelmente esta chegando no proximo dia de compra dele?
ex.: Um cliente troca o oleo do seu carro de dois em dois meses, o sistema deveria me informar qtos dias faltam para sua proxima troca de oleo com base nas trocas anteriormentes realizadas.
Obg amigos

[JoséQuintas]

Complicado, porque pode acontecer do cliente fazer isso em outro lugar, ou parar por um tempo, e isso altera o resultado.

Uma tentativa:
Somar a quantidade de vezes.
Pegar a primeira e última data pra ver quantos dias dá entre um e outro.
Quantidade de dias / quantidade de vezes vai resultar em de quanto em quanto tempo ele aparece.
Pegando a última data mais esse resultado, dá o próximo dia que ele deve aparecer.

Seriam 3 comandos SQL:
- Um pra somar quantidade
- Um pra pegar primeira data
- Um pra pegar última data

A não ser que use UNION ALL ou alguma outra opção de juntar resultados seja usada, mas não sei se faria diferença no tempo de processamento.
E pensando bem, valem três comandos mesmo se calcular todos os clientes de uma vez.

Não tinha pensado nisso, mas pode ser até solução pra uma coisa aqui....

[romulobonnadio]

Boa tarde Jose, infelizmente nao consegui fazer em um comando so
Fiz em dois comandos como vc me informou, ficou bem legal a solucao e rapida!
Obg pela dica.

[alxsts]

Olá!

Como não tenho Foxpro, usei MariaDB mesmo. O problema apresentado é uma ótima oportunidade para aplicar funções de agregação (Count, Sum, Avg, Max, Min) do SQL e outras funções, mostradas abaixo. A lógica é a apresentada pelo Quintas...

- Um pra somar quantidade
- Um pra pegar primeira data
- Um pra pegar última data

... mas em uma única query, como queria romulobonnadio

1 - Criar tabela para testes:

Código: Selecionar todos

CREATE TABLE `tbtroca` (
	`idTroca` INT(11) NOT NULL AUTO_INCREMENT,
	`idCliente` INT(11) NOT NULL DEFAULT '0',
	`idProduto` INT(11) NOT NULL DEFAULT '0',
	`dtTroca` DATE NOT NULL,
	`qtUsada` DECIMAL(10,0) NOT NULL,
	PRIMARY KEY (`idTroca`)
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8;

2 - Populando a tabela:

Código: Selecionar todos

INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (1, 1, '2015-01-10', 3);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (1, 1, '2015-02-10', 2);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (1, 1, '2015-03-10', 1);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (1, 1, '2015-04-10', 4);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (1, 1, '2015-05-10', 4);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (1, 1, '2015-06-10', 2);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (1, 1, '2015-07-10', 3);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (2, 1, '2015-07-03', 3);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (3, 1, '2013-02-10', 3);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (3, 1, '2014-03-21', 5);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (3, 1, '2015-04-10', 3);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (4, 1, '2015-05-23', 1);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (4, 1, '2015-06-10', 2);
INSERT INTO `tbtroca` (`idCliente`, `idProduto`, `dtTroca`, `qtUsada`) VALUES (5, 1, '2015-07-10', 3);

3 - Selecionando dados:

Código: Selecionar todos

SELECT idCliente Cliente, 
       COUNT(idCliente) Trocas, 
		 DATE_FORMAT(MIN(dtTroca), "%d-%m-%Y" ) Primeira, 
		 DATE_FORMAT(MAX(dtTroca), "%d-%m-%Y" ) Última, 
		 DATEDIFF(MAX(dtTroca),MIN(dtTroca)) Intervalo,
		 DATEDIFF(MAX(dtTroca),MIN(dtTroca)) / COUNT(idCliente) Média,
		 DATE_FORMAT(DATE_ADD(MAX(dtTroca), INTERVAL CAST((DATEDIFF(MAX(dtTroca),Min(dtTroca)) / COUNT(idCliente) ) AS Decimal(0)) DAY), "%d-%m-%Y" )  Próxima,
		 AVG(qtUsada) 'Consumo Médio'
  FROM tbtroca
-- WHERE idCliente = 1
GROUP BY idCliente
HAVING Count(idCliente) > 1

4 - Observações:
- Para efeito de exemplo, clientes com apenas uma troca foram ignorados pois não teriam média. Num cenário real, pode-se optar por outro tratamento para o caso.
- Nas linhas 6 e 7 da consulta, a intenção era usar a função de agregação AVG mas, dá um erro que ainda não consegui solucionar.

5 - Exibindo resultados:

[JoséQuintas]

Muito bom.
Achei muito interessante o comando.
É por isso que eu me considero um principiante em MySQL, essa parte é novidade pra mim.

[jelias]

Parabéns Alexandre.

Também considero que tenho muito o que aprender quando o assunto é SQL. Você usou muita coisa que não conheço.

Saudações,

Júlio.

[JoséQuintas]

Aproveitei pra testar num tbrowse.

Código: Selecionar todos

      oTBrowse := { ;
         { "CLIENTE",           { || jpcadas->cdCodigo + " " + jpcadas->cdNome } }, ;
         { PNOT0270Titulo(),    { || PNOT0270Texto() } } }

Código: Selecionar todos

STATIC FUNCTION PNOT0270Titulo()
   RETURN "Primeira  Última   Dias(T)   Dias(M)    Próxima  Valor(Média)"

STATIC FUNCTION PNOT0270Texto()
   LOCAL dDatIni, dDatPri, dDatUlt, nQtdTot := 0, nValTot := 0, cTexto := ""

   dDatIni := Date() - nPeriodo // usar quantos dias pra análise
   dDatPri := Date()
   dDatUlt := Date() - nPeriodo

   SELECT jpnota
   OrdSetFocus( "jpnota3" )
   SET SOFTSEEK ON
   SEEK jpcadas->cdCodigo + Dtos( dDatIni )
   SET SOFTSEEK OFF
   DO WHILE jpnota->nfCadDes == jpcadas->cdCodigo .AND. .NOT. Eof()
      IF jpnota->nfValNot > nValMin .AND. jpnota->nfStatus != "C"
         Encontra( jpnota->nfTransa, "jptransa", "numlan" )
         IF "VENDA" $ jptransa->trReacao
            IF "-1" $ jptransa->trReacao
               nValTot += jpnota->nfValNot
               nQtdTot += 1
               dDatPri := Min( dDatPri, jpnota->nfDatEmi )
               dDatUlt := Max( dDatUlt, jpnota->nfDatEmi )
            ELSEIF "+1" $ jptransa->trReacao
               nValTot -= jpnota->nfValNot
            ENDIF
         ENDIF
      ENDIF
      SKIP
   ENDDO
   cTexto += Dtoc( dDatPri ) + "  "
   cTexto += Dtoc( dDatUlt ) + "  "
   cTexto += Transform( dDatUlt - dDatPri, "@E 999,999" ) + "  "
   cTexto += Transform( ( dDatUlt - dDatPri ) / nQtdTot, "@E 999,999" ) + "  "
   cTexto += Dtoc( dDatUlt + ( ( dDatUlt - dDatPri ) / nQtdTot ) ) + "  "
   cTexto += Transform( nValTot / nQtdTot, "@E 999,999,999.99" )
   SELECT jpcadas
   RETURN cTexto

Tem que levar em conta:
- Somente notas ref. vendas
- descontar notas de devolução

Neste fiz pelo valor.

[JoséQuintas]

Ainda estou revendo o cálculo.
Para data, a primeira compra deve ser deixada de lado.
Ainda estou na dúvida sobre o valor também ser deixado de lado ou não.

Exemplo: duas compras de 5.000 e 10.000, uma no dia 1 e outra no dia 31

Na rotina acima: total 2 compras, total 15.000, tempo 30 dias
média de data: 30/2 = 15 uma compra a cada 15 dias... ERRADO demorou 30 dias pra comprar de novo
média de valor: 7.500 por compra ... talvez

A explicação do erro de data é simples: estamos calculando os intervalos de data, e não as datas.
Um intervalo é um espaço entre uma data e outra.
Tendo duas datas, temos somente UM intervalo.

Por isso fiquei na dúvida do valor da primeira compra entrar no cálculo.
Se estamos analisando os intervalos, talvez o primeiro valor não faça parte de um intervalo.

[romulobonnadio]

Realmente parabens Alexandre, vi hj que nao sei quase nada em SQL!
Obrigado!

[JoséQuintas]

Uia... 5 anos depois kkkk

é praticamente o mesmo original.
A diferença é que pra não complicar o comando, primeiro fiz um select só dos valores, e depois o select com os cálculos.

Tempo de execução: 0,281 segundos BASE REAL

Significa que o cliente vai ver o browse disso em MEIO SEGUNDO.

[alxsts]

Olá!

Parabéns @Quintas.

Eu jamais me lembraria deste tópico. Agradeço aos colegas pelo incentivo.

[JoséQuintas]

Parabéns @Quintas.
Eu jamais me lembraria deste tópico. Agradeço aos colegas pelo incentivo.

Parabéns pra mim nada.
Você que postou a solução.

Eu achei muito interessante, fiz em DBF, mas não esqueci que tinha visto aqui no fórum em SQL.
Como agora ia fazer em SQL, facilitou olhar novamente o post.

Podemos dizer que só usei o que recomendo fazer: deixar o fonte fácil, pra manutenção fácil.
Acabei fazendo isso com o comando SQL.

Aliás....
Já agradeci a sua rotina de browse pra ADO, mas agradecer de novo.
https://pctoledo.org/forum/viewto ... sql+server

É só verificar no fórum: foi depois dela que peguei o MySQL pra valer....
Acho que era só o browse que me faltava pra ir em frente.