识别 sql 中的重复原始(贷款)

Question

>我有这样的表格：

Client  Branch  Amount  Date
1        2      1500    1.1.14
1        2      1400    3.1.14
1        3      1500    1.1.14
1        4      300     7.1.14
1        5      1500    1.1.14
------------------------------
2        2      300     1.1.14
2        2      300     1.1.14
2        5      300     1.1.14
2        3      400     4.1.14
------------------------------           
3        2      300     1.1.14
3        2      300     1.1.14
3        5      300     1.1.14
3        5      300     1.1.14
3        3      400     4.1.14
4        2      300     1.1.14  
4        2      300     1.1.14 
4        5      300     1.1.14 
4        5      300     1.1.14  
4        5      300     1.1.14 

我想要的输出应该是这样的：

Client   Branch  Amount  Date   Ind  Loan_Distinct_Num
1        2      1500    1.1.14  0         1
1        2      1400    3.1.14  0         2 
1        3      1500    1.1.14  1         1
1        4      300     7.1.14  0         3
1        5      1500    1.1.14  1         1
-------------------------------------------------
2        2      300     1.1.14  0         1
2        2      300     1.1.14  0         2
2        5      300     1.1.14  1         2
2        3      400     4.1.14  0         3
--------------------------------------------------           
3        2      300     1.1.14  0         1
3        2      300     1.1.14  0         2 
3        5      300     1.1.14  1         1
3        5      300     1.1.14  1         2
3        3      400     4.1.14  0         3
------------------------------------------------     
4        2      300     1.1.14  0         1
4        2      300     1.1.14  0         2
4        5      300     1.1.14  1         1
4        5      300     1.1.14  1         2
4        5      300     1.1.14  0         3

那我想做什么呢？(评论：这些记录只是一个示例数据)

好吧，这些是规则： 客户已从同一家银行的一家分行转移到另一家分行。问题是分支为他写入了几次数据。我想识别重复的贷款。需要两个步骤：

步骤1 ： 假设：Same_Amount + Same_Date +DiffrentDate ---> Ind = 1 在第一个原始记录之后

。Ind 字段如何工作？

例如： 在客户端 = 1 的分区中，金额 1500 在同一日期和不同的分支上收获 3 次，但只有此详细信息的最后两条记录将获得 Ind 的"1"值，第一个将获得 Ind = 0，因为它不是重复贷款，这是第一次具有此金额和日期的记录出现在数据中。

在客户端 = 2 的情况下，分支 = 2有两条记录，分支 = 5 只有一条记录，因此在这种情况下，我将假设分支 = 2 的最后一条记录被重复。

在像客户端 = 3 的情况下，分支 = 2 中有两条记录，分支 = 5 中有两条记录，因此在这种情况下，我将假设分支 2 的两个贷款都被重复。

在客户= 4的情况下，它将与客户3相同，但还有另一个记录，但我会认为它是一个新的，因为我没有额外的过去贷款与她沟通。

第 2 步：我想为每个客户创建自己不同的贷款编号

关于如何解决这个问题或简单的问题的任何帮助？

注释：SQL服务器2008。

Answer 1

首先 - 将数据设置到表中。我添加了一个身份列 ID，因此我们有一些东西可以排序 - 您在注释中指定您的数据按特定顺序排列。

declare @data table (ID int identity(1,1), Client int, Branch int, Amount int, [Date] date);
insert into @data values
(1,2,  1500,'2014-01-01'),
(1,2,  1400,'2014-03-01'),
(1,3,  1500,'2014-01-01'),
(1,4,  300,'2014-07-01'),
(1,5,  1500,'2014-01-01'),
(2,2,  300,'2014-01-01'),
(2,2,  300,'2014-01-01'),
(2,5,  300,'2014-01-01'),
(2,3,  400,'2014-04-01'),
(3,2,  300,'2014-01-01'),
(3,2,  300,'2014-01-01'),
(3,5,  300,'2014-01-01'),
(3,5,  300,'2014-01-01'),
(3,3,  400,'2014-04-01'),
(4,2,  300,'2014-01-01'),
(4,2,  300,'2014-01-01'),
(4,5,  300,'2014-01-01'),
(4,5,  300,'2014-01-01'),
(4,5,  300,'2014-01-01');

以下是我们执行查询的地方：

--In the first cte, we take all the data, and partition it up into individual loans (partition by Client, Amount, Date).
with cte1 as (
select *, ROW_NUMBER() over (partition by Client, Amount, Date order by ID) as rowno from @data
), cte2 as (
--in this cte, we get a list of distinct loans. We will use another rownumber in a bit to find our Loan_Distinct_Num
select distinct Client, Amount, [Date] from @data
)
select cte1.Client, cte1.Branch, cte1.Amount, cte1.[Date]
-- If rowno = 1, it's the first instance of that combination
, case when rowno = 1 then 0 else 1 end as ind
, b.Loan_Distinct_Num
from cte1
left join (select cte2.*, ROW_NUMBER() over (partition by Client order by [Date]) as Loan_Distinct_Num
-- This is where our distinct loan number comes from
from cte2 
) as b
on b.Client = cte1.Client and b.Amount = cte1.Amount and b.[Date] = cte1.[Date]
order by ID

Answer 2

如果存在具有不同分支 # 的先前记录，如果 ind 应该只为 1，这是一个答案(请参阅第 7 行)。此外，使用 dense_rank 按loan_distinct_num中的金额/日期对贷款进行分组。该列的逻辑似乎更复杂 - 如果这是一次性修复，我可能会使用游标遍历表并应用一些更复杂的逻辑来填充该列，而不是尝试在查询中计算它。

-- sample data
declare @data table (ID int identity(1,1), Client int, Branch int, Amount int, [Date] date);
insert into @data values
(1,2,  1500,'2014-01-01'),
(1,2,  1400,'2014-03-01'),
(1,3,  1500,'2014-01-01'),
(1,4,  300,'2014-07-01'),
(1,5,  1500,'2014-01-01'),
(2,2,  300,'2014-01-01'),
(2,2,  300,'2014-01-01'),
(2,5,  300,'2014-01-01'),
(2,3,  400,'2014-04-01'),
(3,2,  300,'2014-01-01'),
(3,2,  300,'2014-01-01'),
(3,5,  300,'2014-01-01'),
(3,5,  300,'2014-01-01'),
(3,3,  400,'2014-04-01'),
(4,2,  300,'2014-01-01'),
(4,2,  300,'2014-01-01'),
(4,5,  300,'2014-01-01'),
(4,5,  300,'2014-01-01'),
(4,5,  300,'2014-01-01');
-- query
select client, branch, amount, date, 
case when exists (select * from @data t2 where client = tbl.client and branch <> tbl.branch and amount = tbl.amount and date = tbl.date and id < tbl.id) then 1 else 0 end as ind,
DENSE_RANK() over (partition by client order by date, amount asc) as loan_disinct_num
from @data tbl
order by id;