关于在Chinook数据库中创建BestSeller视图的SQLite查询技术咨询

Let's break down how to build your BestSeller view step by step. The goal is to pull the top-selling album (track sales count) for each genre, with the required columns: Genre, Album, Artist, and Sales.

Method 1: Using Window Functions (Recommended for SQLite 3.25+) #

SQLite 3.25 and above supports window functions like ROW_NUMBER(), which makes this task clean and efficient. Here's the full query to create the view:

CREATE VIEW BestSeller AS
WITH AlbumSales AS (
    SELECT
        g.Name AS Genre,
        a.Title AS Album,
        ar.Name AS Artist,
        SUM(ii.Quantity) AS Sales,
        -- Rank albums within each genre by sales (highest first)
        ROW_NUMBER() OVER (PARTITION BY g.Name ORDER BY SUM(ii.Quantity) DESC) AS Rank
    FROM invoice_items ii
    -- Join tables to connect sales data to tracks, albums, artists, and genres
    JOIN tracks t ON ii.TrackId = t.TrackId
    JOIN albums a ON t.AlbumId = a.AlbumId
    JOIN artists ar ON a.ArtistId = ar.ArtistId
    JOIN genres g ON t.GenreId = g.GenreId
    -- Group to calculate total sales per unique album/genre/artist combo
    GROUP BY g.Name, a.AlbumId, a.Title, ar.Name
)
-- Select only the top-ranked album per genre
SELECT Genre, Album, Artist, Sales
FROM AlbumSales
WHERE Rank = 1
ORDER BY Genre;

How this works: #

1. CTE AlbumSales: This common table expression first calculates total sales for each album by summing Quantity from invoice_items. It joins all necessary tables to pull in genre, album, and artist names.
2. Window Function: ROW_NUMBER() assigns a unique rank to each album within its genre, sorted by total sales descending. The top-selling album in each genre gets a rank of 1.
3. Final Filter: We only keep rows where the rank is 1 to get the top album per genre.

Method 2: Compatible with Older SQLite Versions #

If you're using an older SQLite version that doesn't support window functions, use this subquery-based approach:

CREATE VIEW BestSeller AS
SELECT
    g.Name AS Genre,
    a.Title AS Album,
    ar.Name AS Artist,
    SUM(ii.Quantity) AS Sales
FROM invoice_items ii
JOIN tracks t ON ii.TrackId = t.TrackId
JOIN albums a ON t.AlbumId = a.AlbumId
JOIN artists ar ON a.ArtistId = ar.ArtistId
JOIN genres g ON t.GenreId = g.GenreId
GROUP BY g.Name, a.AlbumId, a.Title, ar.Name
-- Keep only albums that match the highest sales total in their genre
HAVING SUM(ii.Quantity) = (
    SELECT MAX(AlbumTotal)
    FROM (
        SELECT SUM(ii2.Quantity) AS AlbumTotal
        FROM invoice_items ii2
        JOIN tracks t2 ON ii2.TrackId = t2.TrackId
        JOIN genres g2 ON t2.GenreId = g2.GenreId
        WHERE g2.Name = g.Name
        GROUP BY t2.AlbumId
    ) AS GenreMaxSales
)
ORDER BY Genre;

How this works: #

1. Main Query: Calculates total sales per album just like the first method.
2. HAVING Clause: Subqueries find the maximum sales total for any album in the current genre, then filters the main query to only keep albums that match that maximum value.

Handling Ties #

If you want to include all albums that tie for top sales in a genre (instead of just picking one), replace ROW_NUMBER() in Method 1 with RANK() or DENSE_RANK(). For example:

RANK() OVER (PARTITION BY g.Name ORDER BY SUM(ii.Quantity) DESC) AS Rank

This assigns the same rank to albums with identical sales totals, so all ties will be included when you filter for Rank = 1.

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