我在一个SQL表中有以下数据.看SQL fiddle就可以玩了.
| id | pointsEarned | createdAt |
|---|---|---|
| 234-00000206-0 | 500 | 2023-05-03T09:05:05.034Z |
| 234-00000206-0 | 1000 | 2023-05-12T09:05:05.034Z |
| 234-00000206-0 | 800 | 2023-05-15T09:05:05.034Z |
| 234-00000206-0 | 300 | 2023-05-21T09:05:05.034Z |
| 234-00000206-0 | 1100 | 2023-05-28T09:05:05.034Z |
数据按如下方式创建.
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');
从这些数据中，我正在寻找那些已经超过1.000分的ID，以及发生的日期.在样本数据中，这应该已经是5月12日在第二行.
我try 了这条SQL语句，但这里的结果当然是5月28日.
SELECT
id,
SUM(pointsEarned) as points,
MAX(createdAt) as lastActivity
FROM
activities
GROUP BY
id
HAVING
points > 1000;
然后我在考虑使用窗口函数，并try 了这个，但得到了一个错误，表明该语句是错误的.你知道如何修复错误(参见上面的SQL fiddle链接)，以及如何将查询限制在1.000点以内吗？
SELECT
id,
SUM(pointsEarned) OVER(ORDER BY createdAt) points
FROM activities;
PS:SQL小提琴使用MySQL.最后，我需要Google BigQuery的声明.