代码之家 › 专栏 › 技术社区 › praveen

购买前获取最后一个事件

sql-server

praveen · 技术社区 · 8 年前

有人能帮我查询一下购买前的最后一条记录吗? 数据集如下所示

UserID  EventType   ProductID   EventTime   Price   Campaign
123ABC  Click   P1  5/9/2018 2:33   NULL    C1
123ABC  Click   P1  5/10/2018 2:07  NULL    C1
123ABC  Click   P1  5/16/2018 2:14  NULL    C1
123ABC  Click   P2  5/9/2018 2:33   NULL    C1
123ABC  Click   P2  5/10/2018 2:07  NULL    C1
123ABC  Click   P2  5/16/2018 2:14  NULL    C1
123ABC  Purchase    P2  5/22/2018 4:11  19.44   NULL
123ABC  Click   P3  5/9/2018 2:33   NULL    C1
123ABC  Click   P3  5/10/2018 2:07  NULL    C1
123ABC  Click   P3  5/11/2018 15:57 NULL    C1
123ABC  Click   P3  5/16/2018 2:14  NULL    C1
123ABC  Purchase    P4  5/22/2018 4:11  31.44   NULL

输出

UserID  EventType   ProductID   EventTime       Price   Campaign
123ABC  Click        P2         5/16/2018 2:14  19.44   C1
123ABC  NoEvent      P4         5/22/2018 4:11  31.44   NULL

我需要在购买前找到最后一条点击记录即

  123ABC  Click   P2  5/16/2018 2:14  NULL    C1

如果在购买特定产品之前没有点击,那么只输出购买记录

123ABC  NoEvent      P4         5/22/2018 4:11  31.44   NULL

我试图通过下面的查询找到导致购买的记录组的序列例子

Select *, row_number() over(partition by UserId,ProductId order by EventTime) as Ordering from Purchase

UserID  EventType   ProductID   EventTime   Price   Campaign Ordering
123ABC  Click   P1  5/9/2018 2:33   NULL    C1 1
123ABC  Click   P1  5/10/2018 2:07  NULL    C1 2
123ABC  Click   P1  5/16/2018 2:14  NULL    C1 3 
123ABC  Click   P2  5/9/2018 2:33   NULL    C1 1
123ABC  Click   P2  5/10/2018 2:07  NULL    C1 2
123ABC  Click   P2  5/16/2018 2:14  NULL    C1 3
123ABC  Purchase    P2  5/22/2018 4:11  19.44   NULL 4
123ABC  Click   P3  5/9/2018 2:33   NULL    C1 1
123ABC  Click   P3  5/10/2018 2:07  NULL    C1 2
123ABC  Click   P3  5/11/2018 15:57 NULL    C1 3
123ABC  Click   P3  5/16/2018 2:14  NULL    C1 4
123ABC  Purchase    P4  5/22/2018 4:11  31.44   NULL 5

在上面的分组中,我只需要考虑那些在其中有购买的组,然后过滤数据。目前我坚持这种方法。有人能帮我解答这个问题吗

SQLFiddle

2 回复 | 直到 8 年前

MT-FreeHK 8 年前

create table temp (
  EventType varchar(10),
  ProductID varchar(10),
  Ordering int,
  Price int
);

insert into temp
Select EventType, ProductID,
row_number(), Price
over(partition by UserId,ProductId order by EventTime) as Ordering 
from Purchase;


select p.UserID, p.EventType, p.ProductID, p.EventTime, t.Price, p.Campaign from (
select ProductID, CASE WHEN Ordering = 1 THEN 1 ELSE Ordering -1 END as Ordering, Price
from temp t
where t.EventType = 'Purchase') a,
(
Select *,
row_number() 
over(partition by UserId,ProductId order by EventTime) as Ordering 
from Purchase
) p
where a.ProductID = p.ProductID
and a.Ordering = p.Ordering

当然,可以改进以减少一些语法…我认为不必创建新的临时表。另外,这是你想要的结果吗?

Amit Sukralia 8 年前

WITH CTE_PurchaseEvent AS
(SELECT 
  UserID,
  ProductId, 
  EventTime,
  ISNULL(lag(EventTime,1) over (PARTITION BY UserID, ProductId ORDER BY EventTime), '2000-01-01') AS PreviousPurchaseEventTime
FROM 
  Purchase
WHERE
  EventType = 'Purchase')  
,CTE_NonPurchaseEvent AS
(SELECT 
  Row_Number() OVER (PARTITION BY UserID, ProductId order by EventTime desc) as RowNum, 
  * 
FROM 
   Purchase  
WHERE
   EventType != 'Purchase')

SELECT 
  PurchaseEvent.UserId,
  PurchaseEvent.ProductId,
  PurchaseEvent.EventTime AS PurchaseTime,
  (SELECT MAX(p.EventTime) FROM CTE_NonPurchaseEvent P WHERE P.UserId = PurchaseEvent.UserId AND P.ProductId = PurchaseEvent.ProductId and 
   P.EventTime > PurchaseEvent.PreviousPurchaseEventTime AND P.EventTime < PurchaseEvent.EventTime) AS LastEventTimeBeforePurchase
FROM
  CTE_PurchaseEvent AS PurchaseEvent

这也将满足相同用户多次购买相同产品的情况。我添加了一些示例数据来测试查询。检查并让我知道它们是否是无效的场景。 SQL Fiddle