查找每个客户组的最新帐户 [英] Finding each customer group's most recent account
问题描述
我有一个包含客户信息的表.每个客户都分配有一个客户ID(他们的SSN)，他们在开设更多帐户时会保留下来.两个客户可能在同一个帐户中，每个帐户都有自己的ID.帐号没有按日期排序.
I have a table that contains customers information. Each customer is assigned a Customer ID (their SSN) that they retain as they open more accounts. Two customers may be on the same account, each with their own ID. The account numbers are not ordered by date.
我想找到每个客户或客户组的最新帐户.如果两个客户曾经一起使用过一个帐户，我想返回一个客户曾经使用过的最新帐户.
I would like to find the most recent account of each customer or group of customers. If two customers have ever been on an account together, I want to return the most recent account either customer has been on.
这是一个示例表，其中包含一些可能的情况.
Here is a sample table with some of the possible cases.
示例表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输出两个单独的客户组:
Alternatively, I would accept Case 7 outputting the two separate customer groups:
10007 '2016-02-01' 1122 1123 --Case7 result 1
10107 '2017-06-02' 1124 1125 --Case7 result 2
因为案例8a& 8b代表该公司承认客户值得拥有单独的帐户，然后我们希望将他们的组视为拆分，因此它具有单独的结果集.
Because Cases 8a & 8b would represent the company acknowledging the customers are worthy of holding separate accounts, we would want to then consider their group as splitting, so it has separate sets of results.
此外，在大多数情况下，客户都有很多帐户，并且通常情况下加班匹配上述情况是很常见的.例如，一个客户可以有五个帐户(案例1)，然后与另一个客户一起开设一个或多个帐户(案例3)，有时切换主帐户持有人(案例4)，然后第一个客户再次开始开设单个帐户(情况5b).
Also, in most scenarios the customers have many accounts, and mix and matching the above cases overtime is common. For example, a single customer can have five accounts (Case 1), then later opens one or more accounts with another customer (Case 3) sometimes switching the primary account holder (Case 4) then afterwards the first customer begins opening individual accounts again (Case 5b).
每当acctnumbers唯一且任何Cust ID匹配时，我都试图将表与其自身的副本连接起来.但是，这会删除只有一个帐户的客户，因此我添加了一个在客户编号或帐户编号以及客户编号分组上均不匹配的客户组成的联合.
I have attempted joining the table to a copy of itself whenever acctnumbers are unique and any of the Cust IDs match. However, this removes customers who have only had one account so I added a union of cust that have no matches on the custid or account number and groups by custid.
不幸的是，第二部分不仅包括案例0中的监护权，而且还有一些不应被排除在外的监护权.
Unfortunately, the second piece does not only include custids from case 0 and there are some custids which are excluded all together that shouldn't be.
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
Thank you for all the great answers and comments so far. I have been trying out the queries and comparing results.
@VladimirBaranov提出了一个罕见的案例，我以前在评论其他答案时没有考虑过.
@VladimirBaranov has brought up a rare case that I had not previously considered in comments to other answers.
与案例7类似，如果处理了Case8，但不是预期的，这将是一个奖励.
Similarly to case 7, it will be a bonus if Case8 is handled, but not expected.
案例9很重要，应该处理9a和9b的结果.
Case 9 is important and the result for 9a and 9b should be handled.
我注意到最初的7例案例中存在问题.
I noticed issues with my original set of 7 cases.
在较新的帐户中，当不再有客户使用该帐户时，始终是第二个借款人.这完全是无意的，您可以查看这些示例中的任何一个，并且任何一个客户都可能成为最近帐户上的剩余客户.
In more recent accounts, when a customer is no longer on the account, it was always the second borrower that remained. This was entirely unintentional, you can look at any of those examples and either customer can potentially be the remaining customer on the most recent account.
此外，每个案例都具有最少数量的帐户以准确显示该案例正在测试的内容，但这并不常见.通常，在每种情况下的每个步骤中，在客户切换为添加第二个客户之前，都有5、10、15个或更多帐户，然后这两个帐户可以在一起拥有多个帐户.
Also, each case had the minimum number of accounts to display exactly what the case was testing, but this is not common. Usually in each step of each case there can be 5, 10, 15 or more accounts before a customer switches to adding on a second customer, and those two can then have many accounts together.
回顾答案，我发现很多人都有索引，创建，更新和其他特定于能够编辑数据库的条款.不幸的是，我是该数据库的使用者，所以我拥有只读访问权限，并且可以用来与数据库进行交互的程序会自动拒绝它们.
Reviewing the answers I see many have index, create, update and other clauses specific to being able to edit the database. Unfortunately, I am on the consumer side of this database so I have read only access, and the program I can use to interact with the database automatically rejects them.
推荐答案
要将逻辑应用于每个子集，一个很好的运算符是CROSS APPLY运算符.这样我们就可以找到每个客户ID的最新帐户.
To apply logic to each subset a good operator to use is the CROSS APPLY operator. This allows us to find the most recent account for each Customer Id.
设置
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个客户在3个客户之间转移的情况.最终选择中的self join和WHERE子句覆盖了这些内容.
The CROSS APPLY operators walk the cases backward to apply the logic to each joint account case while ensuring the most recent account is carried over. This alone covers most of the cases. The only lingering cases are the ones with 3 accounts being shifted between 3 customers. The self join and WHERE clause in the final select cover these.
结果
+------------+------------+---------+---------+
| 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 |
+------------+------------+---------+---------+
这篇关于查找每个客户组的最新帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
