SQL 查询给出一个数据值的总和并计算它超过 60 的次数

Question

我正在尝试查询以下数据。对于每个时间段，从 PrctBusy 列我想要最大、最小、平均、总计数和次数 > 60。

Array           TimeStamp       Category Instance PrctBusy  QueUtil
000198701258    2016-07-01 00:00    FE_DIR  FA-1E   59      0
000198701258    2016-07-01 00:00    FE_DIR  FA-1F   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-1G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-1H   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-2E   64      0
000198701258    2016-07-01 00:00    FE_DIR  FA-2F   10      0
000198701258    2016-07-01 00:00    FE_DIR  FA-2G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-2H   10      0
000198701258    2016-07-01 00:00    FE_DIR  FA-3E   64      0
000198701258    2016-07-01 00:00    FE_DIR  FA-3F   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-3G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-3H   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-4E   64      0
000198701258    2016-07-01 00:00    FE_DIR  FA-4F   10      0
000198701258    2016-07-01 00:00    FE_DIR  FA-4G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-4H   9       0
000198701258    2016-07-01 00:05    FE_DIR  FA-1E   62      0
000198701258    2016-07-01 00:05    FE_DIR  FA-1F   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-1G   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-1H   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2E   66      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2F   13      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2G   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2H   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3E   66      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3F   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3G   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3H   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4E   66      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4F   13      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4G   13      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4H   11      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1E   52      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1F   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1G   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1H   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2E   56      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2F   13      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2G   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2H   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3E   56      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3F   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3G   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3H   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4E   56      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4F   13      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4G   13      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4H   11      0

我尝试了几种不同的查询，最接近我想要的是:

SELECT TimeStamp, 
    max(prctbusy) AS maxPrctBusy, 
    min(prctbusy) AS minPrctBusy, 
    AVG(prctbusy) AS avgPrctBusy, 
    count(prctbusy) AS numPrctBusy,  
  ( SELECT count(prctbusy) 
    FROM VMAXPortUtil 
    WHERE array like '%1258%' and Category like '%FE_DIR%' and prctbusy > 60
    ) AS above60 
FROM VMAXPortUtil
WHERE array like '%1258%' and Category like '%FE_DIR%' 
GROUP BY TimeStamp 
Order by TimeStamp

不幸的是，结果不是很正确，给出的总数超过 60，而不是每个时间戳。

我得到了什么:

TimeStamp       maxPrctBusy minPrctBusy avgPrctBusy numPrctBusy above60
2016-07-01 00:00    64      9           22          16          8494
2016-07-01 00:05    66      11          25          16          8494
2016-07-01 00:10    56      11          23          16          8494

我需要什么:

TimeStamp       maxPrctBusy minPrctBusy avgPrctBusy numPrctBusy above60
2016-07-01 00:00    64      9           22          16          3
2016-07-01 00:05    66      11          25          16          4
2016-07-01 00:10    56      11          23          16          0

我认为联合可能是我需要的，并计算 60 以上的数字。但我还没有弄清楚如何使结果行数相同。如有任何建议，我们将不胜感激。

Answer 1

SELECT TimeStamp, 
    max(prctbusy) AS maxPrctBusy, 
    min(prctbusy) AS minPrctBusy, 
    AVG(prctbusy) AS avgPrctBusy, 
    count(prctbusy) AS numPrctBusy,
    sum(case when prctbusy > 60 then 1 else 0 end) AS above60
FROM VMAXPortUtil
WHERE array like '%1258%' and Category like '%FE_DIR%' 
GROUP BY TimeStamp 
Order by TimeStamp

假设您希望保留 numPrctBusy，这应该可以解决问题。

您可以在聚合函数中添加非组字段，允许您进行比较检查