在 BigQuery 上计算客户第一笔订单和第二笔订单之间的平均时间(以天为单位)

Question

我试图计算客户重复购买所需的平均天数。

这是我所拥有的

Email_address, 
Order_name, 
Date, 
Order_number,
lead(date) over (partition by email_address order by Order_number) next_order_date,
lead(Order_number) over (partition by email_address order by Order_number) next_order_number
FROM `dbOrders` 
Group by 1,2,3,4 

它正在返回这个

[
{
"Email_address": "[e-mail]",
"Order_name": "#1234",
"Date": "2019-11-08 00:18:00 UTC",
"Order_number": "1",
"next_order_date": "2019-11-28 20:54:00 UTC",
"next_order_number": "2"
},

我现在想得到所有第一次和第二次购买之间的平均天数，然后是第二次和第三次购买 et

示例数据集

|---------------------|------------------|------------------|------------------| | Email_address | Order_name | Order_number | Date | |---------------------|------------------|------------------|------------------| | [e-mail] | 364 | 1 | 2019-08-11 | |---------------------|------------------|------------------|------------------| | [e-mail] | 366 | 2 | 2019-08-29 | |---------------------|------------------|------------------|------------------| | [e-mail] | 495 | 1 | 2019-09-04 | |---------------------|------------------|------------------|------------------| | [e-mail] | 564 | 2 | 2019-10-04 | |---------------------|------------------|------------------|------------------|

我想找到两个客户订单之间的平均时差。 [e-mail] = 18 天 [e-mail] = 30 天

平均值 = 24 天

Answer 1

您似乎在请求聚合查询：

select seqnum,
avg(date_diff(date, next_date, day)) as avg_days_to_next_order
from (select o.*,
row_number() over (partition by email_address order by date) as seqnum,
lead(date) over (partition by email_address order by date) as next-date
from `dbOrders`  o
) o
group by seqnum
order by seqnum;