SQL - 计算月度回报的同比增长 [英] SQL - Calculating Year Over Year Growth for monthly returns

问题描述

我有一个表格，其中包含以下形式的每月数据:

I have a table that has monthly data in the form of:

Date | Value
2018-06 | 100
2018-07 | 105
2017-06 | 90
2017-07 | 92

获取这些数据并计算每个月的年同比回报的最佳方法是什么?我希望它看起来像:

What would be the best way to take this data and calculate a year over year return for each month? I would want it to look like:

Date | YoY growth
2018-06 | 0.11111
2018-07 | 0.1413

推荐答案

我会简单地解析日期并使用 lag():

I would simply parse the date and use lag():

select date, value, prev_value,
       (value - prev_value) / prev_value as YOY_growth
from (select t.*,
             lag(value) over (partition by right(date, 2)
                              order by left(date, 4)
                             ) as prev_value
      from t
     ) t
where prev_value is null

这篇关于SQL - 计算月度回报的同比增长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！