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'之前的部分
    SELECT
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(city, ',', 1), '0.5', 1)) AS city, -- 去除可能的前后空白，并截取第一个城市名称
        0.5 AS value -- 硬编码分割后每个部分的值
    FROM original_data
    WHERE city LIKE '%,%' -- 只选择city字段包含逗号的记录
    UNION ALL
    -- 选择city字段，使用SUBSTRING_INDEX函数分割字符串，获取最后一个逗号后的值，并截取'0.5'之前的部分
    SELECT
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(city, ',', -1), '0.5', 1)) AS city, -- 去除可能的前后空白，并截取最后一个城市名称
        0.5 AS value
    FROM original_data
    WHERE city LIKE '%,%'
    UNION ALL
    -- 选择不包含逗号的city字段，即没有被分割的完整值
    SELECT
        city,
        value
    FROM original_data
    WHERE city NOT LIKE '%,%' -- 选择city字段不包含逗号的记录
),
-- 定义另一个名为filtered_values的CTE，用于过滤掉空或NULL的城市名称
filtered_values AS (
    SELECT
        city,
        value
    FROM
        split_values
    WHERE
        city IS NOT NULL AND city != '' -- 确保城市名称不为空
),
-- 定义第三个CTE，aggregated_values，用于对过滤后的城市名称进行分组并求和
aggregated_values AS (
    SELECT
        city,
        SUM(value) AS total_value -- 对每个城市的所有value值进行求和
    FROM
        filtered_values
    GROUP BY
        city
)
-- 最终选择语句，从aggregated_values CTE中选择城市和它们的总价值
SELECT
    city,
    total_value
FROM
    aggregated_values
ORDER BY
    city; -- 按城市名称排序结果

二、python解决：