时间戳

首页 > 解决方案 > 根据 Hive 中的大小进行过滤

hive - 根据 Hive 中的大小进行过滤

问题描述

我想根据大小过滤配置单元中的记录，如何实现？请帮忙。

询问

SELECT 
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk,
    COLLECT_LIST(t3.measure_title) as all_measure_desc,
    size( COLLECT_LIST(t3.measure_title)) as ps
FROM qms_gic_lifecycle t1 
INNER JOIN dim_member t2 
on t1.member_id = t2.member_id
INNER JOIN dim_quality_measure t3 
on t1.quality_measure_id = t3.quality_measure_id
where t1.status <> 'closed'

GROUP BY  
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk;

标签： hivehiveql

解决方案

由于您拥有具有大小值的ps字段，因此将您的查询用作子查询，然后将 where 子句与ps字段一起使用以仅过滤匹配的行。

hive> Select * from (
SELECT 
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk,
    COLLECT_LIST(t3.measure_title) as all_measure_desc,
    size( COLLECT_LIST(t3.measure_title)) as ps
FROM qms_gic_lifecycle t1 
INNER JOIN dim_member t2 
on t1.member_id = t2.member_id
INNER JOIN dim_quality_measure t3 
on t1.quality_measure_id = t3.quality_measure_id
where t1.status <> 'closed'

GROUP BY  
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk) s 
    where s.ps >= <size_value>;

推荐阅读