SQL：使用分层（相关）客户评估新客户类型或现有客户类型

I need a more efficient way to evaluate if a customer is new or existing. Criteria for new customer:

我需要一种更有效的方法来评估客户是新的还是现有的。新客户的标准：

Customer, or any related customer, has not placed an order between begin date and end date
客户或任何相关客户尚未在开始日期和结束日期之间下订单

I wrote a function to do this, and it works, but it is very slow. We have hierarchical customers with child, parent,...ultimate parent. My function works by taking the ultimate parent of any given customer, selecting all of its children, and then evaluating if an order belonging to any of those children exists in the orders table between the begin date and end date.

我写了一个函数来做到这一点，它的工作原理，但它很慢。我们有分层的客户，包括孩子，父母，...终极父母。我的功能是通过获取任何给定客户的最终父母，选择其所有子项，然后评估属于任何这些子项的订单是否存在于开始日期和结束日期之间的订单表中。

The function returns 1 (True) for new customers or 0 (False) for existing customers.

该函数为新客户返回1（True）或为现有客户返回0（False）。

Is there a more efficient way to do this? My function took approximately 20 minutes to run when called on ~3k records.

有没有更有效的方法来做到这一点？在调用~3k记录时，我的功能大约需要20分钟。

Function:

功能：

set ansi_nulls on
go
set quoted_identifier on
go

create function dbo.is_new_customer (
@ultimate_parent_number nvarchar(255),
@existing_customer_begin_date date,
@existing_customer_end_date date
)
returns int
as
begin
declare @return int

set @return = (
    case
        when not exists (
            select
            t1.customer_number
            from orders_table as t1
            inner join (
                select
                customer_number
                from customers_table
                where ultimate_parent_number = @ultimate_parent_number
            ) as t2
            on t2.customer_number = t1.customer_number
            where t1.order_date between @existing_customer_begin_date
            and @existing_customer_end_date
        )
        then 1
        else 0
    end
)
return @return

end
go

1 个解决方案

#1

Try it like this using cross apply, also check for indexes, tell management studio to bring the actual execution plan, there you'll probably find recommendations for indexes here is how

像这样尝试使用交叉应用，也检查索引，告诉管理工作室带来实际的执行计划，那里你可能会找到索引的建议这里是如何

set ansi_nulls on
go
set quoted_identifier on
go

create function dbo.is_new_customer (
@ultimate_parent_number nvarchar(255),
@existing_customer_begin_date date,
@existing_customer_end_date date
)
returns int
as
begin
declare @return int

set @return = (
    case
        when not exists (
            select
            t1.customer_number
            from 
            (select
                customer_number
                from customers_table
                where ultimate_parent_number = @ultimate_parent_number) as t2

            cross apply ( select * from orders_table as t1 where t2.customer_number = t1.customer_number) as t1
            where t1.order_date between @existing_customer_begin_date
            and @existing_customer_end_date
        )
        then 1
        else 0
    end
)
return @return

end
go

This may also help you understand how to speed up that query When should I use Cross Apply over Inner Join?

这也可以帮助您了解如何加快查询速度何时应该使用Cross Apply over Inner Join？

#1