假设我有一个如下所示的采购表:
Purchases
---------
Item_ID
Purchase_Date
Customer_ID
我如何才能获得从第一次购买开始,距离最后一次购买至少 X 天的每位客户的购买记录?例如,如果我们有以下数据,并且 x=10:
Item_ID PurchaseDate Customer_ID
123 07/29/23 1000
123 08/04/23 1000
123 08/16/23 1000
563 07/03/23 7785
563 07/05/23 7785
788 08/17/23 2489
查询应返回:
Item_ID PurchaseDate Customer_ID
123 07/29/23 1000
123 08/11/23 1000
563 07/03/23 7785
788 08/17/23 2489
因此,对于客户 1000 来说,第一次购买很重要,因为它是新购买的。第二次购买距上次购买不到 10 天,因此被丢弃。该客户的第三次购买才有效,因为距离上次购买已经超过或等于 10 天。对于客户 7785,仅计算第一次,因为第二次购买距离上次购买不到 10 天,最后对于客户 2489,它只有一次新购买,因此仅算作新购买。(请注意,第一次购买始终计算在内) )
我正在考虑使用滞后函数
SELECT
t.Item_ID,
t.PurchaseDate,
t.Customer_ID
FROM (
SELECT
p.Item_ID,
p.PurchaseDate,
p.Customer_ID,
LAG OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) next_purchase
ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) rownum
FROM
PurchaseTable p
) t
WHERE
DATEDIFF(DAY, t.PurchaseDate, t.next) >= 10) or rownum=1
编辑:抱歉,我的描述不正确。我将问题和示例更改为正确的细节。
我已经接受了你的代码并对其进行了一些修改 - 根据你的描述,它给出了所要求的结果:
与您的代码最大的区别是,LAG 给您负值(它检查前一行并从较早的日期中减去较晚的日期 - 因此为负值。因此,我已将 @threshold 声明为 -10(天)并显示所有内容差异-10天或更多(即-11,-12,...)。我还故意注释掉rownum = 1,因为这对purchasing_difference IS NULL来说是多余的(对于每个第一个(或仅)它都是空的)组中的行 - 因此它限定要显示的行)。