按时间间隔分组

发布于 2024-12-13 10:42:31 字数 292 浏览 3 评论 0原文

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

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 语句中的间隔。我该如何插入这些？

原文

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)

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小ぇ时光︴ 2024-12-20 10:42:31

创建一个表，其中包含以 15 分钟为增量的每个可能的时间戳，然后从该表与上面的查询执行 LEFT JOIN。

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

如果您知道图表始终涵盖 24 小时时间段，则只需创建一个包含数字 0-95 的表格，然后对于每个条目将其添加到图表的开始时间。

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

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

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

回复收藏 0 原文

慕烟庭风 2024-12-20 10:42:31

像这样的事情可能会对你有所帮助。

declare @startDate datetime
        ,@endDate datetime

set @startDate = '2011-10-25'
set @endDate = '2011-10-26'

;with fifteenMinutes
as
(
    select  dateadd(minute, datediff(minute, 0, @startDate) / 15 * 15, 0) as q
    UNION ALL
    select  dateadd(minute, 15, q)
    from fifteenMinutes
    where q < @endDate
)

select * from fifteenMinutes

Something like this might help you.

declare @startDate datetime
        ,@endDate datetime

set @startDate = '2011-10-25'
set @endDate = '2011-10-26'

;with fifteenMinutes
as
(
    select  dateadd(minute, datediff(minute, 0, @startDate) / 15 * 15, 0) as q
    UNION ALL
    select  dateadd(minute, 15, q)
    from fifteenMinutes
    where q < @endDate
)

select * from fifteenMinutes

回复收藏 0 原文

~没有更多了~