SQL，python，knime将数据混合的文字数字拆出来，合并计算实战
将下面将数据混合的文字数字拆出来，合并计算
一、SQL解决：
---创建表插入数据
CREATE TABLE original_data (id INT AUTO_INCREMENT PRIMARY KEY,city VARCHAR(255),value DECIMAL(10, 2)
);INSERT INTO original_data (city, value) VALUES
('上海0.5, 浙江0.5', NULL),
('北京0.5, 天津0.5', NULL),
('天津', 1.0),
('北京', 1.0),
('上海', 2.0),
('浙江', 3.0);
-- 使用WITH语句定义了一个名为split_values的公用表表达式（CTE）
WITH split_values AS (-- 选择city字段，使用SUBSTRING_INDEX函数分割字符串，获取第一个逗号前的值，并截取'0.5'之前的部分SELECTTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(city, ',', 1), '0.5', 1)) AS city, -- 去除可能的前后空白，并截取第一个城市名称0.5 AS value -- 硬编码分割后每个部分的值FROM original_dataWHERE city LIKE '%,%' -- 只选择city字段包含逗号的记录UNION ALL-- 选择city字段，使用SUBSTRING_INDEX函数分割字符串，获取最后一个逗号后的值，并截取'0.5'之前的部分SELECTTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(city, ',', -1), '0.5', 1)) AS city, -- 去除可能的前后空白，并截取最后一个城市名称0.5 AS valueFROM original_dataWHERE city LIKE '%,%'UNION ALL-- 选择不包含逗号的city字段，即没有被分割的完整值SELECTcity,valueFROM original_dataWHERE city NOT LIKE '%,%' -- 选择city字段不包含逗号的记录
),
-- 定义另一个名为filtered_values的CTE，用于过滤掉空或NULL的城市名称
filtered_values AS (SELECTcity,valueFROMsplit_valuesWHEREcity IS NOT NULL AND city != '' -- 确保城市名称不为空
),
-- 定义第三个CTE，aggregated_values，用于对过滤后的城市名称进行分组并求和
aggregated_values AS (SELECTcity,SUM(value) AS total_value -- 对每个城市的所有value值进行求和FROMfiltered_valuesGROUP BYcity
)
-- 最终选择语句，从aggregated_values CTE中选择城市和它们的总价值
SELECTcity,total_value
FROMaggregated_values
ORDER BYcity; -- 按城市名称排序结果