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

史蒂文·马克斯

我有一个查询:

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] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

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

来自分类Dev

MS Access SQL计算dBµV的对数平均值

来自分类Dev

在SQL中找到最大平均值

来自分类Dev

如何在一个MS SQL查询中获得不同的平均值和总和值?

来自分类Dev

MS Access SQL平均值函数(带小数位)

来自分类Dev

提高查询性能,计算MS-Access中大型数据集的7天滚动平均值

来自分类Dev

如何在可以访问最小值、最大值、Q1、Q3 和平均值但无法访问变量的 MS Excel 中创建箱线图?

来自分类Dev

最小和最大的平均值

来自分类Dev

动态MS SQL查询

来自分类Dev

SQL查询计算平均值的平均值

来自分类Dev

在一组列中而不是逐列中找到最小最大平均值,75和95分位数

来自分类Dev

SQL 最小值的平均值

来自分类Dev

计算MS Excel中具有不同ID的行的平均值

来自分类Dev

MS Excel 2016:如何获取大量离散范围内的数字平均值?

来自分类Dev

如何获得最大平均值及其按月分组的ID

来自分类Dev

如何从MS Access / SQL中的第一条记录开始获取一列中每三个记录的平均值?

来自分类Dev

对平均值函数进行SQL查询

来自分类Dev

获取子查询SQL的平均值

来自分类Dev

SQL SELECT 查询总和 > 平均值

来自分类Dev

MS Access平均或总和

来自分类Dev

MS Access SQL插入查询

来自分类Dev

优化MS-SQL查询

来自分类Dev

MS SQL查询打卡时间

来自分类Dev

获得最大,平均值和最小值

来自分类Dev

用MS子连接MS SQL Server

来自分类Dev

MS Access查询语法

来自分类Dev

MS访问查询聚合

来自分类Dev

MS Access:查询

来自分类Dev

MS访问查询列表