Clustering of Customers with LIMIT 10 per Store

Question

Clustering of Customers with LIMIT 10 per Store

Navigation

#1 by (0 votes)

0

Good morning, I have the following query for MYSQL 8:

With    

Empresa as (Select aurea.oi, aurea.razao, aurea.fantasia From aurea Where aurea.id in ( 1302, 791, 2496, 1681, 1203 )),

CapaPedido as (Select cpd.oi, cpd.cp_tipo, cpd.cp_codi, cpd.cp_datc, cpd.cp_fatu, cpd.cp_clie, Empresa.razao, Empresa.fantasia
               from cpd
               inner join Empresa on Empresa.oi = cpd.oi
               where cpd.cp_fatu = 'S'
               and cpd.cp_nfop in ('N', 'F')
               and datediff(date(now()), date(cpd.cp_datc)) <= 180),

TabelaPedido as (Select CapaPedido.oi, CapaPedido.razao, CapaPedido.fantasia, CapaPedido.cp_clie, sum(tbp.tp_quan) as Quantidade, sum(tbp.tp_quan * tbp.tp_valo) as valorTotal 
                 from tbp FORCE INDEX(inner_cpd)
                 inner join CapaPedido on CapaPedido.oi = tbp.oi and CapaPedido.cp_codi = tbp.tp_codi and CapaPedido.cp_tipo = tbp.tp_tipo
                 group by CapaPedido.oi, CapaPedido.cp_clie
                 order by CapaPedido.oi ASC, valorTotal DESC)

select Pedido.*, Tabela.valorTotal
from (select TabelaPedido.oi from TabelaPedido inner join Empresa on Empresa.oi = TabelaPedido.oi group by TabelaPedido.oi) as Pedido
left join (select TabelaPedido.oi, TabelaPedido.cp_clie, valorTotal from TabelaPedido group by TabelaPedido.oi, TabelaPedido.cp_clie limit 10) as Tabela on Tabela.oi = Pedido.oi

That returns me:

Where Nul should appear a sequence of 10 results for each 'OI' that in this case is the store, I can only make it appear for the first one.

I need to group 10 customers who bought most per store, did anyone ever have to do that?

I need to resolve in MYSQL, which already brings the result ready.

Thank you

mysql

asked by anonymous 11.09.2017 / 18:02

1 answer

"Uncaught SyntaxError: Unexpected token:" and "Access-Control-Allow-Origin" Read HTML inside a confirmButtonText of the SweetAlert plugin

score 0 · Answer 1

Solution

With 

Empresa as (Select aurea.oi, aurea.razao, aurea.fantasia From aurea Where aurea.id in ( 1302, 791, 2496, 1681, 1203 )),

CapaPedido as (Select cpd.oi, cpd.cp_tipo, cpd.cp_codi, cpd.cp_datc, cpd.cp_fatu, cpd.cp_clie, Empresa.razao, Empresa.fantasia
               from cpd
               inner join Empresa on Empresa.oi = cpd.oi
               where cpd.cp_fatu = 'S'
               and cpd.cp_nfop in ('N', 'F')
               and datediff(date(now()), date(cpd.cp_datc)) <= 180),

TabelaPedido as (Select CapaPedido.oi, CapaPedido.razao, CapaPedido.fantasia, CapaPedido.cp_clie, sum(tbp.tp_quan) as Quantidade, sum(tbp.tp_quan * tbp.tp_valo) as valorTotal 
                 from tbp FORCE INDEX(inner_cpd)
                 inner join CapaPedido on CapaPedido.oi = tbp.oi and CapaPedido.cp_codi = tbp.tp_codi and CapaPedido.cp_tipo = tbp.tp_tipo
                 group by CapaPedido.oi, CapaPedido.cp_clie
                 order by CapaPedido.oi ASC, valorTotal DESC)

select oi, razao, fantasia, Quantidade, valorTotal, lojaRank
from 
   (
     select oi, cp_clie, razao, fantasia, Quantidade, valorTotal,
     @lojaRank := if(@currentRank = oi, @lojaRank + 1, 1) as lojaRank,
     @currentRank := oi
     from TabelaPedido
     order by oi, valorTotal DESC
   ) rank
where lojaRank <= 10

[] 's