SQL选择每天总计数,但按日期和小时分组(每小时运行总计)
我发现了类似于我想要的东西here,但它没有给出完全正确的输出,而且我正在一个愚蠢的时刻,试图想出正确的解决方案...SQL选择每天总计数,但按日期和小时分组(每小时运行总计)
我们有一个AVS服务器(实时帐户验证服务,但这是无关紧要的),我想知道有多少请求今天(或任何其他日子),在每个小时。例如,假设在上午8点到9点之间发出了100个请求,并且在9点到10点之间发出了50个请求。输出应该有在8AM,和在9AM,但低于我的查询被显示的请求的数量在每一个小时来代替。
我可以看到它为什么这样做...它通过小时分组,因此它显示我在每个小时的要求,但是这不是我想要的东西,这是每小时一个运行总计。 (所以我可以每天运行它,看看请求的数量看起来像昨天这个时间,等等......并且指出这是一个缓慢还是快速的日子。)我该怎么做?
DECLARE @CurrentDate DATETIME = '14 nov 2014'
SELECT
CAST(DateCreated AS DATE) [Date],
DATEPART(hour,DateCreated) [Hour],
COUNT(ID) [Requests]
FROM Request
WHERE
DateCreated >= @CurrentDate AND Request.DateCreated < DATEADD(DAY, 1, @CurrentDate)
GROUP BY
CAST(DateCreated AS DATE),
DATEPART(hour, DateCreated)
ORDER BY
CAST(DateCreated AS DATE)
感谢您对所有的答案。虽然他们没有做到我想要的东西,但这是我的最后一个问题,以防它可能帮助某人。答案的问题,也可能是我的问题,结果只显示每小时的总请求数,而不是当天的最终总数。我的查询在下面给出了每小时的请求数,以及当天的最终请求数,或者当前总数,如果是当天。
DECLARE @CurrentDate DATETIME = '17 Nov 2014'
SELECT * FROM (
SELECT DISTINCT
CAST(DateCreated AS DATE) [Date],
CASE
WHEN LEN(CAST(DATEPART(hour,DateCreated) AS VARCHAR)) = 1 THEN '0' + CAST(DATEPART(hour,DateCreated) AS VARCHAR)
ELSE CAST(DATEPART(hour,DateCreated) AS VARCHAR)
END + ':00:00' [Time],
rank() OVER (ORDER BY DATEPART(hour, DateCreated))[Requests]
FROM Request
WHERE
DateCreated >= @CurrentDate AND DateCreated < DATEADD(DAY, 1, @CurrentDate)
UNION
SELECT
CAST(@CurrentDate AS DATE),
CONVERT(VARCHAR(8),
(SELECT TOP 1 DateCreated FROM Request WHERE DateCreated >= @CurrentDate AND Request.DateCreated < DATEADD(DAY, 1, @CurrentDate) ORDER BY DateCreated DESC),
108),
(SELECT COUNT(*) from Request WHERE DateCreated >= @CurrentDate AND Request.DateCreated < DATEADD(DAY, 1, @CurrentDate))
) t
WHERE t.Requests > 0
ORDER BY t.Time
今天运行查询提供了这样的输出:
+------------+----------+----------+
| Date | Time | Requests |
+------------+----------+----------+
| 2014-11-17 | 07:00:00 | 1 |
| 2014-11-17 | 08:00:00 | 9 |
| 2014-11-17 | 09:00:00 | 49 |
| 2014-11-17 | 10:00:00 | 113 |
| 2014-11-17 | 11:00:00 | 225 |
| 2014-11-17 | 12:00:00 | 294 |
| 2014-11-17 | 13:00:00 | 363 |
| 2014-11-17 | 13:09:46 | 383 |
+------------+----------+----------+
DECLARE @CurrentDate DATETIME = '14 nov 2014';
WITH hourValues(hourVal) as (
select distinct cast(convert(varchar(14), @CurrentDate, 120) + '00' as datetime)
from [Request]
where datediff(day, [DateCreated], @CurrentDate) = 0)
SELECT
CAST(DateCreated AS DATE) [Date],
hourVal,
COUNT(case when [DateCreated] < dateadd(hour, 1, hourVal) then 1 else null end) [Requests]
FROM Request, hourValues
WHERE
datediff(day, [DateCreated], @CurrentDate) = 0
GROUP BY
CAST(DateCreated AS DATE),
hourVal
ORDER BY
[Date],
HourVal
下面应该工作
DECLARE @CurrentDate DATETIME = '13 nov 2014'
SELECT DISTINCT
CAST(DateCreated AS DATE) [Date],
DATEPART(hour,DateCreated) [Hour],
COUNT(ID) OVER (ORDER BY DATEPART(hour, DateCreated))
FROM Request
WHERE
DateCreated >= @CurrentDate AND DateCreated < DATEADD(DAY, 1, @CurrentDate)
ORDER BY
DATEPART(hour, DateCreated)
不知道这是什么版本的SQL,但是我将接受这个...但是,它只适用于我if我将查询更改为使用rank()函数。因此它使用“rank()OVER(ORDER BY DATEPART(hour,DateCreated))” – 2014-11-14 13:06:57
这是针对SQL Server 2008 R2的。 – 2014-11-14 13:39:58
--if you use MS SQL Server 2008 R2 or later version (2012/2014) then try this
DECLARE @CurrentDate DATE = CONVERT(DATE, GETDATE() - 1) , --yesterday
@FinishDate AS DATE = CONVERT(DATE, GETDATE() + 1) --tomorrow
SELECT DISTINCT
CONVERT(DATE, DateCreated) AS [Date] ,
DATEPART(HOUR, DateCreated) AS [Hour] ,
COUNT(ID) OVER (PARTITION BY CONVERT(DATE, DateCreated) ORDER BY DATEPART(HOUR,
DateCreated)) AS [Cumulative Count]
FROM Request
WHERE CONVERT(DATE, DateCreated) BETWEEN @CurrentDate
AND @FinishDate
ORDER BY CONVERT(DATE, DateCreated) ,
DATEPART(HOUR, DateCreated)
是Microsoft SQL服务器? Msg 102,Level 15,State 1,Line 15 ')'附近语法不正确。 – 2014-11-14 12:48:54
如果我删除了第15行的右括号并将[请求]更改为[请求],它可以正常工作,但并不能完全满足我的要求。它给出了单个日期,小时值和请求,但是如果我为昨天的日期运行它,它会给我当天的总请求数。 – 2014-11-14 12:51:04
编辑修复了在第4行中为'@CurrentDate'修正错字 - 交换'getdate()' – paul 2014-11-14 13:03:47