自上次提交记录以来，金额是否有所增加?

Question

我有一个表格，格式如下

Name            ID            Amount
Customer 1      1             £100
Customer 1      2             £50
Customer 1      3             £75
Customer 2      1             £0
Customer 2      2             £1000
Customer 3      1             £50
Customer 3      2             £20
Customer 3      3             £10

ID将始终指示最近的字段，其中1 =最近的，我需要做什么，如果找出客户从他们以前的ID中看到了价值的增加。

例如，顾客1增加了£50，顾客2减少了£1000，顾客3增加了£10和£30。

帮助是感激的，谢谢

Answer 1

select c1.*, c2.* 
  from customer c1 
  join customer c2 
    on c2.ID = c1.ID  + 1
   and c2.Name = c1.Name 
   and c2.Amnount < c1.Amnount

Answer 2

您需要移动差值，通常基于LAG/LEAD。Teradata不支持这种语法，但是可以很容易地重写:

Amount -
min(Amount)  -- LEAD(Amount) over partition by Name order by ID)
over (partition by Name
      order by ID
      rows between 1 following and 1 following) as Diff

如果要只过滤增长，可以添加

QUALIFY Diff > 0