sql - 分组方式：计算字段以返回 bigquery 中的相应日期

Question

我需要做一个用户级别的分析。由于每个用户的数据有很多不同的行（与不同的事件相关），我需要按用户分组并创建一些表示不同行的计算字段。其中一个字段是计算自用户上次购买以来的天数（今天 - 上次购买日期）。我已经尝试了很多不同的代码，也做了很多研究，但找不到解决方案。

对我来说更有意义但不起作用的代码如下：

使用 case when 语句

SELECT CASE WHEN LAST(tr_orderid <> "") THEN 
DATEDIFF(CURRENT_DATE(),event_date) ELSE NULL END AS recency_lastbooking
FROM df
GROUP BY domain_userid

使用 IF 语句

SELECT IF(LAST(tr_total > 0), DATEDIFF(CURRENT_DATE(),event_date), NULL) 
AS recency_lastbooking
FROM df
GROUP BY domain_userid

我得到的错误是：表达式 'event_date' 不在 GROUP BY 列表中

我想如果我使用 LAST(event_date) 查询将返回特定用户所有行中的最后一个日期，而不是返回用户有购买事件的最后一天。

PS：我可以使用 tr_total (总交易) > 0 或 tr_orderid (交易订单id) <> ""

谢谢！

标签： sqlgoogle-bigquerycalculated-field

Answer 1

我想你只想要一个窗口功能：

SELECT DATE_DIFF(CURRENT_DATE,
                 MAX(tr_orderid) OVER (PARTITION BY domain_userid),
                 day
                ) AS recency_lastbooking
FROM df;