没有Datediff的SQL datediff？

Question

我有以下数据示例，其中有3列：

Customer_ID；Customer_Class；Purchase_day

我如何计算每一类顾客之间的平均购买天数？

我试过使用CTE，但我真的想不出如何在这里使用datediff：

WITH recency_cte --(SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT Customer_ID, Customer_class, purchase_day
    FROM transactions_table b join customer_table a on customer_id=b.customer_id
    group by Customer_ID, Customer_class, purchase_day
    order by purchase_day asc
)
-- Define the outer query referencing the CTE name.
SELECT customer_class, avg(datediff(call_day)
FROM recency_cte
group by a.comm

谢谢。

Answer 1

这可能会有帮助。请注意，这将是有点有限的，只有返回客户谁已经购买了至少两个不同的日子。我已经包含了一些(注释掉的)示例数据，这样您就可以看到我期望底层数据是什么样子。我还假设customer_class是customer_id唯一的:即每个customer_id只有一个customer_class。如果不是这样，则需要将customer_class添加到两个CTE之间的联接条件中。

WITH recency_cte --(SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT Customer_ID, Customer_class, purchase_day, ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY purchase_day) AS RowNumber
    FROM transactions_table b join customer_table a on customer_id=b.customer_id
     -- (
        --SELECT 1 Customer_ID, 'a' Customer_Class, '6/01/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/03/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/04/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/05/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/08/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/12/2014' Purchase_Day
        --UNION 
        --SELECT 1 Customer_ID, 'a' Customer_Class, '6/12/2014' Purchase_Day
     -- )s
    GROUP BY Customer_ID, Customer_class, purchase_day
)
-- Define the outer query referencing the CTE name.
SELECT Day1.customer_class, avg(datediff(day, Day1.Purchase_Day, Day2.Purchase_Day)) AverageDaysBetweenPurchases
FROM 
    recency_cte Day1
     INNER JOIN 
    recency_cte Day2 ON 
        Day1.Customer_ID = Day2.Customer_ID AND 
        Day1.RowNumber + 1 = Day2.RowNumber
group by Day1.Customer_Class