使用 count(*) 有窗框的。理想情况下，你会使用一个间隔。但是bigquery还不支持这种语法。所以转换成一个数字：

select t.*,
       count(*) over (partition by customer_id
                      order by unix_date(date)
                      range between 364 preceding and current row
                     ) as order_rank
from t;

这将一年视为365天，这似乎适用于大多数目的。

我建议你用 over 子句并限制 where 条款。你的案子不需要一个窗口。如果你考虑一个从过去的365天到现在的周期，这是可行的：

select t.*,
       count(*) over (partition by customer_id
                      order by date
                     ) as c
from `your-table` t
where date > DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
order by customer_id, c

如果您需要某个特定年份，例如2019年，您可以执行以下操作：

select t.*,
       count(*) over (partition by customer_id
                      order by date
                     ) as c
from `your-table` t
where date between cast("2019-01-01" as date) and cast("2019-12-31" as date)
order by customer_id, c