多次拆分查詢結果

我需要創建一個過程來對我的數據庫進行統計。

首先，我決定創建一個臨時表來“簡化”我的工作。

它包含一天工作單元（NumCavalier）的所有相關操作（FormIdNb）。

-- Every action from all units will be stored in this temp table
-- DateHeure = Time of action
-- NumCavalier = ID of working unit
-- FormIdNb = Action type
CREATE TABLE #tmp_DailyMissions (
DateHeure datetime,
NumCavalier  nchar(3),
FormIdNb int
)

INSERT INTO #tmp_DailyMissions
SELECT        DateHeure, Cavalier.NumCavalier, FormIdNb
FROM            ECN4, Cavalier
WHERE        (DateHeure BETWEEN @StartTime AND @EndTime) AND (Cavalier.Terminal = @terminal) AND (Cavalier.NumCavalier = ECN4.NumCavalier)

問題是，我無法提出我想要的要求：

1. 我需要按時間間隔拆分結果（1 天 15 分鐘）
2. 我需要將每個工作單元“NumCavalier”的“FormIdNb”的最大值再次拆分為：

FormIdNb > 3 ==> 工作單元在該時間間隔內處於活動狀態

FormIdNb = 3 ==> 工作單元在該時間間隔內空閒（如果在同一時間間隔內不活動）

FormIdNb < 3 ==> 工作單元在該時間間隔內不活動（如果在同一時間間隔內不活動或空閒）

3. 最後，根據之前的值對每個工作單元的時間間隔進行計數。

我已經這樣做了，它可以部分工作，但它沒有考慮前面連接的值（例如：一個工作單元可以同時顯示為活動和空閒，等等……）。

我認為我必須進行很多更改才能使其正常工作：

;WITH 
time_cte(StartTime, EndTime) AS
(
SELECT @starttime StartTime, DATEADD(mi, 15, @starttime) EndTime
UNION ALL
SELECT EndTime, DATEADD(mi, 15, EndTime) FROM time_cte
WHERE EndTime < @EndTime
)

SELECT StartTime, Count(DISTINCT CavsTerminal_ACTIVE.NumCavalier),
Count(DISTINCT CavsTerminal_IDLE.NumCavalier), 
Count(DISTINCT CavsTerminal_INACTIVE.NumCavalier)
FROM time_cte
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) > 3
) CavsTerminal_ACTIVE ON  CavsTerminal_ACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) = 3
) CavsTerminal_IDLE ON  (CavsTerminal_IDLE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) < 3
) CavsTerminal_INACTIVE ON  (CavsTerminal_INACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
GROUP BY StartTime

如果不實施 #2，您的查詢可能如下所示：

WITH time_cte(StartTime, EndTime) AS
 (
   SELECT
     @starttime StartTime,
     DATEADD(mi, 15, @starttime) EndTime

   UNION ALL

   SELECT
     StartTime = @starttime,
     EndTime   = DATEADD(mi, 15, @starttime)
   FROM
     time_cte
   WHERE
     EndTime < @EndTime
 )
SELECT
 t.StartTime,
 UnitCount = COUNT(d.NumCavalier)
FROM
 time_cte AS t
 LEFT OUTER JOIN #tmp_DailyMissions AS d
   ON d.DateHeure >= t.StartTime
   AND d.DateHeure < t.EndTime
GROUP BY
 t.StartTime
;

現在，為了COUNT(d.NumCavalier)根據 的值將 拆分為三個值，您在計數時FormIdNb檢查該值- 如下所示：

WITH time_cte(StartTime, EndTime) AS
 (
   SELECT
     StartTime = @starttime,
     EndTime   = DATEADD(mi, 15, @starttime)

   UNION ALL

   SELECT
     EndTime,
     DATEADD(mi, 15, EndTime)
   FROM
     time_cte
   WHERE
     EndTime < @EndTime
 )
SELECT
 t.StartTime,
 ActiveUnitCount   = COUNT(CASE WHEN d.FormIdNb < 3 THEN d.NumCavalier END),
 IdleUnitCount     = COUNT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END),
 InactiveUnitCount = COUNT(CASE WHEN d.FormIdNb > 3 THEN d.NumCavalier END)
FROM
 time_cte AS t
 LEFT OUTER JOIN #tmp_DailyMissions AS d
   ON d.DateHeure >= t.StartTime
   AND d.DateHeure < t.EndTime
GROUP BY
 t.StartTime
;

這稱為條件聚合：您正在根據某些條件聚合結果。

該方法的魅力之一是，如果需要，您仍然可以通過包含COUNT(d.NumCavalier)在結果列集中獲得相同輸出中的總計數。

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