我正在寻找一种从mysql中选择数据并返回聚合数据的时间序列作为绘制图形的平面矩阵的方法。对于每个合作伙伴，我想知道他一天发布了多少个帖子和后续版本。
我有一个带有伙伴伙伴postid，... postvariationid，... createdate（日期时间）的平面表。我确实有几个合作伙伴，我想互相比较，例如伙伴A，伙伴B ...伙伴N
我汇总了每个合作伙伴每天的计数
SELECT P.partner
, DATE_FORMAT(P.datefield, '%Y-%m-%d') AS pivot_date
, COUNT(DISTINCT P.postid) AS nb_post
, COUNT(DISTINCT P.postvariationid) AS nb_postvariation
, COUNT(DISTINCT P.postvariationid) / COUNT(DISTINCT P.postid) AS avg_postvariation
FROM posts P
GROUP BY P.partner, DATE_FORMAT(P.datefield, '%Y-%m-%d')
ORDER BY pivot_date ASC
DDL
CREATE TABLE `posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` varchar(255) NOT NULL,
`partner` varchar(50) NOT NULL,
`postid` varchar(255) NOT NULL,
`postvariationid` varchar(255) NOT NULL,
`dateupdated` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
样本数据
INSERT INTO posts (`id`, `uid`,`partner`,`postid`, `postvariationsid`) VALUES
(null,'1','partnerA','postA','postA_var1','2014-09-25 15:58:01'),
(null,'2','partnerA','postA','postA_var2','2014-09-25 15:58:01'),
(null,'3','partnerA','postA','postA_var3','2014-09-25 15:58:01'),
(null,'4','partnerA','postB','postB_var1','2014-09-25 15:58:01'),
(null,'5','partnerA','postB','postB_var1','2014-09-25 15:58:01'),
(null,'6','partnerA','postB','postB_var1','2014-09-25 15:58:01'),
(null,'7','partnerC','postC','postC_var1','2014-09-25 15:58:01'),
(null,'8','partnerD','postD','postD_var1','2014-09-25 15:58:01'),
(null,'9','partnerE','postE','postC_var1','2014-09-25 15:58:01'),
(null,'11','partnerA','postK','postK_var1','2014-09-24 15:58:01'),
(null,'12','partnerA','postK','postK_var2','2014-09-24 15:58:01'),
(null,'13','partnerA','postK','postK_var3','2014-09-24 15:58:01'),
(null,'14','partnerA','postL','postL_var1','2014-09-24 15:58:01'),
(null,'15','partnerA','postL','postL_var2','2014-09-24 15:58:01'),
(null,'16','partnerA','postL','postL_var3','2014-09-24 15:58:01'),
(null,'17','partnerC','postM','postM_var1','2014-09-24 15:58:01'),
(null,'18','partnerD','postN','postN_var1','2014-09-24 15:58:01'),
(null,'19','partnerE','postO','postO_var1','2014-09-24 15:58:01'),
(null,'21','partnerA','postF','postF_var1','2014-09-23 15:58:01'),
(null,'22','partnerA','postF','postF_var2','2014-09-23 15:58:01'),
(null,'23','partnerA','postF','postF_var3','2014-09-23 15:58:01'),
(null,'24','partnerA','postG','postG_var1','2014-09-23 15:58:01'),
(null,'25','partnerA','postG','postG_var1','2014-09-23 15:58:01'),
(null,'26','partnerA','postG','postG_var1','2014-09-23 15:58:01'),
(null,'27','partnerC','postH','postH_var1','2014-09-23 15:58:01'),
(null,'28','partnerD','postI','postI_var1','2014-09-23 15:58:01'),
(null,'29','partnerE','postJ','postJ_var1','2014-09-23 15:58:01');
该查询每天返回4行。每个伙伴一个。
如何展平我作为每个合作伙伴输出的带有一行列的表，例如：date_day，partnerA_nb_posts，partnerA_nb_postvariations，partnerB_nb_posts，partnerB_nb_postvariations ...每天仅一行？
非常感谢您的专业知识！
最佳答案
也许您想要这个动态的枢纽？如果您添加更多的合作伙伴，这将继续添加新的列，只是不要在phpmyadmin中尝试，请使用一些现代客户端（如heidisql）。
SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN partner= ''',
partner,
''' THEN nb_post END) AS ',
CONCAT(partner,'nb_post'),
',MAX(CASE WHEN partner= ''',
partner,
''' THEN nb_postvariation END) AS ',
CONCAT(partner,'nb_postvariation')
)
)INTO @sql
FROM
(SELECT P.partner
, DATE_FORMAT(P.dateupdated, "%Y-%m-%d") AS pivot_date
, COUNT(DISTINCT P.postid) AS nb_post
, COUNT(DISTINCT P.postvariationid) AS nb_postvariation
, COUNT(DISTINCT P.postvariationid) / COUNT(DISTINCT P.postid) AS avg_postvariation
FROM posts P
GROUP BY P.partner, DATE_FORMAT(P.dateupdated, "%Y-%m-%d")
ORDER BY pivot_date ASC)x;
SET @sql=CONCAT('SELECT pivot_date, ',@sql,' FROM
(SELECT P.partner
, DATE_FORMAT(P.dateupdated, "%Y-%m-%d") AS pivot_date
, COUNT(DISTINCT P.postid) AS nb_post
, COUNT(DISTINCT P.postvariationid) AS nb_postvariation
, COUNT(DISTINCT P.postvariationid) / COUNT(DISTINCT P.postid) AS avg_postvariation
FROM posts P
GROUP BY P.partner, DATE_FORMAT(P.dateupdated, "%Y-%m-%d")
ORDER BY pivot_date ASC)x
GROUP BY pivot_date');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
没有动态部分，查询将看起来像
MAX(CASE WHEN partner= ... THEN ) as每个伙伴。我们使用动态sql，因此我们不需要为每个客户端使用新的大小写。这是结果
FIDDLE
然后，我们只需将此@sql变量以及所有这些情况合并到查询的主要语料库中，即可得到最终结果。
FINAL query which gives the desired results
如果您复制上面的结果并直接运行它，那么您将获得所需的结果，所有这些mambo-jambo都会构建此查询。
至于插入，只需创建一个具有与查询结果匹配的字段的新表，然后简单地执行INSERT ..INTO SELECT。不确定为什么2个值为null，这可能是sqlfiddle问题。
FIDDLE
或者更简单地获取查询并执行CREATE TABLE ..作为SELECT ..保留列名
CREATE TABLE SELECT
FIDDLE