我不能完全肯定这个词的意思 group_1 以及 group_2 示例查询中的聚合。然而，问题的实质似乎是如何基于聚合函数的结果过滤结果集( count )而不是过滤单个输入行的值。apachehive通过使用sql HAVING 条款。
在下面的示例中，输入关系包含6行 id 设置为 1 和4排 id 设置为 2 . 查询包含子句 HAVING guest_reviews >= 5 . 因为 HAVING 子句中，结果集只包含 id 与 1 . 没有带的输出行 id 设置为 2 .

WITH table_name AS (
    SELECT 1 AS id, 1 AS guest_review_1, 1 AS guest_review_2 UNION ALL
    SELECT 1 AS id, 2 AS guest_review_1, 2 AS guest_review_2 UNION ALL
    SELECT 1 AS id, 3 AS guest_review_1, 3 AS guest_review_2 UNION ALL
    SELECT 1 AS id, 4 AS guest_review_1, 4 AS guest_review_2 UNION ALL
    SELECT 1 AS id, 5 AS guest_review_1, 5 AS guest_review_2 UNION ALL
    SELECT 1 AS id, 6 AS guest_review_1, 6 AS guest_review_2 UNION ALL
    SELECT 2 AS id, 1 AS guest_review_1, 1 AS guest_review_2 UNION ALL
    SELECT 2 AS id, 2 AS guest_review_1, 2 AS guest_review_2 UNION ALL
    SELECT 2 AS id, 3 AS guest_review_1, 3 AS guest_review_2 UNION ALL
    SELECT 2 AS id, 4 AS guest_review_1, 4 AS guest_review_2
)
SELECT
    id,
    count(DISTINCT guest_review_1) AS guest_reviews,
    count(DISTINCT(CASE WHEN guest_review_2 < 1 THEN guest_review_1 END)) AS group_1,
    count(DISTINCT(CASE WHEN guest_review_2 >= 2 AND guest_review_2 < 3 THEN guest_review_1 END)) as group_2
FROM table_name
WHERE guest_review_2 IS NOT NULL
GROUP BY id
HAVING guest_reviews >= 5
;