如何在 Redshift 中使用 SQL 引用同一列前一行的值?

Question

我的背景是Excel建模，我对SQL很陌生。我有一个如下所示的表格。我想创建"desired_col"，它假设预测月份的复合月增长率为 1 %：

region | category       | date    | date_type | revenue | desired_col
---------------------------------------------------------------------
East   | Inventory      | 07/2017 | Actual    | 25      | 25
East   | Non-Inventory  | 07/2017 | Actual    | 20      | 20
West   | Inventory      | 07/2017 | Actual    | 18      | 18
West   | Non-Inventory  | 07/2017 | Actual    | 16      | 16
East   | Inventory      | 08/2017 | Forecast  | 0       | 25.25
East   | Non-Inventory  | 08/2017 | Forecast  | 0       | 20.2
West   | Inventory      | 08/2017 | Forecast  | 0       | 18.18
West   | Non-Inventory  | 08/2017 | Forecast  | 0       | 16.16
East   | Inventory      | 09/2017 | Forecast  | 0       | 25.5025
East   | Non-Inventory  | 09/2017 | Forecast  | 0       | 20.402
West   | Inventory      | 09/2017 | Forecast  | 0       | 18.3618
West   | Non-Inventory  | 09/2017 | Forecast  | 0       | 16.3216

现在，我可以使用 LAG 函数在预测中的第一个月(上面的例子中为 8 月(完成此操作：

CASE WHEN date_type = 'Actual' THEN revenue ELSE 
LAG( revenue , 1 ) OVER ( PARTITION BY region, category ORDER BY date ) * 1.01
END

但上述语句返回 0 表示 9 月及以后。这在 Excel 中很简单，但我在这里被难住了。你能给我任何建议吗？谢谢！

Answer 1

您可以根据上一个实际月份和预测月份之间的月数确定必要的偏移量，并将其用作LAG的动态偏移量和增长率的功率：

with 
get_offsets as (
select *
,case when to_date(date,'MM/YYYY')>'2017-07-01' then datediff(month,'2017-07-01',to_date(date,'MM/YYYY'))::integer end as this_offset
from your_table
)
select *
,case when date_type = 'Actual' then revenue 
else lag(revenue, this_offset) over (partition by region, category order by date) * 1.01 ^ this_offset
end
from get_offsets

Answer 2

LAG 声明不适用于 9 月以后，因为 Lag(1( 将关注 8 月的收入为零。 9 月将使用 8 月的零计算，10 月将使用 9 月的零计算，依此类推。

您需要像这样循环 LAG 语句：

LAG( LAG( revenue , 1 ) OVER ( PARTITION BY region, category ORDER BY date ) * 1.01 , 1 ) OVER ( PARTITION BY region, category ORDER BY date ) * 1.01

。但是你需要这样做几个月，你想预测未来。 即非常凌乱。

我认为您将不得不使用循环和变量(就像 un-SQL 一样(。 但好消息是，它将更符合您熟悉的Excel解决方案。