從不同計數過濾結果

我需要修改一個程序來對我的數據庫 進行統計。

我有一個臨時表，其中包含進行這些統計所需的所有數據。

它包含一天內工作單元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)

這個臨時表將儲存所有工作單元在一整天內所做的所有操作（就像一個日誌系統）。FormIdNb

的重要值為：

FormIdNb > 3 ==> 工作單元處於活動狀態

FormIdNb = 3 ==> 工作單元處於空閒狀態

FormIdNb < 3 ==> 工作單元處於非活動狀態

我現在收到的請求（感謝@AndriyM），返回在一天中的每個時間間隔（15 分鐘）中 處於ACTIVE、IDLE和INACTIVE的工作單元的數量：

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(DISTINCT(CASE WHEN d.FormIdNb > 3 THEN d.NumCavalier END)),
 IdleUnitCount     = COUNT(DISTINCT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END)),
 InactiveUnitCount = COUNT(DISTINCT(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
;

問題是，正如我上面所說，我使用的臨時表將數據儲存為日誌系統。

這意味著對於同一時間間隔的工作單元，它可以包含FormIdNb的任何值。 例如：1 行FormIdNb = 2、1 行FormIdNb = 3 和 1 行FormIdNb = 4。

我的問題是：

如何讓請求擺脫不相關的價值觀？

更準確地說：

如果一個工作單元是ACTIVE ，但在一段時間內也是IDLE和/或INACTIVE ，如何從**IDLE和INACTIVE計數中“刪除”這個工作單元，並且只保留每個間隔的FormIdNb的最大值時間？

同意 Scott Hodgin，您基本上要考慮MAX(FormIdNb)而不僅僅是FormIdNb，並根據 MAX 結果是否大於、等於或小於 3 來計算結果。

正如 Scott 建議的那樣，將相關子查詢添加到連接條件是一種方法。

另一種方法是使用派生表首先獲取每個NumCavalier和範圍的最大FormIdNb ：

SELECT
 t.StartTime,
 d.NumCavalier,
 MaxFormIdNb = MAX(d.FormIdNb)
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,
 d.NumCavalier

然後才得到計數。這是整個查詢：

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
 StartTime,
 ActiveUnitCount   = COUNT(CASE WHEN MaxFormIdNb > 3 THEN d.NumCavalier END),
 IdleUnitCount     = COUNT(CASE WHEN MaxFormIdNb = 3 THEN d.NumCavalier END),
 InactiveUnitCount = COUNT(CASE WHEN MaxFormIdNb < 3 THEN d.NumCavalier END)
FROM
 (
   SELECT
     t.StartTime,
     d.NumCavalier,
     MaxFormIdNb = MAX(d.FormIdNb)
   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,
     d.NumCavalier
 ) AS s
GROUP BY
 t.StartTime
;

請注意，它現在只是COUNT(…)，而不是COUNT(DISTINCT …)，因為派生表無論如何只產生不同的NumCavalier值。

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