重複數據的聚合

我們有一個關於重複數據的有趣問題。我們的源文件可以包含單個員工福利的多行數據。例如，可以將醫療保健福利拆分並報告為 3 行（3 行只是一個範例 - 可能更多）。

數據載入到暫存中沒有問題，並且將包含單個和多個收益行的混合。到目前為止，我所做的是執行聚合以查找具有多個福利的標識（聚合employeeid 和benefitid），如果它> 1，則將其移動到單獨的臨時表。

我現在需要做的是分析包含多個福利的暫存表，並根據employeeid 和benefitid 匯總數據。讓我們假設一個好處有 3 行。

employeeid 和 benefitid 將是辨識這些行的關鍵。其他行可能包括福利提供者、年度成本、合格和福利名稱（以及更多，但這些涵蓋了輸出單行所需的 4 種聚合類型）。

1. 如果 3 的列表中有多個提供者，我們應該將值設置為“多個”，否則它應該是不同的值
2. 每年的費用很簡單…所有 3 行的總和
3. 如果任何活動標誌為真，則將該值設置為真（1 可能為真，2 可能為假）
4. 如果有多個福利名稱，我們只選擇第一個非空值。

數據聚合後（3 行為 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

挑選查詢的一些技巧

• DISTINCT 我覺得這是它最髒的部分，但它解決了問題
• MIN/MAX BenefitProvider - 我找到我的視窗的第一個和最後一個（員工和福利 ID），如果它們不同，我通過 CASE 表達式確定，我使用文本 Multiple。否則，我只是抓住了最小值，但最大值也可以。哎呀，我可能只是跳過了第二個聚合呼叫，只是將其命名為列名。
• MAX 標誌 - 我通過使用無法聚合的位數據類型盡可能地做到了這一點，因此我顯式轉換為整數，然後將結果轉換回位。雖然相同的視窗。最終結果是，如果我的標誌設置為 true，它將跨行保留
• MIN BenefitName - 這裡的規則是選擇第一個非空的福利名稱。聚合函式消除 NULL 並返回文本“First”

引用自：https://dba.stackexchange.com/questions/126129