如何从statuses表查询产品最近20个活跃日的首日及销量统计？

Hey there! Let's work through your problem with the statuses table. First, I'll make a few reasonable assumptions about your table structure since you didn't spell it out— I'm guessing it has at least product_id (unique identifier for each product), active_date (the date the status/sales record was logged), and sales (the daily sales amount for that product on that date).

Solution Breakdown #

We can use window functions to first rank each product's active dates from newest to oldest, then filter to keep only the most recent 20 active days. From there, it's straightforward to calculate the first day of that 20-day window and sum up the total sales.

Case 1: Each product has one record per active date

If your statuses table already has one row per product per active date (no duplicate dates for the same product), this query will work:

WITH ranked_active_dates AS (
    SELECT
        product_id,
        active_date,
        sales,
        -- Rank dates from newest (1) to oldest for each product
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY active_date DESC) AS date_rank
    FROM statuses
)
SELECT
    product_id,
    -- The earliest date in our top 20 is the first day of the recent 20 active days
    MIN(active_date) AS first_day_of_last_20_active_days,
    -- Sum all sales from the top 20 active days
    SUM(sales) AS total_sales_last_20_active_days
FROM ranked_active_dates
WHERE date_rank <= 20
GROUP BY product_id;

Case 2: Multiple records per product per active date

If your table might have multiple entries for the same product on the same date (e.g., split sales records), we first need to aggregate daily sales before ranking:

WITH daily_sales_aggregated AS (
    -- Combine all sales for a product on the same day
    SELECT
        product_id,
        active_date,
        SUM(sales) AS daily_total_sales
    FROM statuses
    GROUP BY product_id, active_date
),
ranked_active_dates AS (
    SELECT
        product_id,
        active_date,
        daily_total_sales,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY active_date DESC) AS date_rank
    FROM daily_sales_aggregated
)
SELECT
    product_id,
    MIN(active_date) AS first_day_of_last_20_active_days,
    SUM(daily_total_sales) AS total_sales_last_20_active_days
FROM ranked_active_dates
WHERE date_rank <= 20
GROUP BY product_id;

Key Notes #

• If a product has fewer than 20 active days total, the query will automatically use all its available active days (since the date_rank will only go up to the number of days it has, and date_rank <=20 will include all of them).
• The ROW_NUMBER() function ensures we get the most recent 20 distinct active days— even if there are gaps between dates (e.g., a product wasn't active on some days), we only count the days that have status records.

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