对多个分区使用滞后和超前函数

Question

输入

month       seller product amount
2021-10-01    A     corn    20€
2021-10-02    A     corn    40€
2021-10-02    B     grain   30€  
2021-10-03    B     grain   10€     
2021-10-03    A     corn    null    
.....

我想计算每个月和上个月农产品采购之间的差值，并用以下事件标记它们：

(if cost purchase this month > cost purchase last month --> increase 
if cost purchase this month < cost purchase last month --> decrease
if cost purchase this month = x and cost purchase last month is null or 0 --> new
if cost purchase this month is null or 0 and cost purchase last month is not null --> stop  
)

预期输出：

month       seller product amount  last_month_amount  delta   event
2021-10-01    A     corn    20€           null         20€     new
2021-10-02    A     corn    40€           20€          20€   increase
2021-10-02    B     grain   30€           null         30€     new
2021-10-03    B     grain   10€           30€         -20€   decrease
2021-10-03    A     corn    null          40€         -40€    stop     

如果只有一个产品，我可以做：

select month 
, seller
, product
, amount
, lag(amount) over (partition by seller,product order by month) as last_month_amount
, amount - last_month_amount as delta 
, case when delta >0 and min(month) over (partition by seller) = month then 'new' 
when delta >0 then 'increase' 
when delta <0 then 'decrease'
when (delta is null or delta = 0) then 'stop'
end as event 

然而，将多种农产品放在同一个月会打乱逻辑。如何将一个产品的逻辑调整为多个产品？

我想如果我试图获得玉米的last_month_amount，它会返回谷物的上个月数量。我可以使用"；当"；，但如果有很多产品，它就不起作用。

Answer 1

您非常接近。一些小问题可能是阻碍你前进的全部原因。首先，你不能在分配别名last_month_amount的同一个查询中引用它。我的偏好是将第一部分拉到CTE中，然后在主查询中进行计算。

其次，由于没有一致地处理null，您可能还会看到异常行为。由于如果您查看分区中的第一行，lag可能返回null，因此delta应该解决这种可能性。您似乎知道delta可能为null(根据event大小写语句中的逻辑)，但delta当前第一行为null，而不是最后一行，所以我认为'stop'大小写向后。

您还缺少一个FROM；也许您在缩写或简化代码。

最后；月份；在这篇文章中使用的是令人困惑的，部分原因是它是DATE列的一个奇怪名称。当你说"；"同月"；，目前还不清楚你指的是同一天还是同一个月。如果您可能有相同月份(日期)、卖家和产品的不同行，那么您将需要order by中的第二列，否则您将获得不可预测的订单。在不知道这些的情况下，我会假设你已经想清楚了，order by month已经足够好了。

如果上个月的金额为零，您可能还希望事件是"新的"，因为前一行将是"停止"。我已经做出了改变，但如果我的假设是错误的，我会相应地调整。

以下是与这些更改相同的代码：

with rows_in_context AS (
select month 
, seller
, product
, amount
, lag(amount) over (partition by seller,product order by month) as last_month_amount
from some_table
)
select *
, coalesce(amount,0) - coalesce(last_month_amount,0) as delta 
, case when COALESCE(last_month_amount,0) = 0 then 'new' 
when COALESCE(amount,0) = 0 then 'stop'
when delta > 0 then 'increase' 
when delta < 0 then 'decrease'
end as event 
from rows_in_context
order by month, seller, product;