在Hive中提取字符串内指定代码对应值的最大值

原始待处理字符串：

D.07.01.01=1;B=NUL;A=NUL;D.07.01.01=NUL;R=1;D.07.01.01=2;D.07.01.08=2;D.07.01.07=1;D.07.01.06=2;D.07.01.05=1;D.07.01.04=2;D.07.01.03=1;F=YES;D.07.01.02=1

需求：提取键D.07.01.01对应的所有值（1、NUL、2），计算其中的整数最大值，期望结果为2。

问题分析 #

你之前写的SQL：

select split(regexp_replace('D.07.01.01=1;B=NUL;A=NUL;D.07.01.01=NUL;R=1;D.07.01.01=2;D.07.01.08=2;D.07.01.07=1;D.07.01.06=2;D.07.01.05=1;D.07.01.04=2;D.07.01.03=1;F=YES;D.07.01.02=1', '.*D\.07\.01\.01=(\d+).*', '$1'), ';')

仅返回["2"]，原因是：

• .*D\.07\.01\.01=(\d+).*会匹配整个字符串，只捕获最后一个符合\d+的数字值，忽略了前面的匹配项和非数字的NUL。
• 这种全局替换的方式无法保留所有匹配的键值对。

正确解法 #

通过拆分键值对、过滤目标键、处理非数值的方式来实现：

WITH raw_data AS (
    SELECT 'D.07.01.01=1;B=NUL;A=NUL;D.07.01.01=NUL;R=1;D.07.01.01=2;D.07.01.08=2;D.07.01.07=1;D.07.01.06=2;D.07.01.05=1;D.07.01.04=2;D.07.01.03=1;F=YES;D.07.01.02=1' AS str
),
kv_pairs AS (
    SELECT explode(split(str, ';')) AS kv FROM raw_data
),
filtered_kv AS (
    SELECT 
        split(kv, '=')[0] AS key,
        split(kv, '=')[1] AS value
    FROM kv_pairs
    WHERE split(kv, '=')[0] = 'D.07.01.01'
)
SELECT max(CASE WHEN value REGEXP '^\\d+$' THEN cast(value AS int) ELSE NULL END) AS max_value
FROM filtered_kv;

步骤说明： #

1. raw_data CTE：存储原始字符串。
2. kv_pairs CTE：用split(str, ';')拆分字符串为单个键值对，再用explode将数组转为行数据。
3. filtered_kv CTE：拆分每个键值对为key和value，过滤出目标键D.07.01.01的所有行。
4. 最终查询：用CASE语句将非数字值（如NUL）转为NULL，再通过max函数计算整数最大值。

执行后将返回结果2，符合需求。

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