如何在SQL中实现周环比增长

Question

广告表：
-每天每个广告一行

date    |   ad_id  | account_id | spend  
2018-05-01   123     1101        100  
2018-05-02   123     1101        125  
2018-05-03   124     1101        150  
2018-05-04   124     1101        150    
2018-05-04   125     1105        150  
2018-05-04   126     1105        150  
2018-05-04   123     1101        150  
2018-01-01   123     1101        150              

我试图写一个查询来找出：自1月1日以来，每天的总支出和每周的变化。

所以，一周比一周应该显示第8天的总支出——第1天的总支支出。我可以实现滞后窗口功能，但如果日期栏中没有提到第一天，我不知道该怎么办。假设5月的第一天没有支出，那么如果我使用了滞后函数，答案就会出错。有没有一种方法可以让我写一个查询，通过日期而不是滞后函数来查找总支出？如果第一天我没有支出，我可以得到1200-0=1200，这是WOW的变化。此外，我无法创建一个可以加入广告表的日期表。

到目前为止，我已经写了这么多：

select dates, sum(spend) "total_spend_each_day",
from fb_ads as f
where dates>= '2018-01-01'
group by dates
order by 1;

期望输出：

date     |  total_spend_each_day |  Week_over_week_change
2018-05-01       500                    Null
2018-05-02       600                    Null
2018-05-03       700                    Null
2018-05-04       800                    Null
2018-05-05       900                    Null
2018-05-06       1000                   Null
2018-01-07       1100                   Null
2018-01-08       1200                   700

Answer 1

只需使用lag()。假设你每天至少有一条记录：

select dates, sum(spend) as total_spend_each_day,
sum(spend) - lag(sum(spend), 7) over (order by dates) as diff
from fb_ads as f
where dates >= '2018-01-01'
group by dates
order by 1;

如果你没有每天的数据，那么只需使用带有range():的窗口框架

select dates, sum(spend) as total_spend_each_day,
(sum(spend) -
max(sum(spend)) over (order by dates range between interval 7 day and interval 7 day)
) as diff
from fb_ads as f
where dates >= '2018-01-01'
group by dates
order by 1;