php - 使用 PHP 和 Mysql 计算增长率

Question

我正在尝试计算一些产品在不同时期的复合年增长率 (CAGR)。我有一个 MySQL 数据库，如下所示:

product, asof, sales
abc, 2013-06-30, 36000000
abc, 2013-12-31, 48000000
abc, 2014-01-31, 51000000
abc, 2014-02-28, 56000000
xyz, 2013-06-30, 26000000
xyz, 2013-12-31, 33000000
xyz, 2014-01-31, 33000000
xyz, 2014-02-28, 36000000

我发现 CAGR 的函数应该是这样的:

// EV = Ending Value
// BV = Beginning Value

function cagr($EV, $BV, $Period ) {
    $cagr = pow(($EV/$BV),(1/$Period)) - 1;
    return $cagr;
}

任何帮助或指导将不胜感激!谢谢!

编辑:

我的最终目标是创建一个表格，其中包含每种产品的 3 个月、1 年和 3 年复合年增长率。因此，更具体地说，我需要有关需要哪些 MySQL 查询以及如何构建 PHP 以填充表的帮助。

编辑 2:

因此，在@Barmar 的帮助下，我已经弄清楚了如何获得 CAGR。我想不通的是如何使用 PHP 将其转换为表格格式。

像下面这样使用 strtotime 获取不同时间段的月末日期的最佳方法是什么？

$lastdate = "2014-04-30";
$month3 = date('Y-m-d', strtotime('last day 3 months ago', strtotime($lastdate)));
$month6 = date('Y-m-d', strtotime('last day 6 months ago', strtotime($lastdate)));
$year1 = date('Y-m-d', strtotime('last day of months 1 year ago', strtotime($lastdate)));
$year3 = date('Y-m-d', strtotime('last day 36 months ago', strtotime($lastdate)));
$year5 = date('Y-m-d', strtotime('last day 60 months ago', strtotime($lastdate)));

我一直在使用一个函数将日期传递到@Barmar 在下面描述的 MySQL 查询中。

function queryproduct ($db, $end_date, $begin_date) {

    $q_product = "SELECT r.product, POWER(s1.sales/s2.sales, 1/(datediff(end_date, begin_date)/365)) - 1 AS cagr
                FROM (
                    SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin_date
                    FROM test
                                WHERE asof BETWEEN '$begin_date' AND '$end_date'
                    GROUP BY product
                    -- Prevent divide by 0 if we don't have a range
                    HAVING end_date != begin_date) AS r
                JOIN test AS s1 ON s1.product = r.product AND s1.asof = end_date
                JOIN test AS s2 ON s2.product = r.product AND s2.asof = begin_date;";

    $q_result = mysqli_query($db, $q_product);

    return $q_result;
}

Answer 1

SELECT r.product, POWER(s1.sales/s2.sales, 1/(datediff(end_date, begin_date)/365)) - 1 AS cagr
FROM (
    SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin_date
    FROM sales
    WHERE asof BETWEEN :range_start AND :range_end
    GROUP BY product
    -- Prevent divide by 0 if we don't have a range
    HAVING end_date != begin_date) AS r
JOIN sales AS s1 ON s1.product = r.product AND s1.asof = end_date
JOIN sales AS s2 ON s2.product = r.product AND s2.asof = begin_date

DEMO

更新:

您可以获得多个时期:

SELECT DISTINCT r1y.product product, end_date,
       begin1y, POWER(s_end.sales/s_1y.sales, 1/(datediff(r1y.end_date, begin1y)/365)) - 1 AS cagr_1y,
       begin3y, POWER(s_end.sales/s_3y.sales, 1/(datediff(r1y.end_date, begin3y)/365)) - 1 AS cagr_3y,
       begin5y, POWER(s_end.sales/s_5y.sales, 1/(datediff(r1y.end_date, begin5y)/365)) - 1 AS cagr_5y
FROM (
    SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin1y
    FROM sales
    WHERE asof > DATE_SUB(NOW(), INTERVAL 1 YEAR)
    GROUP BY product
    -- Prevent divide by 0 if we don't have a range
    HAVING end_date != begin1y) AS r1y
JOIN (
    SELECT product, MIN(asof) AS begin3y
    FROM sales
    WHERE asof > DATE_SUB(NOW(), INTERVAL 3 YEAR)
    GROUP BY product) AS r3y
ON r1y.product = r3y.product
JOIN (
    SELECT product, MIN(asof) AS begin5y
    FROM sales
    WHERE asof > DATE_SUB(NOW(), INTERVAL 5 YEAR)
    GROUP BY product) AS r5y
ON r1y.product = r5y.product
JOIN sales AS s_end ON s_end.product = r1y.product AND s_end.asof = r1y.end_date
JOIN sales AS s_1y ON s_1y.product = r1y.product AND s_1y.asof = begin1y
JOIN sales AS s_3y ON s_3y.product = r1y.product AND s_3y.asof = begin3y
JOIN sales AS s_5y ON s_5y.product = r1y.product AND s_5y.asof = begin5y

DEMO