如何在Hive SQL中筛选存在0分评分的唯一品牌？

Got it, let's work through this problem step by step. You want to get unique brands that have at least one record where RATING = 0—so A and B in your example, since C has no 0 ratings. Your initial SELECT DISTINCT BRAND is a start, but we need to add logic to filter only brands with those 0-rated records. Here are three solid approaches for Hive SQL:

方法1：GROUP BY + HAVING（最简洁高效） #

This is probably the cleanest way. We group the table by brand, then use HAVING to filter groups where there's at least one RATING=0 entry:

SELECT BRAND
FROM MY_TABLE
GROUP BY BRAND
HAVING SUM(CASE WHEN RATING = 0 THEN 1 ELSE 0 END) > 0;

解释： #

• GROUP BY BRAND clusters all records by their brand.
• The SUM(CASE...) counts how many times RATING=0 occurs per brand. If the count is greater than 0, the brand stays in the result.

方法2：EXISTS子查询（贴近你最初的思路） #

If you prefer using a subquery to check existence (like the logic you mentioned), this works perfectly. We check for each brand if there's a matching record with RATING=0:

SELECT DISTINCT BRAND
FROM MY_TABLE t1
WHERE EXISTS (
    SELECT 1
    FROM MY_TABLE t2
    WHERE t2.BRAND = t1.BRAND
      AND t2.RATING = 0
);

解释： #

• t1 and t2 are aliases for the same table (self-join).
• For every unique brand in t1, the subquery checks if t2 has any record with the same brand and RATING=0. If yes, the brand is kept.

方法3：IN子查询（直观易懂） #

First, we get all brands that have at least one RATING=0 record, then select the unique brands from that list:

SELECT DISTINCT BRAND
FROM MY_TABLE
WHERE BRAND IN (
    SELECT BRAND
    FROM MY_TABLE
    WHERE RATING = 0
);

解释： #

• The inner subquery pulls every brand that appears alongside RATING=0.
• The outer query then takes distinct brands from the main table that are in that inner list.

All three methods will give you the expected result: A and B. Depending on your table size, GROUP BY + HAVING might be the most efficient since it scans the table once, while the subquery methods might do two scans—but for most cases, all will work smoothly.

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