从单列计算百分比增长

Question

我正在尝试计算单个列(COUNT列)的增长百分比，如下所示:

DATE        COUNT
2017-05-22  20
2017-05-29  42
2017-06-05  123
2017-06-12  21
2017-06-19  535
2017-06-26  3236
2017-07-03  32133

下面是期望输出百分比增长列考虑到中间COUNT列每周的变化:

DATE        COUNT   Perc Growth
2017-05-22  20  
2017-05-29  42      110.00%
2017-06-05  123     192.86%
2017-06-12  21      -82.93%
2017-06-19  535     2447.62%
2017-06-26  3236    504.86%
2017-07-03  32133   892.99%

当我执行以下SQL行时，这并没有给我正确的百分比增长值!任何建议吗?

SELECT DATE_TRUNC(WEEK, t.DATE_CREATED) as DATE_CREATED,
COUNT(t.COUNT)                   as COUNT_SIGN_UPS,
100 * ( COUNT(*) - LAG(COUNT(*), 1) OVER (order by DATE_CREATED / LAG(COUNT(*), 1) over (order by DATE_CREATED)) || '%') as percent_growth
FROM data t
GROUP BY 1
ORDER BY 1 ASC;

Answer 1

如何使用子查询:

select DATE_CREATED
,COUNT_SIGN_UPS 
, ((COUNT_SIGN_UPS /LAG(COUNT_SIGN_UPS,1,COUNT_SIGN_UPS) OVER (order by DATE_CREATED) - 1) * 100.0 as percent_growth
from (
SELECT DATE_TRUNC(WEEK, t.DATE_CREATED) as DATE_CREATED,
COUNT(t.COUNT)                   as COUNT_SIGN_UPS
FROM data t
GROUP BY 1
ORDER BY 1 ASC;
) t

Answer 2

CREATE TABLE growth (
id       int primary key auto_increment
, xdate    date
, cnt      int
, expected decimal(6,2)
);
INSERT INTO growth (xdate, cnt, expected) VALUES
('2017-05-22',  20   ,    NULL)
, ('2017-05-29',  42   ,  110.00)
, ('2017-06-05',  123  ,  192.86)
, ('2017-06-12',  21   ,  -82.93)
, ('2017-06-19',  535  , 2447.62)
, ('2017-06-26',  3236 ,  504.86)
, ('2017-07-03',  32133,  892.99)
;
SELECT t.*
, ROUND((100.0 * t.cnt) / LAG(t.cnt) OVER (ORDER BY id) - 100, 2) AS pct
FROM growth AS t
ORDER BY id
--------------
+----+------------+-------+----------+---------+
| id | xdate      | cnt   | expected | pct     |
+----+------------+-------+----------+---------+
|  1 | 2017-05-22 |    20 |     NULL |    NULL |
|  2 | 2017-05-29 |    42 |   110.00 |  110.00 |
|  3 | 2017-06-05 |   123 |   192.86 |  192.86 |
|  4 | 2017-06-12 |    21 |   -82.93 |  -82.93 |
|  5 | 2017-06-19 |   535 |  2447.62 | 2447.62 |
|  6 | 2017-06-26 |  3236 |   504.86 |  504.86 |
|  7 | 2017-07-03 | 32133 |   892.99 |  892.99 |
+----+------------+-------+----------+---------+

我使用了自动生成的id。但是这个日期也可以用来订购。这只是展示了基本形式。不需要子查询

显示表单不需要聚合(计数)。只需将cnt替换为最终查询的任何聚合计算(以及GROUP BY)。