sql - 避免 SQL 中的循环 - 在最后一个标记事件后至少 30 天为客户标记最早的事件

Question

我们的一项措施取决于在最后一次“标记”联系之后至少 30 天确定第一个联系客户的人，其中第一个联系始终被标记。例如，我们在 7 月 1 日首次获得客户 - 该员工获得联系“点数”。然后，客户在 7 月 10 日调用另一位工作人员 - 他们没有得到联系“点”。客户在 9 月 1 日(7 月 1 日最后一次标记联系人后 62 天)打电话，并与第三名工作人员通话 - 他们得到了一个联络点。最后，他们在 9 月 20 日与第四个人交谈 - 第四个人没有得分。

我们使用循环实现 SQL Server:

1. 标记第一个事件
2. 在最新标记事件后至少 30 天找到所有事件并最早“评分”
3. 重复第 2 步，直到找不到更多事件

我很想删除这个循环，因为它非常慢，一遍又一遍地读取表格，而且通常我更喜欢基于集合的操作而不是循环。我希望有人已经解决了这个问题并可以提供一些建议。

这是现在的样子:

DROP TABLE IF EXISTS #CUSTOMER_CONTACT
--Set up our table with the contact dates of each customer
--This is the "live" table in our system
CREATE TABLE #CUSTOMER_CONTACT (
                CUSTOMER_CODE       INT NOT NULL,
                EVENT_DATE          DATE NOT NULL,
                CONTACT_STAFF_CODE  VARCHAR(20) NOT NULL,
                FIRST_CONTACT_IN_30 INT NOT NULL,
                CONSTRAINT PK_#CUSTOMER_CONTACT PRIMARY KEY CLUSTERED ( CUSTOMER_CODE, EVENT_DATE )
                )

--Insert some dummy data for one customer over a few years
INSERT INTO #CUSTOMER_CONTACT( CUSTOMER_CODE, EVENT_DATE, CONTACT_STAFF_CODE, FIRST_CONTACT_IN_30 )
VALUES  ( 1, '2014-10-07', 'A', 0 ), 
        ( 1, '2014-12-01', 'A', 0 ), 
        ( 1, '2015-10-06', 'B', 0 ), 
        ( 1, '2015-10-07', 'C', 0 ), 
        ( 1, '2015-10-15', 'C', 0 ), 
        ( 1, '2015-11-10', 'C', 0 ), 
        ( 1, '2016-03-15', 'D', 0 ), 
        ( 1, '2016-03-18', 'A', 0 ), 
        ( 1, '2016-05-02', 'D', 0 ), 
        ( 1, '2016-09-09', 'D', 0 ), 
        ( 1, '2017-10-09', 'C', 0 ), 
        ( 1, '2017-10-10', 'B', 0 ), 
        ( 1, '2017-10-11', 'B', 0 ), 
        ( 1, '2017-10-20', 'D', 0 ), 
        ( 1, '2018-10-10', 'D', 0 ), 
        ( 1, '2019-03-07', 'D', 0 ), 
        ( 1, '2019-06-12', 'A', 0 ), 
        ( 1, '2019-08-02', 'A', 0 )

--Tag the very first contact per client so the staff person "A" gets a "point"
UPDATE  C
SET     C.FIRST_CONTACT_IN_30 = 1
FROM    #CUSTOMER_CONTACT AS C
            INNER JOIN  (
                        SELECT      CUSTOMER_CODE,
                                    MIN( EVENT_DATE ) AS FIRST_EVENT_DATE
                        FROM        #CUSTOMER_CONTACT
                        GROUP BY    CUSTOMER_CODE
                        ) AS M
                ON          M.CUSTOMER_CODE = C.CUSTOMER_CODE
                AND         M.FIRST_EVENT_DATE = C.EVENT_DATE

DROP TABLE IF EXISTS #NEXT_CONTACT_DATE
--This temp table will store the next event to be tagged
CREATE TABLE #NEXT_CONTACT_DATE (
                CUSTOMER_CODE   INT NOT NULL,
                EVENT_DATE      DATE NOT NULL,
                CONSTRAINT PK_#NEXT_CONTACT_DATE PRIMARY KEY CLUSTERED ( CUSTOMER_CODE, EVENT_DATE )
                )

--Just in case there is a flaw in my logic for the WHILE I'll use this to cap the iterations
DECLARE @I INT = 1

--For each customer, as long as I can find any record at least 30 days after the latest tagged records, keep
--on looping
WHILE EXISTS    (
                SELECT  *
                FROM    #CUSTOMER_CONTACT AS CC
                            INNER JOIN  (
                                        --Find the latest tagged event
                                        SELECT      CUSTOMER_CODE,
                                                    MAX( EVENT_DATE ) AS LATEST_CONTACT_DATE
                                        FROM        #CUSTOMER_CONTACT
                                        WHERE       FIRST_CONTACT_IN_30 = 1
                                        GROUP BY    CUSTOMER_CODE
                                        ) AS L
                                ON          L.CUSTOMER_CODE = CC.CUSTOMER_CODE
                                --Find any event at least 30 days after that
                                AND         DATEADD( DAY, 30, L.LATEST_CONTACT_DATE ) <= CC.EVENT_DATE
                ) AND
                --And make sure I haven't hit my iteration limit
                @I <= 100
BEGIN

    TRUNCATE TABLE #NEXT_CONTACT_DATE
    --Now store the earliest contact date for each customer that was at least 30 days
    --after the latest tagged event date
    INSERT INTO #NEXT_CONTACT_DATE( CUSTOMER_CODE, EVENT_DATE )
    SELECT      CC.CUSTOMER_CODE,
                MIN( CC.EVENT_DATE ) AS EVENT_DATE
    FROM        #CUSTOMER_CONTACT AS CC
                    INNER JOIN  (
                                SELECT      CUSTOMER_CODE,
                                            MAX( EVENT_DATE ) AS LATEST_CONTACT_DATE
                                FROM        #CUSTOMER_CONTACT
                                WHERE       FIRST_CONTACT_IN_30 = 1
                                GROUP BY    CUSTOMER_CODE
                                ) AS L
                        ON          L.CUSTOMER_CODE = CC.CUSTOMER_CODE
                        AND         DATEADD( DAY, 30, L.LATEST_CONTACT_DATE ) <= CC.EVENT_DATE
    GROUP BY    CC.CUSTOMER_CODE

    --Update the main table to tag that contact date to give a "point" to that staff member
    UPDATE  CC
    SET     CC.FIRST_CONTACT_IN_30 = 1
    FROM    #CUSTOMER_CONTACT AS CC
                INNER JOIN  #NEXT_CONTACT_DATE AS N
                    ON          N.CUSTOMER_CODE = CC.CUSTOMER_CODE
                    AND         N.EVENT_DATE = CC.EVENT_DATE

    --Don't forget to increment so my loop isn't endless
    SET @I = @I + 1

END

--Check the data at the end.
--It looks good to me
SELECT      CC.CUSTOMER_CODE,
            CC.EVENT_DATE,
            CC.CONTACT_STAFF_CODE,
            CC.FIRST_CONTACT_IN_30
FROM        #CUSTOMER_CONTACT AS CC

下表显示了结果。您可以看到前 3 个事件中的每一个都被标记为相隔 30 天，但第 4 个事件在第 3 个事件之后不到 30 天。事件 5 仍在事件 3 的 30 天内，但事件 6 现在距离最新标记的事件已超过 30 天，因此它也被标记。

CUSTOMER_CODE   EVENT_DATE  CONTACT_STAFF_CODE  FIRST_CONTACT_IN_30
1               07/10/2014  A                   1
1               01/12/2014  A                   1
1               06/10/2015  B                   1
1               07/10/2015  C                   0
1               15/10/2015  C                   0
1               10/11/2015  C                   1
1               15/03/2016  D                   1
1               18/03/2016  A                   0
1               02/05/2016  D                   1
1               09/09/2016  D                   1
1               09/10/2017  C                   1
1               10/10/2017  B                   0
1               11/10/2017  B                   0
1               20/10/2017  D                   0
1               10/10/2018  D                   1
1               07/03/2019  D                   1
1               12/06/2019  A                   1
1               02/08/2019  A                   1

您可以假设一位客户没有重复的联系日期，因此我不必担心标记两行。

我还没有把它从循环中去掉。我一直认为 ROW_NUMBER 或一些累积 SUM 有一些聪明之处，但我一直坚持这样一个事实，即每次标记事件时计数器都需要重置，这可能是上次标记事件后 31 天，或 365 天。

Answer 1

这个逻辑需要循环。如果您只是寻找 30 天的安静期，则不需要循环。但如果每天都有事件，请考虑并发症。

您可以使用递归 CTE 在数据库中 执行循环。这应该比在外部使用多个查询快得多。代码如下所示:

with cs as (
      select cc.*, row_number() over (partition by customer_code order by event_date) as seqnum
      from customer_contact cc
     ),
     cte as (
      select customer_code, event_date, contact_staff_code, seqnum, 1 as flag
      from cs
      where seqnum = 1
      union all
      select cte.customer_code, cs.event_date, cs.contact_staff_code, cs.seqnum,
             (case when cs.event_date > dateadd(day, 30, cte.event_date) then 1 else 0 end)
      from cte join
           cs
           on cs.customer_code = cte.customer_code and cs.seqnum = cte.seqnum + 1
     )
select *
from cte
order by customer_code, event_date;

Here是一个数据库<> fiddle 。