逐月增长(同列)

yh2wf1be 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(266)

我已经研究了我的其他解决方案，似乎没有一个适合我。我有一个非常简单的问题。
我有一张名为“止赎”的table。它有3列，分别命名为：止赎id，期间，止赎。句点是唯一的主键。止赎列的数据类型为十进制。
我现在的查询是：选择monthname（period）作为reported\u month，year（period）作为reported\u year，format（forecloses，0）作为forecloses，

(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%      |
    +----------------+---------------+--------------+------------+

sql mysql

来源：https://stackoverflow.com/questions/54058337/month-to-month-growth-same-column

1条答案

按热度按时间

xwbd5t1u1#

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

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;

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

赞(0）回复(0）举报 2021-06-15

我来回答

逐月增长(同列)

1条答案

相关问题

热门标签

最新问答