none
Amostragem de Registros, de hora em hora RRS feed

  • Pergunta

  • Boa tarde pessoal, tudo certo?

    Já quebrei a cabeça um monte para pensar numa solução de script, mas não consegui nada de concreto, vejamos se podem me ajudar:

    Imaginem uma grande quantidade de registros, de cerca de um ano de dados, onde todos possuem um campo data/hora. Eu preciso selecionar um registro desses a cada 1 hora, uma espécie de amostragem de hora em hora. Existe algum recurso que eu possa utilizar para isso? Detalhe é que são horários bem dinâmicos, não são horas cheias, redondinhas, isso é o que mais complica.

    Obs.: preciso rodar esse script direto no Manager mesmo, apenas para conseguir estes dados, não vou usar dentro de nenhuma aplicação.

    Espero que possam me ajudar.

    Grato...


    >>> Paz e Bem! Força Sempre! <<<
    terça-feira, 18 de janeiro de 2011 17:28

Respostas

  • Vertão, estou te passando um exemplo utilizando RowNumber ordenado por um número randomico (newid) e particionado somente pela hora do registro. Dessa forma você consegue quantos registros de cada hora vc quiser, no exemplo somente 1 (Where RandId <=1).

     

    Create Table #tbTeste (id Int Identity, Data DateTime)
    
    Insert Into #tbTeste (Data) Values('2/13/2007 18:36:11.08')
    Insert Into #tbTeste (Data) Values('2/13/2007 18:36:0.00' )
    Insert Into #tbTeste (Data) Values('2/13/2007 18:44:8.79' )
    Insert Into #tbTeste (Data) Values('2/13/2007 19:01:10.78')
    Insert Into #tbTeste (Data) Values('2/13/2007 19:18:8.67' )
    Insert Into #tbTeste (Data) Values('2/13/2007 19:22:10.07')
    Insert Into #tbTeste (Data) Values('2/13/2007 19:35:14.04')
    Insert Into #tbTeste (Data) Values('2/13/2007 19:47:10.41')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:00:13.48')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:13:11.30')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:25:14.20')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:29:14.12')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:38:12.11')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:50:12.17')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:59:15.48')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:16:12.09')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:28:14.28')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:37:11.38')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:58:13.78')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:02:14.60')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:14:12.11')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:35:13.60')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:40:11.75')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:48:13.44')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:52:10.86')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:05:12.40')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:17:9.90' )
    Insert Into #tbTeste (Data) Values('2/13/2007 23:26:11.80')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:34:13.15')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:51:11.08')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:55:13.07')
    
    
    Select *
     from (Select id, Data, row_number() OVER (Partition By DateAdd(hour, Datediff(Hour, 0, Data), 0) ORDER BY NEWID()) randid
         From #tbTeste) As Tabela
    Where RandId <= 1
    


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    • Sugerido como Resposta Alisson Cardoso quarta-feira, 19 de janeiro de 2011 16:57
    • Marcado como Resposta Eder Costa terça-feira, 25 de janeiro de 2011 14:46
    terça-feira, 18 de janeiro de 2011 18:25
  • Vertão, dá sim, mas você sempre vai pegar o valor mais próximo da hora cheia, isso quer dizer que não é mais uma amostragem e sim sempre o menor horario de cada hora. Dessa forma é só alterar o Order by no Row_Number.

     

    Select *
     from (Select id, Data, row_number() OVER (Partition By DateAdd(hour, Datediff(Hour, 0, Data), 0) ORDER BY Data) randid
         From #tbTeste) As Tabela
    Where RandId <= 1
    
    



    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    • Marcado como Resposta Eder Costa terça-feira, 25 de janeiro de 2011 14:46
    terça-feira, 18 de janeiro de 2011 19:19

Todas as Respostas

  • Segue abaixo uma parte dos dados para servir de exemplo. Se possível, o ideal seria eu conseguir, para cada hora, selecionar o registro mais próximo da hora cheia, conforme demonstrado nos registros em destaque:

    Time stamp: Value:
    2/13/2007 18:36 11.08
    2/13/2007 18:36 0.00
    2/13/2007 18:44 8.79
    2/13/2007 19:01 10.78
    2/13/2007 19:18 8.67
    2/13/2007 19:22 10.07
    2/13/2007 19:35 14.04
    2/13/2007 19:47 10.41
    2/13/2007 20:00 13.48
    2/13/2007 20:13 11.30
    2/13/2007 20:25 14.20
    2/13/2007 20:29 14.12
    2/13/2007 20:38 12.11
    2/13/2007 20:50 12.17
    2/13/2007 20:59 15.48
    2/13/2007 21:16 12.09
    2/13/2007 21:28 14.28
    2/13/2007 21:37 11.38
    2/13/2007 21:58 13.78
    2/13/2007 22:02 14.60
    2/13/2007 22:14 12.11
    2/13/2007 22:35 13.60
    2/13/2007 22:40 11.75
    2/13/2007 22:48 13.44
    2/13/2007 22:52 10.86
    2/13/2007 23:05 12.40
    2/13/2007 23:17 9.90
    2/13/2007 23:26 11.80
    2/13/2007 23:34 13.15
    2/13/2007 23:51 11.08
    2/13/2007 23:55 13.07


    >>> Paz e Bem! Força Sempre! <<<
    terça-feira, 18 de janeiro de 2011 17:41
  • Vertão, estou te passando um exemplo utilizando RowNumber ordenado por um número randomico (newid) e particionado somente pela hora do registro. Dessa forma você consegue quantos registros de cada hora vc quiser, no exemplo somente 1 (Where RandId <=1).

     

    Create Table #tbTeste (id Int Identity, Data DateTime)
    
    Insert Into #tbTeste (Data) Values('2/13/2007 18:36:11.08')
    Insert Into #tbTeste (Data) Values('2/13/2007 18:36:0.00' )
    Insert Into #tbTeste (Data) Values('2/13/2007 18:44:8.79' )
    Insert Into #tbTeste (Data) Values('2/13/2007 19:01:10.78')
    Insert Into #tbTeste (Data) Values('2/13/2007 19:18:8.67' )
    Insert Into #tbTeste (Data) Values('2/13/2007 19:22:10.07')
    Insert Into #tbTeste (Data) Values('2/13/2007 19:35:14.04')
    Insert Into #tbTeste (Data) Values('2/13/2007 19:47:10.41')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:00:13.48')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:13:11.30')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:25:14.20')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:29:14.12')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:38:12.11')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:50:12.17')
    Insert Into #tbTeste (Data) Values('2/13/2007 20:59:15.48')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:16:12.09')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:28:14.28')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:37:11.38')
    Insert Into #tbTeste (Data) Values('2/13/2007 21:58:13.78')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:02:14.60')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:14:12.11')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:35:13.60')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:40:11.75')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:48:13.44')
    Insert Into #tbTeste (Data) Values('2/13/2007 22:52:10.86')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:05:12.40')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:17:9.90' )
    Insert Into #tbTeste (Data) Values('2/13/2007 23:26:11.80')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:34:13.15')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:51:11.08')
    Insert Into #tbTeste (Data) Values('2/13/2007 23:55:13.07')
    
    
    Select *
     from (Select id, Data, row_number() OVER (Partition By DateAdd(hour, Datediff(Hour, 0, Data), 0) ORDER BY NEWID()) randid
         From #tbTeste) As Tabela
    Where RandId <= 1
    


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    • Sugerido como Resposta Alisson Cardoso quarta-feira, 19 de janeiro de 2011 16:57
    • Marcado como Resposta Eder Costa terça-feira, 25 de janeiro de 2011 14:46
    terça-feira, 18 de janeiro de 2011 18:25
  • Vertão,

    Eu cheguei num resultado. Talvez não seja a forma mais clean de se fazer mas... dá uma olhada.

     

    use tempdb

    create table t1 (id int identity, data datetime)

    insert into t1 values('2/13/2007 18:36')
    insert into t1 values('2/13/2007 18:36')
    insert into t1 values('2/13/2007 18:44')
    insert into t1 values('2/13/2007 19:01')
    insert into t1 values('2/13/2007 19:18')
    insert into t1 values('2/13/2007 19:22')
    insert into t1 values('2/13/2007 19:35')
    insert into t1 values('2/13/2007 19:47')
    insert into t1 values('2/13/2007 20:00')
    insert into t1 values('2/13/2007 20:13')
    insert into t1 values('2/13/2007 20:25')
    insert into t1 values('2/13/2007 20:29')
    insert into t1 values('2/13/2007 20:38')
    insert into t1 values('2/13/2007 20:50')
    insert into t1 values('2/13/2007 20:59')
    insert into t1 values('2/13/2007 21:16')
    insert into t1 values('2/13/2007 21:28')
    insert into t1 values('2/13/2007 21:37')
    insert into t1 values('2/13/2007 21:58')
    insert into t1 values('2/13/2007 22:02')
    insert into t1 values('2/13/2007 22:14')
    insert into t1 values('2/13/2007 22:35')
    insert into t1 values('2/13/2007 22:40')
    insert into t1 values('2/13/2007 22:48')
    insert into t1 values('2/13/2007 22:52')
    insert into t1 values('2/13/2007 23:05')
    insert into t1 values('2/13/2007 23:17')
    insert into t1 values('2/13/2007 23:26')
    insert into t1 values('2/13/2007 23:34')
    insert into t1 values('2/13/2007 23:51')
    insert into t1 values('2/13/2007 23:55')

    -- Objetivo
    --2/13/2007 19:01 10.78
    --2/13/2007 20:00 13.48
    --2/13/2007 20:59 15.48
    --2/13/2007 22:02 14.60
    --2/13/2007 23:05 12.40

    select
        convert(varchar, data, 103) + ' ' + cast(datepart(hh, data) as varchar) as data
        , min(datepart(hh, data)) as hora
        , min(datepart(minute, data)) as minuto
    from t1
    group by convert(varchar, data, 103) + ' ' + cast(datepart(hh, data) as varchar)
    order by 2, 3

     

    []'s


    Erickson Ricci - MCP, MCTS, MCTS BI ericksonricci.wordpress.com Twitter: @EricksonRicci
    terça-feira, 18 de janeiro de 2011 18:53
  • Vertão,

    Eu cheguei num resultado. Talvez não seja a forma mais clean de se fazer mas... dá uma olhada.

     

    use tempdb

    create table t1 (id int identity, data datetime)

    insert into t1 values('2/13/2007 18:36')
    insert into t1 values('2/13/2007 18:36')
    insert into t1 values('2/13/2007 18:44')
    insert into t1 values('2/13/2007 19:01')
    insert into t1 values('2/13/2007 19:18')
    insert into t1 values('2/13/2007 19:22')
    insert into t1 values('2/13/2007 19:35')
    insert into t1 values('2/13/2007 19:47')
    insert into t1 values('2/13/2007 20:00')
    insert into t1 values('2/13/2007 20:13')
    insert into t1 values('2/13/2007 20:25')
    insert into t1 values('2/13/2007 20:29')
    insert into t1 values('2/13/2007 20:38')
    insert into t1 values('2/13/2007 20:50')
    insert into t1 values('2/13/2007 20:59')
    insert into t1 values('2/13/2007 21:16')
    insert into t1 values('2/13/2007 21:28')
    insert into t1 values('2/13/2007 21:37')
    insert into t1 values('2/13/2007 21:58')
    insert into t1 values('2/13/2007 22:02')
    insert into t1 values('2/13/2007 22:14')
    insert into t1 values('2/13/2007 22:35')
    insert into t1 values('2/13/2007 22:40')
    insert into t1 values('2/13/2007 22:48')
    insert into t1 values('2/13/2007 22:52')
    insert into t1 values('2/13/2007 23:05')
    insert into t1 values('2/13/2007 23:17')
    insert into t1 values('2/13/2007 23:26')
    insert into t1 values('2/13/2007 23:34')
    insert into t1 values('2/13/2007 23:51')
    insert into t1 values('2/13/2007 23:55')

    -- Objetivo
    --2/13/2007 19:01 10.78
    --2/13/2007 20:00 13.48
    --2/13/2007 20:59 15.48
    --2/13/2007 22:02 14.60
    --2/13/2007 23:05 12.40

    select
        convert(varchar, data, 103) + ' ' + cast(datepart(hh, data) as varchar) as data
        , min(datepart(hh, data)) as hora
        , min(datepart(minute, data)) as minuto
    from t1
    group by convert(varchar, data, 103) + ' ' + cast(datepart(hh, data) as varchar)
    order by 2, 3

     

    []'s


    Erickson Ricci - MCP, MCTS, MCTS BI ericksonricci.wordpress.com Twitter: @EricksonRicci

    Olá Erickson, fiz um teste rápido aqui, gostei da solução devido os horários chegarem próximos da hora cheia. Porém, o resultado ficou um pouco estranho por separar data/hora/minuto, não consegui juntar tudo de volta, hehe...

    data                | hora | minuto

    13/02/2007 18 | 18    | 36
    13/02/2007 19 | 19    | 1
    13/02/2007 20 | 20    | 0
    13/02/2007 21 | 21    | 16
    13/02/2007 22 | 22    | 2
    13/02/2007 23 | 23    | 5


    >>> Paz e Bem! Força Sempre! <<<
    terça-feira, 18 de janeiro de 2011 19:10
  • Vertão, estou te passando um exemplo utilizando RowNumber ordenado por um número randomico (newid) e particionado somente pela hora do registro. Dessa forma você consegue quantos registros de cada hora vc quiser, no exemplo somente 1 (Where RandId <=1).

     

     

    Create Table #tbTeste (id Int Identity, Data DateTime)
    
    
    
    Insert Into #tbTeste (Data) Values('2/13/2007 18:36:11.08')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 18:36:0.00' )
    
    Insert Into #tbTeste (Data) Values('2/13/2007 18:44:8.79' )
    
    Insert Into #tbTeste (Data) Values('2/13/2007 19:01:10.78')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 19:18:8.67' )
    
    Insert Into #tbTeste (Data) Values('2/13/2007 19:22:10.07')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 19:35:14.04')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 19:47:10.41')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 20:00:13.48')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 20:13:11.30')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 20:25:14.20')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 20:29:14.12')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 20:38:12.11')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 20:50:12.17')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 20:59:15.48')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 21:16:12.09')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 21:28:14.28')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 21:37:11.38')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 21:58:13.78')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 22:02:14.60')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 22:14:12.11')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 22:35:13.60')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 22:40:11.75')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 22:48:13.44')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 22:52:10.86')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 23:05:12.40')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 23:17:9.90' )
    
    Insert Into #tbTeste (Data) Values('2/13/2007 23:26:11.80')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 23:34:13.15')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 23:51:11.08')
    
    Insert Into #tbTeste (Data) Values('2/13/2007 23:55:13.07')
    
    
    
    
    
    Select *
    
     from (Select id, Data, row_number() OVER (Partition By DateAdd(hour, Datediff(Hour, 0, Data), 0) ORDER BY NEWID()) randid
    
       From #tbTeste) As Tabela
    
    Where RandId <= 1
    
    

     


    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

    Olá Fausto!

    Boa solução, fiz um teste rápido aqui, ficou legal. Só preciso buscar agora uma melhoria para que os registros selecionados sejam o mais próximo possivel do horário cheio, pode ser pra cima ou pra baixo, não tem problema. Alguma idéia?


    >>> Paz e Bem! Força Sempre! <<<
    terça-feira, 18 de janeiro de 2011 19:12
  • Vertão, dá sim, mas você sempre vai pegar o valor mais próximo da hora cheia, isso quer dizer que não é mais uma amostragem e sim sempre o menor horario de cada hora. Dessa forma é só alterar o Order by no Row_Number.

     

    Select *
     from (Select id, Data, row_number() OVER (Partition By DateAdd(hour, Datediff(Hour, 0, Data), 0) ORDER BY Data) randid
         From #tbTeste) As Tabela
    Where RandId <= 1
    
    



    Tks. Fausto Fiorese Branco MCTS, MCITP/DBA 2005 | MCITP/DBA 2008 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco
    • Marcado como Resposta Eder Costa terça-feira, 25 de janeiro de 2011 14:46
    terça-feira, 18 de janeiro de 2011 19:19
  • Vertão, aproveitando os dados montado pelo fausto, segue minha sugestão.

     

    SELECT 
    	X.Data 
    FROM
    (
    	SELECT DISTINCT
    		A.Data, 
    		ROW_NUMBER() OVER
    		( PARTITION BY DateAdd(HH,1+Datediff(HH,0,B.Data),0)
    		 ORDER BY ABS(DATEDIFF(SS,A.Data, DateAdd(HH,1+Datediff(HH,0,B.Data),0)))
    		) AS ID
    	FROM 
    		#tbTeste AS A CROSS JOIN
    		#tbTeste AS B
    ) AS X
    WHERE 
    	X.ID = 1
    	
    /* saida
    
    Data
    -----------------------
    2007-02-13 19:01:10.780
    2007-02-13 20:00:13.480
    2007-02-13 20:59:15.480
    2007-02-13 21:58:13.780
    2007-02-13 23:05:12.400
    2007-02-13 23:55:13.070
    
    (6 linha(s) afetadas)
    */		
    
    


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    • Sugerido como Resposta Eder Costa terça-feira, 25 de janeiro de 2011 14:46
    quinta-feira, 20 de janeiro de 2011 16:04