首页 文章

需要帮助来计算超大MySQL表中所有列的失败百分比

提问于
浏览 次
0

我很难计算出我庞大的MySQL表中每列的失败百分比 . 以下是小表的外观示例：

假设TABLE1有5列100行，
CREATE TABLE IF NOT EXIST TABLE1 (id VARCHAR(255) NOT NULL, col1 DOUBLE NOT NULL, col2 DOUBLE NOT NULL, col3 NOT DOUBLE NULL, col4 NOT DOUBLE NULL);

从“col1”到“col4”的每一列都有自己的上限和下限，我需要找到“col1”到“col4”失败的百分比 . 以下是我如何运行计算的示例 .

计算总行数并按列分组"id"
SELECT id, COUNT(*) FROM TABLE1 GROUP BY id;

计算col1，col2，col3，col4满足所有限制的行总数，并按列分组"id"
SELECT id, COUNT(*) FROM TABLE1 WHERE (col1 BETWEEN 0 AND 10) AND (col2 BETWEEN 10 AND 20) AND (col3 BETWEEN 20 AND 30) AND (col3 BETWEEN 30 AND 40) GROUP BY id;

计算不符合col1限制的总行数
SELECT id, COUNT(col1) FROM TABLE1 WHERE (col1 NOT BETWEEN 0 AND 10) GROUP BY id;

计算满足col1限制但不符合col2限制的行总数，按"id"分组
SELECT id, COUNT(col2) FROM TABLE1 WHERE (col1 BETWEEN 0 AND 10) AND (col2 NOT BETWEEN 10 AND 20) GROUP BY id;

计算满足col1，col2限制但不符合col3限制的行总数，分组"id"
SELECT id, COUNT(col3) FROM TABLE1 WHERE (col1 BETWEEN 0 AND 10) AND (col2 BETWEEN 10 AND 20) AND (col3 NOT BETWEEN 20 AND 30) GROUP BY id;

计算满足col1，col2，col3限制但不符合col4限制的行总数，按"id"分组
SELECT id, COUNT(col4) FROM TABLE1 WHERE (col1 BETWEEN 0 AND 10) AND (col2 BETWEEN 10 AND 20) AND (col3 BETWEEN 20 AND 30) AND (col3 NOT BETWEEN 30 AND 40) GROUP BY id;

我编写了一个R脚本来执行上述5个查询，并将结果合并到一个数据框下 . 以下是R处理后的输出示例：
id,total_no_rows,yield,col1,col2,col3,col4
CATEGORY1,25,80%,2%,8%,4%,6%,0%
CATEGORY2,25,70%,6%,14%,2%,6%,2%
CATEGORY3,25,90%,5%,0%,5%,0%,0%
CATEGORY4,25,65%,20%,2.5%,2.5%,5%,5%

现在使用这种方法我可以很快得到小表的结果 . 但是，如果表变得非常大，比如1000列和100万行，则完成计算的时间约为2小时，这是非常长的 .

无论如何我可以加快计算速度吗？

  • 我试过索引，但显然MySQL无法索引1000列 .

  • 尝试同时查询（一次10个查询）但没有太大改进 . （我顺便使用InnoDB）

  • 我已经阅读了一些帖子，其中用户建议将表拆分为较小的块以加快查询执行速度 . 但是，我的原始数据管理不善（长篇故事），所有数据都转储到一个大文本文件中 . 因此，将原始数据划分为较小的块将是一个挑战 .

如果您有任何其他方法可以解决此类问题，请告诉我 .

编辑：
看起来Mani的提议确实节省了大量时间来获得结果 . 但是，对于非常大的表（数千列和数百万行），完成查询的时间仍然大约需要10分钟 . 有没有办法进一步改善查询时间？

mysql sql sql-server r

1 回答

  • 0

    您可以使用案例并在单个选择匹配中查找所有可能的场景 . 它会减少你的时间 .

    例

    select id, count(*), 
sum(case when col1 between 0 and 10 then 1 else 0 end) col1_yes,
sum(case when (col1 not between 0 and 10) and (col2 between 0 and 10) then 1 
else 0 end) col1no_col2yes 
from table 
group by id;
    回复于

相关问题