BigQuery标准SQL如何逐行计算事件表的环比百分比变化率

你可以通过窗口函数LAG实现该需求，完整SQL如下：

WITH ranked_click_events AS (
  SELECT
    event,
    event_count,
    -- 按点击量降序排序，取上一行的点击量数值
    LAG(event_count) OVER(ORDER BY event_count DESC) AS prev_count
  FROM `你的项目ID.你的数据集名.你的埋点表名` -- 替换为你的实际表路径
)
SELECT
  event,
  event_count,
  CASE
    -- 首行无上一行数据，直接返回0
    WHEN prev_count IS NULL THEN '0'
    -- 计算变化率并格式化为百分比，保留2位小数
    ELSE FORMAT("%.2f%%", SAFE_DIVIDE(event_count - prev_count, prev_count) * 100)
  END AS `% change`
FROM ranked_click_events
ORDER BY event_count DESC;

关键说明 #

• 窗口函数LAG的排序规则和最终输出排序规则完全一致，保证取到的是相邻上一行的点击量
• 用SAFE_DIVIDE避免除零错误，即使出现上一行点击量为0的异常场景也不会报错
• 负值会自动保留负号，符合需求
• 如果你需要和示例一致用逗号作为小数分隔符，可将格式化部分修改为：

  REPLACE(FORMAT("%.2f%%", SAFE_DIVIDE(event_count - prev_count, prev_count) * 100), '.', ',')
  

• 如果你需要实现示例中最后一行整数百分比的效果，可以额外加判断逻辑动态调整小数位数。

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