MS SQL查询每天的最小值/最大值/平均值

debugcn 发表于 Dev

史蒂文·马克斯

我有一个查询：

WITH cte AS(
SELECT T3.DateTime AS AADateTime,
        (T3.In_Minbps/1000)/1000 AS MinReceiveMbps,
                (T3.In_Maxbps/1000)/1000 AS MaxReceiveMbps,
                (T3.In_Averagebps/1000)/1000 AS AvgReceiveMbps,
                (T3.Out_Minbps/1000)/1000 AS MinTransmitMbps,
                (T3.Out_Maxbps/1000)/1000 AS MaxTransmitMbps,
                (T3.Out_Averagebps/1000)/1000 AS AvgTransmitMbps
FROM dbo.Nodes AS T1
    INNER JOIN dbo.Interfaces AS T2 ON [T1].[NodeID] = [T2].[NodeID]
    INNER JOIN InterfaceTraffic AS T3 ON [T2].[InterfaceID] = [T3].[InterfaceID]
WHERE [T1].[Caption] = 'cust-firewall01' 
    AND [T2].[InterfaceName] = 'reth0' 
    AND DateTime >= '2014-08-01 00:00:00' AND DateTime <= '2014-08-31 23:59:59'
)
SELECT MIN(AADateTime) AS AADateTime,
        MIN(MinReceiveMbps) AS MinReceiveMbps,
        MAX(MaxReceiveMbps) AS MaxReceiveMbps,
        MIN(MinTransmitMbps) AS MinTransmitMbps,
        MAX(MaxTransmitMbps) AS MaxTransmitMbps,
        AVG(AvgTransmitMbps) AS AvgTransmitMbps,
        AVG(AvgReceiveMbps) AS AvgReceiveMbps
FROM cte

上面的查询有效，但是返回所有记录的最小值/最大值/平均值，我需要做的是每天返回最小值/最大值/平均值。表格数据示例为：

Date,                In_Minbps, In_Maxbps, In_Averagebps, Out_Minbps, Out_Maxbps, Out_Averagebps
2014-08-01 00:00:00, 403227.2, 3489988,    1986171,       6509198,    6.510824e+07, 33357.06
2014-08-01 01:00:00, 404039.1, 3626866,    2211984,       4491261,    6.61291e+07, 37061.19

每天基本上有24条记录，我每天需要这样做：

SELECT MIN(AADateTime) AS AADateTime,
        MIN(MinReceiveMbps) AS MinReceiveMbps,
        MAX(MaxReceiveMbps) AS MaxReceiveMbps,
        MIN(MinTransmitMbps) AS MinTransmitMbps,
        MAX(MaxTransmitMbps) AS MaxTransmitMbps,
        AVG(AvgTransmitMbps) AS AvgTransmitMbps,
        AVG(AvgReceiveMbps) AS AvgReceiveMbps
FROM cte

恩德奇

SELECT CAST(Datetimefield AS DATE) AS Date,
        MIN(MinReceiveMbps) AS MinReceiveMbps,
        MAX(MaxReceiveMbps) AS MaxReceiveMbps,
        MIN(MinTransmitMbps) AS MinTransmitMbps,
        MAX(MaxTransmitMbps) AS MaxTransmitMbps,
        AVG(AvgTransmitMbps) AS AvgTransmitMbps,
        AVG(AvgReceiveMbps) AS AvgReceiveMbps
FROM cte
GROUP BY CAST(Datetimefield AS DATE)

它按日期分组您的记录。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。