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

Question

我通过将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"。

Answer 1

您可以尝试在CONVERT(char(10), timestamp,126)上使用avg和group by，并将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窗口函数来获得每个base的第一个ssi。

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