时间戳

首页 > 解决方案 > 对 CSV 列求和并将 CSV 结果保存在 MySQL 的 varchar 列中

mysql - 对 CSV 列求和并将 CSV 结果保存在 MySQL 的 varchar 列中

问题描述

我想对 Counters 列求和以在 Hour 表中求和，然后在 Day 表中求和

我怎样才能得到这个预期的结果，我怎么能从它开始？，我知道在存储过程中使用 substring_index 和循环我可以达到预期的结果，但它很复杂，因为我的每个实体的长度不同&每个实体的总和并将其存储在同样的格式对我来说很乏味。提前致谢

分钟表（在这里我每分钟收到每个实体的新计数器值条目）

Timestamp   Entity    Counters(varchar)
 13:01:00    ABC       1,0,0,1,3,0... N-length of counters 
 13:01:00    XYZ       12,0,0,0,0,4... K-length of counters
 13:02:00    ABC       1,0,2,1,0,0... N
 13:02:00    XYZ       1,4,2,0,0,0....K
 13:03:00    ABC       0,0,0,0,0,1... N
 13:03:00    XYZ       0,2,2,0,0,0....K
 . . 
 . 
 14:01:00
 14:02:00
 ..

预期结果

小时表（按实体和小时分组）

Timestamp   Entity    Counters 
 13:00:00    ABC       2,0,2,2,3,1...  N (sum of all counters within that Hour for respective entity)
 13:00:00    XYZ       13,6,4,0,0,4....K
 14:00:00    ABC       0,0,0,0,0,0... N (sum of 14th hour) 
 14:00:00    XYZ       0,0,0,0,0,0... K (sum of 14th hour)
 ... 
 .

标签： mysqlsql

解决方案

您的主要关注点应该是修复您的数据模型。怎么了？这里有一些事情：

  • 在列中存储多个值是一个坏主意。
  • 将数字存储为字符串是一个坏主意。
  • SQL 具有相当糟糕的字符串处理功能。

每个值都应该是另一个表中的单独行。虽然这需要更多空间，但它可能也更有效。在表中插入新行通常比更新行快得多。

假设您因为其他人的数据建模决策非常非常糟糕而被困在您的数据模型中，您可以用蛮力来解决这个问题。

首先确定行中值的最大数量。然后您可以单独提取每个并将它们添加在一起：

select t.*,
       ( (substring_index(counters, ',', 1) + 0) +
         (case when counters like '%,%' then sub string_index(substring_index(counters, ',', 2), ',', -1) + 0 else 0 end) +
         (case when counters like '%,%,%' then sub string_index(substring_index(counters, ',', 3), ',', -1) + 0 else 0 end) +
         (case when counters like '%,%,%,%' then sub string_index(substring_index(counters, ',', 4), ',', -1) + 0 else 0 end) +
         . . . 
        ) as total
from t;

推荐阅读