查找每个客户组的最新帐户

2024-06-25

我有一个包含客户信息的表。每个客户都会分配一个客户 ID（他们的 SSN），他们在开设更多帐户时会保留该 ID。两个客户可能使用同一个帐户，每个客户都有自己的 ID。帐号不按日期排序。

我想找到每个客户或客户组的最新帐户。如果两个客户曾经一起使用过一个帐户，我想返回任一客户最近使用过的帐户。

这是包含一些可能情况的示例表。

示例表 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

期望的结果：

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

或者，我会接受案例 7 输出两个单独的客户组：

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

因为案例 8a 和 8b 代表公司承认客户值得持有单独的账户，所以我们希望将他们的组视为拆分，因此它有单独的结果集。

而且，大多数场景下，客户拥有多个账户，混合搭配上述情况的加班情况也很常见。例如，一个客户可以拥有五个账户（案例 1），然后与另一个客户开设一个或多个账户（案例 3），有时会切换主要账户持有人（案例 4），然后第一个客户开始再次开设个人账户（情况 5b)。

当 acctnumbers 唯一并且任何 Cust ID 匹配时，我尝试将表连接到其自身的副本。但是，这会删除只有一个帐户的客户，因此我添加了一个 cust 并集，该并集与 custid 或帐号以及按 custid 分组没有匹配项。

不幸的是，第二部分不仅包括案例 0 中的粘虫，而且还有一些不应该被排除的粘虫。

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

Update

感谢您迄今为止所有精彩的回答和评论。我一直在尝试查询并比较结果。

@VladimirBaranov 提出了一个罕见的案例，我之前在对其他答案的评论中没有考虑过这个案例。

与案例 7 类似，如果案例 8 得到处理，将是一个奖励，但不是预期的。

情况 9 很重要，应该处理 9a 和 9b 的结果。

Update 2

我注意到我原来的 7 个案例有问题。

在最近的账户中，当客户不再在该账户上时，留下来的总是第二个借款人。这完全是无意的，您可以查看这些示例中的任何一个，其中任何一个客户都可能是最近帐户上的剩余客户。

此外，每个案例都有最少数量的帐户来准确显示案例正在测试的内容，但这并不常见。通常，在每种情况的每个步骤中，在客户切换到添加第二个客户之前，可以有 5、10、15 或更多帐户，然后这两个客户可以一起拥有许多帐户。

回顾答案，我发现许多答案都有索引、创建、更新和其他特定于能够编辑数据库的子句。不幸的是，我位于该数据库的使用者端，因此我具有只读访问权限，并且我可以用来与数据库交互的程序会自动拒绝它们。

我要感谢 Jeff Breadner 提供的带有示例数据的 DDL。

您必须逐个 CTE 逐步运行以下查询并检查中间结果以了解其作用。它假设AcctNumber在给定表中是唯一的。

首先，我想找到每个客户的最新帐户。这是一个简单的top-n-per-group查询，我正在使用ROW_NUMBER接近这里。

CTE_Customers通过将所有个人客户放在一起制作一个简单的列表Cust1ID and Cust2ID. CTE_RN为它们分配行号。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从原始表中获取每一行并将最新帐户连接到其中两次 - forCust1D and 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 |
+---------+---------+-------------+---------------------+

The MaxLatestAcctNumber这里适用于两者Cust1ID and Cust2ID。同一客户可能会在此处列出多次，我们需要使用最新的帐户再次选择条目。这是一对的最新帐户，不是针对个人客户的。

方法与一开始相同。两者都放Cust1ID and 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);

Query

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

Result

+------------+------------+---------+---------+
| 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, not 10007，因为这是客户稍后的帐户1122 and 1123.

当我查看执行计划时，我看到后面的查询CTE_LatestAccounts基本上运行了四次。

如果您保存结果，很可能CTE_LatestAccounts放入具有适当索引的临时表中，整体性能会更好。

像这样的事情：

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

如果您确实需要在链长度任意时将所有链接的客户合并/分组到一行中，则可以使用递归查询来完成，如下所示，例如，此处：如何找到无向图的所有连通子图 https://stackoverflow.com/q/35254260/4116017

使用某个 GroupID 标记每个客户后，请查找每个客户的最新帐户，如该查询开头所示。然后找到该组中的最新帐户（而不是像此查询中那样的简单对）。

对于大型数据集来说，查找链接问题中无向图的所有子图的查询可能会非常慢，并且有有效的基于非集的算法可以做到这一点。

如果您知道链的最大长度不能超过某个数字，则可以使此递归查询更加高效。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)