for - sql server select top 5 of each group

Encontrar la cuenta más reciente de cada grupo de clientes.

Question

Tengo una tabla que contiene información de los clientes. A cada cliente se le asigna un ID de cliente (su número de seguro social) que conserva cuando abre más cuentas. Dos clientes pueden estar en la misma cuenta, cada uno con su propia identificación. Los números de cuenta no están ordenados por fecha.

Me gustaría encontrar la cuenta más reciente de cada cliente o grupo de clientes. Si dos clientes alguna vez han estado juntos en una cuenta, quiero devolver la cuenta más reciente en la que haya estado cualquiera de los clientes.

Aquí hay una tabla de muestra con algunos de los casos posibles.

Ejemplo de tabla ACCT:

acctnumber  date            Cust1ID     Cust2ID 
10000       ''2016-02-01''    1110        NULL    --Case0-customer has only ever had
                                                --one account

10001       ''2016-02-01''    1111        NULL    --Case1-one customer has multiple
10050       ''2017-02-01''    1111        NULL    --accounts
400050      ''2017-06-01''    1111        NULL
10089       ''2017-12-08''    1111        NULL

10008       ''2016-02-01''    1120        NULL    --Case2-customer has account(s) and later
10038       ''2016-04-01''    1120        NULL
10058       ''2017-02-03''    1120        1121    --gets account(s) with another customer

10002       ''2016-02-01''    1112        NULL    --Case3-customer has account(s) and later
10052       ''2017-02-02''    1113        1112    --becomes the second customer on another
10152       ''2017-05-02''    1113        1112    --account(s)

10003       ''2016-02-02''    1114        1115    --Case4-customer and second customer
7060        ''2017-02-04''    1115        1114    --switch which is first and second

10004       ''2016-02-02''    1116        1117    --Case5-second customer later gets
10067       ''2017-02-05''    1117        NULL    --separate account(s)
10167       ''2018-02-05''    1117        NULL

50013       ''2016-01-01''    2008        NULL    --Case5b -customer has account(s) & later
50014       ''2017-02-02''    2008        2009    --gets account(s) with second customer &
50015       ''2017-04-04''    2008        NULL    --later still first customer gets
100015      ''2018-05-05''    2008        NULL    --separate account(s)

30005       ''2015-02-01''    1118        NULL    --Case6-customer has account(s) 
10005       ''2016-02-01''    1118        NULL
10054       ''2017-02-02''    1118        1119    --gets account(s) with another
40055       ''2017-03-03''    1118        1119
10101       ''2017-04-04''    1119        NULL    --who later gets separate account(s)
10201       ''2017-05-05''    1119        NULL
30301       ''2017-06-06''    1119        NULL
10322       ''2018-01-01''    1119        NULL

10007       ''2016-02-01''    1122        1123    --Case7-customers play musical chairs
10057       ''2017-02-03''    1123        1124
10107       ''2017-06-02''    1124        1125

50001       ''2016-01-01''    2001        NULL    --Case8a-customers with account(s)
50002       ''2017-02-02''    2001        2002    --together each later get separate
50003       ''2017-03-03''    2001        NULL    --account(s)
50004       ''2017-04-04''    2002        NULL

50005       ''2016-01-01''    2003        NULL    --Case8b-customers with account(s)
50006       ''2017-02-02''    2003        2004    --together each later get separate
50007       ''2017-03-03''    2004        NULL    --account(s)
50008       ''2017-04-04''    2003        NULL
50017       ''2018-03-03''    2004        NULL
50018       ''2018-04-04''    2003        NULL

50009       ''2016-01-01''    2005        NULL    --Case9a-customer has account(s) & later
50010       ''2017-02-02''    2005        2006    --gets account(s) with a second customer
50011       ''2017-03-03''    2005        2007    --& later still gets account(s) with a
                                                --third customer

50109       ''2016-01-01''    2015        NULL    --Case9b starts the same as Case9a, but
50110       ''2017-02-02''    2015        2016    
50111       ''2017-03-03''    2015        2017    
50112       ''2017-04-04''    2015        NULL    --after all accounts with other customers
50122       ''2017-05-05''    2015        NULL    --are complete, the original primary
                                                --customer begins opening individual
                                                --accounts again

Resultados deseados:

acctnumber  date            Cust1ID     Cust2ID 
10000       ''2016-02-01''    1110        NULL    --Case0    
10089       ''2017-12-08''    1111        NULL    --Case1
10058       ''2017-02-03''    1120        1121    --Case2
10152       ''2017-05-02''    1113        1112    --Case3
7060        ''2017-02-04''    1115        1114    --Case4
10167       ''2018-02-05''    1117        NULL    --Case5
100015      ''2018-05-05''    2008        NULL    --Case5b
10322       ''2018-01-01''    1119        NULL    --Case6
10107       ''2017-06-02''    1124        1125    --Case7
50003       ''2017-03-03''    2001        NULL    --Case8a result 1
50004       ''2017-04-04''    2002        NULL    --Case8a result 2
50017       ''2018-03-03''    2004        NULL    --Case8b result 1
50018       ''2018-04-04''    2003        NULL    --Case8b result 2
50011       ''2017-03-03''    2005        2007    --Case9a
50122       ''2017-05-05''    2015        NULL    --Case9b

Alternativamente, aceptaría el Caso 7 que genera los dos grupos de clientes separados:

10007       ''2016-02-01''    1122        1123    --Case7 result 1
10107       ''2017-06-02''    1124        1125    --Case7 result 2

Debido a que los casos 8a y 8b representarían a la compañía reconociendo que los clientes son dignos de tener cuentas separadas, querríamos considerar a su grupo como una división, por lo que tiene conjuntos de resultados separados.

Además, en la mayoría de los escenarios, los clientes tienen muchas cuentas, y la combinación y el emparejamiento de los casos anteriores son comunes. Por ejemplo, un solo cliente puede tener cinco cuentas (Caso 1), luego abre una o más cuentas con otro cliente (Caso 3) a veces cambiando el titular de la cuenta principal (Caso 4) y luego el primer cliente comienza a abrir cuentas individuales nuevamente ( Caso 5b).

He intentado unir la tabla a una copia de sí misma cuando los números de registro son únicos y cualquiera de las ID de Cust coincide. Sin embargo, esto elimina a los clientes que solo han tenido una cuenta, por lo que agregué una unión de cust que no tiene coincidencias en el número de cuenta o el número de cuenta y los grupos de custodia.

Desafortunadamente, la segunda parte no solo incluye los custodios del caso 0 y hay algunos que están excluidos todos juntos que no deberían estarlo.

select
    max(date1) as date,
    cust1id1 as cust1id
from
(
select
    acctnumber as [acctnumber1],
    date as [date1],
    cust1id as [cust1id1],
    cust2id as [cust2id1]
from 
    acct
) t1
join
(
select
    acctnumber as [acctnumber2],
    date as [date2],
    cust1id as [cust1id2],
    cust2id as [cust2id2]
from 
    acct
) t2
on t1.date1 > t2.date2 and
(t1.cust1id1 = t2.cust1id2 or
t1.cust1id1 = t2.cust2id2 or
t1.cust2id1 = t2.cust2id2)
Group by
cust1id1
union
select
    max(date1) as date,
    cust1id1 as cust1id
from
(
select
    acctnumber as [acctnumber1],
    date as [date1],
    cust1id as [cust1id1],
    cust2id as [cust2id1]
from 
    acct
) t1
join
(
select
    acctnumber as [acctnumber2],
    date as [date2],
    cust1id as [cust1id2],
    cust2id as [cust2id2]
from 
    acct
) t2
on (t1.acctnumber1 != t2.acctnumber2 and
t1.cust1id1 != t2.cust1id2 and
t1.cust1id1 != t2.cust2id2 and
t1.cust2id1 != t2.cust2id2)
group by
cust1id1

Actualizar

Gracias por todas las grandes respuestas y comentarios hasta ahora. He estado probando las consultas y comparando resultados.

@VladimirBaranov ha presentado un caso raro que no había considerado anteriormente en los comentarios a otras respuestas.

De manera similar al caso 7, será un bono si se maneja Case8, pero no se espera.

El caso 9 es importante y el resultado para 9a y 9b debe manejarse.

Actualización 2

Noté problemas con mi set original de 7 casos.

En cuentas más recientes, cuando un cliente ya no está en la cuenta, siempre fue el segundo prestatario el que se quedó. Esto fue totalmente involuntario, puede ver cualquiera de esos ejemplos y cualquiera de los clientes puede ser el cliente restante en la cuenta más reciente.

Además, cada caso tenía el número mínimo de cuentas para mostrar exactamente lo que el caso estaba probando, pero esto no es común. Por lo general, en cada paso de cada caso puede haber 5, 10, 15 o más cuentas antes de que un cliente cambie para agregar un segundo cliente, y esas dos pueden tener muchas cuentas juntas.

Revisando las respuestas, veo que muchos tienen cláusulas de índice, creación, actualización y otras específicas para poder editar la base de datos. Desafortunadamente, estoy en el lado del consumidor de esta base de datos, por lo que solo tengo acceso de lectura, y el programa que puedo usar para interactuar con la base de datos los rechaza automáticamente.

Answer 1

¿Podría simplemente usar una combinación a la izquierda para unirse a cuentas con otras cuentas "vinculadas" con fechas potencialmente posteriores, y luego simplemente filtrar los registros donde la tabla de "Cuentas posteriores" no es nula? Algo como esto:

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

Esto debería devolver los resultados como se esperaba:

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

Answer 2

Dejo mi respuesta original en su lugar, porque el enfoque podría funcionar para alguien más que esté buscando esto en el futuro.

No puedo averiguar cómo hacer esto sin un cursor. Como tal, cualquier otra respuesta que proporcione la respuesta correcta (que no use un cursor) va a superar a esta. No soy lo suficientemente inteligente como para averiguar qué aspecto tiene, pero tendría que incluir un CTE recursivo desagradable.

El verdadero truco es agrupar todas las cuentas que alguna vez estuvieron relacionadas entre sí. Eso se hace en la gran cadena de cursores if / then / else en la parte superior, que se puede limpiar un poco. He dejado mis declaraciones de print depuración en su lugar, obviamente se pueden eliminar.

También puede hacer que la tabla Asociaciones sea permanente, en lugar de usar una variable de tabla.

Una vez más, en cuanto al rendimiento, esto va a ser muy, muy malo, pero funciona. Estoy deseando ver lo que otros inventan. Gracias por la pregunta de alta calidad, también, que hizo la vida mucho más fácil.

El código:

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

Los resultados:

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

Answer 3

Estoy seguro de que hay un enfoque mucho más fácil, pero esto es lo que he tenido en mente:

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)

Answer 4

Me gustaría agradecer a Jeff Breadner por la DDL con datos de muestra.

Tendrá que ejecutar la consulta a continuación, paso a paso, CTE por CTE y examinar los resultados intermedios para comprender lo que hace. Se supone que AcctNumber es único en la tabla dada.

Al principio quiero encontrar la última cuenta para cada cliente individual. Es una consulta simple top-n-per-group y estoy usando un enfoque ROW_NUMBER aquí.

CTE_Customers hace una lista simple de todos los clientes individuales al juntar Cust1ID y Cust2ID . CTE_RN les asigna números de fila. CTE_LatestAccounts ofrece la cuenta más reciente para cada cliente individual:

+------------------+------------+--------+
| 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 |
+------------------+------------+--------+

La tarea se complica al tener pares de clientes que "propagan" la última cuenta a otro cliente.

Los pares de clientes se definen en la tabla original, por lo que CTE_MaxLatestAccounts toma cada fila de la tabla original y une dos veces las cuentas más recientes, para Cust1D y Cust2ID . Para cada par, elijo una de las dos últimas cuentas, la más reciente. Por lo tanto, un cliente que pertenece a un par puede obtener una cuenta de su socio.

+---------+---------+-------------+---------------------+
| 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 |
+---------+---------+-------------+---------------------+

El MaxLatestAcctNumber aquí es aplicable tanto a Cust1ID como a Cust2ID . El mismo cliente puede aparecer varias veces aquí y debemos elegir una entrada nuevamente con la última cuenta. Aquí esta es la última cuenta de un par, no para un cliente individual.

El enfoque es el mismo que en el principio. Ponga a los clientes Cust1ID y Cust2ID en una lista: CTE_CustomersWithLatestAccountFromPair . Asigne números de fila en CTE_CustomersWithLatestAccountFromPairRN y elija la cuenta final en CTE_FinalAccounts .

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

Ahora solo necesitamos filtrar la tabla original y dejar solo las filas (cuentas) que aparecen en esta lista. Vea el resultado final a continuación.

Data de muestra

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);

Consulta

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)
;

Resultado

+------------+------------+---------+---------+
| 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    |
+------------+------------+---------+---------+

Este resultado coincide con el resultado deseado, excepto el último caso 7.

Mi consulta no intenta seguir la cadena de clientes vinculados de longitud arbitraria y se limita a procesar un par a la vez. Es por eso que el resultado del caso 7 no es una fila. La consulta siempre elegirá la fila / cuenta con la última fecha ( 10107 ) y también puede seleccionar la (s) cuenta (s) en el medio de la cadena. En este caso, eligió una fila 10057 , no 10007 , porque esta es una cuenta posterior para los clientes 1122 y 1123 .

Cuando miré el plan de ejecución, vi que la consulta detrás de CTE_LatestAccounts se ejecuta esencialmente cuatro veces.

Es probable que si guarda el resultado de CTE_LatestAccounts en una tabla temporal con los índices adecuados, el rendimiento general sería mejor.

Algo como esto:

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)
;

Si realmente necesita fusionar / agrupar a todos los clientes vinculados en una fila cuando la longitud de la cadena es arbitraria, puede hacerlo con una consulta recursiva como se muestra, por ejemplo, aquí: Cómo encontrar todos los subgrafos conectados de un gráfico no dirigido

Una vez que haya etiquetado a cada cliente con algún GroupID, busque la cuenta más reciente para cada cliente individual al comienzo de esta consulta. Luego busque la última cuenta entre el grupo (en lugar de para el par simple como en esta consulta).

La consulta que encuentra todos los subgrafos de un grafo no dirigido en la pregunta vinculada puede ser bastante lenta para un conjunto de datos grande y existen algoritmos eficientes basados ​​en no conjuntos para hacerlo.

Si sabe que la longitud máxima de la cadena no puede superar un número, es posible hacer que esta consulta recursiva sea más eficiente.

Answer 5

Mi respuesta es incorrecta, perdón por publicar prematuramente. Estoy trabajando en una idea diferente, volveré pronto.

Respuesta original:

Suponiendo que su formato de fecha es MM.DD.YY, tengo el código como se muestra a continuación. No entiendo por qué su conjunto de resultados deseado no incluye filas para CustID 1116 o 1118, pero veo cómo incluirlos duplicará 1117 y 1119 respectivamente, a menos que los datos de origen se modifiquen para eliminar estos valores duplicados de 1117 y 1119. Los resultados. Por ahora, tengo esta solución provisional, pendiente de su respuesta.

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

El conjunto de resultados:

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

Answer 6

Para aplicar la lógica a cada subconjunto, un buen operador para usar es el operador de CROSS APPLY CRUZADA. Esto nos permite encontrar la cuenta más reciente para cada ID de cliente.

Preparar

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)

Ejecución

Aplicación cruzada

;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

Los operadores de CROSS APPLY CRUZADA recorren los casos para aplicar la lógica a cada caso de cuenta conjunta al tiempo que garantizan que la cuenta más reciente se transfiere. Esto solo cubre la mayoría de los casos. Los únicos casos persistentes son aquellos con 3 cuentas cambiadas entre 3 clientes. La unión automática y la cláusula WHERE en la selección final cubren estos.

Resultados

+------------+------------+---------+---------+
| 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    |
+------------+------------+---------+---------+

Answer 7

no deberíamos preocuparnos por usar EXISTS, ya que funciona rápido en ese caso y supongo que es la solución más simple posible:

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
        )

He asumido que usted tiene índices separados en CUST1ID y otro en CUST2ID . Puede comparar el resultado sin un índice ascendente en el campo DT ("fecha") y con él. Puede acelerar su consulta o disminuir la velocidad. No sé cómo se ven sus datos reales.

Answer 8

Esto es bastante complejo ...

Primero quieres identificar grupos de clientes. Eso es todos los clientes que estaban relacionados directa o indirectamente. Con los pares de clientes A / B, B / C, D / E, D / F, G / A, H / A, H / F tendrías un solo grupo, por ejemplo. En SQL esto requiere una consulta recursiva.

SQL Server carece de una detección de ciclo en consultas recursivas. Por lo tanto, de los clientes A / B obtendríamos todos los pares que contienen A o B, que son B / C, A / BG / A, H / A y A / B en realidad. Incluso, si detectamos este círculo directo (el mismo par), continuaríamos con B / C buscando todos los registros que contienen B o C. Y uno de estos es A / B nuevamente y una vez más estamos en un ciclo. Una forma de lidiar con esto es construir una cadena de clientes que ya han visitado y no volver a visitarlos.

Nuestro resultado es todos los clientes con todos los clientes conectados directa o indirectamente. Usando la agregación, podemos tomar el socio mínimo por cliente y usar esto como una clave de grupo. En el ejemplo anterior, todos los clientes están relacionados con A, por lo que A es todo su socio mínimo, lo que demuestra que todos pertenecen al mismo grupo. Si agregamos dos registros X / Y y Z / -, entonces tenemos dos grupos más: X e Y pertenecientes al grupo X, y Z perteneciente al grupo Z.

Estos grupos utilizamos para buscar nuestros registros originales de nuevo. Con ROW_NUMBERnumeramos el último registro de cada grupo con # 1. Entonces guardamos solo eso y hemos terminado.

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;

Demostración de Rextester: http://rextester.com/RWCQ83881

Answer 9

Intente a continuación la consulta. Es largo, porque hay que aplicar funciones de ventanas repetidas veces (no se pueden anidar en una sola consulta), pero la consulta en sí es bastante simple. La idea central es dividir a los clientes que nunca comparten la cuenta de los clientes que lo hacen. Después de eso, para el cliente de cuenta única, la columna de agrupación es fácil Cust1ID, pero para otras, debe realizar algunas de las operaciones que se describen a continuación para obtener la columna de agrupación:

Para obtener la columna de agrupación (para cutomers de cuentas múltiples), debe aplicar la siguiente lógica:

Ponga a todos los primeros clientes junto con los segundos clientes en la misma columna usando UNION ALL(CTE llamado cteen la consulta). Luego, cuando ordene por esa columna y verifique ambos ID con los ID de la siguiente fila, puede verificar si están "conectados", es decir, tienen al menos un ID igual:

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

De esta manera puede diferenciar los grupos y dentro de ellos los grupos toman el máximo respectivamente a la fecha ( dtcolumna).

Data de muestra:

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

ACTUALIZAR

Este código, de acuerdo con la explicación de OP, trata a todos los clientes que hayan estado juntos en una cuenta como el mismo grupo (y esto es transitivo ¹ ), por lo tanto, para casos adicionales, 8a y 8b, los resultados son:

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

¡Como solo habrá 2 grupos!

¹⁾ que significa, si el elemento aestá en el grupo con el elemento by bestá en el mismo grupo como elemento ccontinuación, implica que ay ctambién están en el mismo grupo.