使用group by对大数据执行步骤/范围查询

6tqwzwtp 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(284)

我有一个表，每几秒钟存储一个传感器的温度读数
示例数据如下所示

nId nOperationId    strDeviceIp     nIfIndex    nValue      nTimestamp
97  2               192.168.99.252  1           26502328    1593828551
158 2               192.168.99.252  1           26501704    1593828667
256 2               192.168.99.252  1           26501860    1593828788
354 2               192.168.99.250  1           26501704    1593828908
452 2               192.168.99.250  1           26501692    1593829029

我想得到每个设备的平均温度，所以我运行了以下查询

select strDeviceIp, AVG(CAST(nValue as bigint)) as val1
from myTable
where nOperationId = 2 and nTimestamp >= 1593828600 and nTimestamp <= 1593838600
group by  strSwitchIp;

在那里我可以通过我想要的时间范围。
我的问题是，这给了我总平均值，但我想要的步骤或范围，我想实现，而不是一行，我会得到一个范围内的所有值/步骤，比如说5分钟作为一行。
p、我想展示一个图表。
运行以下查询

strSwitchIp     average
192.168.99.252  26501731

但我想

strSwitchIp     average     timestamp
192.168.99.252  26201731    1593828600
192.168.99.252  26532731    1593828900
192.168.99.252  24501721    1593829200
192.168.99.252  26506531    1593829500

在本例中，我希望每300秒为每个设备获取一行。

sql sql-server

来源：https://stackoverflow.com/questions/62767892/query-using-group-by-with-steps-range-over-large-data

2条答案

按热度按时间

lvmkulzt1#

自从你的 nTimestamp 是秒数，您只需将它添加到 GROUP BY . 除以300得到300秒（5分钟）的间隔。在sql server中 / 是整数除法，它丢弃小数部分。

select 
    strSwitchIp
    ,AVG(CAST(nValue as bigint)) as val1
    ,(nTimestamp / 300) * 300 AS Timestamp
from myTable
where 
    nOperationId = 2 and nTimestamp >= 1593828600 and nTimestamp <= 1593838600
group by
    strSwitchIp
    ,nTimestamp / 300
;
``` `nTimestamp / 300` 给出一个整数，是自1970年以来的5分钟间隔数。 `/` 这里舍弃分数部分。
当这个数字再乘以300时，它又变成1970年以来的秒数，但“四舍五入”到最接近的5分钟间隔。正如你在问题中所显示的预期结果。
例如：

1593828667 / 300 = 5312762.2233333333333333333333333
discard fractional part
1593828667 / 300 = 5312762
5312762 * 300 = 1593828600

因此，1593828600和1593828899之间的所有时间戳变为1593828600，并且这些时间戳的所有值被分组到一行并求平均值。

赞(0）回复(0）举报 2021-07-24

uurity8g2#

您可以这样使用分区：

select strDeviceIp, AVG(CAST(nValue as bigint)) as val1,
ROW_NUMBER() over(partition by nTimestamp order by nTimestamp desc) as ROW_NO from AmyTable) Q  where q.ROW_NO%5=0
....

赞(0）回复(0）举报 2021-07-24

我来回答

使用group by对大数据执行步骤/范围查询

2条答案

相关问题

热门标签

最新问答