Tableau croisé dynamique MySQL
Si j'ai une table MySQL ressemblant à ceci:
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
Est-il possible d'exécuter une requête MySQL pour obtenir une sortie comme ceci:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages ------------------------------------------------------------- CompanyA 0 0 1 3 CompanyB 1 1 2 0
L'idée est que pagecount peut varier de sorte que le montant de la colonne de sortie devrait refléter cela, une colonne pour chaque action/pagecount paire, puis le nombre de visites par company_name. Je ne suis pas sûr si cela s'appelle un tableau croisé dynamique mais quelqu'un l'a suggéré?
8 réponses
Fondamentalement est un tableau croisé dynamique.
Un joli tutoriel sur la façon d'y parvenir peut être trouvé ici: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Je vous conseille de lire ce post et d'adapter cette solution à vos besoins.
mise à Jour
Après que le lien ci-dessus ne soit plus disponible, je me sens obligé de fournir des informations supplémentaires pour vous tous qui recherchez des réponses MySQL pivot ici. Il vraiment eu une grande quantité d'informations, et je ne mettrai pas tout à partir de là ici (encore plus puisque je ne veux pas copier leur vaste connaissance), mais je vais donner quelques conseils sur la façon de traiter les tableaux croisés dynamiques de la manière sql généralement avec l'exemple de peku qui a posé la question en premier lieu.
Peut-être que le lien revient bientôt, je vais garder un œil sur elle.
La manière de feuille de calcul...
Beaucoup de gens utilisent simplement un outil comme MSExcel, OpenOffice ou autre feuille de calcul-outils à cette fin. C'est une solution valide, il suffit de copier les données là-bas et d'utiliser les outils proposés par L'interface graphique pour résoudre ce problème.
Mais... ce n'était pas la question, et cela pourrait même conduire à certains inconvénients, comme la façon d'obtenir les données dans la feuille de calcul, La mise à l'échelle problématique et ainsi de suite.
La manière SQL...
Étant donné que sa table ressemble à ceci:
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;
Regardez maintenant dans sa table désirée:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
Les lignes (EMAIL, PRINT x pages) ressemblent à des conditions. Le regroupement principal est par company_name.
Afin de mettre en place les conditions, cela crie plutôt pour utiliser le CASE-déclaration. Afin de regrouper par quelque chose, eh bien, utilisez ... GROUP BY.
Le SQL de base fournissant ce pivot peut ressembler à ceci:
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`;
Cela devrait fournir le résultat souhaité très rapidement. L'inconvénient majeur de cette approche, plus vous voulez de lignes dans votre tableau croisé dynamique, plus vous devez définir de conditions dans votre SQL déclaration.
Cela peut être traité, aussi, donc les gens ont tendance à utiliser des instructions préparées, des routines, des compteurs et autres.
Quelques liens supplémentaires à ce sujet sujet:
Ma solution est en T-SQL sans pivots:
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
Pour MySQL, vous pouvez directement mettre des conditions dans la fonction SUM() et elle sera évaluée comme booléenne 0 ou 1 et ainsi vous pouvez avoir votre compte en fonction de vos critères sans utiliser IF/CASE instructions
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
Pour le pivot dynamique, utilisez GROUP_CONCAT avec CONCAT.
La fonction GROUP_CONCAT concatène les chaînes d'un groupe en une chaîne avec différentes options.
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;
Un standard-SQL version à l'aide de la logique booléenne:
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;
SQL Violon.
Comment?
TRUE OR NULL les rendements TRUE.FALSE OR NULL les rendements NULL.NULL OR NULL les rendements NULL.
Et COUNT ne compte que les valeurs non nulles. Voilá.
Il existe un outil appelé MySQL Pivot Table generator, il peut vous aider à créer un tableau croisé dynamique basé sur le web que vous pouvez ensuite exporter vers excel(si vous le souhaitez). cela peut fonctionner si vos données sont dans une seule table ou dans plusieurs tables .
Tout ce que vous devez faire est de spécifier la source de données des colonnes (il prend en charge les colonnes dynamiques), les lignes, les valeurs dans le corps de la table et la relation de table (s'il y en a)
La page d'accueil de cet outil est http://mysqlpivottable.net
La bonne réponse est:
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
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 ;