Início / dba / Perguntas / 17999
Accepted
dublintech
Asked: 2012-05-17 08:27:34 +0800 CST

Agrupar por minuto e intervalos de dois minutos

  • 772

Eu tenho uma coluna de data e hora e posso facilmente executar consultas com um grupo na minha coluna de data e hora. No entanto, gostaria de executar consultas de grupo para

  • intervalos de 1 minuto
  • intervalos de 2 minutos
  • intervalos de 5 minutos
  • intervalos de 1 hora
  • etc.

Como eu faço isso?

sql-server sql-server-2008

3 respostas

  1. Best Answer

    Primeiro, uma tabela e alguns dados de amostra para brincar:

    USE tempdb;
GO
CREATE TABLE dbo.SomeTable(dt DATETIME);
GO
SET NOCOUNT ON;
GO
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -22, GETDATE());
GO 45
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -19, GETDATE());
GO 32
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -17, GETDATE());
GO 21
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -12, GETDATE());
GO 16
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -5, GETDATE());
GO 55
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -2, GETDATE());
GO 26
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -1, GETDATE());
GO 71
INSERT dbo.SomeTable(dt) SELECT GETDATE();
GO 14

    (Eu faria isso no sqlfiddle, mas não tenho certeza se ele suporta GO <int>muitas linhas e engasga com INSERT> 8.000 caracteres.)

    Agora um procedimento armazenado:

    CREATE PROCEDURE dbo.GetGroupedIntervals
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @IntervalCount INT, @StartDate SMALLDATETIME;

    SELECT
        @StartDate = DATEADD(MINUTE, -1, MIN(dt)), 
        @IntervalCount = (DATEDIFF(MINUTE, MIN(dt), MAX(dt)) 
            + @MinuteInterval) / @MinuteInterval
          FROM dbo.SomeTable -- WHERE ...;

    ;WITH dates(s,e) AS
    (
        SELECT 
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n), @StartDate)
        FROM
        (
          SELECT 
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_objects AS o CROSS JOIN sys.all_columns AS c
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt) 
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

    E alguns exemplos de uso:

    EXEC dbo.GetGroupedIntervals @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 5;

    Para resumir, mostrarei os resultados da última chamada, mas você pode brincar com as outras.

    StartDate            c
-------------------  ----
2012-05-16 12:51:00  77
2012-05-16 12:56:00  21
2012-05-16 13:01:00  16
2012-05-16 13:06:00  55
2012-05-16 13:11:00  111

    Algumas notas:

    • É provável que a junção usando s.dt tenha um desempenho melhor do que qualquer método de extração usando datepart se a coluna datetime em sua tabela base tiver um índice (ou poderá no futuro).
    • Presumi que você queria mostrar todos os intervalos dentro do intervalo. Se você não quiser mostrar os intervalos com 0 contagens, basta alterar a junção externa esquerda para uma junção interna.
    • Desço um minuto caso a data de início tenha sido arredondada quando convertida para SMALLDATETIME. Para o intervalo de 1 minuto e possivelmente outros, isso pode levar a 0 contagens para o primeiro intervalo. Você pode ajustar como esse arredondamento acontece (por exemplo, você pode usar FLOOR() para garantir que ele sempre arredonde para baixo). Tudo depende de quão preciso você precisa ser.
    • Eu não incluí nenhuma cláusula WHERE, mas você pode precisar delas para filtrar. Por exemplo, sua consulta pode querer todos os intervalos de um determinado dia. Você pode querer alterar o cálculo de datespara produzir todos os intervalos do dia, em vez de todos os intervalos entre o tempo mínimo e máximo encontrados dbo.SomeTablenaquele dia. A variação a seguir cuida disso, apresentando os dados de um único dia a partir da meia-noite e incrementando por @MinutInterval:

    ...

    CREATE PROCEDURE dbo.GetGroupedIntervalsByDay
    @Date           DATE,
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @IntervalCount INT,
        @StartDate     SMALLDATETIME = @Date;

    SELECT
        @IntervalCount = 1440 / @MinuteInterval;

    ;WITH dates(s,e) AS
    (
        SELECT 
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n),   @StartDate)
        FROM
        (
          SELECT 
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_columns AS o
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt) 
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

    Exemplos de chamadas:

    EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 5;

    Resultados resumidos da última chamada:

    StartDate             c
-------------------   ----
2012-05-16 00:00:00   0
2012-05-16 00:05:00   0
2012-05-16 00:10:00   0
...
2012-05-16 12:40:00   0
2012-05-16 12:45:00   0
2012-05-16 12:50:00   45
2012-05-16 12:55:00   53
2012-05-16 13:00:00   16
2012-05-16 13:05:00   55
2012-05-16 13:10:00   111
2012-05-16 13:15:00   0
2012-05-16 13:20:00   0
...
2012-05-16 23:45:00   0
2012-05-16 23:50:00   0
2012-05-16 23:55:00   0

    (Novamente, se você não quiser incluir os intervalos sem contagens, altere a junção externa esquerda para uma junção interna. Você também pode encontrar alguns resultados estranhos se escolher um intervalo que não se encaixe perfeitamente dentro de 1440. I' Vou deixar esse caso como um exercício para o leitor.)

    • 8 
  2. declare @T table
(
  Value datetime
);

insert into @T values ('2012-01-01T00:02:00');
insert into @T values ('2012-01-01T00:03:00');
insert into @T values ('2012-01-01T00:04:00');
insert into @T values ('2012-01-01T00:05:00');
insert into @T values ('2012-01-01T00:06:00');
insert into @T values ('2012-01-01T00:07:00');
insert into @T values ('2012-01-01T00:08:00');
insert into @T values ('2012-01-01T00:09:00');
insert into @T values ('2012-01-01T00:10:00');
insert into @T values ('2012-01-01T00:11:00');

-- Interval in minutes    
declare @Interval int
set @Interval = 4

select count(*) as "Count",
       dateadd(minute, (datediff(minute, 0, Value) / @Interval) * @Interval, 0) as "Value"
from @T
group by dateadd(minute, (datediff(minute, 0, Value) / @Interval) * @Interval, 0);

    Resultado:

    Count       Value
----------- -----------------------
2           2012-01-01 00:00:00.000
4           2012-01-01 00:04:00.000
4           2012-01-01 00:08:00.000
    • 2

  3. Como DATEPARTretorna números inteiros...

    SELECT
    (DATEPART(MINUTE, StartDate) / 5) * 5, COUNT(*)
FROM
    Table
GROUP BY
    (DATEPART(MINUTE, StartDate) / 5) * 5
ORDER BY
    (DATEPART(MINUTE, StartDate) / 5) * 5

    Os números mostrados são o início do período de cinco minutos. Portanto, a linha rotulada como "30" abrange os eventos que ocorrem em X:30, X:31, X:32, X:33 e X:34.

    • 0

relate perguntas