就像我做的那样
SELECT [Date]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY [Date]
如何指定分组时段?我使用的是MS SQL 2008。
我已经试过了，使用% 10和/ 10。
SELECT MIN([Date]) AS RecT, AVG(Value)
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY (DATEPART(MINUTE, [Date]) / 10)
ORDER BY RecT
是否有可能使日期输出没有毫秒?
当前回答
对于SQL Server 2012，虽然我相信它可以在SQL Server 2008R2中工作，但我使用以下方法将时间切片到毫秒:
DATEADD(MILLISECOND, -DATEDIFF(MILLISECOND, CAST(time AS DATE), time) % @msPerSlice, time)
这是通过:
获取固定点和目标时间之间的毫秒数:@ms = DATEDIFF(MILLISECOND, CAST(time AS DATE)， time) 将这些毫秒分割为时间切片的余数为:@rms = @ms % @msPerSlice 将该余数的负数与目标时间相加以获得切片时间:DATEADD(MILLISECOND， -@rms, time)
不幸的是，由于溢出了微秒和更小的单位，因此更大、更精细的数据集将需要使用不太方便的固定点。
我没有严格地对其进行基准测试，我也不是大数据行业的人，所以您的情况可能会有所不同，但性能并不明显比我们在设备和数据集上尝试的其他方法差，而且任意切片在开发人员便利性方面的付出对我们来说是值得的。
其他回答
作者最初给出的答案还不错。为了扩展这个想法，你可以这样做
group by datediff(minute, 0, [Date])/10
这样你就可以按更长的时间分组，比如60分钟，720分钟，也就是半天。
如果你想实际显示日期，有一个变量分组，并能够指定大于60分钟的时间框架:
DECLARE @minutes int
SET @minutes = 90
SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Date]) / @minutes * @minutes, 0) as [Date],
AVG([Value]) as [Value]
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY
DATEDIFF(MINUTE, 0, [Date]) / @minutes
短而甜
GROUP BY DATEDIFF(MINUTE, '2000', date_column) / 10
感谢德里克的回答，这构成了这篇文章的核心。
实际使用情况
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '2000', aa.[date]) / 10 * 10, '2000')
AS [date_truncated],
COUNT(*) AS [records_in_interval],
AVG(aa.[value]) AS [average_value]
FROM [friib].[dbo].[archive_analog] AS aa
-- WHERE aa.[date] > '1900-01-01'
GROUP BY DATEDIFF(MINUTE, '2000', aa.[date]) / 10
-- HAVING SUM(aa.[value]) > 1000
ORDER BY [date_truncated]
详情及评论
The MINUTE and 10 terms can be changed to any DATEPART and integer,1 respectively, to group into different time intervals. e.g. 10 with MINUTE is ten minute intervals; 6 with HOUR is six hour intervals. If you change the interval a lot, you might benefit from declaring it as a variable. DECLARE @interval int = 10; SELECT DATEADD(MINUTE, DATEDIFF(…) / @interval * @interval, '2000') … GROUP BY DATEDIFF(…) / @interval Wrapping it with a DATEADD invocation with a multiplier will give you a DATETIME value, which means: Data sources over long time intervals are fine. Some other answers have collision between years. Including it in the SELECT statement will give your output a single column with the truncated timestamp. In the SELECT, the division (/) operation after DATEDIFF truncates values to integers (a FLOOR shortcut), which yields the beginning of time intervals for each row. If you want to label each row with the middle or end of its interval, you can tweak the division in the second term of DATEADD with the bold part below: End of interval: …) / 10 * 10 + 10 , '2000'), credit to Daniel Elkington. Middle of interval: …) / 10 * 10 + (10 / 2.0) , '2000').
琐事
'2000'是一个“锚定日期”，SQL将围绕它执行日期数学。大多数示例代码使用0作为锚，但是JereonH发现在按秒或毫秒对最近的日期进行分组时遇到整数溢出
如果您的数据跨越几个世纪，3在GROUP BY中使用单个锚定日期数秒或毫秒仍然会遇到溢出。对于这些查询，你可以要求每行将分箱比较锚定到它自己的日期的午夜:
使用DATEADD(DAY, DATEDIFF(DAY, 0, aa.[date])， 0)来代替上面出现的'2000'。您的查询将完全不可读，但它将工作。 另一种替代方法可能是CONVERT(DATETIME, CONVERT(DATE, aa.[DATE]))作为替换。
1 If you want all :00 timestamps to be eligible for binning, use an integer that your DATEPART's maximum can evenly divide into.4 As a counterexample, grouping results into 13-minute or 37-hour bins will skip some :00s, but it should still work fine. 2 The math says 232 ≈ 4.29E+9. This means for a DATEPART of SECOND, you get 4.3 billion seconds on either side, which works out to "anchor date ± 136 years." Similarly, 232 milliseconds is ≈ 49.7 days. 3 If your data actually spans centuries or millenia and is still accurate to the second or millisecond… congratulations! Whatever you're doing, keep doing it. 4 If you ever wondered why our clocks have a 12 at the top, reflect on how 5 is the only integer from 6 (half of 12) or below that is not a factor of 12. Then note that 5 × 12 = 60. You have lots of choices for bin sizes with hours, minutes, and seconds.
试试这个查询。它是一列。(参考@nobilist的答案)
GROUP BY CAST(DATE(`your_date_field`) as varchar) || ' ' || CAST(HOUR(`your_date_field`) as varchar) || ':' || CAST(FLOOR(minute(`your_date_field`) / 10) AS varchar) || '0' AS date_format
应该是这样的
select timeslot, count(*)
from
(
select datepart('hh', date) timeslot
FROM [FRIIB].[dbo].[ArchiveAnalog]
)
group by timeslot
(不是100%确定语法-我更喜欢Oracle的那种人)
在Oracle中:
SELECT timeslot, COUNT(*)
FROM
(
SELECT to_char(l_time, 'YYYY-MM-DD hh24') timeslot
FROM
(
SELECT l_time FROM mytab
)
) GROUP BY timeslot
