sql - 如果某人之前购买过相同的产品以及他是否购买过任何产品，请标记某人

Question

情况:

我需要添加两个这样标识的列标志:

• 此人是否在购买日期之前购买了相同产品。
• 此人是否在购买日期之前购买了任何其他产品。

输出应该有 5 列:

1. 电子邮件
2. 产品名称
3. 购买日期
4. 相同产品(0 = 否，1 = 是)
5. 任何产品(0 = 否，1 = 是)

原始数据如下所示:

abc@gmail.com   cucumber    01-02-2019
abc@gmail.com   orange      04-02-2019
abc@gmail.com   grapefruit  15-02-2019
cde@gmail.com   blackberry  06-02-2019
cde@gmail.com   lime        15-02-2019
cde@gmail.com   lime        20-02-2019
zzz@gmail.com   apple       02-02-2019
zzz@gmail.com   apple       18-02-2019
zzz@gmail.com   orange      19-02-2019
zzz@gmail.com   apple       28-02-2019

目标:

我的输出看起来像这样:

Email           ProductName DatePurchased   SameProduct     AnyProduct
abc@gmail.com   cucumber    01-02-2019      0               0
abc@gmail.com   orange      04-02-2019      0               1
abc@gmail.com   grapefruit  15-02-2019      0               1
cde@gmail.com   blackberry  06-02-2019      0               0
cde@gmail.com   lime        15-02-2019      0               1
cde@gmail.com   lime        20-02-2019      1               1
zzz@gmail.com   apple       02-02-2019      0               0   
zzz@gmail.com   apple       18-02-2019      1               1   
zzz@gmail.com   orange      19-02-2019      0               1
zzz@gmail.com   apple       28-02-2019      1               1

我尝试过的:我尝试了两次加入自身并使用案例语句，但我觉得这种方式非常低效。

虚拟数据:

create table #table1 (email varchar(20), productname varchar(20), datepurchased date)
insert into #table1 values
('abc@gmail.com','cucumber','2019-02-01'),
('abc@gmail.com','orange','2019-02-04'),
('abc@gmail.com','grapefruit','2019-02-15'),
('cde@gmail.com','blackberry','2019-02-06'),
('cde@gmail.com','lime','2019-02-15'),
('cde@gmail.com','lime','2019-02-20'),
('zzz@gmail.com','apple','2019-02-02'),
('zzz@gmail.com','apple','2019-02-18'),
('zzz@gmail.com','orange','2019-02-19'),
('zzz@gmail.com','apple','2019-02-28')

注意:我的实际数据有超过1亿行。我不确定哪种类型的查询会使数据处理尽可能快。

Answer 1

获取结果的另一种选择。

我使用 ROW_NUMBER()-1 以便我们可以为第一次出现的值赋予零值。然后我使用 SIGN() 将任何正值转换为 1。

SELECT *,
    SameProduct = SIGN(ROW_NUMBER() OVER(PARTITION BY email, productname ORDER BY datepurchased)-1),
    AnyProduct  = SIGN(ROW_NUMBER() OVER(PARTITION BY email ORDER BY datepurchased)-1)
FROM #table1
ORDER BY email, datepurchased;

如果需要，可以将其转换为 bit 以获得与使用 SIGN() 相同的结果，但只是在所有值为正的情况下。

SELECT *,
    SameProduct = CAST(ROW_NUMBER() OVER(PARTITION BY email, productname ORDER BY datepurchased)-1 AS bit),
    AnyProduct  = CAST(ROW_NUMBER() OVER(PARTITION BY email ORDER BY datepurchased)-1 AS bit)
FROM #table1
ORDER BY email, datepurchased;