时间戳

首页 > 解决方案 > bigquery 中一年一周的滑动窗口聚合

google-bigquery - bigquery 中一年一周的滑动窗口聚合

问题描述

我的问题是关于 bigquery 中的滑动窗口总结。

我有一张如下表

run_id      year_week     value
  001        201451         5
  001        201452         8
  001        201501         1
  001        201505         5
  003        201352         8
  003        201401         1
  003        201405         5

在这里，每周的范围可以从 01 到 53。例如，2014 年有上周，即 201452，但 2015 年有上周，即 201553。如果它让生活更轻松，我只有 5 年，2013 年、2014 年、2015 年， 2016 年和 2017 年，只有 2015 年的周数达到 53 周。

现在对于每次运行，我都试图获得值的滑动窗口总和。每个 都假定当前 run_id （例如）year_week的下一个 5（包括它自己）的值的总和。例如，以下可能是当前表的输出year_week001

run_id    year_week   aggregate_sum
  001      201451      5+8+1+0+0
  001      201452      8+1+0+0+0
  001      201501      1+0+0+0+5
  001      201502      0+0+0+5+0
  001      201503      0+0+5+0+0
  001      201504      0+5+0+0+0
  001      201505      5+0+0+0+0
  003      201352      8+1+0+0+0
  003      201401      1+0+0+0+5
  003      201402      0+0+0+5+0
  003      201403      0+0+5+0+0
  003      201404      0+5+0+0+0
  003      201405      5+0+0+0+0

为了解释发生了什么， 201451 包括它自己在内的接下来的 5 周将是 201451,201452,201501,201502,201503 。如果当前表中有这些周的值，run_id我们只需将它们相加，即 5+8+1+0+0，因为year_week如果 a 不在表中，则对应的值为 0。

是否可以在 bigquery 中使用滑动窗口操作来做到这一点？

标签： google-bigquerybigquery-standard-sqllegacy-sql

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
WITH weeks AS (
  SELECT 100* year + week year_week
  FROM UNNEST([2013, 2014, 2015, 2016, 2017]) year, 
  UNNEST(GENERATE_ARRAY(1, IF(EXTRACT(ISOWEEK FROM DATE(1+year,1,1)) = 1, 52, 53))) week
), temp AS (
  SELECT i.run_id, w.year_week, d.year_week week2, value
  FROM  weeks w 
  CROSS JOIN (SELECT DISTINCT run_id FROM `project.dataset.table`) i
  LEFT JOIN `project.dataset.table` d
  USING(year_week, run_id)
)
SELECT * FROM (
  SELECT run_id, year_week, 
    SUM(value) OVER(win) aggregate_sum
  FROM temp
  WINDOW win AS (
    PARTITION BY run_id ORDER BY year_week ROWS BETWEEN CURRENT row AND 4 FOLLOWING
  )
)
WHERE NOT aggregate_sum IS NULL

您可以使用来自您问题的虚拟数据来测试/玩上面的内容，如下所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '001' run_id, 201451 year_week, 5 value UNION ALL
  SELECT '001', 201452, 8 UNION ALL
  SELECT '001', 201501, 1 UNION ALL
  SELECT '001', 201505, 5 
), weeks AS (
  SELECT 100* year + week year_week
  FROM UNNEST([2013, 2014, 2015, 2016, 2017]) year, 
  UNNEST(GENERATE_ARRAY(1, IF(EXTRACT(ISOWEEK FROM DATE(1+year,1,1)) = 1, 52, 53))) week
), temp AS (
  SELECT i.run_id, w.year_week, d.year_week week2, value
  FROM  weeks w 
  CROSS JOIN (SELECT DISTINCT run_id FROM `project.dataset.table`) i
  LEFT JOIN `project.dataset.table` d
  USING(year_week, run_id)
)
SELECT * FROM (
  SELECT run_id, year_week, 
    SUM(value) OVER(win) aggregate_sum
  FROM temp
  WINDOW win AS (
    PARTITION BY run_id ORDER BY year_week ROWS BETWEEN CURRENT row AND 4 FOLLOWING
  )
)
WHERE NOT aggregate_sum IS NULL
-- ORDER BY run_id, year_week

结果为 

Row run_id  year_week   aggregate_sum    
1   001     201447      5    
2   001     201448      13   
3   001     201449      14   
4   001     201450      14   
5   001     201451      14   
6   001     201452      9    
7   001     201501      6    
8   001     201502      5        
9   001     201503      5    
10  001     201504      5    
11  001     201505      5
12  003     201348      8    
13  003     201349      9    
14  003     201350      9    
15  003     201351      9    
16  003     201352      9    
17  003     201401      6    
18  003     201402      5    
19  003     201403      5    
20  003     201404      5    
21  003     201405      5

笔记; 这是为了 -I only have 5 years, 2013, 2014, 2015, 2016 and 2017但可以在几周内轻松延长 CTE

推荐阅读