时间戳

首页 > 解决方案 > 逐月增长（同一列）

mysql - 逐月增长（同一列）

问题描述

我查看了其他解决方案，但似乎没有一个对我有用。我有一个非常简单的问题。

我有一个名为止赎的表。它有 3 列名为：foreclosures_id、period、foreclosures。句点是唯一的主键。止赎列的数据类型是十进制。

我当前的查询是：SELECT MONTHNAME(period) AS Reported_Month, YEAR(period) AS Reported_Year, FORMAT(foreclosures, 0) AS Foreclosures,

(SELECT FORMAT(SUM(f2.foreclosures),0) FROM foreclosures f2
WHERE f2.period <= f1.period AND f1.foreclosures IS NOT NULL)  
AS YTD_Total,

MONTHNAME(DATE_SUB(period, INTERVAL 2 MONTH))  AS Real_Month,
YEAR(DATE_SUB(period, INTERVAL 2 MONTH))  AS Real_Year

FROM foreclosures f1;

我正在尝试生成以下“增长”列。请帮忙？

    +----------------+---------------+--------------+------------+
    | Reported_Month | Reported_Year | Foreclosures | Growth %  |
    +----------------+---------------+--------------+------------+
    | January        |          2016 |          201 |            |
    | February       |          2016 |          332 | 65.2%      |
    | March          |          2016 |          240 | -27.7%     |
    | April          |          2016 |          369 | 53.8%      |
    +----------------+---------------+--------------+------------+

标签： mysqlsql

解决方案

您可以使用与累积总和类似的逻辑来获取上一期间的止赎：

SELECT MONTHNAME(period) AS Reported_Month,
       YEAR(period) AS Reported_Year,
       FORMAT(foreclosures, 0) AS Foreclosures,
       (SELECT f2.foreclosures
        FROM foreclosures AS f2
        WHERE f2.period < f.period AND
              f.foreclosures IS NOT NULL
        ORDER BY f2.period DESC
       ) as prev_foreclosures
FROM foreclosures f;

对于增长度量，只需应用您将用于该计算的算术。

推荐阅读