BigQuery：时间序列数据的每日平均值计算

针对你的需求，分两种场景给出实现方法，以下示例基于标准SQL，不同数据库语法略有差异：

场景1：计算每个路由器+网络的全年整体日均吞吐量 #

先将细粒度时间序列数据按天聚合得到每日吞吐量，再按路由器ID和网络名称计算全年所有每日值的平均值：

WITH daily_throughput AS (
    -- 第一步：按路由器、网络、日期聚合，得到每日平均吞吐量
    SELECT
        router_id,
        network_name,
        DATE(timestamp) AS measurement_date,
        AVG(throughput) AS daily_avg_throughput  -- 若需总吞吐量则替换为SUM(throughput)
    FROM router_throughput
    -- 筛选过去一年的数据
    WHERE timestamp >= CURRENT_DATE - INTERVAL '365 days'
    GROUP BY router_id, network_name, DATE(timestamp)
)
-- 第二步：计算每个路由器+网络的全年每日平均值
SELECT
    router_id,
    network_name,
    AVG(daily_avg_throughput) AS yearly_overall_daily_avg
FROM daily_throughput
GROUP BY router_id, network_name;

场景2：计算每个日期的滚动年日均吞吐量（过去一年到当日的平均） #

用窗口函数实现按路由器+网络分组、时间范围为过去一年的滚动平均值：

WITH daily_throughput AS (
    SELECT
        router_id,
        network_name,
        DATE(timestamp) AS measurement_date,
        AVG(throughput) AS daily_avg_throughput
    FROM router_throughput
    WHERE timestamp >= CURRENT_DATE - INTERVAL '365 days'
    GROUP BY router_id, network_name, DATE(timestamp)
)
SELECT
    router_id,
    network_name,
    measurement_date,
    daily_avg_throughput,
    -- 窗口范围：当前日期往前365天到当日
    AVG(daily_avg_throughput) OVER (
        PARTITION BY router_id, network_name
        ORDER BY measurement_date
        RANGE BETWEEN INTERVAL '365 days' PRECEDING AND CURRENT ROW
    ) AS rolling_year_daily_avg
FROM daily_throughput
ORDER BY router_id, network_name, measurement_date;

不同数据库的语法适配 #

• MySQL：需将日期转为时间戳来定义范围，替换窗口子句为：

  AVG(daily_avg_throughput) OVER (
      PARTITION BY router_id, network_name
      ORDER BY UNIX_TIMESTAMP(measurement_date)
      RANGE BETWEEN 365*86400 PRECEDING AND CURRENT ROW
  )
  

• SQL Server：用DATEADD替换区间语法：

  AVG(daily_avg_throughput) OVER (
      PARTITION BY router_id, network_name
      ORDER BY measurement_date
      RANGE BETWEEN DATEADD(day, -365, measurement_date) PRECEDING AND CURRENT ROW
  )
  

内容的提问来源于stack exchange，提问作者Ani