Customer Grouping with LIMIT 10 per Store

Question

Good morning guys, 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 

Who returns to me:

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

I need to group together 10 customers who bought the most per store, someone already had to do it?

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

Thank you

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