Obtain error data in a query in mysql with the condition that it only shows the errors that cover 80% of the total errors

Question

Obtain error data in a query in mysql with the condition that it only shows the errors that cover 80% of the total errors

Navigation

#1 by (1 votes)

2

I have the following doubt, although I already did it by taking the data and processing it with php I wanted to know if I can already bring the information from the base as follows:

Based on this query I get a list of errors with their respective impacts (total of those errors):

SELECT mtg.idTemaGestion, mtg.detTemaGestion,
count(mtg.idTemaGestion) as total
from mac_gestion mg
inner join mac360_mac_gestion mmg on mg.idMacGestion = mmg.idMacGestion_FK
inner join mac360 m on m.idMac360 = mmg.idMac360_FK
inner join mac_tipogestion tg on tg.idTipoGestion = mg.idTipoGestion_FK
inner join datos_nomina dn on dn.idDatosNomina = m.idDatosNominaRecibe_FK
inner join site si on si.idSite = dn.idSite_FK
inner join mac_temagestion mtg on mtg.idTemaGestion = mg.idTemaGestion_FK
where mg.error = 'si'
and si.idSIte in (2)
and tg.idTipoGestion in (1)
group by mtg.idTemaGestion
order by total desc;

After this, what I need is to build something like a pareto where it only shows me the errors that cover 80%.

My total of errors is 189
As I show in the image taking the% of impact and then adding them I am armed with the group of data that I have to show since they cover 80% of the total errors, the others are not necessary since the impact is little or null.

Now, is it possible to return that information by query directly and not have to process the data afterwards? With stored procedures? Can you declare variables within the query and validate from there? They guide me to know what I have to look for in order to answer this doubt I have, thank you very much.

BASANDOME SOLUTION IN THE ANSWER:

SELECT *, round((c.acumulado / total), 2) as pareto
from (
SELECT titulo, cantidad,
(select sum(cantidad) from errores) as total,
@acum:= @acum + cantidad as acumulado
from errores
JOIN (SELECT @acum:= 0) r
order by cantidad desc
) c
WHERE C.ACUMULADO/Total <= 0.80

mysql

asked by Juan 14.11.2018 в 18:58

source

1 answer

Driver laravel moficar to search with more than one product Doubt ascending / descending order

score 1 · Accepted Answer

Of course you can, you need two things:

The sum total of the amounts
And a cumulative for each row from a descending ranking

The total can be handled by means of a subquery, the accumulated one eventually also or using a variable.

The following is a proof of concept:

create table Errores (
    Titulo varchar(100), 
    Cantidad INT
)

insert into Errores (Titulo, Cantidad)
VALUES  ('Error A', 15), 
        ('Error B', 85), 
        ('Error C', 25), 
        ('Error D', 64), 
        ('Error E', 10) 

SELECT *,
    C.ACUMULADO/Total
    FROM (SELECT   t.Titulo,
                   t.Cantidad,
                   @acum:= @acum+ t.Cantidad AS ACUMULADO,
                   tot.Total   
                   FROM Errores t
                   JOIN (SELECT @acum:= 0) r
                   JOIN (SELECT SUM(Cantidad) as 'Total' FROM Errores) tot
                   ORDER BY t.Cantidad desc
          ) C
          WHERE C.ACUMULADO/Total <= 0.8
          ORDER BY Cantidad desc

drop table Errores

Result (We filter up to the maximum value less than 80%):

╔═══╦═════════╦══════════╦═══════════╦═══════╦═══════════════════╗
║ # ║ Titulo  ║ Cantidad ║ ACUMULADO ║ Total ║ C.ACUMULADO/Total ║
╠═══╬═════════╬══════════╬═══════════╬═══════╬═══════════════════╣
║ 1 ║ Error B ║ 85       ║ 85        ║ 199   ║ 0,42713567839196  ║
╠═══╬═════════╬══════════╬═══════════╬═══════╬═══════════════════╣
║ 2 ║ Error D ║ 64       ║ 149       ║ 199   ║ 0,748743718592965 ║
╚═══╩═════════╩══════════╩═══════════╩═══════╩═══════════════════╝

In this example, the Errores table comes to represent your query, maybe you should enter it previously in a temporary table since you use it again in a subcosulta, depending on the version of MySQL you could even use a CTE .

If what you are looking for is to reach the first row that exceeds 80% you could solve it in the following way:

SELECT *    
    FROM (SELECT   t.Titulo,
                   t.Cantidad,
                   (CASE WHEN @acum/tot.Total < 0.8 THEN 0 ELSE 1 END) as 'Mayor80',
                   (@acum := @acum + t.Cantidad)/tot.Total as 'Porcentaje'
                   FROM Errores t
                   JOIN (SELECT @acum := 0) r
                   JOIN (SELECT SUM(Cantidad) as 'Total' FROM Errores) tot
                   ORDER BY t.Cantidad desc
          ) C
          WHERE Mayor80 = 0
          ORDER BY Cantidad desc

Demo: sql fiddle