修改GroupBy以獲得正確的結果

我有大量資料集，其中包含非活動客戶的歷史資料及其計費（服務）資訊。我需要按 ClientId、Service 和 Year 對資料進行分組，并生成匯總欄位，例如 TotalCharges、TotalProvidedServices 和 DaysStayedWithUs（此列的問題）。

我能夠成功地按 ClientId 和 Service 對資料進行分組，但是按 Year 分組會產生錯誤的結果，我知道為什么。

原因是因為從 ServiceDate 列生成的 Year 列最初用于創建 FirstServiceDate 和 LastServiceDate => 以便能夠生成 DaysStayedWithUs（LastServiceDate - FirstServiceDate 將給我們客戶與我們在一起的天數（DaysStayedWithUs））。當我按年份分組時，查詢會找到 FirstServiceDate、LastServiceDate 并根據它計算 DaysStayedWithUs ......因此我必須找到另一種方法來實作我需要的結果（如下所示）。

客戶表：

 ClientId

1234567890

計費表：

ClientId       Service      ServiceDate   ChargesTotal

1234567890     Cleaning       12/4/2018       190.17
1234567890     Cleaning       1/22/2019        97.8
1234567890     Cleaning       1/29/2019        97.8
1234567890     Cleaning       2/5/2019         97.8
1234567890     Cleaning       2/12/2019        97.8
1234567890     Cleaning       3/5/2019         97.8
1234567890     Cleaning       2/19/2019        97.8
1234567890     Cleaning       3/12/2019        97.8
1234567890     Cleaning       3/19/2019        97.8
1234567890     Cleaning       3/26/2019        97.8

我的代碼：

SELECT GroupedTable.ClientId, 
        GroupedTable.Service, 
        ---GroupedTable.Year,
        GroupedTable.FirstServiceDate, 
        GroupedTable.LastServiceDate, 
        GroupedTable.TotalCharges, 
        GroupedTable.TotalProvidedServices, 
        GroupedTable.DaysStayedWithUs

FROM (SELECT MainTable.ClientId, 
        MainTable.Service,
        SUM(MainTable.Charges) AS TotalCharges,      
        COUNT(*) AS TotalProvidedServices, 
        DATEDIFF(DAY, MIN(MainTable.ServiceDate), MAX(MainTable.ServiceDate)) as DaysStayedWithUs,
        ---MainTable.Year,
        MIN(MainTable.ServiceDate) AS FirstServiceDate,
        MAX(MainTable.ServiceDate) AS LastServiceDate

        FROM (SELECT c.ClientId, 
                b.Service,
                b.ServiceDate,
                ---YEAR(b.ServiceDate) AS Year, 
                b.Charges

            FROM Client as c

            LEFT JOIN Billing as b
                ON (c.ClientId = b.ClientId)

            WHERE b.ClientId = 1234567890

            ) as MainTable

---GROUP BY MainTable.ClientId, MainTable.ServiceIdentifier, MainTable.Year) AS GroupedTable
GROUP BY MainTable.ClientId, MainTable.Service) AS GroupedTable

上面的代碼產生正確的結果：（年份取消選擇）

如果我在 Group By 中包含 Year，則輸出為：

PS 我用黃色標記了有問題的單元格（技術上 FirstServiceDate 和 LastServiceDate 需要更正，然后 DaysStayedWithUs 將自行調整）

結果我需要完成：

希望有可能實作。謝謝。

uj5u.com熱心網友回復：

使用視窗函式MIN OVER來MAX OVER查看一年是否是客戶和服務的第一年/最后一年。

SELECT 
  c.clientid, 
  b.service,
  YEAR(b.servicedate) as year, 
  SUM(b.charges) AS total_charges,
  COUNT(*) AS total_provided_services
  CASE WHEN YEAR(b.servicedate) = MIN(YEAR(b.servicedate)) OVER (PARTITION BY c.clientid, b.service)
    THEN MIN(b.servicedate) ELSE DATEFROMPARTS(YEAR(b.servicedate), 1, 1)
  END AS first_service_date, 
  CASE WHEN YEAR(b.servicedate) = MAX(YEAR(b.servicedate)) OVER (PARTITION BY c.clientid, b.service)
    THEN MAX(b.servicedate) ELSE DATEFROMPARTS(YEAR(b.servicedate), 12, 31)
  END AS last_service_date,
  DATEDIFF(DAY, 
    CASE WHEN YEAR(b.servicedate) = MIN(YEAR(b.servicedate)) OVER (PARTITION BY c.clientid, b.service)
      THEN MIN(b.servicedate) ELSE DATEFROMPARTS(YEAR(b.servicedate), 1, 1) END, 
    CASE WHEN YEAR(b.servicedate) = MAX(YEAR(b.servicedate)) OVER (PARTITION BY c.clientid, b.service)
      THEN MAX(b.servicedate) ELSE DATEFROMPARTS(YEAR(b.servicedate), 12, 31) END 
  ) AS days_stayed_with_us
FROM client AS c
LEFT JOIN billing AS b ON b.clientid = c.clientid
WHERE c.clientid = 1234567890
GROUP BY c.clientid, b.service, YEAR(b.timebillingservicedate)
ORDER BY c.clientid, b.service, YEAR(b.timebillingservicedate);

標籤：

上一篇：從日期范圍插入多行

下一篇：在perl中，在命令列上呼叫的腳本與腳本所需的檔案的公認條款是什么？[復制]