DB2查询：查找gold_class为当日首条记录的day_of_year

Got it, let's work through this problem. You need to identify which day_of_year values have gold_class as the first record in their group, right? Here's how to approach it in DB2:

Core Idea #

Since each day_of_year has records for almost all classes (in random order), we can use a window function to assign a row number to each record within its day_of_year group. Then we just filter for rows where the row number is 1 AND the class is gold_class.

Query Without Explicit Sort Field (Using Default Order) #

If you don't have a specific field to define "first" (like a sale timestamp or insertion time), you can use a placeholder in the ORDER BY clause to get the default record order for each group. Note that this relies on DB2's default row order, which might not be 100% consistent if the table is modified, but it works for your stated scenario:

SELECT day_of_year
FROM (
    SELECT 
        day_of_year,
        class,
        -- Assign row number per day_of_year, using default order
        ROW_NUMBER() OVER (PARTITION BY day_of_year ORDER BY 1) AS row_num
    FROM your_table_name  -- Replace with your actual table name
) AS ranked_records
WHERE class = 'gold_class' AND row_num = 1;

More Reliable Query (With Explicit Sort Field) #

If your table has a field that can definitively order records (like a timestamp of when the sale was recorded), use that instead of ORDER BY 1—this makes the result consistent even if the table's physical order changes:

SELECT day_of_year
FROM (
    SELECT 
        day_of_year,
        class,
        -- Use a meaningful sort field (e.g., sale timestamp) to determine "first"
        ROW_NUMBER() OVER (PARTITION BY day_of_year ORDER BY sale_timestamp ASC) AS row_num
    FROM your_table_name  -- Replace with your actual table name
) AS ranked_records
WHERE class = 'gold_class' AND row_num = 1;

Notes #

• Replace your_table_name with the actual name of your movie ticket sales table.
• If gold_class is case-sensitive in your DB2 setup, make sure the string matches exactly (e.g., 'Gold_Class' instead of 'gold_class' if that's how it's stored).

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