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.
Recientemente en grupos de .NET salió el tema de cómo se pueden redimensionar las dos dimensiones de una matriz de dos dimensiones sin perder los valores que ya se tienen almacenados en dicha matriz.
Venga, una vez más, publico las estadísticas de visitas a este blog del mes recién cerrado. Como siempre, si hay alguien interesado en realizar el seguimiento, puede hacerlo a través del 
Estos últimos días he estado liado programando una clase que tira intensamente de reflexión (me refiero a System.Reflection, no a que haya estado reflexionando intensamente, que también) para relacionar un objeto de una clase determinada (la que sea) con una tabla en la base de datos. Así, utilizando esa clase auxiliar podemos decirle que cargue un objeto a partir de un registro de una tabla o que lo guarde en ella. Y sirve para cualquier clase que tenga una tabla relacionada en la base de datos. Quizá otro día cuelgo esa clase… Pero el caso es que mientras lo desarrollaba he tenido algunos problemillas por no encajarme exactamente los tipos de datos que me devolvía MySQL y los que esperaba .NET, así que tras haber estado buscando cuáles son las equivalencias exactas, paso a exponer la tabla de correspondencias entre tipos de datos tanto en MySQL como en VisualBasic.NET como en el .NET Framework.
Últimos comentarios
RSS