SQL需求：计算商品过去40个季度的平均销售额

Let's break down how to solve this problem efficiently, while accounting for the dynamic date range rule (adjusting if 2019Q4 data is available).

Core Approach #

Instead of hardcoding date ranges, we can use window functions to dynamically select the most recent 40 quarters for each article. This automatically handles the rule where if 2019Q4 exists, we shift the range forward—no manual date checks needed.

Step-by-Step SQL Implementation #

1. Rank Quarters by Recency: First, we assign a rank to each sales record per article, with the newest quarter ranked #1.
2. Filter Top 40 Quarters: We only keep records ranked 1 to 40 (the most recent 40 quarters).
3. Calculate Average: Finally, we group by article and compute the average of the filtered sales amounts.

Here's the complete SQL query:

SELECT
    AVG(amountofsale) AS Avg,
    article
FROM (
    SELECT
        article,
        amountofsale,
        -- Assign rank: newest quarter = 1, next = 2, etc.
        ROW_NUMBER() OVER (
            PARTITION BY article 
            ORDER BY year DESC, quarter DESC
        ) AS qtr_rank
    FROM table1
) ranked_sales
-- Keep only the most recent 40 quarters per article
WHERE qtr_rank <= 40
GROUP BY article;

Why This Works #

• Dynamic Range Handling: The ROW_NUMBER() window function automatically adjusts to the latest available quarter for each article. If 2019Q4 exists for an article, it becomes rank #1, and the 40th rank will be 2010Q1. If only up to 2019Q3 is available, the 40th rank will be 2009Q4—exactly matching your requirements.
• Flexibility: This query works even if some articles have missing quarters (though your sample data shows continuous quarters). If you need to enforce exactly 40 quarters (exclude articles with fewer), add HAVING COUNT(*) = 40 after GROUP BY article.

Example Output #

Using your sample data, the query will return exactly the expected result:

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