MySQL中带有许多动态列的数据透视表

Question

MySQL中带有许多动态列的数据透视表

3

我在MySQL中制作PIVOT表格遇到了问题。我有以下表格。(这只是一个缩小的演示表格。真实的表格有4000个股票，例如10个交易日期和20个测量值)

     CREATE TABLE `levermann` (
       `RecNum` bigint(20) NOT NULL AUTO_INCREMENT,
       `Tradedate` date DEFAULT NULL,
       `Stock_Short` varchar(50) DEFAULT NULL,
       `Country` varchar(2) DEFAULT NULL,
       `LScore2` int(11) DEFAULT NULL,
       `MarketCAPUSD` bigint(20) DEFAULT NULL,
       PRIMARY KEY (`RecNum`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

     -- ----------------------------
     -- Records of levermann
     -- ----------------------------
     INSERT INTO `levermann` VALUES ('8099', '2018-05-23', 'ANDR.VI', 'VI', '-9', '5109518494');
     INSERT INTO `levermann` VALUES ('8100', '2018-05-23', 'BWO.VI', 'VI', '-7', '4241189324');
     INSERT INTO `levermann` VALUES ('8101', '2018-05-23', 'CAI.VI', 'VI', '-7', '3222135865');
     INSERT INTO `levermann` VALUES ('8102', '2018-05-09', 'CWI.VI', 'VI', '-8', null);
     INSERT INTO `levermann` VALUES ('8103', '2018-05-23', 'EBS.VI', 'VI', '-7', '18317742129');
     INSERT INTO `levermann` VALUES ('8104', '2018-05-23', 'FLU.VI', 'VI', '-8', '3176359049');
     INSERT INTO `levermann` VALUES ('8105', '2018-05-23', 'IIA.VI', 'VI', '-8', '2767477473');
     INSERT INTO `levermann` VALUES ('8106', '2018-05-23', 'LNZ.VI', 'VI', '-9', '3027507195');

输出应该是一个表格，其中这8个STOCKCODE（例如ANDR.VI）中的每一个都应该是一列，具有可选择的测量值（例如LScore2），按交易日期（=行）分组。

我找到了这个MYSQL中的示例，但我并不完全理解它。另外我已经做了以下工作：

    SELECT
    tradedate, 
    GROUP_CONCAT(stock_short) as STOCKCODE
    FROM
    levermann
    GROUP BY
    Tradedate;

但是这里的股票代码在一个单元格中而不是标题中。下面是所需输出示例的图像。总列数约为4000（表中的最大列数不超过4096）。交易日期（=行）为2年共约350天/年。

显然，应该通过动态方式创建列，而不能通过AS语句硬编码。

有没有解决这个难题的方法？非常感谢。

更新：我想到了一个类似于动态运行的语句..

    SET @sql = NULL;

    SELECT GROUP_CONCAT(concat(LScore2,' AS `LScore_',Stock_Short,'`')) into 
    @sql from levermann;

    SET @sql = CONCAT('SELECT tradedate, ', @sql, '
              FROM levermann 
               GROUP BY tradedate');

     PREPARE stmt FROM @sql;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

但是我不知道如何创建这个字符串:

LScore AS LScore_ANDR.VI, LScore AS LScore_BWO.VI, ...

我很怀疑它是否可以在MySQL中完成。当然，我也可以制作一个php脚本。但我想学习如何在MySQL中完成。

更新2：我想我可以做到。我不太确定它是否正确，但它是动态创建的。在MySQL中。

- Walter Schrabmair

3个回答

0

如果StockCodes列表是固定的，那么我们是否可以简单地固定新表的列，然后过滤原始表中的数据以复制到新表中呢？

至少在Excel中手动操作时，这将是一个流程。

- AndiAna

谢谢你的建议，但最好能自动完成。我已经用PHP成功地实现了它。 - Walter Schrabmair

0

在SQL中，您无法编写任何查询，根据它开始读取数据时找到的数据值动态扩展列。所有列必须在准备查询时在查询的选择列表中固定--在读取任何数据之前。

这意味着您必须知道所有不同的值，并且需要生成一个选择列表，其中每个值都有一列，以便将其包含在透视表中。

您可以通过执行两个查询来解决此问题：一个用于获取所有不同的股票值：

SELECT DISTINCT Stock_Short FROM levermann

然后根据该结果，为每个股票值格式化一个非常长的SQL查询，每个表达式都有一个。从你知道你想要的列开始，这不是动态股票相关列之一：

SELECT Tradedate,

然后对于第一个查询结果中的每一行，添加一个类似的列：

MAX(CASE Stock_Short WHEN <value> THEN LScore2 END) AS <alias>,

最后将查询的结尾附加上：

FROM levermann
GROUP BY Tradedate;

我的建议是，既然您有4000个不同的股票价值，您应该直接从数据库中获取数据，并使用应用程序代码呈现一个透视显示。也就是说，循环遍历SQL查询结果，所有4000个行（而不是列），并将其排列到应用程序空间中的对象中。然后按照您想要的方式格式化该对象以进行显示。

- Bill Karwin

非常感谢，也许你是对的，我以为我可以像上面的更新一样创建一个动态 SQL 语句。 - Walter Schrabmair

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Walter Schrabmair · Accepted Answer

我想我明白了：

SET SESSION group_concat_max_len = @@max_allowed_packet;
SET @sql = NULL;

SELECT GROUP_CONCAT(concat('MAX(CASE Stock_Short WHEN \'',Stock_Short,'\'   THEN \'',LScore2,'\' END) AS `LScore_',Stock_Short,'`')) into @sql from levermanndemo where country = 'VI';

SET @sql = CONCAT('SELECT tradedate, ', @sql, '
              FROM levermanndemo  
               GROUP BY tradedate');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

输出：

更新：不幸的是，如果表格看起来像这样，它会造成麻烦。

我尝试了这个解决方案：

    SET SESSION group_concat_max_len = @@max_allowed_packet;
    SET @sql = NULL;

    SELECT  GROUP_CONCAT( DISTINCT concat('MAX(CASE WHEN p.Stock_Short = 
    \'',f.Stock_Short,'\' AND `Tradedate` = \'', f.tradedate,'\'  THEN 
    \'',f.LScore2,'\' ELSE NULL END) AS   `LScore_',f.Stock_Short,'`')) 
    into @sql from  levermanndemo f ;

    SET @sql = CONCAT('SELECT p.tradedate, ', @sql, ' FROM levermanndemo p 
    GROUP BY p.tradedate');
    #SELECT @sql; 

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

输出结果不是我们想要的，也是错误的。现在有两列相同的股票（BWO.VI 和 BWO.VI1），我想将这两列合并在一起。但是如何做到呢？