sql - 如何从 BigQuery 中的累积成本中获取每小时滞后成本?
问题描述
我似乎无法得到每小时的成本......我目前的成本是一个累积指标。所以为了得到每小时的花费,我尝试了一个滞后函数(见下面的代码)。但是,我尝试的任何方法似乎都不是解决方案......
此外,数据按天、小时和国家分组,这让事情变得更有趣。
尝试的代码
SELECT DISTINCT
o.country AS country,
extract(DATE FROM o.date_start) AS date,
EXTRACT(HOUR FROM DATETIME(o._sdc_extracted_at, "Europe/Amsterdam")) AS hour,
sum(o.spend) AS cost,
sum(o.spend) - (LAG(sum(o.spend), 1) OVER (PARTITION BY o.date_start, country ORDER BY country )) AS cost_hourly
FROM `table1` o
WHERE extract(date from o.date_start) < '2021-02-26'
OR extract(date from o.date_start) = CURRENT_DATE()-1
GROUP BY o.country, o.date_start, o._sdc_extracted_at
例如,原始数据如下所示:
日期 | 小时 | 国家 | cum_cost |
---|---|---|---|
2020-12-04 | 0 | 英国 | 0 |
2020-12-04 | 0 | 我们 | 60 |
2020-12-03 | 23 | 英国 | 50 |
2020-12-03 | 23 | 我们 | 500 |
2020-12-03 | 22 | 英国 | 40 |
2020-12-03 | 22 | 我们 | 300 |
所需数据应如下所示:
日期 | 小时 | 国家 | cum_cost | cost_hourly |
---|---|---|---|---|
2020-12-04 | 0 | 英国 | 0 | 0 |
2020-12-04 | 0 | 我们 | 60 | 60 |
2020-12-03 | 23 | 英国 | 50 | 10 |
2020-12-03 | 23 | 英国 | 500 | 200 |
2020-12-03 | 22 | 我们 | 40 | 0 |
2020-12-03 | 22 | 我们 | 300 | 0 |