就像我做的那样
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
是否有可能使日期输出没有毫秒?
当前回答
我知道我讲这个有点晚了，但我用了这个——非常简单的方法。这可以让你得到60分钟的切片，没有任何舍入问题。
Select
CONCAT(
Format(endtime,'yyyy-MM-dd_HH:'),
LEFT(Format(endtime,'mm'),1),
'0'
) as [Time-Slice]
其他回答
作者最初给出的答案还不错。为了扩展这个想法，你可以这样做
group by datediff(minute, 0, [Date])/10
这样你就可以按更长的时间分组，比如60分钟，720分钟，也就是半天。
终于讲完了
GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)
MySql:
GROUP BY
DATE(`your_date_field`),
HOUR(`your_date_field`),
FLOOR( MINUTE(`your_date_field`) / 10);
如果你想实际显示日期，有一个变量分组，并能够指定大于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.
