SQL，python，knime将数据混合的文字数字拆出来，合并计算（学习笔记）

将下面将数据混合的文字数字拆出来，合并计算
在这里插入图片描述

一、SQL解决：

---创建表插入数据
CREATE TABLE original_data (id INT AUTO_INCREMENT PRIMARY KEY,city VARCHAR(255),value DECIMAL(10, 1)
);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; -- 按城市名称排序结果

在这里插入图片描述

二、python解决：

1、确保你已经安装了Python和pip。打开命令行或终端，然后输入以下命令：

pip install pymysql

2、建立test.py,里面内容如下：

import pymysql
import pandas as pd# Connect to the MySQL database
connection = pymysql.connect(host='127.0.0.1',user='root',password='root',database='test'
)# 读取original_data表数据
query = 'SELECT * FROM original_data'
df = pd.read_sql(query, connection)
print("################展示数据库数据########################")
print(df)
# 关闭数据库连接
connection.close()# 初始化一个空的数据框来存储处理后的数据
result = pd.DataFrame(columns=['city', 'value'])# 处理每一行数据
for index, row in df.iterrows():if pd.isnull(row['value']):# 如果 value 列为空，用 str.extract 提取城市和对应的值parts = row['city'].split(', ')for part in parts:# 使用正则表达式提取城市和对应的值import rematch = re.match(r'(\D+)([0-9.]+)', part)if match:city, value = match.groups()result = result.append({'city': city.strip(), 'value': float(value)}, ignore_index=True)else:# 如果 value 列不为空，直接使用result = result.append({'city': row['city'], 'value': row['value']}, ignore_index=True)# 按城市分组并求和
result = result.groupby('city').sum().reset_index()# 重命名列
result.columns = ['city', 'total_value']
print("################最终显示结果########################")
print(result)