mysql - Sql 查询进行 pandas 因式分解。 group by 后的累积和？

Question

我有这个数据框:基本上每一行都是一个客户一天执行的一笔交易。同一客户在同一天和不同日期进行多笔交易。我想获得一个列来显示客户之前访问的次数。

id  date   purchase 

id1 date1  $10    

id1 date1  $50    

id1 date2  $30     

id2 date1  $10     

id2 date1  $10     

id3 date3  $10     

添加访问列后:

id  date   purchase  visit

id1 date1  $10         0 

id1 date1  $50         0

id1 date2  $30         1

id2 date1  $10         0

id2 date2  $10         1

id2 date3  $10         2 

我在pandas中使用factorize来做到这一点:

df.visits = 1 
df.visits = df.groupby('id')['date'].transform(lambda x: pd.factorize(x)[0]) 

我想通过 SQL 来完成，查询会是什么样的？

Answer 1

您需要带有 PARTITION BY 的 DENSE_RANK():

创建示例数据集:

IF OBJECT_ID('Source', 'U') IS NOT NULL 
  DROP TABLE Source; 

CREATE TABLE Source
(
  id varchar(30),
  Date varchar(30),
  purchase varchar(30)
)

INSERT INTO Source
VALUES
('id1', 'date1', '$10'),   
('id1', 'date1', '$50'),   
('id1', 'date2', '$30'),    
('id2', 'date1', '$10'),   
('id2', 'date2', '$10'),  
('id2', 'date3', '$10')

<小时/>

SELECT *, 
  DENSE_RANK() OVER (PARTITION BY id ORDER BY date) - 1 AS visit
FROM Source

输出