我有这样的数据:
日期 | 错误 | 销售额|
---|---|---|
7/1 | 1 | 10|
7/2 | 2 | 12 |
7/3 | 3 | 15 |
7/4 | 4 | 20 |
7/5 | 5 | 21 |
7/6 | 6 | 24 |
7/7 | 7 | 27 |
7/8 | 8 | 30 |
假设表中每天都有数据(如您的示例数据(,只需使用lag()
:
select date, error, sale,
lag(error, 1) over (order by date) as prev1_error,
lag(sale, 1) over (order by date) as prev1_sale,
lag(error, 2) over (order by date) as prev2_error,
lag(sale, 2) over (order by date) as prev2_sale,
lag(error, 3) over (order by date) as prev3_error,
lag(sale, 3) over (order by date) as prev3_sale,
. . .
from t;
不幸的是,AmazonAthena并不(完全(支持range between
窗口规范。如果你错过了几天,那么你有三个选择:
- 将问题重新定义为占用表中最后7天的时间,并使用上述解决方案
- 加上缺少的日期
- 使用
join
溶液