Início / dba / Perguntas / 126129
Accepted
Clive Strong
Asked: 2016-01-14 07:41:24 +0800 CST

Agregação de dados duplicados

  • 772

Temos um problema interessante em torno de dados duplicados. Nossos arquivos de origem podem conter várias linhas de dados para um único benefício de funcionário. Por exemplo, um benefício de assistência médica pode ser dividido e relatado em 3 linhas (3 é apenas um exemplo - pode ser muito mais).

Os dados são carregados na preparação sem problemas e conterão uma mistura de linhas de benefícios únicos e múltiplos. O que fiz até agora foi realizar a agregação para localizar a identificação com vários benefícios (agregados em Employeesid e BenefitID) e, se for > 1, movê-lo para uma tabela de preparação separada.

O que preciso fazer agora é analisar a tabela de preparação com vários benefícios e agregar os dados com base em Employeesid e BenefitID. Vamos supor que um benefício tenha 3 linhas.

OEmployeeid e o BenefitID serão a chave para identificar essas linhas. Outras linhas podem incluir o provedor de benefícios, custo anual, nome do benefício e elegível (e muito mais, mas abrangem os 4 tipos de agregação necessários para gerar uma única linha).

  1. se houver vários provedores na lista de 3, devemos definir o valor como "Múltiplo", caso contrário, deve ser o valor distinto
  2. o custo anual é simples...SOMA de todas as 3 linhas
  3. se algum dos sinalizadores ativos for verdadeiro, defina o valor como verdadeiro (1 pode ser verdadeiro e 2 pode ser falso)
  4. se houver vários nomes de benefício, apenas selecionamos o primeiro valor não nulo.

Depois que os dados são agregados (3 linhas em 1, aplicando as regras acima), eles são mesclados com a tabela de benefícios de linha única.

ssis

1 respostas

  1. Best Answer

    Como você está usando 2012+, você tem funções de janela disponíveis para você, o que acho que torna isso mais fácil de resolver.

    Aqui está a minha configuração.

    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
)
;

    Eu então olharia para uma consulta algo como isto

    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

    Escolhendo alguns dos truques da consulta

    • DISTINCT Eu sinto que esta é a parte mais suja, mas resolve o problema
    • MIN/MAX BenefitProvider - Encontro o primeiro e o último da minha janela (IDs do funcionário e do benefício) e se forem diferentes, o que determino via expressão CASE, utilizo o texto Multiple. Caso contrário, peguei o mínimo, mas o máximo também funcionaria. Heck, eu provavelmente poderia ter apenas pulado a segunda chamada de agregação e apenas feito o nome da coluna.
    • Sinalizador MAX - tornei isso o mais difícil possível usando um tipo de dados de bit no qual você não pode agregar, então eu converto explicitamente para um número inteiro e, em seguida, converto os resultados disso de volta para um bit. Mesmo janela embora. O resultado líquido é que, se em qualquer lugar meu sinalizador estiver definido como verdadeiro, ele será preservado nas linhas
    • MIN BenefitName - a regra aqui é escolher o primeiro nome de benefício não nulo. A função agregada elimina o NULL e retorna o texto "First"
    • 1

relate perguntas