主页 / dba / 问题 / 126129
Accepted
Clive Strong
Asked: 2016-01-14 07:41:24 +0800 CST

重复数据的聚合

  • 772

关于重复数据，我们有一个有趣的问题。我们的源文件可以包含针对单个员工福利的多行数据。例如，医疗保健福利可以拆分并报告为 3 行（3 只是一个示例 - 可能更多）。

数据已毫无问题地加载到暂存中，并且将包含单个和多个收益行的混合。到目前为止，我所做的是执行聚合以找到具有多个福利的标识（聚合在 employeeid 和 benefitid 上），如果它 > 1，则将其移至单独的暂存表。

我现在需要做的是分析具有多个福利的临时表，并根据 employeeid 和 benefitid 聚合数据。假设一个收益有 3 行。

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

  1. 如果 3 的列表中有多个提供者，我们应该将该值设置为“多个”，否则它应该是不同的值
  2. 年度成本很简单...所有 3 行的总和
  3. 如果任何活动标志为真，则将值设置为真（1 可能为真，2 可能为假）
  4. 如果有多个福利名称，我们只选择第一个非空值。

数据聚合后（3 行合并为 1，应用上述规则），然后将其与单行收益表合并。

ssis

1 个回答

  1. Best Answer

    由于您使用的是 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

    挑选查询的一些技巧

    • DISTINCT 我觉得这是其中最脏的部分，但它解决了问题
    • MIN/MAX BenefitProvider - 我为我的窗口找到第一个和最后一个（员工和福利 ID），如果它们不同，我通过 CASE 表达式确定，我使用文本 Multiple。否则，我只是抓住了最小值，但最大值也可以。哎呀，我可能可以跳过第二个聚合调用，直接将其设为列名。
    • MAX 标志 - 我通过使用无法聚合的位数据类型尽可能地使它变得困难，所以我明确地转换为整数，然后将结果转换回位。虽然有相同的窗口。最终结果是，如果我的标志在任何地方设置为 true，它将跨行保留
    • MIN BenefitName - 此处的规则是选择第一个非空收益名称。聚合函数消除 NULL 并返回文本“First”
    • 1

相关问题