Calcular el promedio o modo en Mysql

By admin on Sep 28, 2010 | In Aprendiz, Trucos avanzado | Send feedback »

Para calcular la media (o promedio) podemos utilizar la función AVG en Mysql.

Code:

select avg(sal) as prom_sal
from emp;

prom_sal
========
2073.9286

Recorda sin embargo que la función no funciona en un campo NULL. Si deseas incluir valores NULL (por ejemplo, asuma un valor cero)
el uso COALESCE cambia por defecto el valor NULL a cero.

select avg(coalesce(sal,0)) as mean_sal
from emp;

Ahora podemos producir la cifra media por columna agrupadas, por ejemplo, por departamento

Code:

select deptno, avg(coalesce(sal,0)) as mean_sal
from emp
group by deptno;

deptno mean_sal
====== ========
10 2916.6667
20 2175.0000
30 1568.3333

El modo es el valor que se produce con mayor frecuencia en un determinado conjunto de datos.
Si quiero saber de la tabla Productos, lo que es el modo del campo FULLPRICE de un grupo de productos determinados (en este caso 'CA'), puedo usar una subconsulta para encontrar el modo.
La subconsulta calcula la cuenta diferentes para cada Fullprice, mientras que la parte principal de la consulta se elige el precio que se produce más.

Code:

SELECT FullPrice FROM product
WHERE productgroupcode = 'CA'
GROUP BY FullPrice
HAVING count(*) >= all (SELECT count(*)
                        FROM product
                        WHERE productgroupcode = 'CA'
                        GROUP BY FullPrice)

FullPrice
=========
14

Hay otro requisito común en la búsqueda de un promedio, que se llama la mediana.
La mediana es el valor del punto medio de un conjunto de elementos ordenados.
Para calcular la mediana se puede utilizar un "SELF-JOIN" o producto cartesiano de una tabla consigo misma (combinación de la tabla a sí mismo), y luego usar la cláusula HAVING para filtrar las filas donde el número de
veces (p. FULLPRICE = P. FULLPRICE) es mayor o igual al número de veces (P. FULLPRICE> P. FULLPRICE):

Code:

SELECT avg(FullPrice)
FROM ( select q.FullPrice
      FROM product q, product p
      WHERE p.productgroupcode = q.productgroupcode
      AND q.productgroupcode = 'CA'
      GROUP BY q.FullPrice
      HAVING sum(case when q.FullPrice = p.FullPrice then 1 else 0 end)
                     >= abs(sum(sign(q.FullPrice - p.FullPrice)))
      ) t

avg(FullPrice)
=========
14.0000

Podemos mostrar el resultado de las tareas individuales con el código que se mueve el cálculo SUMA en la sentencia SELECT:

Code:

select q.FullPrice, p.FullPrice,
       sum(case when q.FullPrice = p.FullPrice then 1 else 0 end) as cnt1,
       abs(sum(sign(q.FullPrice - p.FullPrice))) as cnt2
      FROM product q, product p
      WHERE p.productgroupcode = q.productgroupcode
      AND q.productgroupcode = 'CA'
group by q.FullPrice

FullPrice cnt1 cnt2
10 --- 25 --- 130
11 --- 4 --- 38
12 --- 1 --- 16
14 --- 64 --- 56
15 --- 1 --- 2
20 --- 1 --- 4
24 --- 9 --- 24
26 --- 1 --- 12
29 --- 9 --- 48
39 --- 25 --- 120
630 --- 1 --- 30

Por último, aquí voy a enumerar los registros para que pueda ver todo el conjunto con el que trabajamos.

Code:

SELECT PRODUCTID, FULLPRICE
FROM product
WHERE PRODUCTGROUPCODE = 'CA'
ORDER BY FULLPRICE

PRODUCTID --- FULLPRICE
12175 ---- 10
12014 ---- 10
13297 ---- 10
10002 ---- 10
10003 ---- 10
10838 ---- 11
13109 ---- 11
13107 ---- 12
13299 ---- 14
12177 ---- 14
14030 ---- 14
13621 ---- 14
12016 ---- 14
11003 ---- 14
11057 ---- 14
11056 ---- 14
10001 ---- 15
12949 ---- 20
13360 ---- 24
13470 ---- 24
10955 ---- 24
13359 ---- 26
13298 ---- 29
12015 ---- 29
12176 ---- 29
13024 ---- 39
10863 ---- 39
13023 ---- 39
10862 ---- 39
10993 ---- 39
12141 ---- 630

Tags: mediana, modo, promedio

enlaces

By admin on Feb 16, 2010 | In Aprendiz, Trucos avanzado | Send feedback »

Sólo quería entrar en un par de cosas que son de interés para aquellos que utilizan o programan de Mysql. En primer lugar, si uses Twitter la mejor manera de buscar artículos relacionados con MySQL es entrar #mysql en el campo de búsqueda.
En segundo lugar, este parece ser un enlace ideal para explorar más comandos en Mysql:
sentencias-en-mysql-que-tal-ves-no-conocias

Tags: twitter

Proyecto Euler 2 en Mysql

By admin on Sep 5, 2009 | In Trucos avanzado | Send feedback »

Aquí está la pregunta 2 del Proyecto de Euler The Euler Project :
Cada nuevo número de la secuencia de Fibonaci se genera sumando los dos números que lo preceden.
Empezando por el 1 y el 2, los diez primeros números serán:1, 2, 3, 5, 8, 13, 21, 34, 55, 89, ...

Hallar la suma de todos números pares de la secuencia de Fibonaci que no exceda de cuatro millónes.


Pues, en cuanto a una solución de Perl, tenemos el siguiente código

Code:

#!/usr/bin/perl
 
@f=(1,2);
$sum=2;
while(($f=$f[0]+$f[1]) <= 4_000_000) {
       shift @f; push @f,$f;
$sum+=$f if $f%2==0 }
print $sum, "\n";
# Answer = 4613732

Vemos que el programa Perl se suma a $sum donde la variable $f es un número par, y los lazos hasta que el valor cambiado en la memoria (que se añade a cada valor anterior para producir la serie de Fibonacci) es superior a 4.000.000.


¿Cómo podemos lograr esto en MySQL? Una vez más (como en la primera entrada) vamos a utilizar variables para iterar. Con el fin de obtener la secuencia de Fibonacci se puede utilizar este...

Code:

set @varsum=1;
set @varadd=1;
set @var1=1;
set @var2=1;
 
select @varsum:=@varadd+@varsum as fibonacci,
       @varadd:=@var2,
       @var2:=@varsum, orderid
from orders limit 40;


(orders es una tabla que yo uso que tiene miles de filas).
Puse 40 en la cláusula de LIMIT después de un poco de ensayo y error. Recuerdes restaurar las variables cada vez que se ejecuta una consulta como esta.
A continuación, puede hacer una suma de los valores devueltos cuando el valor de Fibonacci es menos de 4 millones de dólares, mediante un INNER SELECT:

Code:

set @varsum=1;
set @varadd=1;
set @var1=1;
set @var2=1;
 
select sum(fibonacci) from (select @varsum:=@varadd+@varsum as fibonacci,@varadd:=@var2,@var2:=@varsum, orderid from orders limit 40) tmp where fibonacci < 4000000;

Pero estamos sumando todos los números haciendo esto - el problema dice que sólo debe sumar los números pares de la secuencia. Modificar fácilmente (véte la solución para el número 1) utilizando la función MOD:

Code:

set @varsum=1;
set @varadd=1;
set @var1=1;
set @var2=1;
 
select sum(fibonacci) from
(select @varsum:=@varadd+@varsum as fibonacci,
@varadd:=@var2, @var2:=@varsum
from orders limit 40) tmp
where fibonacci < 4000000
and mod(fibonacci,2)=0;
 
+----------------+
| sum(fibonacci) |
+----------------+
|        4613732 |
+----------------+
1 row in set (0.27 sec)


Espero que este código es de utilidad para alguien.
Saludos, Mark

Tags: mod, project euler, variables
1 2 3 4 5 6 7 8 >>