sql - 如果先前日期中存在新值，则计算重复项

Question

我有一个自动表“Customers_Status”，如果新客户注册了一项服务，他们将被标记为 1，否则为 0。但是，我想检查新客户是否曾经使用过他们用于激活服务的序列号对于其他客户，以及使用了多少次。

Customers_Status桌子：

ID      Date_Key      New_Customer      Serial_No
1       20200920           1             A12345
2       20200920           1             A123456
3       20200920           1             A1234567
4       20200920           1             A12345678
5       20200920           1             A123456789

我试图做一个自加入，它需要永远运行，我不确定它是否正确：

select 
t1.ID, t1.Date_Key, count(t1.New_Customer), t1.Serial_No, count(t2.Serial_No) as Serial_No_Usage_Count
from Customers_Status t1
left join (select * from Customers_Status t2 where Date_Key between 20200101 and 20200901) t2 on t1.Serial_No = t2.Serial_No
where t1.New_Customer = 1
and t1.Date_KEY = 20200920
group by t1.ID, t1.Date_Key, t1.Serial_No

输出应该类似于

ID      Date_Key      New_Customer      Serial_No            Serial_No_Used_Before       Serial_No_usage_count
1       20200920           1              A12345                   No                             0
2       20200920           1              A123456                  Yes                            12
3       20200920           1              A1234567                 Yes                            3
4       20200920           1              A12345678                No                             0
5       20200920           1              A123456789               Yes                            47

标签： sqloracle

Answer 1

您可以使用窗口函数：

select cs.*,
       (case when new_customer = 1 and
                  min(date_key) over (partition by Serial_No) < date_key
             then 'Yes' else 'No'
        end)
from customer_status cs;

如果您只想过滤新客户，请使用子查询：

select cs.*
from (select cs.*,
             (case when new_customer = 1 and
                        min(date_key) over (partition by Serial_No) < date_key
                   then 'Yes' else 'No'
              end) as flag
      from customer_status cs
     ) cs
where new_customer = 1;