我的问题或多或少是理论上的——按时间间隔获取查询数据的最佳方法是什么。
情况
我有一个管理计数器的系统（数以千计的计数器 - 计算电力/水的消耗量等......），每 15 分钟对计数器进行采样并存储在数据库中。有一张表存储所有计数器数据 [id、counterID、Timestamp、CounterValue]。
现在，我想查询有关Interval（每小时/每天/每周/每月）消耗的数据。
间隔消耗定义为下一个间隔开始时的第一次测量（计数）减去当前间隔开始时的第一次测量。由于所有测量值都被聚合（计数器），因此两个计数器之间的增量表示间隔内的消耗。
因为有很多数据，我希望查询速度快，所以我需要一种更好的方法，然后只查询整个表......（我认为......）查询整个数据（即使数据量很小 - 这个查询是减缓...）：
SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime
FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number
FROM counters_data) A
WHERE row_number = 1
Order by dateOfData;
我想到了一些方法来做到这一点：
- 在测量表中添加 [isHourly、isDaily、isWeekly...] 布尔列，当我存储数据时，我将更新这些标志。
- 为每个间隔添加包含列 [id、date、counterId、counterDataRecord] 的表，这样就会有 tblDailyCounter、tblWeeklyCounter 等...
我还有其他想法吗？
这些方法的优点和缺点是什么？
最推荐/最佳实践是什么？
那么这个答案取决于您的数据库是如何设计和许可的。
如果您拥有企业许可证，则应该查看分区。这样您就可以对数据进行分区，这将有助于您查询这些表。如果你进行了良好的分区，你将能够只从磁盘中读取表的一小部分，而不是查询所有内容。
我不知道你的应用程序是如何写入你的数据库的。但也许可以标记一个小时的第一条记录，这样您以后就不需要再以这种方式过滤它了？
另一个想法是 SQL 代理，它将在后台为您执行这些聚合。也许这可能是每小时一次，并将所有聚合值写入另一个累积表，您可以更快地查询它。