时间戳

首页 > 解决方案 > 根据引用表中的计数将记录拆分为存储桶

sql - 根据引用表中的计数将记录拆分为存储桶

问题描述

这是一个简化版本，精简到我的核心问题。我有一个包含数百万行数据的 ContactData 表，每个联系人记录都使用 ReferenceID 分为多个类别。现在，我必须根据来自单独的 NewValues 表的计数（也按 ReferenceID）为每个联系人记录分配一个新的 UpdatedValue。将哪些记录分配给每个组并不重要，它可以是随机的或其他的，只要每组的记录数是正确的。

使用下面的示例来说明：如果#ContactData 中有800 条ReferenceID=1 的记录，那么使用#NewValues 中的RecordTotal 计数，我想将200 分配给Group1，将350 分配给Group2，将250 分配给Group3。

我可以使用嵌套循环和更新来做到这一点。但是 NewValues 中的数据会定期更改，因此将组分配给联系人也会随之更改。此外，生成的更新联系人数据将转储到单独的第三个表中，而不是更新原始的 ContactData 表。因此，我希望在将数据选择到第三个表中时，有任何更简单的方法可以动态分配此值。以下是用于说明的示例表和数据。任何帮助将不胜感激。

DROP TABLE IF EXISTS #ContactData 
CREATE TABLE #ContactData  ( 
    RowId INT IDENTITY(1,1) NOT NULL, 
    ReferenceID INT,
    FirstName VARCHAR(10)) 
GO
INSERT INTO #ContactData (ReferenceID,FirstName)
VALUES (1,'John'), (1,'Mary'), (1,'Dan'), (2,'Sue'), (2,'Harvey'), (3,'Frank'), (3,'Mike')
GO
DROP TABLE IF EXISTS #NewValues 
CREATE TABLE #NewValues  ( 
    RowId INT IDENTITY(1,1) NOT NULL, 
    ReferenceID INT, 
    RecordTotal DECIMAL(10,4),
    UpdatedValue NVARCHAR(20)
    ) 
GO
INSERT INTO #NewValues (ReferenceID,RecordTotal,UpdatedValue)
VALUES (1,200,'Group1'), (1,350,'Group2'), (1,250,'Group3'), (2,500,'Group4'), (2,300,'Group5'), (3,150,'Group6'), (3,850,'Group7')
GO

标签： sqlsql-servertsqlsql-server-2017

解决方案

避免对此类事情使用循环。窗口函数对于这类问题非常有用。

0 -我在循环中使用您的代码创建了一个示例数据集：

declare @i int = 1
while(@i <200)
begin

    INSERT INTO #ContactData (ReferenceID,FirstName)
    VALUES (1,'John'), (1,'Mary'), (1,'Dan'), (2,'Sue'), (2,'Harvey'), (3,'Frank'), (3,'Mike')

    set @i = @i + 1
end

1 -计算每个 ReferenceID 的最大和最小行值：

select
    f1.*
    ,   sum(RecordTotal) over(partition by ReferenceID order by RowId asc) - RecordTotal minValue
    ,   sum(RecordTotal) over(partition by ReferenceID order by RowId asc)  maxValue
from #NewValues f1

2 -然后您需要按任何列计算每个 ReferenceID 排序：

select
    *
,   sum(1) over(partition by ReferenceID order by RowId asc) rn    
from #ContactData f1

3 -通过使用在步骤 2中计算的rn ，您可以将记录动态分配给存储桶。这是完整的代码：

select
    g1.*
,   g2.UpdatedValue as Bucket
from 
    (
    select
        *
    ,   sum(1) over(partition by ReferenceID order by RowId asc) rn    
    from #ContactData f1
    ) g1
inner join 
    (
    select
        f1.*
        ,   sum(RecordTotal) over(partition by ReferenceID order by RowId asc) - RecordTotal minValue
        ,   sum(RecordTotal) over(partition by ReferenceID order by RowId asc)  maxValue
    from #NewValues f1
    ) g2 on g1.ReferenceID = g2.ReferenceID and g1.rn >= g2.minValue and g1.rn < g2.maxValue

推荐阅读