计算每位顾客订单间的平均时间

Question

我在各个地方搜索，但似乎找不到如何计算每个客户订单之间平均时间的方法。我使用Oracle SQL Developer。我尝试使用lag()函数，但没有成功。

以下示例数据显示一个客户进行了3次购买。所有购买之间的平均时间为7.5天((6+9)/2)。

CustID   OrderDate 
-------  ---------
1000000  14-AUG-12
1000000  23-AUG-12
1000000  29-AUG-12

那么，如何计算每个客户的平均值呢？任何帮助将不胜感激。

- user2335923

---

也许可以看看这篇帖子：https://dev59.com/rGPVa4cB1Zd3GeqP2Ra6。 - Antoine

Answer 1

如果只需要平均值，那么我认为您可以通过查看最小和最大日期并通过订单之间的差距来除以数量来简单查询而不需要窗口函数：

SELECT CustID, (MAX(OrderDate) - MIN(OrderDate)) / (COUNT(*) - 1)
  FROM Orders
 GROUP BY CustID
HAVING COUNT(*) > 1

- Ed Plese

---

非常合乎逻辑的解决方案，我最终使用了CTE，因为我想要所有客户的平均值。 - Tom

Answer 2

关键在于使用滞后分析函数。

select cust_id , avg(orderdate - lag_orderdate) as avg_time_between_orders
from (
    select cust_id , orderDate , lag(orderdate) over (partition by cust_id) as lag_orderdate
    from  orders )

- haki

Answer 3

< p > LAG 分析函数肯定是答案，但查询应该更像这样：

SELECT CustID, AVG(OrderDate - PriorDate)
FROM (
  SELECT
    CustID,
    OrderDate,
    LAG(OrderDate) OVER (PARTITION BY CustID ORDER BY OrderDate) as PriorDate
  FROM Orders)
GROUP BY CustID

- Ed Gibbs

Answer 4

以Ed的答案为起点，使用CTE获取所有订单的平均值。

 WITH Sales_CTE (CustomerId, OrderDays, OrderCount)
    AS
    (
    SELECT CustomerId, (datediff(day,Min(OrderTime),Max(OrderTime))) / (COUNT(*) - 1) AS OrderDays, COUNT(*) AS OrderCount
      FROM tblStoreOrders  GROUP BY CustomerId HAVING COUNT(*) > 1  
    )
    SELECT Avg(OrderDays) FROM Sales_CTE

- Tom

Answer 5

Select customers.customerName, customers.CustomerNumber, T.date_diff as date_diff
 from customers 
join
(SELECT customerNumber, avg(datediff(shippedDate, orderDate))  as date_diff FROM classicmodels.orders group by customerNumber) AS T
on 
customers.customerNumber = T.customerNumber
order by date_diff

- Akshay Kanwar