首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >用PHP和Mysql计算增长率

问用PHP和Mysql计算增长率
EN

Stack Overflow用户
提问于 2014-05-14 20:51:46
回答 1查看 2.5K关注 0票数 1

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

代码语言:javascript
复制
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的功能应该是这样的:

代码语言:javascript
复制
// 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 help的CAGR。我无法理解的是如何使用PHP将其转换为表格格式。

以下是使用strtotime获取不同时间段的月结束日期的最佳方法吗?

代码语言:javascript
复制
$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查询中。

代码语言:javascript
复制
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;
}
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-05-14 21:03:59

代码语言:javascript
复制
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

演示

更新:

您可以使用以下方法获得多个句点:

代码语言:javascript
复制
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

演示

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/23664884

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档