错误：带有 ORDER BY 子句的聚合窗口函数需要框架子句

在 Redshift 上输入以下查询时，我收到“错误：带有 ORDER BY 子句的聚合窗口函数需要框架子句”消息。请帮忙 - 我正在尝试查看从第一天到今天会员的成长情况。谢谢。

select date(timestampregistered), count(distinct(memberid)),
(SUM(count(distinct(memberid))) OVER (ORDER BY date(timestampregistered)))
  AS total_users
from table
order by date(timestampregistered);

有几件事正在发生。首先，您似乎缺少按日期正确操作 COUNT() 的 GROUP BY 子句。

接下来，您需要指定要 SUM() 的“计数”范围。具体来说，您希望对当前行日期之前的日期（包括当前行的日期）的计数进行求和，但不求较晚日期的计数。

select date(timestampregistered), count(distinct(memberid)),
(SUM(count(distinct(memberid))) OVER (ORDER BY date(timestampregistered) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
  AS total_users
from table
group by date(timestampregistered)
order by date(timestampregistered);