window.lag.sql

SELECT
  time, goods_id, amount, 
  --第2引数で遡るステップ数を指定--
  LAG(amount, 1) OVER (PARTITION BY goods_id ORDER BY time) AS lag1,
  --第2引数を指定しない時は1つ前のamountを取得--
  LAG(amount) OVER (PARTITION BY goods_id ORDER BY time) AS lag2,
  --第3引数で存在しない場合のデフォルト値を指定できる--
  LAG(amount, 1, 0) OVER (PARTITION BY goods_id ORDER BY time) AS lag3,
  
  --第2引数で先のステップ数を指定--
  LEAD(amount, 1) OVER (PARTITION BY goods_id ORDER BY time) AS lead1,
  --第2引数を指定しない時は1つ先のamountを取得--
  LEAD(amount) OVER (PARTITION BY goods_id ORDER BY time) AS lead2,
  --第3引数で存在しない場合のデフォルト値を指定できる--
  LEAD(amount, 1, 0) OVER (PARTITION BY goods_id ORDER BY time) AS lead3
FROM sales_slip_10k
ORDER BY goods_id, time
LIMIT 10