SQL/Hive如何解析并求和单行内的所有值

针对你存储为["5","12","13"]格式的字符串列，无需修改表结构（适配只读权限），可以利用数据库的JSON解析或字符串拆分能力实现求和，以下是主流数据库的具体方案：

MySQL 8.0+（推荐） #

利用原生JSON函数直接解析数组，避免字符串替换的潜在问题：

SELECT 
    SUM(CAST(json_val AS UNSIGNED)) AS total_sum
FROM 
    your_table,
    JSON_TABLE(
        your_column,
        "$[*]" COLUMNS (json_val VARCHAR(20) PATH "$")
    ) AS parsed;

原理：JSON_TABLE将JSON数组字符串拆分为单行记录的列，再转成数值类型求和。

MySQL 5.x（无JSON_TABLE） #

通过递归CTE拆分处理后的字符串：

WITH RECURSIVE split_nums AS (
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(your_column, '[', ''), '"', ''), ',', n), ',', -1) AS num_str,
        n
    FROM 
        your_table
    CROSS JOIN 
        (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) nums
    WHERE 
        n <= LENGTH(REPLACE(REPLACE(your_column, '[', ''), '"', '')) - LENGTH(REPLACE(REPLACE(REPLACE(your_column, '[', ''), '"', ''), ',', '')) + 1
)
SELECT SUM(CAST(num_str AS UNSIGNED)) AS total_sum FROM split_nums;

注意：nums子查询中的数字数量需大于等于数组的最大元素个数。

PostgreSQL #

借助JSON类型转换和数组拆分函数：

SELECT 
    SUM((json_array_elements_text(your_column::json))::integer) AS total_sum
FROM your_table;

原理：将字符串转为JSON类型，json_array_elements_text拆分出每个元素，再转成整数求和。

关键提示 #

• 确保列中字符串是标准JSON数组格式（双引号、逗号分隔无多余空格），否则可能导致解析失败；
• 所有查询均为只读操作，无需修改表或权限升级。

内容的提问来源于stack exchange，提问作者GlowFish