查找同一表中两个日期记录之间的天数

Question

我有一个DATE, ORDERID, CLIENTID和STOREID。

我试图获得所有至少下了3个订单的客户的第一个订单日期和第三个订单日期之间的平均天数。

这是我目前所拥有的，但当我添加OrderId时，它不再返回任何东西。

select Date, OrderId, ClientId
from ClientOrders
group by Date, OrderId, ClientId
having count(ClientId) > 3

Answer 1

我们可以在这里使用ROW_NUMBER:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY Date) rn
FROM ClientOrders
)
SELECT AVG(diff)
FROM
(
SELECT DATEDIFF(day,
MAX(CASE WHEN rn = 1 THEN Date END),
MAX(CASE WHEN rn = 3 THEN Date END)) AS diff
FROM cte
GROUP BY ClientId
HAVING COUNT(*) >= 3   -- at least 3 orders
) t;