SQL计算平均时间

Question

我试图计算SQL中每个客户订单之间的平均时间。但是，我遇到了问题，因为我无法在聚合函数上使用LAG()。我如何解决这个问题?

这是我到目前为止没有工作的代码。订单表包含下订单的日期。

SELECT customer.customerid, AVG(DATEDIFF(orders.orderdate, 
LAG(orders.orderdate) OVER (
PARTITION BY orders.customerid ORDER BY orders.orderdate))
) AS avg_time_between_orders
FROM customer
JOIN orders ON customer.customerid = orders.customerid
GROUP BY customerid;

Answer 1

您可以尝试将初始查询拆分为两个(第一个用于Lag，第二个用于聚合)，并将它们与CTE:

合并。

with data as (
select customer.customerid,  
orders.orderdate,
Lag(orders.orderdate) 
over (partition by orders.customerid 
order by orders.orderdate) as PriorOrderDate
from customer join
orders on customer.customerid = orders.customerid)
select customerid,
Avg(DateDiff(orderdate, PriorOrderDate)) as avg_time_between_orders
from data
group by customerid;

Answer 2

这可以通过使用子查询来实现，即创建一个嵌套在另一个查询下面的查询。

select customerid, avg(time_between_orders) from (select t1.orderdate as 
date, t1.orderdate - lag(t1.orderdate) over (order by t1.orderdate) as 
time_between_orders, t2.customerid as customerid from orders as t1 inner join 
customer as t2 on t1.customerid=t2.customerid) as subquery group by 
customerid;

一步一步解释这个查询:

1. 这个查询的最终目的是计算每个客户id的订单之间的平均时间。因此，select customerid, avg(time_between_orders)是在查询的开头定义的。

2. 但是，由于我们没有包含订单间隔时间的表，因此必须使用单独的查询来计算。

3. 因此使用select t1.orderdate as date, t1.orderdate - lag(t1.orderdate) over (order by t1.orderdate) as time_between_orders, t2.customerid as customerid from orders as t1 inner join customer as t2 on t1.customerid=t2.customerid的嵌套查询

4. t1定义为订单表，t2定义为客户表。订单之间的时间使用如上的窗口函数计算。这两个表在customerid的基础上连接在一起，然后允许根据id计算订单之间的平均时间。