BigQuery对接Grafana时序图统计去重计数最大值问题咨询

问题核心 #

原有查询存在三个核心问题导致结果不符合预期：

• 最终统计层误用AVG()窗口函数做累计计算，返回浮点型平均值，而非需要的去重计数结果
• 中间层窗口函数排序字段使用单时间点用户数而非时间字段，累计顺序错乱，统计值不准
• 时间筛选逻辑固定写死当日，和Grafana自定义时间范围的适配性差

原有查询参考 #

with
users as(select timestamp(timestamp) as fecha, count(distinct u) as uin from `test_todaydata_view`
WHERE   ev like "%p%" and date(timestamp) = current_date('UTC-3')   group by fecha order by fecha asc),
filas as (SELECT  timestamp(fecha) + INTERVAL 7 MINUTE as ff, count(uin) OVER(ORDER BY uin asc  ROWS BETWEEN unbounded PRECEDING AND CURRENT ROW) as usuarios FROM users  )
select datetime_trunc(ff,MINUTE) AS fem, AVG(usuarios) OVER(ORDER BY ff  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as usuarios  from filas where $__timeFilter(ff) group by ff,usuarios

优化后可直接使用的查询 #

WITH
base_data AS (
  SELECT
    DATETIME_TRUNC(TIMESTAMP(timestamp), MINUTE, 'UTC-3') AS time_point,
    u AS user_id
  FROM `test_todaydata_view`
  WHERE
    ev LIKE "%p%"
    AND $__timeFilter(TIMESTAMP(timestamp))
),
minute_new_users AS (
  SELECT
    time_point,
    COUNT(DISTINCT user_id) AS new_user_cnt
  FROM base_data
  GROUP BY time_point
),
cumulative_stats AS (
  SELECT
    TIMESTAMP(time_point + INTERVAL 7 MINUTE) AS time,
    SUM(new_user_cnt) OVER (
      ORDER BY time_point ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS usuarios
  FROM minute_new_users
)
SELECT time, usuarios
FROM cumulative_stats
ORDER BY time ASC

优化说明 #

• 移除错误的AVG()聚合逻辑，返回值为整数类型的累计去重用户数，完全匹配count(distinct)的统计要求，不会出现浮点型平均值
• 修正所有窗口函数的排序规则，统一按时间维度升序累计，统计顺序符合时间递增逻辑，数值准确
• 原生适配Grafana的$__timeFilter宏，支持在Grafana界面自定义选择查询时间范围，不再固定仅查询当日数据
• 去掉冗余的分组、排序逻辑，查询分层清晰，执行性能更优
• 输出字段完全符合Grafana Timeseries图表的字段要求：第一列为时间类型、第二列为数值类型，无需额外配置转换即可直接展示

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