时间戳

首页 > 解决方案 > 从具有相同日期和时间的 2 个不同数据表中查找变量的平均值

sql-server - 从具有相同日期和时间的 2 个不同数据表中查找变量的平均值

问题描述

For i As Integer = 0 To table.Rows.Count - 1
Dim temp1 As Integer = table.Rows(i)("Temperature")
Dim temp2 As Integer = table2.Rows(i)("Temperature")
Dim date1 As DateTime = table.Rows(i)("Date Time")
Dim date2 As DateTime = table2.Rows(i)("Date Time")
table.Rows(i)("Temperature") = (table.Rows(i)("Temperature") + table2.Rows(i)("Temperature")) / 2
Next

sensor  Temperature Humidity    Date Time   sensor  Temperature Humidity    Date Time
a   22.6    69.3    2020-08-12 0:04:22  b   23  55.7    2020-08-12 0:02:42
a   22.6    69.3    2020-08-12 0:29:23  b   22.9    55.3    2020-08-12 0:27:43
a   22.5    69.2    2020-08-12 0:59:24  b   22.6    55.5    2020-08-12 0:57:54
a   22.5    69.2    2020-08-12 1:29:26  b   23  55.2    2020-08-12 1:27:54
a   22.2    68.9    2020-08-12 5:02:02  b   23  55.6    2020-08-12 1:57:56
a   22.4    68.8    2020-08-12 7:33:44  b   23  54.5    2020-08-12 2:27:57

这是我从两个表中获取所有温度值和日期时间值并获取平均值的代码。日期时间值可以四舍五入到最接近的 xx:00 或 xx:30 但我如何获得相同日期时间的平均值，因为它们从上面的数据中可以看出，可能缺少日期时间。使用 for 循环 i 只会给我不同日期时间值的平均值。编辑：请不要给出任何与 sql 语句相关的答案，因为检索上述结果的原始语句已经非常复杂，在我的情况下，它不可能用 sql 语句来完成。

标签： sql-servervb.net

解决方案

我会将计算移至 T-SQL 并在两个表的 UNION ALL 上使用 GROUP BY。GROUP BY 必须在一个或多个值上，为您提供所需的时间段。对于不适合现有函数的时隙，需要一个表达式。在这种情况下，它有点棘手，因为你想四舍五入到最接近的半分钟。

我得到了自午夜以来的秒数。我添加 15 秒来将前 15 秒四舍五入。我除以 30 以截断半分钟。乘以 30 以获得秒数。这是添加回午夜的值。“sample_time”是您的“日期时间”。（有些人会使用 0 而不是日期部分，但会在几秒钟内溢出。）

SELECT 
    DATEADD(s, 
        (15+DATEDIFF(s, CAST(CAST(sample_time as date) as datetime), sample_time))/30 * 30, 
        CAST(CAST(sample_time as date) as datetime)
        ) as [TimeSlot],
    AVG(Temperture) as [TemperatureAvg]
FROM (
    SELECT sample_time, temperature
    FROM table1
    UNION ALL
    SELECT sample_time, temperature
    FROM table2
)
GROUP BY DATEADD(s, 
    (15+DATEDIFF(s, CAST(CAST(sample_time as date) as datetime), sample_time))/30 * 30, 
    CAST(CAST(sample_time as date) as datetime)
    )

如果您需要所有时隙的表，则 GROUP BY 是不够的，正如您所指出的。使用“数字表”（网上应该有很多关于此的）来生成您想要的时间。您可以将其放在 CTE、临时表、本地表或派生表中。（如果您想要单个查询，那么 CTE 或派生表是可行的方法。）然后使用该表与 TimeSlot 上的真实数据进行左连接。决定如何使用 ISNULL 等显示空数据。

例如，如果您想要 24 小时的数据，则需要从 0 到 24 * 60 * 2 的数字（24 小时每分钟 2 个插槽）。如果不包括最后一点，请使用 24 * 60 * 2 - 1。如果数字表生成器的数字比您需要的多（对于某些有效方法来说是正常的），请使用 WHERE 子句来获取您需要的范围。给定一个开始日期，DATEADD 函数可用于设置时段的时间列。可能类似于 DATEADD(second, @startdate, [num] * 30)。

推荐阅读