Hive中如何获取用户数Top10的标题并按标题排序？

Got it, let's break down why your original query isn't working as expected and how to fix it.

Why Your Current Query Fails #

When you run:

select title,count(userid) as users from combined_moviedata group by title order by users desc,title asc limit 10

You're telling the database to first sort all titles by users descending, then by title ascending only when two titles have the same users count. Since your top 10 titles likely have distinct users counts (or the ties don't affect the top 10 cutoff), the final result still ends up ordered by users first—so the title sort doesn't change the overall order of the top 10.

What you actually want is:

1. First get the top 10 titles with the highest users counts
2. Then sort only those 10 titles alphabetically by title

Solution 1: Use a Subquery #

Wrap your initial top-10 selection in a subquery, then sort the result set by title:

SELECT title, users
FROM (
    -- First get the top 10 titles by user count
    SELECT title, COUNT(userid) AS users
    FROM combined_moviedata
    GROUP BY title
    ORDER BY users DESC
    LIMIT 10
) AS top_10_titles
-- Now sort those 10 titles alphabetically
ORDER BY title ASC;

Solution 2: Use a CTE (For Modern Databases) #

If your database supports Common Table Expressions (like MySQL 8+, PostgreSQL, SQL Server), this is a cleaner approach:

WITH top_10_titles AS (
    SELECT title, COUNT(userid) AS users
    FROM combined_moviedata
    GROUP BY title
    ORDER BY users DESC
    LIMIT 10
)
SELECT title, users
FROM top_10_titles
ORDER BY title ASC;

Bonus: Handling Ties in User Counts #

If multiple titles have the same user count and you want to include all of them (not just truncate at 10), use RANK() instead of LIMIT:

WITH ranked_titles AS (
    SELECT 
        title, 
        COUNT(userid) AS users,
        RANK() OVER (ORDER BY COUNT(userid) DESC) AS user_rank
    FROM combined_moviedata
    GROUP BY title
)
SELECT title, users
FROM ranked_titles
WHERE user_rank <= 10
ORDER BY title ASC;

This will include all titles that are in the top 10 ranks (even if that means more than 10 results), then sort them alphabetically.

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