我使用的是SQL Server 2012,我需要弄清楚如何为已购买产品的客户拉取跟踪期,其中:
默认跟踪期间为购买日期后的12个月(参见Smith,Jones,Thomas)。
如果客户在该12个月内购买了另一种产品,则跟踪期限将延长。因此,如果客户在2106年6月购买了一件商品,我们将跟踪到2017年5月。如果该客户随后在2016年8月购买了其他产品,我希望记录显示跟踪期间是从2016年6月(第一次购买日期)到2017年8月(请参阅Carpenter)。只要客户在跟踪期结束前继续购买产品,这个过程就应该一直持续下去(见Davis)。
如果客户12个月没有购买另一个产品,然后又购买了一个产品,我需要两个记录,每个跟踪期一个(参见Henderson)。
使用LEAD,我能够确定客户是一次购买还是多次购买,以及购买之间的时间间隔。下面是我的代码:
SELECT DISTINCT
CustomerID,
PurchaseDate,
ISNULL(LEAD(PurchaseDate) OVER (PARTITION BY CustomerID ORDER BY CustomerID, PurchaseDate), DATEADD(MONTH, 12, PurchaseDate)) TrackThroughDate,
DATEDIFF(MONTH, PurchaseDate, ISNULL(LEAD(PurchaseDate) OVER (PARTITION BY CustomerID ORDER BY CustomerID, PurchaseDate), DATEADD(MONTH, 12, PurchaseDate))) AS NumberOfMonths
FROM
PurchaseDetail
WHERE
PurchaseDate > '2014/06/30'
ORDER BY
CustomerID, PurchaseDate这里的一些样本数据是(月数不是必需的,我只是为了让上面的条件在结果中更容易识别而包括它):

所以我需要的是:

有什么好方法的建议吗?
发布于 2018-01-24 04:02:32
您可以使用lag()、运行总和和一些锁来分配跟踪周期:
select pd.customerid,
sum(case when prev_purchasedate > dateadd(year, -1, purchasedate)
then 0 else 1
end) over (partition by customerid order by purchasedate) as trackingperiod
from (select pd.*,
lag(purchasedate) over (partition by customerid order by purchasedate) as prev_purchasedate
from purchasedetail pd
) pd;这定义了基于购买日期的跟踪期间的开始。然后,开始的累积和定义跟踪周期本身。
如果要对这些进行汇总,则逻辑只需在该期间的最大日期上添加一年:
select customerid, min(purchasedate) as purchasedate,
dateadd(year, 1, max(purchasedate)) as trackthroughdate
from (select pd.*,
sum(case when prev_purchasedate > dateadd(year, -1, purchasedate)
then 0 else 1
end) over (partition by customerid order by purchasedate) as trackingperiod
from (select pd.*,
lag(purchasedate) over (partition by customerid order by purchasedate) as prev_purchasedate
from purchasedetail pd
) pd
) pd
group by customerid, trackingperiod;https://stackoverflow.com/questions/48409847
复制相似问题