在 bigquery 中的某个阈值处启动 SUM 聚合

debugcn 发表于 Dev

设备的能源使用量每小时记录一次：

+--------------+-----------+-----------------------+
| energy_usage | device_id |  timestamp            |
+--------------+-----------+-----------------------+
| 10           | 1         |  2019-02-12T01:00:00  |
| 16           | 2         |  2019-02-12T01:00:00  |
| 26           | 1         |  2019-03-12T02:00:00  |
| 24           | 2         |  2019-03-12T02:00:00  |
+--------------+-----------+-----------------------+

我的目标是：

创建两列，一列energy_usage_day用于energy_usage_night(8am-8pm) ，另一列用于(8pm-8am)
创建每月汇总，按 device_id 分组并汇总能源使用情况

所以结果可能是这样的：

+--------------+------------------+--------------------+-----------+---------+------+
| energy_usage | energy_usage_day | energy_usage_night | device_id |  month  | year |
+--------------+------------------+--------------------+-----------+---------+------+
| 80           | 30               | 50                 | 1         | 2       | 2019 |
| 130          | 60               | 70                 | 2         | 3       | 2019 |
+--------------+------------------+--------------------+-----------+---------+------+

以下查询产生这样的结果：

SELECT SUM(energy_usage) energy_usage
  , SUM(IF(EXTRACT(HOUR FROM timestamp) BETWEEN 8 AND 19, energy_usage, 0)) energy_usage_day
  , SUM(IF(EXTRACT(HOUR FROM timestamp) NOT BETWEEN 8 AND 19, energy_usage, 0)) energy_usage_night
  , device_id
  , EXTRACT(MONTH FROM timestamp) month, EXTRACT(YEAR FROM timestamp) year
FROM `data`
GROUP BY device_id, month, year

假设我只对高于某个阈值的能源使用总量感兴趣，例如 50。我想以 50 的总能源使用量开始 SUM。结果应该如下所示：

+--------------+------------------+--------------------+-----------+---------+------+
| energy_usage | energy_usage_day | energy_usage_night | device_id |  month  | year |
+--------------+------------------+--------------------+-----------+---------+------+
| 30           | 10               | 20                 | 1         | 2       | 2019 |
| 80           | 50               | 30                 | 2         | 3       | 2019 |
+--------------+------------------+--------------------+-----------+---------+------+

换句话说：只有当energy_usage 达到阈值50 时，查询才应开始汇总energy_usage、energy_usage_day 和energy_usage_night。

这在bigquery中可能吗？

米哈伊尔·贝里安特

以下是 BigQuery Standard SQL 的逻辑，它仅在达到 50 个（每个设备每月）后才开始聚合使用

#standardSQL
WITH temp AS (
  SELECT *, SUM(energy_usage) OVER(win) > 50 qualified,
    EXTRACT(HOUR FROM `timestamp`) BETWEEN 8 AND 20 day_hour,
    EXTRACT(MONTH FROM `timestamp`) month, 
    EXTRACT(YEAR FROM `timestamp`) year    
  FROM `project.dataset.table`
  WINDOW win AS (PARTITION BY device_id, TIMESTAMP_TRUNC(`timestamp`, MONTH) ORDER BY `timestamp`)
)
SELECT SUM(energy_usage) energy_usage,
  SUM(IF(day_hour, energy_usage, 0)) energy_usage_day,
  SUM(IF(NOT day_hour, energy_usage, 0)) energy_usage_night,
  device_id,
  month, 
  year
FROM temp
WHERE qualified
GROUP BY device_id, month, year

假设当前使用的 SUM 为 49，下一个使用条目的值为 2。SUM 将为 51。因此，使用 2 将添加到 SUM。相反，应该只添加 1 的一半。我们可以在 BigQuery SQL 中解决这样的问题吗？

#standardSQL
WITH temp AS (
  SELECT *, SUM(energy_usage) OVER(win) > 50 qualified,
    SUM(energy_usage) OVER(win) - 50 rolling_sum,
    EXTRACT(HOUR FROM `timestamp`) BETWEEN 8 AND 20 day_hour,
    EXTRACT(MONTH FROM `timestamp`) month, 
    EXTRACT(YEAR FROM `timestamp`) year    
  FROM `project.dataset.table`
  WINDOW win AS (PARTITION BY device_id, TIMESTAMP_TRUNC(`timestamp`, MONTH) ORDER BY `timestamp`)
), temp_with_adjustments AS (
  SELECT *, 
    IF(
      ROW_NUMBER() OVER(PARTITION BY device_id, month, year ORDER BY `timestamp`) = 1, 
      rolling_sum, 
      energy_usage
    ) AS adjusted_energy_usage
  FROM temp 
  WHERE qualified
)
SELECT SUM(adjusted_energy_usage) energy_usage,
  SUM(IF(day_hour, adjusted_energy_usage, 0)) energy_usage_day,
  SUM(IF(NOT day_hour, adjusted_energy_usage, 0)) energy_usage_night,
  device_id,
  month, 
  year
FROM temp_with_adjustments
GROUP BY device_id, month, year

正如你所看到的，我刚刚添加了逻辑temp_with_adjustments（和 rolling_sumtemp以支持这一点） - 其余的都是一样的

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。