按时间间隔分组 [英] Group by Time Interval

问题描述

我需要将我的表分成15分钟间隔。我可以这样做：

I need to Group my Table into 15 minutes Intervals. I can do that with:

select dateadd(minute, datediff(minute, 0, ts) / 15 * 15, 0), sum (goodpieces) 
from StationCount 
Group by dateadd(minute, datediff(minute, 0, ts) / 15 * 15, 0)

但是要在图表中显示返回的数据，我还需要插入没有任何数据并且当前没有出现在我的select语句中的时间间隔。

But to display the returned data in a chart i need to insert also the intervals which don't have any data and aren't currently appearing in my select statement. How do i insert these?

推荐答案

使用每个可能的时间戳以15分钟为增量创建一个表，并从中进行LEFT JOIN到你的查询上面。

Create a table with every possible timestamp in 15 minute increments, and do a LEFT JOIN from it to your query above.

SELECT * FROM timestamps LEFT JOIN (SELECT dateadd......) ON timestamps.timestamp = StationCount.ts

如果您知道您的图表总是覆盖24小时，您只需创建一个表格，数字为0-95，然后为每个条目添加它到图表的开始时间。

If you know your chart always covers a 24 hour period, you only need to create a table with the numbers 0-95, then for each entry add it to the start time of your chart.

SELECT *
  FROM (SELECT dateadd(minute, <starttime>, number*15) timestamp FROM numbers) timestamps LEFT JOIN
       (SELECT dateadd......) ON timestamps.timestamp = StationCount.ts

这篇关于按时间间隔分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！