如何使用SQL计算类字典格式value列中key的加权平均值

核心逻辑说明 #

你不需要真的把key重复对应次数再计算，等价公式为：加权平均值 = sum( key * 对应出现次数 ) / sum( 所有出现次数 )，和你给出的计算逻辑完全一致，性能更高。

SQL没有内置直接处理这种格式的函数，需要通过字符串拆分、字段提取后计算得到结果。

不同SQL方言的实现示例 #

1. Hive/Spark SQL 实现 #

用split拆分字符串，explode行转列，再分组聚合即可：

select 
    id,
    round(sum(key * cnt) / sum(cnt), 2) as value
from (
    select 
        id,
        -- 拆分得到key
        cast(split(item, ':')[0] as int) as key,
        -- 拆分得到出现次数
        cast(split(item, ':')[1] as int) as cnt
    from your_table_name
    -- 按逗号拆分value列，行转列
    lateral view explode(split(value, ',')) t as item
) tmp
-- 过滤掉次数为0的项，减少无效计算
where cnt > 0
group by id, value;

2. MySQL 8.0+ 实现 #

用递归CTE做字符串拆分后计算：

with recursive split_items as (
    select 
        id,
        value,
        1 as idx,
        substring_index(substring_index(value, ',', 1), ',', -1) as item
    from your_table_name
    union all
    select 
        id,
        value,
        idx + 1 as idx,
        substring_index(substring_index(value, ',', idx + 1), ',', -1) as item
    from split_items
    where idx < length(value) - length(replace(value, ',', '')) + 1
)
select 
    id,
    round(sum(cast(substring_index(item, ':', 1) as unsigned) * cast(substring_index(item, ':', -1) as unsigned)) 
    / sum(cast(substring_index(item, ':', -1) as unsigned)), 2) as value
from split_items
where cast(substring_index(item, ':', -1) as unsigned) > 0
group by id, value;

3. PostgreSQL 实现 #

用string_to_array和unnest做拆分：

select 
    id,
    round(sum((split_part(item, ':', 1)::numeric) * (split_part(item, ':', 2)::numeric)) 
    / sum(split_part(item, ':', 2)::numeric), 2) as value
from your_table_name,
unnest(string_to_array(value, ',')) as t(item)
where split_part(item, ':', 2)::int > 0
group by id, value;

结果验证 #

以上代码执行后会自动保留两位小数，输出和预期完全一致：

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