MySQL no admite TRANSFORM PIVOT (pero se pueden obtener resultados parecidos).
Estos últimas días he estado mirando un tema que me preocupaba porque iba a necesitarlo en la aplicación que ando desarrollando... y tras no haber encontrado nada, finalmente me he tenido que poner a fondo con ello hasta sacarlo de una u otra manera. El tema consiste en hacer una consulta de referencias cruzadas en MySQL y, como digo, tras bastante investigar he descubierto que en realidad MySQL no admite esta clase de consultas. Para explicar mejor lo que pretendía hacer voy a basarme en un ejemplo consistente en la siguiente tabla sfc_salefrcast:
| sfc_cus | sfc_mat | sfc_mth | sfc_qty |
+---------+---------+------------+---------+
| 12345 | 1 | 2008-11-01 | 1200 |
| 12345 | 3 | 2008-11-01 | 1500 |
| 54321 | 2 | 2008-11-01 | 2500 |
| 54321 | 3 | 2008-12-01 | 3500 |
| 54321 | 3 | 2009-03-01 | 4500 |
| 54321 | 3 | 2009-07-01 | 4500 |
| 99999 | 4 | 2009-02-01 | 2000 |
| 99999 | 4 | 2009-04-01 | 4000 |
| 99999 | 4 | 2009-06-01 | 6000 |
+---------+---------+------------+---------+
Es una tabla de previsiones de ventas en donde sfc_cus es el código del cliente, sfc_mat el código del material, sfc_mth el mes representado por su primer día y sfc_qty la cantidad que prevemos que vamos a venderle de dicho material a dicho cliente dicho mes (y dichosos nosotros si acertamos).
Bien, pues a partir de esta tabla pretendía conseguir una tabla (o vista... un conjunto de registros, vamos) en la que en cada registro se me mostrara una combinación de cliente y material, y a partir de aquí una columna para cada mes con la cantidad correspondiente. Algo del estilo de lo siguiente...
| sfc_cus | sfc_mat | M0 | M1 | M2 | M3 | M4 | M5 |
+---------+---------+------+------+------+------+------+------+
| 12345 | 1 | 1200 | 0 | 0 | 0 | 0 | 0 |
| 12345 | 3 | 1500 | 0 | 0 | 0 | 0 | 0 |
| 54321 | 2 | 2500 | 0 | 0 | 0 | 0 | 0 |
| 54321 | 3 | 0 | 3500 | 0 | 0 | 4500 | 0 |
| 99999 | 4 | 0 | 0 | 0 | 2000 | 0 | 4000 |
+---------+---------+------+------+------+------+------+------+
...en donde M0 es el mes actual (2008-11-01), M1 es el mes próximo, M2 el siguiente, etc.
Pues bien, si estuviéramos trabajando en Microsoft Access podríamos hacer algo simple como...
SELECT sfc_cus, sfc_mat
FROM sfc_salefrcast
GROUP BY sfc_cus, sfc_mat
PIVOT sfc_mth;
...y obtendríamos algo muy parecido al resultado deseado. Simplemente no nos mostraría las columnas donde no haya ningún valor que mostrar (como M2), pero sería un problema muy menor que solventaríamos sin dificultad. En Microsoft Excel también sería sencillísimo obtener esos resultados a través de una tabla dinámica.
No obstante, trabajo con MySQL. Y por desgracia MySQL no admite TRANSFORM-PIVOT. De hecho no he encontrado ninguna alternativa atractiva para resolver mi problema, de modo que he tenido que rascar un poquito y crear una a medida, que me ha quedado así:
sfc_cus,
sfc_mat,
SUM(IF(MID(sfc_mth, 1, 7) = MID(NOW(), 1, 7), sfc_qty, 0)) AS M0,
SUM(IF(MID(sfc_mth, 1, 7) =
MID(DATE_ADD(NOW(),INTERVAL 1 MONTH), 1, 7),
sfc_qty, 0)) AS M1,
SUM(IF(MID(sfc_mth, 1, 7) =
MID(DATE_ADD(NOW(),INTERVAL 2 MONTH), 1, 7),
sfc_qty, 0)) AS M2,
SUM(IF(MID(sfc_mth, 1, 7) =
MID(DATE_ADD(NOW(),INTERVAL 3 MONTH), 1, 7),
sfc_qty, 0)) AS M3,
SUM(IF(MID(sfc_mth, 1, 7) =
MID(DATE_ADD(NOW(),INTERVAL 4 MONTH), 1, 7),
sfc_qty, 0)) AS M4,
SUM(IF(MID(sfc_mth, 1, 7) =
MID(DATE_ADD(NOW(),INTERVAL 5 MONTH), 1, 7),
sfc_qty, 0)) AS M5
FROM
sfc_salefrcast
GROUP BY
sfc_cus, sfc_mat;
(Ver actualización 1.)
Al no utilizar directamente fechas sino estar jugando con NOW() y la función DATE_ADD, obtenemos siempre resultados para el mes corriente y los siguientes N meses, independientemente de cuando solicitemos esa información a la base de datos.
Ignoro si hay alguna manera más directa de hacerlo (y si la hay y la conoces, estaré encantadísimo de leerla), pero por lo pronto lo he solucionado así...
1. Siguiendo el consejo -uno de ellos- de Luís Medel en los comentarios, he cambiado la versión original que había publicado para dejar esta más simplificada al realizar en un solo paso el sumatorio y el filtrado. Además, Luís propone utilizar CASE WHEN THEN ELSE END en lugar de IF.
2. Pedro Cambra facilita en los comentarios un link a la página de Roland Bouman donde se expone un método para hacerlo de forma dinámica utilizando procedimientos almacenados.
3. Pedro Cambra comenta también que MySQL dispone de la función EXTRACT con el parámetro YEAR_MONTH que efectivamente se podría utilizar para mejorar el apartado del MID(fecha,1,7). Más información en el manual de referencia de MySQL.
Tags: bases de datos, MySQL, referencias cruzadas, transform pivot
Noviembre 27th, 2008 at 15:43
Recuerda, CASE es tu amigo!
Ésto es lo que yo he usado en Oracle para eso mismo. Lo acabo de escribir al vuelo, así que es probable que tenga algún error.
select sfc_cus, sfc_mat,
sum(case when month (sfc_mth) = 1 then sfc_qty else 0 end) M1,
sum(case when month (sfc_mth) = 2 then sfc_qty else 0 end) M2,
sum(case when month (sfc_mth) = 3 then sfc_qty else 0 end) M3,
sum(case when month (sfc_mth) = 4 then sfc_qty else 0 end) M4,
sum(case when month (sfc_mth) = 5 then sfc_qty else 0 end) M5
from sfc_salefrcast
group by sfc_cus, sfc_mat
Noviembre 27th, 2008 at 15:44
Hay un método que ví en el blog de Roland Bouman para hacer esto de forma dinámica, osea si no conoces el número de columnas que tienes que generar usando GROUP_CONCAT y un procedimiento almacenado:
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html
Noviembre 27th, 2008 at 16:04
Luís, mil gracias por tu comentario. Sin embargo tengo una duda... ¿aporta alguna mejora -en rendimiento, se entiende- la versión 1 respecto a la 2?
1) SUM(CASE WHEN MONTH(sfc_mth) = MONTH(DATE_ADD(NOW(),INTERVAL 2 MONTH)) THEN sfc_qty ELSE 0 END) AS M2
2) SUM(IF(MONTH(sfc_mth) = MONTH(DATE_ADD(NOW(),INTERVAL 2 MONTH)), sfc_qty, 0)) AS M2
Noviembre 27th, 2008 at 16:08
Hola Pedro!
Pues caí de casualidad (bueno, de casualidad no, Googleando rato largo) en esa página, pero la verdad es que no me terminó de convencer lo complejo de ese sistema. De todos modos, actualizaré el post para reflejar dicho link (para el que no se lea los comentarios). Gracias!
Noviembre 27th, 2008 at 16:19
Y ya abusando de la confianza (¡no haber entrado!) lo de MONTH(X) está bien, pero en realidad tendría que utilizar YEAR(X) y MONTH(X) ya que no quiero saber cuánto le venderemos en diciembres varios, sino uno en concreto, por eso me decidí a utilizar un MID(X,1,7)... porque... ¿no hay ninguna función propia de MySQL que permita comparar mes y año al unísono, verdad (no valen una stored function)?
Noviembre 27th, 2008 at 16:56
Pues no sabría decirte si existe una mejora en rendimiento, la verdad. El método del "IF" nunca lo he utilizado.
Respecto a lo otro que comentas, se me ocurre que puedes realizar la comparación con la fecha actual aplicando ADDTIME a la columna (sumas meses y listo) Siempre será más rápido que realizar operaciones sobre cadenas.
Si eso no funciona (te hablo sin poder comprobar nada de lo que te digo) intenta con DATEFORMAT(fecha, '%Y%m'). Así obtienes año y mes una tacada (como cadena, claro)
Noviembre 27th, 2008 at 18:33
Por cierto, aprovecho para *volver* a corregirme. Donde digo ADDTIME es ADDDATE
Noviembre 27th, 2008 at 19:04
No se si es exactamente lo que buscas, pero la funcion EXTRACT tiene un parametro YEAR_MONTH para obtener el año y mes de una tacada sin usar DATE_FORMAT
EXTRACT(YEAR_MONTH from sfc_mth)
Creo que la notificacion via mail no te esta funcionando :S
Noviembre 27th, 2008 at 23:53
Gracias por los añadidos, mañana los investigo. Y gracias también por avisarme que no chuta bien el aviso por mail... miraré si se puede revisar algo ahí también. Igual debería ir actualizando a la versión actual de WordPress (estas cosas siempre me cuestan... para qué actualizar lo que funciona).
Mayo 18th, 2009 at 00:20
Muchas Gracias x el ejemplo y las sugerencias de todos, tomando las mismas me quedo asi la tabla cruzada
SELECT
municipio,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200901 then id else 0 end) AS Enero,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200902 then id else 0 end) AS Febrero,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200903 then id else 0 end) AS Marzo,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200904 then id else 0 end) AS Abril,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200905 then id else 0 end) AS Mayo
count(case when EXTRACT(YEAR_MONTH from fecha) = 200906 then id else 0 end) AS Junio,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200907 then id else 0 end) AS Julio,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200908 then id else 0 end) AS Agosto,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200909 then id else 0 end) AS Septiembre,
count(case when EXTRACT(YEAR_MONTH from fecha) = 200910 then id else 0 end) AS Octubre
count(case when EXTRACT(YEAR_MONTH from fecha) = 200911 then id else 0 end) AS Noviembre
count(case when EXTRACT(YEAR_MONTH from fecha) = 200912 then id else 0 end) AS Diciembre
FROM
casos
GROUP BY
municipio
¿Xq no funciona? Es decir no coloca la cantidad x municipio en cada mes, sino q el COUNT me cuenta todos los registros y me coloca el total de registros en cada mes.
Ahora si agrego un campo llamado 'cantidad' a la tabla casos y le coloco 1 a todos los registros, con SUM si funciona lo siguiente:
SELECT
municipio,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200901 then cantidad else 0 end) AS Enero,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200902 then cantidad else 0 end) AS Febrero,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200903 then cantidad else 0 end) AS Marzo,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200904 then cantidad else 0 end) AS Abril,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200905 then cantidad else 0 end) AS Mayo
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200906 then cantidad else 0 end) AS Junio,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200907 then cantidad else 0 end) AS Julio,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200908 then cantidad else 0 end) AS Agosto,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200909 then cantidad else 0 end) AS Septiembre,
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200910 then cantidad else 0 end) AS Octubre
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200911 then cantidad else 0 end) AS Noviembre
Sum(case when EXTRACT(YEAR_MONTH from fecha) = 200912 then cantidad else 0 end) AS Diciembre
FROM
casos
GROUP BY
municipio
¿Xq no funciona con COUNT q seria lo correcto?
Mayo 18th, 2009 at 00:35
Rapidamente he podido solucionar, para obtener una tabla cruzada con la cantidad de registros x mes en el año 2009 solucione de esta forma (es extraño pero me salio fue con SUM y no con COUNT):
SELECT
municipio,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200901 then 1 else 0 end) AS Enero,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200902 then 1 else 0 end) AS Febrero,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200903 then 1 else 0 end) AS Marzo,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200904 then 1 else 0 end) AS Abril,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200905 then 1 else 0 end) AS Mayo,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200906 then 1 else 0 end) AS Junio,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200907 then 1 else 0 end) AS Julio,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200908 then 1 else 0 end) AS Agosto,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200909 then 1 else 0 end) AS Septiembre,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200910 then 1 else 0 end) AS Octubre,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200911 then 1 else 0 end) AS Noviembre,
SUM(case when EXTRACT(YEAR_MONTH from fecha) = 200912 then 1 else 0 end) AS Diciembre
FROM
casos
GROUP BY
municipio
Mayo 26th, 2009 at 10:11
Simplemente excelente, funciona muy bien, muy agradecido.