Tabla dinámica de MySQL
Si tengo una tabla MySQL que se ve algo como esto:
company_name action pagecount ------------------------------- Company A PRINT 3 Company A PRINT 2 Company A PRINT 3 Company B EMAIL Company B PRINT 2 Company B PRINT 2 Company B PRINT 1 Company A PRINT 3
¿Es posible ejecutar una consulta MySQL para obtener una salida como esta:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages ------------------------------------------------------------- CompanyA 0 0 1 3 CompanyB 1 1 2 0
La idea es que pagecount
puede variar, por lo que la cantidad de la columna de salida debe reflejar eso, una columna para cada action
/pagecount
par y luego número de visitas por company_name
. No estoy seguro de si esto se llama una tabla dinámica, pero alguien sugirió que?
8 answers
Esto básicamente es una tabla dinámica.
Un buen tutorial sobre cómo lograr esto se puede encontrar aquí: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Aconsejo leer este post y adaptar esta solución a sus necesidades.
Actualizar
Después de que el enlace anterior ya no esté disponible, me siento obligado a proporcionar información adicional para todos los que buscan respuestas de pivote de mysql aquí. Se realmente tenía una gran cantidad de información, y no voy a poner todo desde allí aquí (incluso más ya que simplemente no quiero copiar su vasto conocimiento), pero daré algunos consejos sobre cómo lidiar con las tablas dinámicas de la manera sql en general con el ejemplo de peku que hizo la pregunta en primer lugar.
Tal vez el enlace vuelva pronto, estaré pendiente de él.
La forma de hoja de cálculo...
Muchas personas simplemente usan una herramienta como MSExcel, OpenOffice u otra hojas de cálculo-herramientas para este propósito. Esta es una solución válida, simplemente copie los datos allí y use las herramientas que ofrece la GUI para resolver esto.
Pero... esta no era la pregunta, e incluso podría llevar a algunas desventajas, como cómo obtener los datos en la hoja de cálculo, escalado problemático, etc.
La forma SQL...
Dado que su mesa se ve algo como esto:
CREATE TABLE `test_pivot` (
`pid` bigint(20) NOT NULL AUTO_INCREMENT,
`company_name` varchar(32) DEFAULT NULL,
`action` varchar(16) DEFAULT NULL,
`pagecount` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=MyISAM;
Ahora mira en su tabla deseada:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
Las filas (EMAIL
, PRINT x pages
) se asemejan a las condiciones. La agrupación principal es por company_name
.
Para establecer las condiciones, esto más bien grita para usar el CASE
-declaración. Con el fin de agrupar por algo, bueno, el uso ... GROUP BY
.
El SQL básico que proporciona este pivote puede verse algo como esto:
SELECT P.`company_name`,
COUNT(
CASE
WHEN P.`action`='EMAIL'
THEN 1
ELSE NULL
END
) AS 'EMAIL',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '1'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 1 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '2'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 2 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '3'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 3 pages'
FROM test_pivot P
GROUP BY P.`company_name`;
Esto debería proporcionar el resultado deseado muy rápido. El mayor inconveniente de este enfoque, cuantas más filas desee en su tabla dinámica, más condiciones necesitará definir en su SQL instrucción.
Esto también se puede tratar, por lo tanto, la gente tiende a usar declaraciones preparadas, rutinas, contadores y demás.
Algunos enlaces adicionales sobre esto tema:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-01-15 08:41:46
Mi solución está en T-SQL sin ningún pivote:
SELECT
CompanyName,
SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM
Company
GROUP BY
CompanyName
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-31 09:20:19
Para MySQL puede poner directamente condiciones en la función SUM()
y será evaluada como booleana 0
o 1
y así puede tener su conteo basado en sus criterios sin usar IF/CASE
declaraciones
SELECT
company_name,
SUM(action = 'EMAIL')AS Email,
SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name
DEMO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-31 09:19:06
Para pivote dinámico, use GROUP_CONCAT
con CONCAT
.
La función GROUP_CONCAT concatena cadenas de un grupo en una cadena con varias opciones.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN action = "',
action,'" AND ',
(CASE WHEN pagecount IS NOT NULL
THEN CONCAT("pagecount = ",pagecount)
ELSE pagecount IS NULL END),
' THEN 1 ELSE 0 end) AS ',
action, IFNULL(pagecount,'')
)
)
INTO @sql
FROM
t;
SET @sql = CONCAT('SELECT company_name, ', @sql, '
FROM t
GROUP BY company_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-10-10 10:51:38
A stardard-SQL versión usando lógica booleana :
SELECT company_name
, COUNT(action = 'EMAIL' OR NULL) AS "Email"
, COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
, COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
, COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM tbl
GROUP BY company_name;
Violín SQL.
¿Cómo?
TRUE OR NULL
rendimientos TRUE
.FALSE OR NULL
rendimientos NULL
.NULL OR NULL
rendimientos NULL
.
Y COUNT
solo cuenta valores no nulos. Voilá.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-18 13:10:21
Hay una herramienta llamada MySQL Pivot Table generator, que puede ayudarlo a crear una tabla dinámica basada en la web que luego puede exportar a excel(si lo desea). puede funcionar si sus datos están en una sola tabla o en varias tablas .
Todo lo que necesita hacer es especificar la fuente de datos de las columnas (admite columnas dinámicas), filas, los valores en el cuerpo de la tabla y la relación de la tabla (si hay alguna)
La página de inicio de esta herramienta es http://mysqlpivottable.net
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-20 19:19:42
La respuesta correcta es:
select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-18 19:44:08
select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E
from
(select t2.name as name,
case when t2.prodid = 1 then t2.counts
else 0 end prod_A,
case when t2.prodid = 2 then t2.counts
else 0 end prod_B,
case when t2.prodid = 3 then t2.counts
else 0 end prod_C,
case when t2.prodid = 4 then t2.counts
else 0 end prod_D,
case when t2.prodid = "5" then t2.counts
else 0 end prod_E
from
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3
group by t3.name ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-11-27 15:24:00