如何使用SQL计算基于年度同比增长率均值的CAGR？

Got it, let's break this down clearly. First, it's important to note that your definition of CAGR here is the arithmetic average of year-over-year (YoY) growth rates (not the standard compound annual growth rate that uses only start and end values). Let's build the SQL to match your exact formula.

Step-by-Step Breakdown #

Your formula requires two core YoY growth calculations:

1. 2016 vs 2015: (2016 Revenue / 2015 Revenue) - 1
2. 2017 vs 2016: (2017 Revenue / 2016 Revenue) - 1
   Then we take the average of these two values to get the final CAGR.

To implement this in SQL, we'll use the LAG() window function to fetch the previous year's revenue for each row, calculate individual YoY growth rates, then average them per advertiser.

Final SQL Query #

SELECT
    ADVERTISER,
    ROUND(AVG((REVENUE / prev_year_revenue) - 1), 2) AS CAGR
FROM (
    -- Subquery to pair each year's revenue with the prior year's revenue
    SELECT
        ADVERTISER,
        YR,
        REVENUE,
        LAG(REVENUE) OVER (PARTITION BY ADVERTISER ORDER BY YR) AS prev_year_revenue
    FROM your_table_name -- Replace this with your actual table name
) AS yearly_growth
-- Filter out the first year (no prior year data to calculate YoY growth)
WHERE prev_year_revenue IS NOT NULL
GROUP BY ADVERTISER;

How This Works #

1. Subquery with LAG(): The LAG(REVENUE) OVER (PARTITION BY ADVERTISER ORDER BY YR) clause grabs the revenue from the previous year for each advertiser, ordered chronologically. This gives us the denominator needed for each YoY growth calculation.
2. Filter Invalid Rows: The WHERE prev_year_revenue IS NOT NULL removes the 2015 row (since there's no 2014 data to compare it to, we can't calculate a YoY rate for it).
3. Calculate & Average Growth: The outer query computes each YoY growth rate, uses AVG() to take the arithmetic mean, and ROUND() to format the result to two decimal places (matching your expected output of 3.75).

Quick Math Verification #

Let's cross-check with your sample data:

• 2016 YoY growth: (48295 / 5560) - 1 ≈ 7.686
• 2017 YoY growth: (39920 / 48295) - 1 ≈ -0.173
• Average: (7.686 + (-0.173)) / 2 ≈ 3.75

This matches your desired output exactly.

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