Apache Hive中AVG函数计算门店VIP占比仅返回0或1的问题

问题场景 #

有两张业务表：

• 采购表purchases：包含客户ID、门店ID、采购日期、采购状态等字段
• VIP客户表vip_customers：包含客户ID、VIP标识is_vip（整数类型，0/1分别表示非VIP/VIP）

要统计2024年各门店的VIP客户占比，编写了如下Hive查询：

select 
 p.store_id,
 count(distinct p.customer_id) as count_customers,
 avg(v.is_vip) as percentage_vip
from schema.purchases p
left join (
    select 
     customer_id,
     is_vip
    from schema.vip_customers
    where data_year = 2024
) v on v.customer_id = p.customer_id
where purchase_date between '2024-1-1' and '2024-12-31'
and p.purchase_ok = 1
group by 1

执行后发现，percentage_vip字段仅返回0或1，但单独查看数据时明明存在VIP与非VIP混合的情况，显然不符合真实占比。

问题原因 #

is_vip是整数类型（INT），Hive的AVG()函数计算整数列的平均值时，会默认返回整数类型结果，相当于对真实小数结果做了取整操作，因此只能得到0或1这两个极端值。

解决方法 #

将is_vip转换为小数类型（比如DECIMAL）后再计算平均值，让AVG()返回精确的小数占比。修正后的查询如下：

select 
 p.store_id,
 count(distinct p.customer_id) as count_customers,
 avg(cast(v.is_vip as decimal(10,2))) as percentage_vip
from schema.purchases p
left join (
    select 
     customer_id,
     is_vip
    from schema.vip_customers
    where data_year = 2024
) v on v.customer_id = p.customer_id
where purchase_date between '2024-1-1' and '2024-12-31'
and p.purchase_ok = 1
group by 1

也可以在子查询中提前完成类型转换，效果一致：

select 
 p.store_id,
 count(distinct p.customer_id) as count_customers,
 avg(v.is_vip_decimal) as percentage_vip
from schema.purchases p
left join (
    select 
     customer_id,
     cast(is_vip as decimal(10,2)) as is_vip_decimal
    from schema.vip_customers
    where data_year = 2024
) v on v.customer_id = p.customer_id
where purchase_date between '2024-1-1' and '2024-12-31'
and p.purchase_ok = 1
group by 1

效果说明 #

转换为DECIMAL类型后，AVG()会计算出真实的占比数值（比如0.35对应35%），完全符合统计需求。

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