sql - 如果某人之前购买过相同的产品并且如果他购买过任何产品，则标记某人

Question

情况：

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

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

输出应该有 5 列：

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

原始数据如下所示：

[e-mail]   cucumber    01-02-2019
[e-mail]   orange      04-02-2019
[e-mail]   grapefruit  15-02-2019
[e-mail]   blackberry  06-02-2019
[e-mail]   lime        15-02-2019
[e-mail]   lime        20-02-2019
[e-mail]   apple       02-02-2019
[e-mail]   apple       18-02-2019
[e-mail]   orange      19-02-2019
[e-mail]   apple       28-02-2019

客观的：

我的输出看起来像这样：

Email           ProductName DatePurchased   SameProduct     AnyProduct
[e-mail]   cucumber    01-02-2019      0               0
[e-mail]   orange      04-02-2019      0               1
[e-mail]   grapefruit  15-02-2019      0               1
[e-mail]   blackberry  06-02-2019      0               0
[e-mail]   lime        15-02-2019      0               1
[e-mail]   lime        20-02-2019      1               1
[e-mail]   apple       02-02-2019      0               0   
[e-mail]   apple       18-02-2019      1               1   
[e-mail]   orange      19-02-2019      0               1
[e-mail]   apple       28-02-2019      1               1

我尝试了什么：我尝试了两次加入自身并使用案例语句，但我觉得这种方式效率极低。

虚拟数据：

create table #table1 (email varchar(20), productname varchar(20), datepurchased date)
insert into #table1 values
('[e-mail]','cucumber','2019-02-01'),
('[e-mail]','orange','2019-02-04'),
('[e-mail]','grapefruit','2019-02-15'),
('[e-mail]','blackberry','2019-02-06'),
('[e-mail]','lime','2019-02-15'),
('[e-mail]','lime','2019-02-20'),
('[e-mail]','apple','2019-02-02'),
('[e-mail]','apple','2019-02-18'),
('[e-mail]','orange','2019-02-19'),
('[e-mail]','apple','2019-02-28')

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

标签： sqlsql-server

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;

如果需要，可以将其转换为位以获得与使用 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;