Hive表关联查询：找出评论数大于2条的用户并输出指定字段

Hey there! Let's work through this problem together. The goal is to pull users who've left more than 2 comments, and output their user ID, username, plus each of their comment descriptions.

Query Implementation #

Here's a straightforward Hive SQL query that achieves this:

SELECT u.userid, u.username, c.commentDescription
FROM user u
INNER JOIN (
    SELECT userid
    FROM comments
    GROUP BY userid
    HAVING COUNT(commentID) > 2
) qualified_users ON u.userid = qualified_users.userid
INNER JOIN comments c ON u.userid = c.userid;

How It Works #

Let's break down the logic step by step:

• Subquery for Qualified Users: The inner subquery groups comments by userid and filters out anyone with 2 or fewer comments using HAVING COUNT(commentID) > 2. This gives us only the users we care about.
• Join with User Table: We join this filtered list with the user table to fetch the corresponding username for each qualifying user ID.
• Join Back to Comments: Finally, we join with the comments table again to get every individual comment description from those users.

Alternative (More Readable) Version Using CTE #

If you prefer cleaner, more modular code, a Common Table Expression (CTE) makes the logic easier to follow:

WITH user_comment_metrics AS (
    SELECT userid, COUNT(commentID) AS total_comments
    FROM comments
    GROUP BY userid
    HAVING total_comments > 2
)
SELECT 
    u.userid,
    u.username,
    c.commentDescription
FROM user u
JOIN user_comment_metrics m ON u.userid = m.userid
JOIN comments c ON u.userid = c.userid;

Both versions will give you the exact output you need: the user's ID, their username, and each of their comments, but only for users who have more than 2 comments in total.

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