SQL group or where by?

Question

我有一个表，用于记录登录到我们系统的用户。它会在用户登录和注销时进行记录。我需要生成一些SQL，它为我们提供了一天中的小时数列表以及在该时间内登录的人数。我可以这样做一次，但我不知道如何延长这一天的每一个小时。如果可能的话，我真的不想要做24个SQL语句!

SELECT     COUNT(userID) AS "count of users"
FROM         LogonTimes
WHERE     (LoginTime > '2011-09-12 09:00:00') AND (LogoffTime < '2011-09-12 10:00:00 ')

上面的代码会产生"3"。这显示有3个人在上午9点到10点之间登录。

任何想法? !

[edit]这是SQL Server的人已经猜到下面-抱歉没有指定!我将尝试这些建议并尽快回复![/edit]

Answer 1

对于SQL Server，您可以做…

declare @DayToCheck datetime 
set @DayToCheck = '2011-09-12'
;with C as
(
  select @DayToCheck as H
  union all
  select dateadd(hour, 1, H)
  from C
  where dateadd(hour, 1, H) < dateadd(day, 1, @DayToCheck)
)
select C.H as [hour],
       count(L.userID) as [count of users]
from C
  left outer join LogonTimes as L
    on L.LogoffTime > C.H and 
       L.LoginTime < dateadd(hour, 1, C.H)
group by C.H       

试试:https://data.stackexchange.com/stackoverflow/q/112462/

SQL Server 2000的一个版本，它使用数字表代替CTE。这里我使用master..spt_values.

declare @DayToCheck datetime
set @DayToCheck = '2011-09-12'
select dateadd(hour, N.number, @DayToCheck) as [hour],
       count(L.userID) as [count of users]
from master..spt_values as N
  left outer join LogonTimes as L
    on L.LogoffTime > dateadd(hour, N.number, @DayToCheck) and
       L.LoginTime < dateadd(hour, N.number + 1, @DayToCheck)
where N.Type = 'P' and
      N.Number between 0 and 23       
group by dateadd(hour, N.number, @DayToCheck)       

Answer 2

将LoginTime列四舍五入到最近的小时，然后按此分组。

按照Martin的观点，如果您希望获得在时间范围内登录的用户总数，则还需要在where子句中添加一些其他条件，即

在当前小时前登录，在当前小时内注销

在当前小时内登录，在当前小时后注销

在当前小时之前登录，在当前小时之后注销

您甚至可能需要考虑未记录的注销日期时间，这取决于您的注销系统的工作方式。

Answer 3

从问题中不清楚你在什么环境中工作(SQL SERVER vs. Oracle vs. Other)。下面是Oracle特有的:

SELECT    
    HOUR_OF_DAY,
    COUNT(userID) AS "count of users"
FROM         LogonTimes,
(
    select TRUNC(SYSDATE)+COUNTER/24  AS HOUR_OF_DAY from 
    (
        select 
            level-1 as COUNTER 
        from dual  
        connect by level <=24
    ) a
    where COUNTER >= 0 
)
WHERE     (LoginTime >= HOUR_OF_DAY) AND (LogoffTime < HOUR_OF_DAY+1/24)

注意:这和你的原文一样，计算的是在一个小时内登录的人，而不一定是每个登录的人(即他们已经登录了，并且保持登录了几个小时)。

编辑:如果你想查看每个人在一个小时内的任何时间点登录，WHERE子句应该是:

WHERE     (LoginTime < HOUR_OF_DAY+1/24) -- logged in before end of the hour
 AND (LogoffTime > HOUR_OF_DAY) --logged off after start of the hour

Answer 4

由于您没有指定SQL语言，所以我将使用SQL Server。我每天都在使用表值函数，但是您也可以使用临时表来做同样的事情。所需的功能是:

CREATE FUNCTION [dbo].[NumbersBetween] (
    @start int,
    @end int
) RETURNS @ret TABLE (Num int) AS BEGIN
    DECLARE @x int
    SET @x = @start
    WHILE (@x <= @end) BEGIN
        INSERT INTO @ret VALUES(@x)
        SET @x = @x + 1
    END
    RETURN
END
GO

测试这个的表是:

CREATE TABLE LogonTimes(
    SessionId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LoginTime datetime NOT NULL,
    LogOffTime datetime NULL
)
INSERT INTO LogonTimes(LoginTime, LogOffTime) VALUES('2011-09-12 09:10', '2011-09-12 10:10')

最后，获取所需信息的函数(假设每天都需要，而不是所有天)是

DECLARE @queryDate datetime
SET @queryDate = '2011-09-12'
SELECT DATEADD(hour, hours.Num, @queryDate) AS HourOfDay, COUNT(LogonTimes.SessionId) AS SessionCount
FROM dbo.NumbersBetween(0, 23) hours 
  LEFT JOIN LogonTimes 
    ON LogonTimes.LoginTime < DATEADD(hour, hours.Num + 1, @queryDate) 
      AND ISNULL(LogonTimes.LogOffTime, GETDATE()) > DATEADD(hour, hours.Num, @queryDate)
GROUP BY hours.Num
ORDER BY HourOfDay

这给出了以下结果(截断以删除开始和结束处的空白)

HourOfDay               SessionCount
----------------------- ------------
....
2011-09-12 07:00:00.000 0
2011-09-12 08:00:00.000 0
2011-09-12 09:00:00.000 1
2011-09-12 10:00:00.000 1
2011-09-12 11:00:00.000 0
....

此解决方案适用于仍在登录的用户、在同一小时内登录和退出的用户以及在午夜后仍在登录的用户。

,

更新了只查询一天的解决方案，删除了SQL2008依赖项。