Microsoft sql moving average

Estou trabalhando com o SQL Server 2008 R2, tentando calcular uma média móvel. Para cada registro na minha opinião, gostaria de coletar os valores dos 250 registros anteriores e, em seguida, calcular a média para esta seleção. As minhas colunas de exibição são as seguintes: TransactionID é exclusivo. Para cada ID de transação. Gostaria de calcular a média do valor da coluna, em relação aos 250 registros anteriores. Então, para TransactionID 300, colete todos os valores das 250 linhas anteriores (a exibição é ordenada por TransactionID) e, em seguida, na coluna MovAvg, escreva o resultado da média desses valores. Estou procurando coletar dados dentro de uma variedade de registros. Perguntou 28 de outubro às 20: 58Previamente, discutimos como escrever médias móveis em Postgres. Pela demanda popular, você mostrou como fazer o mesmo no MySQL e no SQL Server. Bem, cubra como anotar gráficos ruidosos como este: Com uma linha média anterior de 7 dias como esta: A grande ideia Nosso primeiro gráfico acima é bastante ruidoso e difícil de obter informações úteis. Podemos suavizá-lo, traçando uma média de 7 dias em cima dos dados subjacentes. Isso pode ser feito com funções de janela, auto-junções ou subconsultas correlacionadas - bem, cubra os dois primeiros. Bem, comece com uma média anterior, o que significa que o ponto médio no 7º do mês é a média dos primeiros sete dias. Visualmente, isso muda os picos no gráfico à direita, como uma grande espiga é calculada a média nos sete dias seguintes. Primeiro, crie uma tabela de contagem intermediária Queremos calcular uma média sobre as inscrições totais para cada dia. Supondo que tenhamos uma tabela típica de usuários com uma linha por usuário novo e um timestamp createdat, podemos criar nossa tabela agregada de inscrições como assim: No Postgres e no SQL Server você pode usar isso como um CTE. No MySQL você pode salvá-lo como uma tabela temporária. A média de roteamento do Postgres Afortunadamente, o Postgres possui funções de janela que são a maneira mais simples de calcular uma média em execução. Essa consulta pressupõe que as datas não possuem lacunas. A consulta está em média nas últimas sete linhas, não nas sete últimas datas. Se seus dados tiverem lacunas, preencha-os com generateseries ou junte-se contra uma tabela com linhas de data densas. MySQL Rolling Average MySQL não possui funções de janela, mas podemos fazer uma computação similar usando auto-junções. Para cada linha da nossa tabela de contagem, nos juntamos a cada linha que foi nos últimos sete dias e leva a média. Esta consulta lida automaticamente com intervalos de data, pois estamos olhando linhas em um intervalo de datas em vez das N linhas anteriores. O SQL Server Rolling Average SQL Server possui funções de janela, portanto, calcular a média móvel pode ser feito no estilo Postgres ou no estilo MySQL. Por simplicidade, estavam usando a versão MySQL com uma auto-união. Isso é conceitualmente o mesmo que no MySQL. As únicas traduções são a função dateadd e explicitamente denominada grupo por colunas. Outras médias Nós focamos a média de 7 dias na seguinte publicação. Se quisermos ver a média líder de 7 dias, é tão simples como classificar as datas na outra direção. Se quisermos olhar para uma média centrada, use: Postgres: linhas entre 3 anteriores e 3 seguintes MySql: entre signups. date - 3 e signups. date 3 no MySQL SQL Server: entre dateadd (dia, -3, inscrições). Data) e dateadd (dia, 3, signups. date) Você pode gerar todos os valores de data e hora entre as datas de início e término usando uma tabela auxiliar de números. Em seguida, calcule as contagens por hora e faça uma associação externa. Se derramarmos o resultado em uma tabela, podemos usar o operador de aplicação para calcular o avg em movimento. Esta é apenas uma idéia para lhe dar algo para começar. Você precisa adicionar indexação adequada ou usar um método melhor. Editado pelo MVP do Hunchback, moderador terça-feira, 8 de outubro de 2013 18h35 Proposta como resposta por Allen Li - Moderador da MSFT quinta-feira, 10 de outubro de 2013 6:09 Marcado como resposta por ckl42 quinta-feira, 10 de outubro de 2013 8:48 Terça-feira, 8 de outubro de 2013 6:33 PM Será útil se possamos ter esquema de tabela, dados de amostra na forma de instruções quotinsertquot e resultado esperado. Dessa forma, não precisamos adivinhar nomes de colunas, tipo de dados, etc. e poderemos reproduzi-lo apenas copiando e colando. Ajude-nos a poder ajudá-lo. Se você estiver usando o SS 2008 R2, então você não terá acesso ao novo suporte aprimorado para a cláusula OVER (intervalo de linhas entre) para que você precise usar qualquer um dos outros métodos, como o operador APPLY, a auto-associação, etc. Não funciona bem como as funções da janela. Veja se isso ajuda. Terça-feira, 08 de outubro de 2013 3:26 Oi, obrigado pela resposta. Eu vi esse post particular e outros semelhantes, e todos eles parecem começar a partir do ponto em que a tabela contém uma coluna que deve ser calculada como média. No meu caso, é o número de linhas em si que precisa ser calculado como média. Eu esperava que houvesse um padrão para este tipo de consulta, caso em que as colunas reais, seus nomes e tipos não importariam. Se precisamos de alguma coisa para trabalhar, então pode ser bastante simples, por exemplo, USE MyDatabase GO CREATE TABLE dbo. TestTable (Id int IDENTITY (1,1) NOT NULL, Valor varchar (10) NOT NULL, InsertedDate datetime2 (3) NOT NULL, CONSTRAINT PKTestTable PRIMARY KEY CLUSTERED (ID ASC) COM (PADINDEX OFF, STATISTICSNORECOMPUT OFF, IGNOREDUPKEY OFF, ALLOWROWLOCKS ON, ALLOWPAGELOCKS ON) ON PRIMARY) ON PRIMARY SET ANSIPADDING ON GO ALTER TABLE dbo. TestTable ADD CONSTRAINT DFTestTableInsertedDate DEFAULT (getutcdate ( )) PARA InsertedDate GO terça-feira, 8 de outubro de 2013 3:49 PM gt média móvel de linhas inseridas por hora, para cada hora do dia e semana Corrija-me se estou errado, mas isso não parece que você precisa da contagem média móvel . Não há uma janela que esteja se movendo em seu caso, então uma contagem horária comum seria suficiente. Mover significa, pelo menos para mim, que você gostaria de calcular a contagem em cada minuto nos últimos 60 minutos. Se esse for o caso, a semana é um grão maior naquela hora, então estou confuso como você calculará o valor médio. Pode ser o número de linhas em uma semana dividida, mas o número de horas da semana. De qualquer forma, você se importaria de colar dados da amostra e o resultado esperado Editado pelo MVP do Hunchback, Moderador terça-feira, 8 de outubro de 2013 4:58 PM terça-feira, 08 de outubro 2013 4:57 PM Aqui está um exemplo INSERT INTO TestTable (Value, InsertedDate) VALUES (A, 2013-10-07 10:01:00) INSERT INTO TestTable (Value, InsertedDate) VALUES (B, 2013-10-07 10 : 02: 00) INSERT INTO TestTable (Value, InsertedDate) VALUES (A, 2013-10-07 10:03:00) INSERT INTO TestTable (Value, InsertedDate) VALORES (A, 2013-10-07 12:01:00 ) INSERT INTO TestTable (Value, InsertedDate) VALUES (D, 2013-10-07 12:02:00) INSERT INTO TestTable (Value, InsertedDate) VALUES (C, 2013-10-07 14:01:00) INSERT INTO TestTable (Value, InsertedDate) VALORES (A, 2013-10-07 14:02:00) INSERT INTO TestTable (Value, InsertedDate) VALUES (B, 2013-10-07 14:03:00) INSERT INTO TestTable (Value, InsertedDate ) VALORES (A, 2013-10-07 14:04:00) INSERT INTO TestTable (Value, InsertedDate) VALUES (A, 2013-10-08 10:01:00) INSERT INTO TestTable (Value, InsertedDate) VALUES (A, 2013-10-08 10:02:00) INSERT INTO TestTable (Value, InsertedDate) VALORES (A, 2013 -10-08 10:03:00) Tempo total. 2013-10-07 09:00:00 0 2013-10-07 10:00:00 3 2013-10-07 11:00:00 0 2013-10-07 12:00:00 2 2013-10-07 13 : 00: 00 0 2013-10-07 14:00:00 4. 2013-10-08 10:00:00 3. Eu percebo que você vai chamar de falso porque eu rotinei a segunda coluna de saída Total e não Média. Isto é principalmente o que eu procuro, mas também estaria interessado na média nas últimas três horas, saída a cada hora. Portanto, a saída pode parecer Time Total Average. 2013-10-07 09:00:00 0 2013-10-07 10:00:00 3 2013-10-07 11:00:00 0 1 2013-10-07 12:00:00 2 1.67 2013-10- 07 13:00:00 0 0.67 2013-10-07 14:00:00 4 2. 2013-10-08 10:00:00 3. Terça-feira, 8 de outubro de 2013 5:40 PM Você pode gerar todos os valores de data e hora entre as datas de início e término usando uma tabela auxiliar de números. Em seguida, calcule as contagens por hora e faça uma associação externa. Se derramarmos o resultado em uma tabela, podemos usar o operador de aplicação para calcular o avg em movimento. Esta é apenas uma idéia para lhe dar algo para começar. Você precisa adicionar indexação adequada ou usar um método melhor. Editado pelo MVP do Hunchback, moderador terça-feira, 8 de outubro de 2013 18h35 Proposta como resposta por Allen Li - Moderador da MSFT quinta-feira, 10 de outubro de 2013 6:09 Marcado como resposta por ckl42 quinta-feira, 10 de outubro de 2013 8:48 Terça-feira, 8 de outubro de 2013 6:33 PM Não usamos a contagem das linhas físicas no disco como uma chave no RDBMS. Nós não incluímos a tabela de trabalho em um nome de tabela, isso é tão terrível que chamamos de tibbling e rir dos antigos programadores FORTRAN II que ainda fazem esse tipo de coisa. Oh, VALUE é um trabalho reservado no ANSIISO Standard SQL e é muito vago para ser um nome de elemento de dados válido de qualquer maneira. Você precisa de uma chave real, algumas restrições e só parecem usar segundos nos dados da amostra. Meu palpite é que você queria dizer: CREATE TABLE FoobarTests (foovalue VARCHAR (10) NOT NULL CHECK (fofoalue IN (A, B, C, D)), testtimestamp DATETIME2 (0) NOT NULL DEFAULT CURRENTTIMESTAMP PRIMARY KEY) Você também faz Não sei se T-SQL teve sintaxe de inserção ANSIISO há anos: INSERT INTO FoobarTest VALUES (A, 2013-10-07 10:01:00), (B, 2013-10-07 10:02:00), (A , 2013-10-07 10:03:00), (A, 2013-10-07 12:01:00), (D, 2013-10-07 12:02:00), (C, 2013-10- 07 14:01:00), (A, 2013-10-07 14:02:00), (B, 2013-10-07 14:03:00), (A, 2013-10-07 14:04: 00), (A, 2013-10-08 10:01:00), (A, 2013-10-08 10:02:00), (A, 2013-10-08 10:03:00) Da sua narrativa , Você quer uma contagem por horários horários. Use uma tabela de slots de tempo configurados para mais um segundo decimal de precisão do que seus dados. Agora você pode usar matemática temporal para adicioná-lo a uma DATA para TIME (1) obter um DATETIME2 completo (0). Aqui está o esqueleto básico. CREATE TABLE Timeslots (slotstarttime TIME (1) NOT NULL PRIMARY KEY, slotendtime TIME (1) NOT NULL, CHECK (starttime lt endtime)) INSERT INTO Timeslots - 15 min intervalos VALORES (00: 00: 00.0, 00: 14: 59.9 ), (00: 15: 00.0, 00: 29: 59.9), (00: 30: 00.0, 00: 44: 59.9), (00: 45: 00.0, 01: 00: 59.9),. (23: 45: 00.0, 23: 59: 59.9) Aqui está a consulta básica para arredondar para um intervalo de tempo. SELECT CAST (intimestamp AS DATE), T. starttime FROM Timeslots AS T WHERE CAST (intimestamp AS TIME) ENTRE T. slotstarttime AND T. slotendtime --CELKO-- Livros na série Celko para Morgan-Kaufmann Publishing: Analytics e OLAP em SQL Dados e bancos de dados: Conceitos na prática Medições e padrões de dados no SQL SQL para Smarties Estilo de programação SQL Quebra-cabeças e respostas SQL Pensando em conjuntos Árvores e Hierarquias em SQL Terça-feira, 8 de outubro de 2013 10:54 PM Você está fazendo isso apenas crie um Mesa de consulta simples e use uma junção muito rápida e não proprietária. A matemática Tempotal para evitar a otimização é outro problema com sua solução. SQL é um idioma de dados, não um idioma computacional. Sua mentalidade ainda está bloqueada em FORTRAN e codificação processual. --CELKO-- Livros na série Celko para Morgan-Kaufmann Publishing: analítica e OLAP em dados e bases de dados SQL: conceitos na prática Medições e padrões de dados no SQL SQL para Smarties Estilo de programação SQL SQL Puzzles e respostas Pensando em conjuntos Árvores e hierarquias em SQL terça-feira, 8 de outubro de 2013 10:58 PM Obrigado AMB. Isso parece fazer exatamente o que eu estava procurando. Quinta-feira, 10 de outubro de 2013 8:48 AM A Microsoft está realizando uma pesquisa on-line para entender sua opinião sobre o site da Msdn. Se você optar por participar, a pesquisa on-line será apresentada quando você deixar o site Msdn. Gostaria de participar 2017 da Microsoft. Todos os direitos reservados.