python - MySql/Python 中的混合列求和

Question

我有一个 mysql 表，其中有一些整数字段和一些文本字段。在文本字段内，我有多个用逗号分隔的数字。

我需要的是通过对由两个特定键分组的所有列求和来返回结果。求和时，我想对所有整数字段(这很简单)以及所有文本字段进行求和，其中每个逗号分隔值应分别求和。

如果没有例子，我无法更清楚地解释它，这就是我想要的

Table:
Key1    |Key 2  |Col1   |Col2   |Col3   |
A       |X      |2      |12     |2,4,6  |
A       |X      |4      |23     |3,6,9  |
A       |Y      |6      |54     |1,3,5  |
A       |Y      |8      |27     |4,8,12 |
B       |X      |1      |12     |5,10,5 |
B       |X      |3      |31     |6,3,1  |
B       |Y      |5      |23     |1,0,0  |
B       |Y      |7      |91     |2,5,6  |

Output I want:
Key1    |Key 2  |Col1   |Col2   |Col3   |
A       |X      |6      |35     |5,10,15|
A       |Y      |14     |81     |5,11,17|
B       |X      |4      |43     |11,13,6|
B       |Y      |12     |114    |3,5,6  |

我正在使用 mysql 和 python 将输出存储到新表中。对于整数字段，我可以轻松使用 mysql SUM() 函数。对于 Col3，我使用 python map(add,a,b) 函数单独添加值。

问题是，我使用的代码看起来很难看，而且我认为当我处理大量数据时它的效率会很低。有什么建议可以有效地做到这一点吗？

我当前的代码:

cursor = cnx.cursor()
sqlout = "INSERT INTO tb2 (`key1`,`key2`,`col1`,`col2`) SELECT `key1`,`key2`,SUM(`col1`),SUM(`col2`) FROM tb1 GROUP BY `key1`,`key2`"
cursor.execute(sqlout)  // TESTED
cnx.commit()
sqlint = "SELECT `key1`,`key2`,`col3` FROM tb1"
cursor.execute(sqlint)
results = cursor.fetchall()
myres = {}
for row in results:
    myres[row[0],row[1]]= (map(add,myres[row[0],row[1]],row[2])
//USE MYSQL UPDATE COMMAND TO UPDATE tb2 from myres variable // NOT TESTED
cursor.close()
cnx.close()

Answer 1

你根本不需要Python，你可以用普通的MySQL 来完成。首先，定义一些助手:

create function column1(x text) returns integer deterministic
    return substring_index(x,',',1);

create function column2(x text) returns integer deterministic
    return substring_index(substring_index(x,',',-2),',',1);

create function column3(x text) returns integer deterministic
    return substring_index(substring_index(x,',',-1),',',1);

然后，这是查询:

select 
    Key1, Key2, 
    sum(Col1) as Col1,
    sum(Col2) as Col2, 
    concat_ws(',', 
        cast(sum(column1(Col3)) as char(50)),
        cast(sum(column2(Col3)) as char(50)),
        cast(sum(column3(Col3)) as char(50))
    ) as Col3
from YourTable
group by Key1, Key2;