按ID分组计算累计积分首次达标1000的日期（窗口函数实现）

数据源与表结构 #

现有activities表数据如下：

建表及插入数据的SQL语句：

CREATE TABLE activities (
  id varchar(14),
  pointsEarned int,
  createdAt varchar(24)
);
  
INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 500, '2023-05-03T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 1000, '2023-05-12T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 800, '2023-05-15T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 300, '2023-05-21T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 1100, '2023-05-28T09:05:05.034Z');

需求说明 #

找出每个ID累计积分首次超过1000对应的日期（示例数据中该日期为2023-05-12）。

错误尝试分析 #

1. 普通分组聚合语句 #

原分组聚合SQL仅能返回每个ID的总积分和最后活动日期，无法定位到累计积分首次突破1000的时间点：

SELECT 
  id, 
  SUM(pointsEarned) as points, 
  MAX(createdAt) as lastActivity
FROM 
  activities
GROUP BY
  id
HAVING 
  points > 1000;

2. 报错的窗口函数语句 #

原窗口函数未按ID分区，会跨ID累计积分；同时未规范转换日期类型，不符合需求逻辑：

SELECT 
  id,
  SUM(pointsEarned) OVER(ORDER BY createdAt) points
FROM activities;

正确解决方案（适配BigQuery） #

通过CTE结合窗口函数计算每个ID的累计积分，再筛选首次超过1000的日期：

WITH cumulative_points AS (
  SELECT
    id,
    createdAt,
    -- 按ID分区，将字符串日期转为TIMESTAMP确保排序准确，计算从第一条到当前记录的累计积分
    SUM(pointsEarned) OVER (
      PARTITION BY id 
      ORDER BY PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E3SZ', createdAt)
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_points
  FROM activities
)
SELECT
  id,
  MIN(createdAt) AS first_date_exceed_1000
FROM cumulative_points
WHERE total_points > 1000
GROUP BY id;

逻辑说明 #

1. CTE计算累计积分：按ID分区，将字符串格式的createdAt转为BigQuery标准TIMESTAMP类型后排序，计算从该ID第一条记录到当前记录的累计积分。
2. 筛选并取首次日期：过滤出累计积分超过1000的所有记录，对每个ID取最小的createdAt，即为首次超过1000的日期。

执行结果 #

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