如何用SQL按类别统计一天中浏览量最高的时段？

Got it, let's break this down for you. The issue with your original query is that SQL doesn't let you nest aggregate functions like MAX(COUNT(*)) directly—here's why and how to fix it:

When you use GROUP BY, COUNT(*) calculates the row count (viewership) for each grouped combination of game and hour. But MAX() needs to operate on those precomputed counts, not be stacked inside the same SELECT clause. Here are two solid solutions tailored to your Twitch dataset:

Method 1: Window Functions (Cleanest & Most Readable) #

Window functions like RANK() let you rank results within groups (per game) without messy nested subqueries. This works with SQLite (which Codecademy uses for this dataset) and most modern SQL databases:

WITH hourly_view_counts AS (
  -- First, calculate view count per game per hour
  SELECT 
    game,
    strftime('%H', time) AS hour_of_day,
    COUNT(*) AS total_views
  FROM stream
  GROUP BY game, hour_of_day
)
-- Rank hours by view count for each game and pick the top one(s)
SELECT game, hour_of_day, total_views
FROM (
  SELECT 
    *,
    RANK() OVER (PARTITION BY game ORDER BY total_views DESC) AS view_rank
  FROM hourly_view_counts
) ranked_views
WHERE view_rank = 1;

Quick breakdown: #

1. The WITH clause creates a temporary table (hourly_view_counts) that counts how many views each game gets every hour.
2. The inner query uses RANK() to assign a rank to each hour for a game—rank 1 is the peak viewership hour. If multiple hours tie for first place, RANK() will keep all of them (use ROW_NUMBER() instead if you only want one result per game, even with ties).
3. The outer query filters to only keep rows with rank 1, giving you the highest-view hours per game.

Method 2: Nested Subqueries (For Older SQL Dialects) #

If you're working with a system that doesn't support window functions, use nested subqueries to first calculate hourly counts, find the maximum per game, then match back to the corresponding hours:

-- Get hourly view counts per game
SELECT h.game, h.hour_of_day, h.total_views
FROM (
  SELECT 
    game,
    strftime('%H', time) AS hour_of_day,
    COUNT(*) AS total_views
  FROM stream
  GROUP BY game, hour_of_day
) h
-- Join with a subquery that finds the max view count per game
INNER JOIN (
  SELECT game, MAX(total_views) AS max_views
  FROM (
    SELECT 
      game,
      COUNT(*) AS total_views
    FROM stream
    GROUP BY game, strftime('%H', time)
  ) temp
  GROUP BY game
) m ON h.game = m.game AND h.total_views = m.max_views;

Quick breakdown: #

1. The innermost subquery calculates total views per game per hour.
2. The middle subquery finds the highest view count value for each game.
3. The outer query joins these two results to get the hour(s) where the view count matches the maximum for each game.

Either approach will solve your problem—window functions are generally preferred for their simplicity if your database supports them.

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