Поиск самой последней группы клиентов

Ответ 1

Я хотел бы поблагодарить Джеффа Брэднера за DDL с образцовыми данными.

Вы должны будете выполнить нижеуказанный запрос шаг за шагом, CTE-by-CTE и изучить промежуточные результаты, чтобы понять, что он делает. Предполагается, что AcctNumber уникален в данной таблице.

Сначала я хочу найти последнюю учетную запись для каждого отдельного клиента. Это простой запрос на top-n-per-group и я использую здесь ROW_NUMBER.

CTE_Customers делает простой список всех индивидуальных клиентов, объединяя Cust1ID и Cust2ID. CTE_RN присваивает им номера строк. CTE_LatestAccounts дает CTE_LatestAccounts последнюю учетную запись для каждого отдельного клиента:

+------------------+------------+--------+
| LatestAcctNumber |  LatestDT  | CustID |
+------------------+------------+--------+
|            10000 | 2016-02-01 |   1110 |
|            10050 | 2017-02-01 |   1111 |
|            10052 | 2017-02-02 |   1112 |
|            10052 | 2017-02-02 |   1113 |
|             7060 | 2017-02-04 |   1114 |
|             7060 | 2017-02-04 |   1115 |
|            10004 | 2016-02-02 |   1116 |
|            10067 | 2017-02-05 |   1117 |
|            10054 | 2017-02-03 |   1118 |
|            10101 | 2017-06-02 |   1119 |
|            10058 | 2017-02-03 |   1120 |
|            10058 | 2017-02-03 |   1121 |
|            10007 | 2016-02-01 |   1122 |
|            10057 | 2017-02-03 |   1123 |
|            10107 | 2017-06-02 |   1124 |
|            10107 | 2017-06-02 |   1125 |
+------------------+------------+--------+

Задача осложняется наличием пар клиентов, которые "распространяют" последнюю учетную запись другому клиенту.

Партии клиентов определяются в исходной таблице, поэтому CTE_MaxLatestAccounts берет каждую строку из исходной таблицы и дважды присоединяет к ней последние учетные записи - для Cust1D и Cust2ID. Для каждой пары я выбираю одну из двух последних учетных записей - самую последнюю. Таким образом, клиент, принадлежащий паре, может получить учетную запись от своего партнера.

+---------+---------+-------------+---------------------+
| Cust1ID | Cust2ID | MaxLatestDT | MaxLatestAcctNumber |
+---------+---------+-------------+---------------------+
|    1110 | NULL    | 2016-02-01  |               10000 |
|    1111 | NULL    | 2017-02-01  |               10050 |
|    1111 | NULL    | 2017-02-01  |               10050 |
|    1120 | NULL    | 2017-02-03  |               10058 |
|    1120 | 1121    | 2017-02-03  |               10058 |
|    1112 | NULL    | 2017-02-02  |               10052 |
|    1113 | 1112    | 2017-02-02  |               10052 |
|    1114 | 1115    | 2017-02-04  |                7060 |
|    1115 | 1114    | 2017-02-04  |                7060 |
|    1116 | 1117    | 2017-02-05  |               10067 |
|    1117 | NULL    | 2017-02-05  |               10067 |
|    1118 | NULL    | 2017-02-03  |               10054 |
|    1118 | 1119    | 2017-06-02  |               10101 |
|    1119 | NULL    | 2017-06-02  |               10101 |
|    1122 | 1123    | 2017-02-03  |               10057 |
|    1123 | 1124    | 2017-06-02  |               10107 |
|    1124 | 1125    | 2017-06-02  |               10107 |
+---------+---------+-------------+---------------------+

MaxLatestAcctNumber здесь применим как к Cust1ID и к Cust2ID. Один и тот же клиент может быть указан здесь несколько раз, и нам нужно снова выбрать запись с последней учетной записью. Вот это последняя учетная запись пары, а не для отдельного клиента.

Подход такой же, как и в начале. Поместите оба Cust1ID и Cust2ID в список: CTE_CustomersWithLatestAccountFromPair. Назначьте номера строк в CTE_CustomersWithLatestAccountFromPairRN и выберите финальную учетную запись в CTE_FinalAccounts.

+---------------------+
| MaxLatestAcctNumber |
+---------------------+
|               10000 |
|               10050 |
|               10052 |
|               10052 |
|                7060 |
|                7060 |
|               10067 |
|               10067 |
|               10101 |
|               10101 |
|               10058 |
|               10058 |
|               10057 |
|               10107 |
|               10107 |
|               10107 |
+---------------------+

Теперь нам просто нужно отфильтровать исходную таблицу и оставить только те строки (учетные записи), которые отображаются в этом списке. См. Окончательный результат ниже.

Пример данных

declare @ACCT table (
    AcctNumber int,
    dt date,
    Cust1ID int,
    Cust2ID int
);

insert into @ACCT values 
(10000, '2016-02-01', 1110, null),
(10001, '2016-02-01', 1111, null),
(10050, '2017-02-01', 1111, null),
(10008, '2016-02-01', 1120, null),
(10058, '2017-02-03', 1120, 1121),
(10002, '2016-02-01', 1112, null),
(10052, '2017-02-02', 1113, 1112),
(10003, '2016-02-02', 1114, 1115),
(7060,  '2017-02-04', 1115, 1114),
(10004, '2016-02-02', 1116, 1117),
(10067, '2017-02-05', 1117, null),
(10005, '2016-02-01', 1118, null),
(10054, '2017-02-03', 1118, 1119),
(10101, '2017-06-02', 1119, null),
(10007, '2016-02-01', 1122, 1123),
(10057, '2017-02-03', 1123, 1124),
(10107, '2017-06-02', 1124, 1125);

запрос

WITH
CTE_Customers
AS
(
    SELECT
        AcctNumber
        ,dt
        ,Cust1ID AS CustID
    FROM @ACCT
    WHERE Cust1ID IS NOT NULL

    UNION ALL

    SELECT
        AcctNumber
        ,dt
        ,Cust2ID AS CustID
    FROM @ACCT
    WHERE Cust2ID IS NOT NULL
)
,CTE_RN
AS
(
    SELECT
        AcctNumber
        ,dt
        ,CustID
        ,ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY dt DESC) AS rn
    FROM CTE_Customers
)
,CTE_LatestAccounts
-- this gives one row per CustID
AS
(
    SELECT
        AcctNumber AS LatestAcctNumber
        ,dt AS LatestDT
        ,CustID
    FROM CTE_RN
    WHERE rn = 1
)
,CTE_MaxLatestAccounts
AS
(
    SELECT
        A.Cust1ID
        ,A.Cust2ID
        ,CASE WHEN ISNULL(A1.LatestDT, '2000-01-01') > ISNULL(A2.LatestDT, '2000-01-01')
        THEN A1.LatestDT ELSE A2.LatestDT END AS MaxLatestDT
        ,CASE WHEN ISNULL(A1.LatestDT, '2000-01-01') > ISNULL(A2.LatestDT, '2000-01-01')
        THEN A1.LatestAcctNumber ELSE A2.LatestAcctNumber END AS MaxLatestAcctNumber
    FROM
        @ACCT AS A
        LEFT JOIN CTE_LatestAccounts AS A1 ON A1.CustID = A.Cust1ID
        LEFT JOIN CTE_LatestAccounts AS A2 ON A2.CustID = A.Cust2ID
)
,CTE_CustomersWithLatestAccountFromPair
AS
(
    SELECT
        Cust1ID AS CustID
        ,MaxLatestDT
        ,MaxLatestAcctNumber
    FROM CTE_MaxLatestAccounts
    WHERE Cust1ID IS NOT NULL

    UNION ALL

    SELECT
        Cust2ID AS CustID
        ,MaxLatestDT
        ,MaxLatestAcctNumber
    FROM CTE_MaxLatestAccounts
    WHERE Cust2ID IS NOT NULL
)
,CTE_CustomersWithLatestAccountFromPairRN
AS
(
    SELECT
        CustID
        ,MaxLatestDT
        ,MaxLatestAcctNumber
        ,ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY MaxLatestDT DESC) AS rn
    FROM CTE_CustomersWithLatestAccountFromPair
)
,CTE_FinalAccounts
AS
(
    SELECT MaxLatestAcctNumber
    FROM CTE_CustomersWithLatestAccountFromPairRN
    WHERE rn = 1
)
SELECT *
FROM @ACCT AS A
WHERE A.AcctNumber IN (SELECT MaxLatestAcctNumber FROM CTE_FinalAccounts)
;

результат

+------------+------------+---------+---------+
| AcctNumber |     dt     | Cust1ID | Cust2ID |
+------------+------------+---------+---------+
|      10000 | 2016-02-01 |    1110 | NULL    |
|      10050 | 2017-02-01 |    1111 | NULL    |
|      10058 | 2017-02-03 |    1120 | 1121    |
|      10052 | 2017-02-02 |    1113 | 1112    |
|       7060 | 2017-02-04 |    1115 | 1114    |
|      10067 | 2017-02-05 |    1117 | NULL    |
|      10101 | 2017-06-02 |    1119 | NULL    |
|      10057 | 2017-02-03 |    1123 | 1124    |
|      10107 | 2017-06-02 |    1124 | 1125    |
+------------+------------+---------+---------+

Этот результат соответствует вашему желаемому результату, за исключением последнего случая 7.

Мой запрос не пытается следовать цепочке связанных клиентов произвольной длины и ограничивается обработкой одной пары за раз. Поэтому результат 7 не является одной строкой. Запрос всегда будет выбирать строку/учетную запись с самой последней датой (10107), а также может выбирать учетные записи в середине цепочки. В этом случае он выбрал строку 10057, а не 10007, потому что это более поздняя учетная запись для клиентов 1122 и 1123.

Когда я посмотрел план выполнения, я увидел, что запрос за CTE_LatestAccounts выполняется четыре раза.

Вероятно, если вы сохраните результат CTE_LatestAccounts в таблице temp с соответствующими индексами, то общая производительность будет лучше.

Что-то вроде этого:

DECLARE @LatestAccounts TABLE 
    (LatestAcctNumber int, LatestDT date, CustID int PRIMARY KEY);

WITH
CTE_Customers
AS
(
    SELECT
        AcctNumber
        ,dt
        ,Cust1ID AS CustID
    FROM @ACCT
    WHERE Cust1ID IS NOT NULL

    UNION ALL

    SELECT
        AcctNumber
        ,dt
        ,Cust2ID AS CustID
    FROM @ACCT
    WHERE Cust2ID IS NOT NULL
)
,CTE_RN
AS
(
    SELECT
        AcctNumber
        ,dt
        ,CustID
        ,ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY dt DESC) AS rn
    FROM CTE_Customers
)
,CTE_LatestAccounts
-- this gives one row per CustID
AS
(
    SELECT
        AcctNumber AS LatestAcctNumber
        ,dt AS LatestDT
        ,CustID
    FROM CTE_RN
    WHERE rn = 1
)
INSERT INTO @LatestAccounts (LatestAcctNumber, LatestDT, CustID)
SELECT LatestAcctNumber, LatestDT, CustID
FROM CTE_LatestAccounts;


WITH
CTE_MaxLatestAccounts
AS
(
    SELECT
        A.Cust1ID
        ,A.Cust2ID
        ,CASE WHEN ISNULL(A1.LatestDT, '2000-01-01') > ISNULL(A2.LatestDT, '2000-01-01')
        THEN A1.LatestDT ELSE A2.LatestDT END AS MaxLatestDT
        ,CASE WHEN ISNULL(A1.LatestDT, '2000-01-01') > ISNULL(A2.LatestDT, '2000-01-01')
        THEN A1.LatestAcctNumber ELSE A2.LatestAcctNumber END AS MaxLatestAcctNumber
    FROM
        @ACCT AS A
        LEFT JOIN @LatestAccounts AS A1 ON A1.CustID = A.Cust1ID
        LEFT JOIN @LatestAccounts AS A2 ON A2.CustID = A.Cust2ID
)
,CTE_CustomersWithLatestAccountFromPair
AS
(
    SELECT
        Cust1ID AS CustID
        ,MaxLatestDT
        ,MaxLatestAcctNumber
    FROM CTE_MaxLatestAccounts
    WHERE Cust1ID IS NOT NULL

    UNION ALL

    SELECT
        Cust2ID AS CustID
        ,MaxLatestDT
        ,MaxLatestAcctNumber
    FROM CTE_MaxLatestAccounts
    WHERE Cust2ID IS NOT NULL
)
,CTE_CustomersWithLatestAccountFromPairRN
AS
(
    SELECT
        CustID
        ,MaxLatestDT
        ,MaxLatestAcctNumber
        ,ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY MaxLatestDT DESC) AS rn
    FROM CTE_CustomersWithLatestAccountFromPair
)
,CTE_FinalAccounts
AS
(
    SELECT MaxLatestAcctNumber
    FROM CTE_CustomersWithLatestAccountFromPairRN
    WHERE rn = 1
)
SELECT *
FROM @ACCT AS A
WHERE A.AcctNumber IN (SELECT MaxLatestAcctNumber FROM CTE_FinalAccounts)
;

Если вам действительно необходимо объединить/сгруппировать всех связанных клиентов в одну строку, когда длина цепочки произвольная, вы можете сделать это с помощью рекурсивного запроса, например, показанного здесь: Как найти все связанные подграфы неориентированного графа

После того как вы пометили каждого клиента некоторым идентификатором GroupID, найдите последнюю учетную запись для каждого отдельного клиента, как в начале этого запроса. Затем найдите последнюю учетную запись среди группы (а не для простой пары, как в этом запросе).

Запрос, который находит все подграфы неориентированного графа в связанном вопросе, может быть довольно медленным для большого набора данных, и для этого есть эффективные алгоритмы без установки.

Если вы знаете, что максимальная длина цепочки не может превышать некоторого числа, можно сделать этот рекурсивный запрос более эффективным.

Ответ 2

Чтобы применить логику к каждому подмножеству, хорошим оператором для использования является оператор CROSS APPLY. Это позволяет нам найти самую последнюю учетную запись для каждого идентификатора клиента.

Настроить

DECLARE @Stage TABLE
(
    AcctNumber INT
    ,[Date] DATETIME
    ,Cust1Id INT
   ,Cust2Id INT
)

INSERT INTO @Stage (AcctNumber, [Date] ,Cust1Id ,Cust2Id)
VALUES
(10000,'2.1.16',1110,NULL)
,(10001,'2.1.16',1111,NULL)
,(10050,'2.1.17',1111,NULL)
,(10008,'2.1.16',1120,NULL)
,(10058,'2.3.17',1120,1121)
,(10002,'2.1.16',1112,NULL)
,(10052,'2.2.17',1113,1112)
,(10003,'2.2.16',1114,1115)
,(7060,'2.4.17',1115,1114)
,(10004,'2.2.16',1116,1117)
,(10067,'2.5.17',1117,NULL)
,(10005,'2.1.16',1118,NULL)
,(10054,'2.3.17',1118,1119)
,(10101,'6.2.17',1119,NULL)
,(10007,'2.1.16',1122,1123)
,(10057,'2.3.17',1123,1124)
,(10107,'6.2.17',1124,1125)

--Additional Cases to cover
,(50001, '2016-01-01', 2001, NULL)
,(50002, '2017-02-02', 2001, 2002)
,(50003, '2017-03-03', 2001, NULL)
,(50004, '2017-04-04', 2002, NULL)

,(50005, '2016-01-01', 2003, NULL)
,(50006, '2017-02-02', 2003, 2004)
,(50007, '2017-03-03', 2004, NULL)
,(50008, '2017-04-04', 2003, NULL)

выполнение

Крест

;WITH Results AS(
    SELECT DISTINCT S2.*
    FROM @Stage S1
    CROSS APPLY (
        SELECT TOP 1 S2.*
        FROM @Stage S2
        WHERE 
            (S1.Cust1Id = S2.Cust1Id
            OR S1.Cust1Id = S2.Cust2Id
            OR S1.Cust2Id = S2.Cust1Id
            OR S1.Cust2Id = S2.Cust2Id)
        ORDER BY S2.[Date] DESC
            ) S2
)
SELECT R1.*
FROM Results R1
    LEFT JOIN Results R2
        ON R1.Cust2Id = R2.Cust1Id
WHERE R1.[Date] > R2.[Date]
    OR R2.AcctNumber IS NULL

Операторы CROSS APPLY случаи назад для применения логики к каждому совместному делу, при этом последняя учетная запись переносится. Это само по себе охватывает большинство случаев. Единственными затяжными случаями являются те, у которых три счета смещены между 3 клиентами. Предложение self join и WHERE в окончательном выборе охватывают их.

Результаты

+------------+------------+---------+---------+
| AcctNumber | Date       | Cust1Id | Cust2Id |
| 7060       | 2017-02-04 | 1115    | 1114    |
| 10000      | 2016-02-01 | 1110    | NULL    |
| 10050      | 2017-02-01 | 1111    | NULL    |
| 10052      | 2017-02-02 | 1113    | 1112    |
| 10058      | 2017-02-03 | 1120    | 1121    |
| 10067      | 2017-02-05 | 1117    | NULL    |
| 10101      | 2017-06-02 | 1119    | NULL    |
| 10107      | 2017-06-02 | 1124    | 1125    |
| 50003      | 2017-03-03 | 2001    | NULL    |
| 50004      | 2017-04-04 | 2002    | NULL    |
| 50007      | 2017-03-03 | 2004    | NULL    |
| 50008      | 2017-04-04 | 2003    | NULL    |
+------------+------------+---------+---------+

Ответ 3

Я уверен, что есть намного более простой подход, но это то, что я имел в виду:

SELECT 
    a.acctnumber, 
    a.date, 
    a.Cust1ID, 
    a.Cust2ID 
FROM acct a
OUTER APPLY (
SELECT acctnumber
FROM (
SELECT *, 
    ROW_NUMBER() OVER(PARTITION BY acctnumber ORDER BY [date] DESC) AS ACC_RN,
    ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY [date] DESC) AS RN
FROM (
SELECT 
     a1.acctnumber,
     a1.[date],
     a1.Cust1ID AS CustomerID
FROM acct a1
UNION 
SELECT 
     a2.acctnumber,
     a2.[date],
     a2.Cust2ID
FROM acct a2
) D
) C
WHERE 
    RN = 1
AND CustomerID IS NOT NULL
AND ACC_RN = 2
) acc
WHERE a.acctnumber IN(acc.acctnumber)

Ответ 4

Не могли бы вы просто использовать соединение слева для присоединения к учетным записям с другими "связанными" учетными записями с потенциально более поздними датами, а затем просто отфильтровывать записи, где таблица "Более поздняя учетная запись" не равна нулю? Что-то вроде этого:

select ThisAccount.* 
from Accounts ThisAccount
left join Accounts LaterAccount on
    LaterAccount.AcctNumber <> ThisAccount.AcctNumber
    and LaterAccount.dt > ThisAccount.dt
    and
    (   LaterAccount.Cust1ID = ThisAccount.Cust1ID
        or LaterAccount.Cust2ID = ThisAccount.Cust1ID
        or LaterAccount.Cust1ID = ThisAccount.Cust2ID
        or LaterAccount.Cust2ID = ThisAccount.Cust2ID
    )
where LaterAccount.AcctNumber is null
order by ThisAccount.AcctNumber

Это должно вернуть результаты, как ожидалось:

AcctNo  Dt          Cust1   Cust2
7060    2017-02-04  1115    1114
10000   2016-02-01  1110    NULL
10050   2017-02-01  1111    NULL
10052   2017-02-02  1113    1112
10058   2017-02-03  1120    1121
10067   2017-02-05  1117    NULL
10101   2017-06-02  1119    NULL
10107   2017-06-02  1124    1125
50003   2017-03-03  2001    NULL
50004   2017-04-04  2002    NULL
50007   2017-03-03  2004    NULL
50008   2017-04-04  2003    NULL

Ответ 5

Мой ответ неправильный, извините за публикацию преждевременно. Я работаю над другой идеей, я скоро вернусь.

Оригинальный ответ:

Предполагая, что формат даты MM.DD.YY, у меня есть код, как показано ниже. Я не понимаю, почему ваш желаемый набор результатов не включает строки для CustID 1116 или 1118, но я вижу, как их включение будет дублировать 1117 и 1119 соответственно, если исходные данные не будут изменены, чтобы удалить эти повторяющиеся значения 1117 и 1119 из результаты. На данный момент у меня есть это временное решение, до вашего ответа.

declare @ACCT table (
  acctnumber int,
  date date,
  Cust1ID int,
  Cust2ID int
);

insert into @ACCT values (10000, '2016-02-01', 1110, null);
insert into @ACCT values (10001, '2016-02-01', 1111, null);
insert into @ACCT values (10050, '2017-02-01', 1111, null);
insert into @ACCT values (10008, '2016-02-01', 1120, null);
insert into @ACCT values (10058, '2017-02-03', 1120, 1121);
insert into @ACCT values (10002, '2016-02-01', 1112, null);
insert into @ACCT values (10052, '2017-02-02', 1113, 1112);
insert into @ACCT values (10003, '2016-02-02', 1114, 1115);
insert into @ACCT values (7060,  '2017-02-04', 1115, 1114);
insert into @ACCT values (10004, '2016-02-02', 1116, 1117);
insert into @ACCT values (10067, '2017-02-05', 1117, null);
insert into @ACCT values (10005, '2016-02-01', 1118, null);
insert into @ACCT values (10054, '2017-02-03', 1118, 1119);
insert into @ACCT values (10101, '2017-06-02', 1119, null);
insert into @ACCT values (10007, '2016-02-01', 1122, 1123);
insert into @ACCT values (10057, '2017-02-03', 1123, 1124);
insert into @ACCT values (10107, '2017-06-02', 1124, 1125);

with

OneCustId as (
select
  acctnumber,[date], Cust1ID as CustID
from
  @ACCT

union

select
  acctnumber, [date], Cust2ID
from
  @ACCT
),

SortedByLastUsage as (
select
  acctnumber, [date], CustID, row_number() over (partition by CustID order by [date] desc) as RowID
from
  OneCustId
),

LastUsage as (
select
  acctnumber, [date], CustID
from
  SortedByLastUsage
where
  RowID = 1
)

select distinct
  ACCT.acctnumber, ACCT.[date], ACCT.Cust1ID, ACCT.Cust2ID
from
  @ACCT ACCT
  inner join LastUsage on
    ACCT.acctnumber = LastUsage.acctnumber and
    ACCT.[date] = LastUsage.[date] and
    LastUsage.CustID in (ACCT.Cust1ID, ACCT.Cust2ID)
order by
  Cust1ID, Cust2ID

Результат:

acctnumber  date    Cust1ID Cust2ID
10000   2016-02-01  1110    NULL
10050   2017-02-01  1111    NULL
10052   2017-02-02  1113    1112
7060    2017-02-04  1115    1114
10004   2016-02-02  1116    1117
10067   2017-02-05  1117    NULL
10054   2017-02-03  1118    1119
10101   2017-06-02  1119    NULL
10058   2017-02-03  1120    1121
10007   2016-02-01  1122    1123
10057   2017-02-03  1123    1124
10107   2017-06-02  1124    1125

Ответ 6

Я оставляю свой первоначальный ответ на месте, потому что подход может работать для кого-то другого, ищущего это по линии.

Я не могу понять, как это сделать без курсора. Таким образом, любой другой ответ, который дает правильный ответ (который не использует курсор), будет превзойти этот. Я недостаточно умен, чтобы понять, как это выглядит, но это должно было бы включать неприятный рекурсивный CTE.

Настоящий трюк - это получение всех учетных записей, которые когда-либо были связаны друг с другом, сгруппированные вместе. Это делается в большой курсовой цепочке if/then/else вверху, которую можно немного очистить. Я оставил свои отладочные заявления print на месте, они, очевидно, могут быть удалены.

Вы также можете сделать таблицу Ассоциаций постоянной, вместо использования переменной таблицы.

Опять же, по производительности, это будет действительно, очень плохо, но это действительно работает. Я с нетерпением жду встречи с другими. Спасибо за качественный вопрос, который сделал жизнь намного проще.

Код:

declare @Associations table (
  GroupID int,
  CustID int
);

declare @NextGroupID int = 0;
declare @FoundGroup1ID int;
declare @FoundGroup2ID int;
declare @Cust1 int;
declare @Cust2 int;

declare db_cursor cursor for
select Cust1ID, Cust2ID from @ACCT;

open db_cursor;
fetch next from db_cursor into @Cust1, @Cust2;

while @@fetch_status = 0
begin
  set @FoundGroup1ID = null;
  set @FoundGroup2ID = null;
  print '----------------------------'
  print 'Cust1 = ' + isnull(cast(@Cust1 as varchar(max)), 'NULL')
  print 'Cust2 = ' + isnull(cast(@Cust2 as varchar(max)), 'NULL')

  select @FoundGroup1ID = GroupID from @Associations where CustID = @Cust1
  print 'FoundGroup1ID = ' + isnull(cast(@FoundGroup1ID as varchar(max)), 'NULL')

  if @Cust2 is null
  begin
    if @FoundGroup1ID is null 
    begin
      set @NextGroupID = @NextGroupID +1
      print 'Adding Cust1 to new group ' + cast(@NextGroupID as varchar(max))
      insert into @Associations (GroupID, CustID) values (@NextGroupID, @Cust1)
    end
  end 
  else -- @Cust2 is not null
  begin

    print 'FoundGroup2ID = ' + isnull(cast(@FoundGroup2ID as varchar(max)), 'NULL')
    select @FoundGroup2ID = GroupID from @Associations where CustID = @Cust2

    if @FoundGroup1ID is null and @FoundGroup2ID is null
    begin
      set @NextGroupID = @NextGroupID +1
      print 'Adding both to new group ' + cast(@NextGroupID as varchar(max))
      insert into @Associations (GroupID, CustID) values (@NextGroupID, @Cust1)
      insert into @Associations (GroupID, CustID) values (@NextGroupID, @Cust2)
    end 
    else if @FoundGroup1ID is not null and @FoundGroup2ID is null
    begin
      print 'Adding Cust2 to existing group ' + cast(@FoundGroup1ID as varchar(max))
      insert into @Associations (GroupID, CustID) values (@FoundGroup1ID, @Cust2)
    end
    else if @FoundGroup1ID is null and @FoundGroup2ID is not null
    begin
      print 'Adding Cust1 to existing group ' + cast(@FoundGroup2ID as varchar(max))
      insert into @Associations (GroupID, CustID) values (@FoundGroup2ID, @Cust1)
    end
    else -- Neither is null
    begin
      print 'Switching all of GroupID ' + cast(@FoundGroup2ID as varchar(max)) + ' to GroupID ' + cast(@FoundGroup1ID as varchar(max))
      update @Associations set GroupID = @FoundGroup1ID where GroupID = @FoundGroup2ID
    end
  end
  fetch next from db_cursor into @Cust1, @Cust2;
end
close db_cursor;
deallocate db_cursor;

;with

AddedGroupID as (
select
  ACCT.acctnumber,
  ACCT.[date],
  ACCT.Cust1ID,
  ACCT.Cust2ID,
  Associations.GroupID,
  row_number() over (partition by Associations.GroupID order by ACCT.[date] desc) as RowID
from
  @ACCT ACCT
  inner join @Associations Associations on
    Associations.CustID in (ACCT.Cust1ID, ACCT.Cust2ID)
)

select 
  acctnumber, [date], Cust1ID, Cust2ID
from 
  AddedGroupID
where
  RowID = 1

Результаты:

acctnumber  date    Cust1ID Cust2ID
10000   2016-02-01  1110    NULL
10050   2017-02-01  1111    NULL
10058   2017-02-03  1120    1121
10052   2017-02-02  1113    1112
7060    2017-02-04  1115    1114
10067   2017-02-05  1117    NULL
10101   2017-06-02  1119    NULL
10107   2017-06-02  1124    1125

Ответ 7

мы не должны беспокоиться об использовании EXISTS, поскольку он работает быстро в таком случае, и я предполагаю, что это самое простое решение:

SELECT
    A.ACCTNUMBER, A.DT as "date", A.CUST1ID, A.CUST2ID
FROM
    ACCT A
WHERE
        NOT EXISTS
        (SELECT
            *
        FROM
            ACCT A2
        WHERE
            (A2.CUST1ID = A.CUST1ID
            OR A2.CUST2ID = A.CUST1ID
            OR (A.CUST2ID IS NOT NULL AND A2.CUST1ID = A.CUST2ID)
            OR (A.CUST2ID IS NOT NULL AND A2.CUST2ID = A.CUST2ID)
            )
            AND A2.DT>A.DT
        )

Я предположил, что у вас есть отдельные индексы на CUST1ID, а другой - на CUST2ID. Вы можете сравнить результат без восходящего индекса в поле DT ("дата") и с ним. Это может ускорить ваш запрос или замедлить работу - я не знаю, как выглядят ваши реальные данные

Ответ 8

Попробуйте выполнить запрос ниже. Он длительный, потому что необходимо применять многократно оконные функции (вы не можете вложить их в один запрос), но сам запрос довольно прост. Основная идея состоит в том, чтобы разделить клиентов, которые никогда не обмениваются аккаунтами с клиентами, которые это делают. После этого для клиента с одной учетной записью столбец группировки прост, он Cust1ID, но для других вам нужно выполнить некоторые операции, описанные ниже, для получения столбца группировки:

Чтобы получить столбец группировки (для многоблочных счетчиков), вы должны применить следующую логику:

Поместите всех 1-го клиентов вместе со cte клиентами в том же столбце, используя UNION ALL (CTE, называемый cte в запросе). Затем, когда вы сортируете по этому столбцу и проверяете оба идентификатора с идентификаторами следующей строки, вы можете проверить, являются ли они "подключенными", то есть они имеют по крайней мере один идентификатор:

case when Cust1ID in (cust1idLead, cust2idLead) or Cust2ID in (cust1idLead, cust2idLead) then 1 else 0 end SameGroup

Таким образом, вы можете разделить группы и внутри этих групп взять максимум соответственно до даты (столбец dt).

Пример данных:

declare @tbl table (acctnumber int, dt date ,   Cust1ID int,    Cust2ID  int);
insert into @tbl values
(10000, '2.1.16', 1110, null),
(10001, '2.1.16', 1111, null),
(10050, '2.1.17', 1111, null),
(10008, '2.1.16', 1120, null),
(10058, '2.3.17', 1120, 1121),
(10002, '2.1.16', 1112, null),
(10052, '2.2.17', 1113, 1112),
(10003, '2.2.16', 1114, 1115),
(7060, '2.4.17', 1115, 1114),
(10004, '2.2.16', 1116, 1117),
(10067, '2.5.17', 1117, null),
(10005, '2.1.16', 1118, null),
(10054, '2.3.17', 1118, 1119),
(10101, '6.2.17', 1119, null),
(10007, '2.1.16', 1122, 1123),
(10057, '2.3.17', 1123, 1124),
(10107, '6.2.17', 1124, 1125)

T-SQL:

;with SingleAccounts as (
    select cust1id from @tbl
    where Cust2ID is null
    except
    select cust1id from @tbl
    where Cust2ID is not null
    except
    select cust2id from @tbl
), cte as (
    select  acctnumber, dt, Cust1ID, Cust2ID from @tbl
    where Cust1ID not in (select Cust1ID from SingleAccounts)
    union all
    select  acctnumber, dt, Cust2ID, Cust1ID from @tbl
    where Cust1ID not in (select Cust1ID from SingleAccounts) and Cust2ID is not null
), SingleAmountsResult as (
    select acctnumber, dt, cust1id, cust2id,
           ROW_NUMBER() over (partition by cust1id order by dt desc) rn 
    from @tbl 
    where cust1id in (select Cust1ID from SingleAccounts)
), FinalResult as (
    select acctnumber, dt, cust1id, cust2id from SingleAmountsResult
    where rn = 1
    union all
    select acctnumber, dt, cust1id, cust2id
    from (
        select acctnumber, dt, cust1id, cust2id,
               ROW_NUMBER() over (partition by GroupingColumn order by dt desc) rn
        from (
            select acctnumber, dt, cust1id, cust2id,
                   SUM(NewGroup) over (order by cust1id, cust2id) GroupingColumn
            from (
                select acctnumber, dt, cust1id, cust2id,
                       case when LAG(SameGroup) over (order by cust1id, cust2id) = 0 then 1 else 0 end NewGroup
                from (
                    select acctnumber, dt, cust1id, cust2id,
                           case when Cust1ID in (cust1idLead, cust2idLead) or Cust2ID in (cust1idLead, cust2idLead) then 1 else 0 end SameGroup
                    from (
                        select acctnumber, dt, cust1id, cust2id,
                               LEAD(cust1id) over (order by cust1id, cust2id) cust1idLead,
                               LEAD(cust2id) over (order by cust1id, cust2id) cust2idLead
                        from cte
                    ) a 
                ) a
            ) a 
        ) a 
    ) a where rn = 1
)

--this final query gets you correct Cust1ID and Cust2ID, as FinalResult might have them switched
select * from @tbl
intersect
select * from (
    select acctnumber, dt, cust1id, cust2id from FinalResult
    union all
    select acctnumber, dt, cust2id, cust1id from FinalResult
) fr

ОБНОВИТЬ

Этот код, в соответствии с объяснением OP, относится ко всем идентификаторам клиентов, которые когда-либо были на одной учетной записи вместе, как одна и та же группа (и это переходный ¹⁾), таким образом, для дополнительных случаев 8a и 8b результаты:

acctnumber | dt         | Cust1ID | Cust2ID
50004      | 2017-04-04 | 2002    | NULL
50008      | 2017-04-04 | 2003    | NULL 

так как будет только две группы!

¹⁾ это означает, что если элемент a находится в группе с элементом b и b находится в той же группе, что и элемент c то это означает, что a и c также находятся в одной группе.

Ответ 9

Это довольно сложно...

Сначала вы хотите определить группы клиентов. Это все клиенты, которые были прямо или косвенно связаны. С парными потребителями A/B, B/C, D/E, D/F, G/A, H/A, H/F у вас будет только одна группа. В SQL это требует рекурсивного запроса.

SQL Server не хватает обнаружения циклов в рекурсивных запросах. Таким образом, от клиентов A/B вы попадаете ко всем парам, содержащим A или B, которые являются B/C, A/BG/A, H/A и A/B сами по себе. Даже если мы обнаружим этот прямой круг (ту же пару), мы продолжим с B/C, ищем все записи, содержащие B или C. И один из них снова A/B, и еще раз мы находимся в цикле. Один из способов борьбы с этим - создать цепочку клиентов, которые еще не были посещены, и не посещать их снова.

Наш результат - все производители, которые прямо или косвенно связаны с другими клиентами. Используя агрегацию, мы можем взять минимального партнера для каждого клиента и использовать его в качестве группового ключа. В приведенном выше примере все клиенты связаны с A, поэтому A является их минимальным партнером, показывая, что все принадлежат к одной группе. Если добавить две записи X/Y и Z / -, то мы имеем еще две группы: X и Y, принадлежащие X группе, и Z, принадлежащие группе Z.

Эти группы мы используем, чтобы снова искать наши оригинальные записи. С ROW_NUMBER мы ROW_NUMBER каждую последнюю запись группы с №1. Тогда мы сохраняем только те, и мы закончили.

with all_cust(custid) as
(
  select cust1id from mytable
  union
  select cust2id from mytable where cust2id is not null
)
, cte(c1, c2, sofar) as
(
  select custid, custid, '<' + cast(custid as varchar(max)) + '>' from all_cust
  union all
  select cte.c1, case when cte.c2 = m.cust1id then m.cust2id else m.cust1id end,
     cte.sofar + '<' + cast(case when cte.c2 = m.cust1id then m.cust2id else m.cust1id end as varchar(max)) + '>'
  from mytable m
  join cte on cte.c2 in (m.cust1id, m.cust2id)
  and cte.sofar not like '%' + cast(case when cte.c2 = m.cust1id then m.cust2id else m.cust1id end as varchar(max)) + '%'
)
, groups(custid, grp) as
(
  select c1, min(c2) from cte group by c1
)
, ranked as
(
  select *, row_number() over (partition by g.grp order by date desc) as rn 
  from groups g
  join mytable m on g.custid in (m.cust1id, m.cust2id)
)
select acctnumber, date, cust1id, cust2id
from ranked
where rn = 1
order by cust1id;

Демо-версия реестров: http://rextester.com/RWCQ83881