时间戳

首页 > 解决方案 > 如何在 SQL 中正确使用 AVG()？

sql - 如何在 SQL 中正确使用 AVG()？

问题描述

我通过在下表中存储 SSI 值来跟踪 RF 网络中的基站性能：

TABLE BASE_SSI:
    wayside     BIGINT (PK)
    timestamp   datetime
    base        varchar(12)
    ssi         int

SSI 每小时读取一次，并与大约一年的读数一起存储在此表中。

我可以使用以下查询提取过去任何一天的 SSI 值：

select wayside,timestamp,base,ssi from BASE_SSI 
where wayside=225520220000 
and timestamp > DATEADD(day,-7,getdate())
and timestamp < DATEADD(day,-6,getdate())
order by timestamp desc

这给了我这些结果：

wayside         timestamp             base        ssi
225520220000    2018-11-02 00:21:09   423.3.01    33
225520220000    2018-11-01 22:31:03   423.3.01    32
225520220000    2018-11-01 20:40:53   423.3.01    32
225520220000    2018-11-01 18:50:45   423.3.01    31
225520220000    2018-11-01 17:00:35   423.3.01    33
225520220000    2018-11-01 15:10:26   423.3.01    34
225520220000    2018-11-01 13:20:20   423.3.01    38
225520220000    2018-11-01 11:30:11   423.3.01    37
225520220000    2018-11-01 09:40:03   423.3.01    35
225520220000    2018-11-01 07:49:03   423.3.01    35
225520220000    2018-11-01 05:59:50   423.3.01    34
225520220000    2018-11-01 04:09:43   423.3.01    34
225520220000    2018-11-01 02:19:34   423.3.01    34

我需要的是一个查询，它可以为我提供过去 24 小时内所有路边的平均 ssi。结果应该是：

wayside         date         base        avg_ssi
225520220000    2018-11-01   423.3.01    34
225520230000    2018-11-01   423.2.21    21
225520240000    2018-11-01   423.4.11    18
225520250000    2018-11-01   423.1.21    55
225520260000    2018-11-01   422.2.01    62
225520270000    2018-11-01   452.3.07    33
225520280000    2018-11-01   425.1.03    25

如图所示，我只需要平均 ssi 的整数值。

我尝试在 SELECT 语句中使用 AVG(ssi)，但我不确定如何将其应用于选定时间段内的所有路边值。例如，在原始查询中，我在 SELECT 行中添加了“AVG(ssi) as avg_ssi”，但它只为每条记录生成“平均”SSI，这与 SSI 值相同。

我的这个查询的伪 SQL 版本是“显示给定日期每个路边的 24 小时平均 SSI”。

标签： sqlsql-serversyntax

解决方案

您可以尝试使用avgwith group byon CONVERT(char(10), timestamp,126)let datetime 来yyyy-MM-dd格式化group by每个日期。

select wayside,CONVERT(char(10), timestamp,126),base,avg(ssi) as avg_ssi
from BASE_SSI 
where timestamp > DATEADD(day,-7,getdate()) and timestamp < DATEADD(day,-6,getdate())
group by CONVERT(char(10), timestamp,126),base,wayside
order by timestamp desc

编辑

从您的评论中，您可以尝试使用ROW_NUMBER窗口功能来获得ssi每个base.

SELECT wayside,
       CONVERT(char(10), timestamp,126),
       base,
       ssi as avg_ssi 
FROM (
    select wayside,timestamp,base,ssi,Row_number() over(partition by base order by timestamp) rn
    from BASE_SSI 
    where timestamp > DATEADD(day,-7,getdate()) and timestamp < DATEADD(day,-6,getdate())
) t1
where rn = 1
order by timestamp desc

推荐阅读