重复数据的聚合

Question

关于重复的数据，我们有一个有趣的问题。我们的源文件可以包含单个员工福利的多行数据。例如，医疗福利可以分成3行报告(3行只是一个例子-可能更多)。

数据被加载到暂存中，没有任何问题，并且将包含单个和多个受益行的混合。到目前为止，我所做的是执行聚合，以找到与多个好处( employeeid和benefitid上的聚合)的标识，如果它> 1，则将其移动到一个单独的暂存表中。

我现在需要做的是分析具有多种好处的分期表，并根据employeeid和benefitid汇总数据。让我们假设一个好处有3行。

员工和benefitid将是识别这些行的关键。其他行可能包括福利提供者、年度成本、合格和福利名称(以及更多的行，但这些行涵盖了输出单个行所需的4种聚合类型)。

1. 如果在3的列表中有多个提供者，我们应该将值设置为" multiple“，否则它应该是不同的值。
2. 年成本是所有三行的simple...SUM
3. 如果任何活动标志为true，则将值设置为true (1可能为true，2可能为false)
4. 如果有多个福利名称，我们只需选择第一个非空值。

在将数据聚合后(应用上述规则，将3行合并为1 )，然后将其与单行福利表合并。

Answer 1

由于您使用的是2012+，所以您可以使用窗口函数，我认为这使得解决这个问题更容易。

这是我的装备。

IF NOT EXISTS
(
    SELECT * 
    FROM 
        sys.tables AS T 
        INNER JOIN 
            sys.schemas AS S 
            ON s.schema_id = t.schema_id
    WHERE 
        S.name ='dbo' AND T.name = 'StagingBenefit'
)
BEGIN
    CREATE TABLE 
        dbo.StagingBenefit
    (
        StagingBenefitSK int identity(1,1) NOT NULL

    ,   EmployeeID int NOT NULL
    ,   BenefitID int NOT NULL

    ,   BenefitProvider char(1) NOT NULL
    ,   Cost int NOT NULL
    ,   FlagA bit NOT NULL
    ,   FlagB bit NOT NULL
    ,   FlagC bit NOT NULL
    ,   BenefitName varchar(30) NULL
    ,   Scenario varchar(30) NOT NULL
    );
END

TRUNCATE TABLE dbo.StagingBenefit;

INSERT INTO
    dbo.StagingBenefit
SELECT
*
FROM
(
    VALUES
        (1,1, 'A', 100, 0, 0, 0, 'Teeth', 'SingleRow')
    ,   (2,2, 'A', 200, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (2,2, 'A', 222, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (3,3, 'A', 300, 0, 0, 0, 'Teeth', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 330, 1, 0, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 333, 0, 1, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (4,4, 'A', 400, 0, 0, 0, 'Teeth', 'MultipleProviders')
    ,   (4,4, 'A', 440, 0, 0, 0, 'First', 'MultipleProviders')
    ,   (4,4, 'B', 444, 0, 0, 1,    NULL, 'MultipleProviders')
)D
(
    EmployeeID
,   BenefitID

,   BenefitProvider
,   Cost
,   FlagA
,   FlagB
,   FlagC
,   BenefitName
,   Scenario
)
;

然后，我会查看一个类似于这样的查询

SELECT DISTINCT
    SB.EmployeeID
,   SB.BenefitID
,   
    CASE 
        WHEN
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) 
            <> MAX(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
            THEN 'Multiple'
        ELSE
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) 
        END AS BenefitProvider
    -- SUM this
,   SUM(SB.Cost) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) As TotalCost
,   CAST(MAX(CAST(SB.FlagA AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagA
,   CAST(MAX(CAST(SB.FlagB AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagB
,   CAST(MAX(CAST(SB.FlagC AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagC
,   MIN(SB.BenefitName) OVER 
    (
        PARTITION BY SB.EmployeeID, SB.BenefitID 
    ) AS FirstBenefit
,   SB.Scenario
FROM
    dbo.StagingBenefit AS SB

浏览查询的一些技巧

• 我觉得这是最肮脏的部分，但它解决了问题。
• MIN/MAX BenefitProvider -我为我的窗口找到第一个和最后一个(员工和福利ids)，如果它们不同，我通过案例表达式确定它们，我使用文本多次。否则，我只是抓住了最低限度，但最大将工作同样好。见鬼，我可能只是跳过了第二个聚合调用，并使它的列名。
• MAX -我使用了一个不能聚合的位数据类型，从而使这尽可能困难，所以我显式地转换成一个整数，然后将结果转换回bit。不过，窗户也是一样。Net结果是，如果我的标志设置为true的任何地方，它将跨行保留
• MIN BenefitName -这里的规则是选择第一个非空的福利名称。聚合函数消除NULL并返回文本"First“。