每分钟数最后10个数据
每分钟数最后10个数据
提问于 2017-10-31 13:42:57
我正在尝试编写一个SQL查询，该查询必须返回每分钟在最后10分钟内完成了多少事务。比如：
1分钟前:2次交易
2分钟前:1笔交易
..。
9分钟前:3次交易
10分钟前:4次交易
我试着做这个查询：
DECLARE @N int = 10;
DECLARE @NOW DATETIME = GETDATE();
WITH numbers( num ) AS (
SELECT 1 UNION ALL
SELECT 1 + num FROM numbers WHERE num < @N )
SELECT num AS minute,
(
SELECT COUNT(*) AS RESULTS
FROM [ApiTransactions]
WHERE [DateUtc] > DATEADD(year, -1, @NOW)
GROUP BY DATEPART(minute, DATEADD(minute, -num, @NOW))
)
FROM numbers;我还是不知道逻辑是否正确。我所知道的是我收到了错误：
每个组按表达式必须至少包含一个不是外部引用的列。
我为什么要犯这个错误？有更好的方法来进行查询吗？
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-11-01 11:06:52
我设法用两种不同的方式解决了我的问题：
DECLARE @N int = 10;
DECLARE @NOW DATETIME = GETDATE();
WITH numbers( num ) AS (
SELECT 1 UNION ALL
SELECT 1 + num FROM numbers WHERE num < @N )
SELECT num-1 AS minute,
(
SELECT COUNT(*) AS RESULTS
FROM [ApiTransactions]
WHERE [DateUtc] > DATEADD(minute, -num, @NOW) AND [DateUtc] < DATEADD(minute, -num+1, @NOW)
)
FROM numbers;作为产出：
minutes_ago amount
0 4 (most recents. still being updated)
1 0
2 2
3 3
4 1
5 2
6 1
7 2
8 1
9 3并附有：
DECLARE @NOW DATETIME = GETDATE();
SELECT CONVERT(int, DATEDIFF(second, [DateUtc], @NOW)/60) as TimePassed, COUNT([TypeCode]) as Amount
FROM [ApiTransactions]
WHERE [DateUtc] >= DATEADD (minute, -10 , @NOW)
GROUP BY CONVERT(int, DATEDIFF(second, [DateUtc], @NOW)/60)
ORDER BY CONVERT(int, DATEDIFF(second, [DateUtc], @NOW)/60)作为产出：
minutes_ago amount
0 4 (most recents. still being updated)
2 2
3 3
4 1
5 2
6 1
7 2
8 1
9 3Stack Overflow用户
发布于 2017-10-31 13:50:02
除非您需要在没有事务的情况下填写时间，否则不需要使用数字表。我首先要说的是：
SELECT DATEADD(minute, DATEDIFF(minute, 0, DateUtc), 0) as the_minute, COUNT(*)
FROM ApiTransactions
WHERE DateUtc > DATEADD(minute, -10, DateUtc)
GROUP BY DATEADD(minute, DATEDIFF(minute, 0, DateUtc), 0)
ORDER BY the_minute;Stack Overflow用户
发布于 2017-10-31 14:00:07
你可以用这个：
SELECT DATEDIFF(minute,[DateUtc],GETDATE()) as minutes_ago,
COUNT(*) tran_count
FROM ApiTransactions
WHERE DATEDIFF(second,[DateUtc],GETDATE()) <= 600 -- 10 minutes ago
GROUP BY DATEDIFF(minute,[DateUtc],GETDATE())输出：
minutes_ago tran_count
0 2
1 3
2 10
3 15
4 4
5 9
6 12
7 6
8 13
9 4
10 2页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47036911
复制相关文章
相似问题
腾讯云开发者
