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

Question

我想要sum Counters列先在小时表中求和，然后在日表中求和

我怎么能得到这个预期的结果，我怎么能开始使用它?循环在存储过程我可以达到预期的结果，但它是复杂的，因为我的长度每个实体是不同的&对我来说，对每个实体求和并以相同的格式存储是乏味的。提前感谢

分钟表(这里我每分钟接收每个实体的新计数器值条目)

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)
... 
.

Answer 1

您的主要关注点应该放在修复数据模型上。怎么了?以下是一些内容:

• 在列中存储多个值是一个坏主意。
将数字存储为字符串是一个坏主意。
• 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;