在bigquery中计算7、14和30天移动平均值

Question

我在玩bigquery。我有IoT正常运行时间记录作为输入：

+---------------+-------------+----------+------------+
|   device_id   |  reference  |  uptime  | timestamp  |
+---------------+-------------+----------+------------+
| 1             | 1000-5      |  0.7     | 2019-02-12 |
| 2             | 1000-6      |  0.9     | 2019-02-12 |
| 1             | 1000-5      |  0.8     | 2019-02-11 |
| 2             | 1000-6      |  0.95    | 2019-02-11 |
+---------------+-------------+----------+------------+

我想计算按设备分组的正常运行时间的7、14和30天移动平均值。输出应如下所示：

+---------------+-------------+---------+--------+--------+
|   device_id   |  reference  |  avg_7  | avg_14 | avg_30 |
+---------------+-------------+---------+--------+--------+
| 1             | 1000-5      |  0.7    | ..     | ..     |
| 2             | 1000-6      |  0.9    | ..     | ..     |
+---------------+-------------+---------+--------+--------+

我试过的是：

SELECT
    device_id,
    AVG(uptime) OVER (ORDER BY day RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM (
  SELECT device_id, uptime, UNIX_DATE(DATE(timestamp)) as day FROM `uptime_recordings`
)
GROUP BY device_id, uptime, day

我有1000个不同的设备和200 K读数的录音。分组不工作，查询返回200 k记录，而不是1000。有什么不对吗？

Answer 1

我有1000个不同的设备和200 K读数的录音。分组不工作，查询返回200 k记录，而不是1000。有什么不对吗？

而不是GROUP BY device_id, uptime, day，做GROUP BY device_id, day。

完整的工作查询：

WITH data 
AS (
  SELECT title device_id, views uptime, datehour timestamp
  FROM `fh-bigquery.wikipedia_v3.pageviews_2019` 
  WHERE DATE(datehour) BETWEEN '2019-01-01' AND '2019-01-09'
  AND wiki='br'
  AND title='Chile'
)

SELECT device_id, day
  , AVG(uptime) OVER (PARTITION BY device_id ORDER BY UNIX_DATE(day) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM (
  SELECT device_id, AVG(uptime) uptime, (DATE(timestamp)) as day
  FROM `data`
  GROUP BY device_id, day
)

​

​

编辑:根据评论中的要求，不确定总结所有7d平均数的目标是什么：

WITH data 
AS (
  SELECT title device_id, views uptime, datehour timestamp
  FROM `fh-bigquery.wikipedia_v3.pageviews_2019` 
  WHERE DATE(datehour) BETWEEN '2019-01-01' AND '2019-01-09'
  AND wiki='br'
  AND title IN ('Chile', 'Saozneg')
)

SELECT device_id, AVG(avg_7d) avg_avg_7d
FROM (
  SELECT device_id, day
    , AVG(uptime) OVER (PARTITION BY device_id ORDER BY UNIX_DATE(day) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
  FROM (
    SELECT device_id, AVG(uptime) uptime, (DATE(timestamp)) as day
    FROM `data`
    GROUP BY device_id, day
  )
)
GROUP BY device_id 

​

​