使用HiveQL高效查询另一列各值关联的最短字符串值

根据你的需求，我们需要对门店名称和订单ID进行分组，然后在每个分组里筛选出长度最短的用户ID。如果存在多个长度相同的最短用户ID，我们可以额外指定取字典序最小的那个，避免结果出现歧义。

方法一：窗口函数实现（更简洁直观） #

窗口函数可以直接在原数据上完成分组排序，一步定位到目标记录：

SELECT store_name, order_id, user_id
FROM (
    SELECT 
        store_name, 
        order_id, 
        user_id,
        -- 按门店+订单分组，先按用户ID长度升序，再按ID本身字典序升序排名
        ROW_NUMBER() OVER (
            PARTITION BY store_name, order_id 
            ORDER BY LENGTH(user_id) ASC, user_id ASC
        ) AS rank_num
    FROM your_table
) ranked_data
WHERE rank_num = 1;

方法二：聚合函数+子查询实现 #

如果更习惯用分组聚合的思路，可以先计算每个分组内用户ID的最小长度，再关联原表获取对应ID：

SELECT t1.store_name, t1.order_id, MIN(t1.user_id) AS shortest_user_id
FROM your_table t1
JOIN (
    -- 先算出每个门店+订单下用户ID的最小长度
    SELECT store_name, order_id, MIN(LENGTH(user_id)) AS min_id_length
    FROM your_table
    GROUP BY store_name, order_id
) t2 
ON t1.store_name = t2.store_name 
AND t1.order_id = t2.order_id 
AND LENGTH(t1.user_id) = t2.min_id_length
GROUP BY t1.store_name, t1.order_id;

关键细节说明 #

• PARTITION BY store_name, order_id：确保我们只在同一个门店的同一个订单范围内计算，符合你提到的「订单ID在单个门店内唯一」的规则。
• ORDER BY LENGTH(user_id) ASC：优先按用户ID的字符串长度排序，保证最短的ID排在最前面。
• 额外排序条件user_id ASC：如果有多个长度相同的ID，取字典序最小的那个，避免返回随机结果。
• 若你的用户ID是数值类型，需要把LENGTH(user_id)改成LENGTH(CAST(user_id AS STRING))，因为数值类型无法直接用LENGTH函数计算长度。

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