Mysql moving average query

Anteriormente, discutimos como escrever médias de rolamento no Postgres. Pela demanda popular estavam mostrando-lhe como fazer o mesmo no MySQL e SQL Server. Bem cobrir como anotar gráficos barulhentos como este: Com uma linha de 7 dias precedente média como este: A grande idéia Nosso primeiro gráfico acima é muito ruidoso e difícil de obter informações úteis. Podemos suavizar-lo traçando uma média de 7 dias sobre os dados subjacentes. Isso pode ser feito com funções de janela, self-joins, ou subconsultas correlacionadas - bem cobrir os dois primeiros. Bem começar com uma média anterior, o que significa que o ponto médio no dia 7 do mês é a média dos primeiros sete dias. Visualmente isso desloca os picos no gráfico para a direita, uma vez que um grande pico é calculado 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. Assumindo que temos uma tabela típica de usuários com uma linha por novo usuário e um timestamp createdat, podemos criar nossa tabela agregados de inscrições da seguinte forma: No Postgres e SQL Server você pode usar isso como um CTE. No MySQL, você pode salvá-lo como uma tabela temporária. Postgres Rolling Average Felizmente Postgres tem funções de janela que são a maneira mais simples de calcular uma média em execução. Esta consulta assume que as datas não têm lacunas. A consulta é a média das últimas sete linhas, não as últimas sete datas. Se seus dados tiverem lacunas, preencha-as com generateseries ou junção contra uma tabela com linhas de data densas. MySQL Rolling Average O MySQL não possui funções de janela, mas podemos fazer um cálculo semelhante usando auto-uniões. Para cada linha em nossa tabela de contagem, juntamos cada linha que estava nos últimos sete dias e tomamos a média. Esta consulta trata automaticamente as lacunas de data, uma vez que estamos a olhar para linhas dentro de um intervalo de datas em vez das N linhas precedentes. SQL Server Rolling Average O SQL Server possui funções de janela, portanto, calcular a média móvel pode ser feita no estilo Postgres ou no estilo MySQL. Para simplificar, estavam usando a versão MySQL com um auto join. Isso é conceitualmente o mesmo que no MySQL. As únicas traduções são a função dateadd e nomeada explicitamente grupo por colunas. Outras médias Nós nos concentramos na média final de 7 dias neste post. Se nós quisemos olhar para a média 7-dia principal, é tão simples como classificar as datas na outra direção. Se quiséssemos olhar para uma média centrada, use: Postgres: linhas entre 3 precedentes e 3 seguindo 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) Esta é uma pergunta Evergreen Joe Celko. Eu ignoro qual plataforma DBMS é usada. Mas em qualquer caso Joe foi capaz de responder há mais de 10 anos com SQL padrão. Joe Celko SQL Quebra-Cabeças e Respostas citação: Essa última tentativa de atualização sugere que poderíamos usar o predicado para construir uma consulta que nos daria uma média móvel: A coluna extra ou a abordagem de consulta melhor A consulta é tecnicamente melhor porque a abordagem UPDATE Desnormalizar o banco de dados. No entanto, se os dados históricos que estão sendo gravados não vai mudar ea computação da média móvel é cara, você pode considerar usar a abordagem de coluna. SQL consulta quebra-cabeça: por todos os meios uniforme. Você apenas joga para o balde de peso apropriado, dependendo da distância do ponto de tempo atual. Por exemplo quottake weight1 para datapoints dentro de 24hrs de datapoint atual weight0.5 para datapoints dentro de 48hrsquot. Esse caso importa quanto pontos de dados consecutivos (como 6:12 am e 11:48 pm) estão distantes uns dos outros Um caso de uso que eu posso pensar seria uma tentativa de suavizar o histograma onde quer que os pontos de dados não são densos o suficiente ndash msciwoj May 27 15 at 22:22 Eu não tenho certeza que seu resultado esperado (saída) mostra clássico simples movendo (rolando) média de 3 dias. Porque, por exemplo, o primeiro triplo de números por definição dá: mas você espera 4.360 e sua confusão. No entanto, sugiro a seguinte solução, que usa a função de janela AVG. Essa abordagem é muito mais eficiente (clara e menos intensiva em recursos) do que o SELF-JOIN introduzido em outras respostas (e estou surpreso que ninguém tenha dado uma solução melhor). Você vê que o AVG é envolvido com o caso quando rownum gt p. days, em seguida, para forçar NULL s nas primeiras linhas, onde 3 dias Moving Average é sem sentido. Respondeu Feb 23 at 13:12 Podemos aplicar Joe Celkos suja deixada externa juntar método (como citado acima por Diego Scaravaggi) para responder à pergunta como foi solicitado. Gerar a saída solicitada: respondida Jan 9 at 0:33 Sua resposta 2017 Stack Exchange, IncComo calcular uma média móvel SQL sem uma atualização de cursor: Se você estiver trabalhando com as versões mais recentes do SQL Server, você pode usar as funções de janela para realizar a mesma coisa. Eu postei o código atualizado no final do post. Para este vídeo, eu ainda gosto do processo de pensamento de ancorar a uma data. Vídeo: média móvel de 3 dias em SQL Uma maneira eficiente de calcular uma média móvel em SQL usando alguns truques para definir âncoras de data. Há debates sobre a melhor maneira de fazer um SQL Moving Average no SQL Server. Algumas pessoas pensam que há momentos em que um cursor é mais eficiente. Outros acham que você pode fazer tudo de uma maneira baseada em set sem o cursor. No outro dia eu estava indo para calcular uma média móvel e meu primeiro pensamento foi usar um cursor. Eu fiz algumas pesquisas rápidas e encontrei esta pergunta do fórum: Moving Average no TSQL Há uma postagem que mostra uma subconsulta com uma data de âncora para ajudar a encontrar o offset de 1 e 2 dias. Aqui está o script que você pode usar para testar o resultado final do SQL Moving Average de 3 dias. Aqui está a consulta final. Aqui está a consulta que você usaria com o SQL Server 2017. Compartilhe isso: AVG (Transact-SQL) ALL Aplica a função agregada a todos os valores. ALL é o padrão. DISTINCT Especifica que o AVG seja executado somente em cada instância exclusiva de um valor, independentemente do número de vezes que o valor ocorrer. Expressão É uma expressão da categoria de tipo de dados numérico exata ou aproximada, exceto para o tipo de dados bit. Funções agregadas e subconsultas não são permitidas. OVER (partitionbyclause orderbyclause) partitionbyclause divide o conjunto de resultados produzido pela cláusula FROM em partições às quais a função é aplicada. Se não for especificado, a função trata todas as linhas do conjunto de resultados da consulta como um único grupo. Orderbyclause determina a ordem lógica na qual a operação é executada. Pedido por cláusula é necessária. Para obter mais informações, consulte Cláusula OVER (Transact-SQL). O tipo de retorno é determinado pelo tipo de resultado avaliado da expressão. Decimal category (p, s) Se o tipo de dados de expressão é um tipo de dados de alias, o tipo de retorno também é do tipo de dados de alias. No entanto, se o tipo de dados base do tipo de dados de alias é promovido, por exemplo de tinyint para int. O valor de retorno é do tipo de dados promovido e não o tipo de dados alias. AVG () calcula a média de um conjunto de valores dividindo a soma desses valores pela contagem de valores nonnull. Se a soma exceder o valor máximo para o tipo de dados do valor de retorno um erro será retornado. O AVG é uma função determinística quando usado sem as cláusulas OVER e ORDER BY. Ele é não-determinístico quando especificado com as cláusulas OVER e ORDER BY. Para obter mais informações, consulte Funções determinísticas e não determinísticas. A. Usando as funções SUM e AVG para cálculos O exemplo a seguir calcula as horas de férias médias e a soma das horas de folga que os vice-presidentes de Ciclos de Trabalho Aventuras usaram. Cada uma dessas funções agregadas produz um único valor de resumo para todas as linhas recuperadas. O exemplo usa o banco de dados AdventureWorks2017.Mostrar apenas uma fila filho por linha pai Dado tabelas pai (id int não chave primária nula, etc.) e filho (id int não chave nula primária, pid int não referências nulas pai (id), etc. .). Como podemos escrever uma consulta que recupera apenas uma fila filho por pid, mesmo quando a tabela filho tem várias linhas correspondentes MySQL permite o uso de GROUP BY, mesmo quando a lista SELECT não especifica nenhuma função agregada, então isso funcionará: select p. id, c. id de pai p unir filho c no grupo p. idc. pid por p. id Mas é preciso Não, porque ele exibe apenas o primeiro valor c. pid que acontece a encontrar. Para discussão mais aprofundada, ver agregados dentro do grupo. Ignorar valores repetitivos Você deseja relatar todos os valores exclusivos de uma coluna e ignorar todas as linhas repetindo qualquer um desses valores. SELECT col FROM foo GROUP BY col Totais e subtotais - simplesmente Você tem uma tabela que rastreia horas de atendimento. Drop table if exists t create tabela t (data d, id int, hrs int) inserir em valores t (2017-10-1,1,5), (2017-10-1,2,6), (2017-10 -1,3,2), (2017-10-1,3,5), (2017-10-2,1,1), (2017-10-2,1,2), (2017-10-2 , 2,3), (2017-10-2,2,4), (2017-10-3,1,2), (2017-10-3,1,2), (2017-10-3,1 , 2). E você precisa de um resumo de atendimento por data e pessoa. Acontece que essa consulta de agregação é fácil top writemdashfirst escrever uma consulta interna para Group By data e pessoa Com Rollup, em seguida, escrever uma consulta externa que renomeia os rótulos Rollup Null. Alterações de estado de trilha Você tem uma tabela contendo uma seqüência de IDs, estados e datas. O requisito de consulta é listar alterações de estado e suas datas ordenadas por data e contar instâncias de cada estado seqüenciado. Estes dados de uma entrada de blog por um MySQLer russo chamando-se Quassnoi. ----------------------------- ID Estado Datetime ------------------ ----------- 12 1 2009-07-16 10:00 45 2 2009-07-16 13:00 67 2 2009-07-16 14:40 77 1 2009-07-16 15: 00 89 1 2009-07-16 15:30 99 1 2009-07-16 16:00 ----------------------------- Valores vinculados com todos os valores de outra coluna Você tem uma tabela na qual cada linha faz referência a um texto ea uma palavra-chave no texto. DROP TABLE IF EXISTS palavras-chave CREATE TABLE palavras-chave (txtID int, keyword char (8)) INSERT INTO palavras-chave VALUES (1. Foo), (2.bar), (1. Foo), (2. foo). E você quer uma lista de textos que incluem cada palavra-chave. Você pôde pensar que você tem que juntar-se e combinar. Você não. Tudo o que você precisa fazer é contar as palavras-chave distintas que ocorrem para cada texto e, em seguida, para cada texto comparar esse número com toda a lista de palavras-chave distintas: SELECT txtID, COUNT (palavra-chave DISTINCT) AS N FROM palavras-chave GROUP BY txtID HAVING N (SELECT COUNT (palavras-chave DISTINCT) FROM) ---------- txtID N ---------- 2 2 ---------- Agregados dentro do grupo Você tem um produto Tabela com colunas item, fornecedor, preço. Vários fornecedores oferecem vários preços para o mesmo item. Você precisa encontrar o fornecedor com o preço mais baixo para cada item. DROP TABLE IF EXISTS produtos CREATE TABLE produtos (item int, fornecedor int, preço decimal (6,2)) INSERT INTO produtos VALUES (1,1,10), (1,2,15), (2,2,20) , (2,1,21), (2,2,18) SELECT FROM produtos ----------------------- item preço do fornecedor ----- ------------------ 1 1 10.00 1 2 15.00 2 2 20.00 2 1 21.00 Ordem de classificação Sem MSSQLs Função agregada RANK (), como exibimos a ordem de classificação em uma consulta MySQL , Por exemplo a partir de uma tabela como esta vota CREATE TABLE (nome CHAR (10), votos INT) INSERT INTO votos VALUES (Smith, 10), (Jones, 15), (White, 20), (Black, 40), Green, 50), (Brown, 20) A consulta é um dois-passo: 1. Junte a tabela para si mesmo sobre o valor a ser classificado, manipulação laços 2. Grupo e ordem o resultado da auto-junção no ranking: Backslashes In dados Backslashes multiplicar estranhamente: SELECT ab RLIKE ab retorna 1, como faz. SELECT ab RLIKE ab porque em um par de barras invertidas, o segundo não é escapado pelo primeiro, então para comparar dois literais você dobra cada barra invertida no argumento RLIKE. Mas se você estiver consultando uma tabela para tal string do cliente MySQL, essa duplicação acontece duas vezes - uma vez no cliente e uma vez no banco de dados - para encontrar um valor de coluna correspondente ab. Você precisa escrever SELECT desc FROM xxx WHERE desc RLIKE aabb Isso é oito barras invertidas para corresponder a duas Compare dados em duas tabelas Esta consulta UNION s consulta os nomes de colunas correspondentes de duas tabelas e mantém apenas as linhas que ocorrem uma vez na união. Essas são as linhas com dados inigualáveis. Personalize sua lista de colunas como desejado, mas geralmente você quer que ele comece com a chave primária: SELECT MIN (TableName) como TableName, id, col1, col2, col3. FROM (Tabela SELECT a como TableName, a. id, a. col1, a. col2, a. col3. DE uma tabela UNION ALL SELECT b como TableName, b. id, b. col1, b. col2, b. col3. FROM b) COMO tmp GROUP BY id, col1, col2, col3. HAVING COUNT () 1 ORDER BY 1 Comparar estruturas de duas tabelas Para comparar as colunas por nome e posição ordinal nas tabelas test. t1 e test. t2: SELECT MIN (TableName) AS Tabela, nome do colunas AS Column, ordinalposition AS Posição FROM (SELECT t1 Como TableName, columnname, ordinalposition FROM informaçõeschema. columns AS i1 WHERE tableschematest E tablenamet1 Compare duas bases de dados Uma das regras de EF Codds para bases de dados relacionais é a regra de não-back-door: todas as informações sobre tabelas devem ser acessíveis apenas por uma consulta em tabelas. Desde a versão 5, a implementação do MySQL MySQL de informaçõeschema (IS) ajuda a cumprir o requisito Codds. IS fornece metadados em tabelas, por isso é o primeiro lugar para procurar a forma de comparar as estruturas de dois bancos de dados. Em outro lugar nesta página há um modelo de consulta simples Para comparar dados em duas tabelas estruturalmente semelhantes: SELECT MIN (TableName) como TableName, id, col1, col2, col3. FROM (tabela SELECT a como TableName, a. id, a. col1, a. col2, a. col3. Uma tabela UNION ALL SELECT b como TableName, b. id, b. col1, b. col2, b. col3. FROM b) AS tmp Tamanho do banco de dados SELECT tableschema AS Db Nome, Round (Soma (datalengthlndlength) / 1024/1024, 3) AS Db Size (MB), Round (Soma (datafree) / 1024/1024, 3) (MB) FROM informationschema. tables GROUP BY tableschema Encontre o tamanho de todos os bancos de dados no servidor Isso é baseado em uma consulta Mark Leith postada na lista de Discussão Geral do MySQL. DROP VIEW SE EXISTE dbsize CREATE VIEW dbsize AS SELECT s. schemaname AS Esquema, SUM (t. datalength) AS Dados, SUM (t. indexlength) AS Índices, SUM (t. datalength) SUM (t. indexlength) AS Mb Usado, IF SUM (t. datafree) 0, SUM (t. datafree)) Como Mb Free, IF (SUM (t. datafree) 0,, 100 SUM (t. datalength) SUM (t. indexlength)) / ( SUM (t. datalength) SOMA (t. indexlength) SUM (IFNULL (t. datafree, 0))))) AS Pct usado, COUNT (tablename) AS Tabelas FROM informaçõeschema. schemata s LEFT JOIN informaçõeschema. tables t ON s. schemaname t. tableschema GRUPO BY s. schemaname COM ROLLUP Listar bancos de dados, tabelas, colunas SELECT t. tableschema AS Banco de dados, t. tablename AS Tabela, t. tabletype AS Tipo de tabela, c. columnname AS Column, c. datatype AS Tipo de dados FROM informaçõeschema. tables t JOIN informaçõeschema. columns c ON t. tableschema c. tableschema E t. tablename c. tablename ONDE t. tableschema NÃO IN (mysql, informationschema) ORDER BY t. tableschema, t. tabletype, t. tablename, c. ordinalposition Localizar chaves externas de banco de dados cruzadas SELECT u. tableschema como RefSchema, u. tablename como RefTable, u. columnname como RefColumn, u. referencedtableschema como Schema, u. referencedtablename como Tabela, u. referencedcolumnname como Coluna FROM informationschema. tableconstraints AS c JOIN informationschema. keycolumnusage AS u USING (constraintname, constraintname) WHERE c. constrainttypeFOREIGN KEY E u. tableschema ltgt u. referencedtableschema Para encontrá-los para um banco de dados específico, adicione a condição WHERE: AND dbname IN (u. tableschema, u. referencedtableschema) Show Create Trigger O MySQL adicionou um comando Show Create Trigger em 5.1.21. Se você usar uma versão anterior do MySQL, aqui está um procedimento armazenado que se comporta como Show Create Trigger: DROP PROCEDURE SE EXISTE ShowCreateTrigger DELIMITER ir CREATE PROCEDURE ShowCreateTrigger (IN db CHAR (64), IN tbl CHAR (64)) BEGIN SELECT CONCAT (CREATE CHAR (10), CHARACH (10), actionstatement, CHAR (10), ShowView Status da Tabela Equivalente de informationschema Preencher nomes de esquema e tabela em. SELECT nome de tabela, motor, versão, formato de linha, tablerows, avgrowlength, datalength, maxdatalength, indexlength, datafree, autoincremento, createtime, updatetime, checktime, tablecollation, checksum, createoptions, tablecomment FROM informationschema. Tabelas, tabelas, tabelas, tabelas, etc Tabelas Show O comando MySQL SHOW TABLES está bem, mas às vezes queremos um pouco mais de informações. Este procedimento armazenado simples lista o nome da tabela, tipo de mecanismo, versão, collation e rowcount para cada tabela em um banco de dados. (As bases de dados individuais vêm e vão, então mantemos todas essas rotinas armazenadas em todo o banco de dados em um banco de dados do sistema.) DROP PROCEDURE SE EXISTE showtables CREATE PROCEDURE showtables () SELECT tablename AS Tabela, IFNULL (engine, VIEW) , Tablecollation AS Colação, tablerows AS Filas Idade em anos Dada uma data de nascimento em dob. Aqui estão duas fórmulas simples para a idade em anos: Dateformat (FromDays (ToDays (Curdate ()) - ToDays (dob)), Y) 0 Ano (Curdate ()) - Year (dob) - (Right (Curdate (), 5 ) Lt Right (dob, 5)) e aqui está um para a idade em anos a duas casas decimais, ignorando o dia do mês: Round ((((((Year (now () (Agora ()))))) / 12, 2) Diferença de data e hora Encontre a diferença entre dois valores de data e hora em segundos, minutos, horas ou dias. Se dt1 e dt2 são valores de data e hora do formato yyyy-mm-dd hh: mm: ss, o número de segundos entre dt1 e dt2 é UNIXTIMESTAMP (dt2) - UNIXTIMESTAMP (dt1) Para obter o número de minutos dividir por 60, para O número de horas dividir por 3600, e para o número de dias, dividir por 3600 24. Duração em anos, meses, dias e tempo DROP FUNÇÃO SE EXISTE PeriodLen DROP FUNÇÃO SE EXISTE NumLabel DELIMITER ir CREATE FUNÇÃO PeriodLen (dt1 datetime, dt2 datetime ) RETURNS CHAR (128) BEGIN DECLARAR yy, m0, mm, d0, dd, hh, mi, ss, t1 MENSAGEM TIMESTAMPDIFF (MÊS, dt1, dt2) TIMESTAMPDIFF (YEAR, dt1, dt2) Dt2) AJUSTE mm m0 MOD 12 AJUSTE dtmp ADDDATE (dt1, intervalo m0 MÊS) SET d0 TIMESTAMPDIFF (DIA, dt1, dt2) A data da próxima quinta-feira Dada uma data e seu número do dia da semana (1 Domingo, 7Sábado), existem três possibilidades: 1. Hoje é quinta-feira: em seguida, na próxima quinta-feira é de 7 dias a partir de agora. 2. Hoje é antes de quinta-feira: então quinta-feira seguinte é (5 menos o número de dias de hoje de hoje) de agora. 3. Hoje é depois de quinta-feira: então quinta-feira seguinte é 7 (5 menos o número do dia da semana de hoje). Definir dcurdate () set n dayofweek (curdate ()) Selecione d: adddate (curdate (), 0) como date, n: dayofweek (adddate (curdate (), 0) Tabela que rastreia um valor ea hora em que o valor foi medido. Drop table se existir mudanças create table changes (time time, value int) inserir em valores de mudanças (00:00, 0), (01:05, 1), (01:45, 1) , (12:20, 1), (12:40, 0), (14:32, 0), Qual o mês que uma semana cai O mês de uma semana é ambíguo se a semana Meses. Se adotarmos a convenção de que o mês de uma semana é o mês de seu início de domingo, em seguida, em 29 de novembro de 2009. SET weekno Mês CurDate ()) SET data AddDate (2009-01-01, 7weekno) SET dia DayOfWeek (data) SET datecomp IF (dia 1, data, AddDate (data, 1 dia)) SELECT data, dia, datecomp, mês (datecomp) AS mês ------------------- ------------------ date day datecomp month ---------------------------- --------- 2009-12-03 5 2009-11-29 11 ----------------------------- -------- YearMonth () Muitas vezes precisamos computar datetimes com base no ano e mês. Esta função minúscula simplifica tais consultas: set global logbintrustfunctioncreators1 create function ano mês (d date) retorna int retornar 100year (d) mês (d) Então para localizar valores de data dentro do período de três meses delimitado pelo primeiro dia do mês passado eo último Dia do próximo mês, escreva. Selecionar d de tbl em que yearmonth (d) entre yearmonth (curdate () - intervalo 1 mês) e yearmonth (curdate () intervalo 1 month) Trilhas de auditoria e arquitetura point-in-time Encontrar períodos de sobreposição Você tem uma tabela de visitas, Você gostaria de exibir os períodos de tempo durante os quais há sobreposições de tempo de visita. Drop table if exists visitas create table visits (id chave primária, datetime, end datetime) inserir em visitas values ​​(1, 2008-09-01 15:01, 2008-09-01 15:04), (2, 2008 -09-01 15:02, 2008-09-01 15:09), (3, 2008-09-01 15:12, 2008-09-01 15:15), (4, 2008-09-01 16: 11, 2008-09-01 16:23), (5, 2008-09-01 16:19, 2008-09-01 16:25), (6, 2008-09-01 17:52, 2008-09- 01 17:59), (7, 2008-09-01 18:18, 2008-09-01 18:22), (8, 2008-09-01 16:20, 2008-09-01 16:22), Localizar duplicados seqüenciados Uma tabela que rastreia períodos de tempo pode exigir exclusividade de período. Isso significa que não tem duplicatas seqüenciadas. Se uma tabela tem colunas processID, startdate e enddate, essas três colunas são período único se não existir nenhum par de linhas com o mesmo processID e sobreposição startdate e enddate valores. Se houver um tal par de linhas, a tabela exibe duplicação sequenciada. Outra maneira de dizê-lo: se um instante é a menor unidade de data e hora das colunas startdate e enddate, então se não houver duplicados seqüenciados, há exatamente um valor processID em qualquer instante. Aqui está uma consulta para encontrar duplicados seqüenciados para essas colunas: SELECT t. processid FROM tbl t WHERE EXISTS (SELECT FROM tbl AS t3 WHERE t3.processid IS NULL) OU EXISTS (SELECT FROM tbl AS t1 WHERE 1 lt (SELECT COUNT ) FROM tbl AS t2 WHERE t1.processid t2.processid E t1.startdate lt t2.enddate AND t2.startdate lt t1.enddate)) In ou out em uma data e hora determinados Empregados soco in e out. Você acompanha esses eventos com. Drop table if exists tbl create tabela tbl (empno smallint, clockdate date, clocktime time, clocktype char (1)) inserir em tbl valores (1, 2017-05-15, 09:00:00, I), (1, 2017 -05-15, 11:00:00, O), (1, 2017-05-15, 12:30:00, I), (1, 2017-05-15, 19:00:00, O) Foi Empregado 1 em ou para fora em 12:30 pm em 15 maio 2017 Usar um self-join para jogar o dentro e para fora eventos de encontro ao datetime dado. Selecione if (count (1), Yes, No) como InStore de tbl a join tbl b usando (empno, clockdate) onde empno1 e a. clockdate2017-5-15 e a. clocktimelt12: 30: 00 e a. clocktypeI e b. clocktimegt12: 30: 00 e b. clocktypeO --------- InStore --------- Yes --------- Peak visita contagens por período de data / hora Você tem uma tabela de visitas (Id int, datetime de início, datetime de fim). E você deseja acompanhar a contagem de visitas de pico. Uma solução simples é auto-juntar IDs não coincidentes e tempos de visita sobrepostos, grupo por ID e, em seguida, ordenar pelas contagens resultantes: SELECT a. id, groupconcat (b. id) como Overlaps, count (b. id) 1 Como OverlapCount FROM visita um JOIN visitas b on a. id lt b. id e a. start lt b. end e b. start lt a. end GRUPO por a. id ORDER BY OverlapCount DESC Cronograma da tabela dinâmica Você tem uma tabela de programação Período, dia, assunto, sala) com um período chave primário, dia para evitar reservas duplicadas. Você deseja exibir a programação como períodos, assuntos e salas em linhas e dias da semana em colunas. SELECT período, MAX (IF (dia1, CONCAT (sujeito, sala)) AS Seg, MAX (IF2, CONCAT (assunto, sala) ,,)) AS Qua, MAX (SE) (dia, CONCAT (assunto, sala) Por período MAX () escolhe existentes sobre entradas em branco, e GROUP BY linhas tudo na mesma linha. Exibir os valores das colunas que ocorrem N vezes SELECT id FROM tbl GRUPO BY id HAVING COUNT () N Mude a condição HAVING para 1 para listar valores duplicados, etc. Pais sem filhos Dado tabelas pai (id INT), filho (id INT, parentid INT ). Como encontramos pais sem filhos? É o Todo X para o qual não existe um padrão Y, que pode ser escrito como uma junção de exclusão. SELECT parent. id FROM pai LEFT JOIN filho ON parent. id child. parentid WHERE child. parentid É NULL ou com uma subconsulta NOT EXISTS, que é logicamente equivalente à exclusão join, mas geralmente executa muito mais lento: SELECT parent. id AS ParentID FROM pai WHERE NOT EXISTS (SELECT pai. id FROM pai JOIN criança ON pai. ID filho. parentid) Partes que têm contratos com um outro Você tem uma tabela de partidos que contém informações sobre povos nomes etc e uma tabela de contratos onde cada linha tem ClientID e contractorID apontando para um valor parties. partyID - ou seja, cada linha de contratos aponta para duas linhas de partidas. Você deseja listar os nomes de todos os contratados e seus clientes. SELECT clientpartyID, pCli. name AS Cliente, contractorpartyID, pCon. name AS Contratante FROM contratos INNER JOIN festas AS pCli ON contracts. clientpartyID pCli. partyID INNER JOIN festas AS pCon ON contract. contractorpartyID pCon. partyID Pais com e sem filhos Você tem festas E tabelas de contratos. Cada linha contratos tem um valor contratantepartyID que referencia uma linha em partes e um valor clientpartyID que também faz referência a uma linha em partes. Como listar todas as partes e seus contratos, mostrando espaços em branco como strings vazias ao invés de NULLs SELECT parties. partyID, IFNULL (contractorpartyID,) AS contratante, IFNULL (clientpartyID,) AS client FROM partes LEFT JOIN contractorclient ON partyIDcontractorpartyID ORDER BY partyID --- -------------------------- partyID cliente contratante --------------------- -------- 1 2 2 1 3 ----------------------------- Paginação Suponha que você tenha uma lista telefônica de Nomes, endereços, etc. Você está exibindo 20 linhas por página, você está na página 100, e você deseja exibir a página 99. Como você faz isso sabendo apenas qual página você está assumindo. Números de página baseados em 1 que você está na página P cada página mostra N linhas, em seguida, a fórmula geral para traduzir um número de página com base em 1 em um primeiro argumento LIMIT é. MAX (0, P-1) N que para a 99ª página de 20 linhas avalia para 1960, eo segundo argumento para LIMIT é apenas N, para ver página 99, escreva. SELECT. LIMIT (1960, N) O problema com isso é a escala. MySQL doesnt otimizar LIMIT em todos os bem. SELECT. LIMIT 1000000,20 irá, infelizmente, recuperar não apenas as vinte linhas a partir da milionésima linha que irá recuperar um milhão e vinte linhas antes que você mostra o 20 que você pediu Quanto maior o resultado, o limite mais longo leva. Na cláusula WHERE e certifique-se de que existe um índice de cobertura para a coluna de paginação. Em uma tabela de 100.000 inteiros aleatórios indexados, SELECT. ONDE. Para os últimos 20 inteiros na tabela é duas vezes mais rápido que a consulta de LIMIT comparável. Com um milhão de inteiros, seu mais de 500 vezes mais rápido Se a sua interface pede para mostrar apenas 20 linhas por página em uma determinada ordem, recuperar as vinte linhas, mais a linha imediatamente antes do conjunto se ele existe, mais a próxima linha após os vinte Se existir. Quando o usuário clica no botão Página anterior, ajuste a cláusula WHERE para especificar linhas onde o valor da chave é ORDER BY o índice atual DESC LIMIT 20 da mesma forma quando o usuário clica no botão Próxima Página, a cláusula WHERE especifica os valores-chave da linha Logo após o conjunto, e ORDER BY o índice atual ASC LIMIT 20. Fazer valores de uma seqüência de coluna Você tem uma tabela tbl com uma coluna cujos valores podem ser livremente substituídos e que precisa ser preenchido com valores perfeitamente seqüenciais começando com 1: SET i0 UPDATE tbl SET keycol (i: i1) Mas mais frequentemente, o que Você precisa é um mecanismo para garantir uma seqüência ininterrupta de valores chave inteiros sempre que uma linha é inserida. Aqui está a má notícia: o autoincremento não fornece essa lógica, por exemplo, não impede falhas de inserção, edição da coluna ou exclusão subseqüente. A boa notícia é que a lógica para garantir uma seqüência ininterrupta é direta: (ii) criar uma tabela com a seqüência desejada em uma coluna e outras colunas para acompanhar o uso (para usar essa tabela para colunas seqüenciais em várias outras tabelas (Iii) escrever Triggers para buscar a próxima chave sequencial disponível a partir dessa tabela, marcá-la usada e atribuí-la em Insert (iv) colocar toda essa lógica dentro de blocos de transações (v) proibir a exclusão na mesa. Acompanhamento da conclusão do projeto passo a passo Uma tabela mestre tem uma linha para cada projeto e o número de etapas seqüenciais necessárias para concluir cada projeto. Uma tabela de detalhes tem uma linha por projeto por etapa concluída: TABELA DROP EXISTE t1 TABELA CREATE t1 (id INT, nome do projeto CHAR (2), projectsteps INT) INSERT INTO t1 VALUES (1, xx, 3), (2, yy, 3), (3, zz, 5) TABELA DROP EXISTE t2 CRIAR TABELA t2 (Raios vencedores Dada uma tabela de IDs e resultados ganhos-perdidos, como podemos encontrar a tabela de queda de raias vencedoras mais longa se existir resultados criar resultados de tabela Int, caracter de resultado (1)) inserir nos valores de resultados (1, w), (2, 1), (3, 1), (4, w), (5, w) , L), (8, w), (9, w) seleccionar dos resultados -------------- id result -------------- 1 w 2 l 3 l Grande distância de círculo Encontre a distância em quilómetros entre dois pontos na superfície da terra. É exatamente o tipo de problema para o qual as funções armazenadas foram feitas. Para uma aproximação de primeira ordem, ignore os desvios da superfície terrestre da perfeição COS (lat1-lat2) (1COS (lon1-lon2)) - COS (lat1lat2) (1-COS (lon1-lon2), e a distância em radianos é dada por um número de fórmulas trigonométricas, ) Rads ACOS (---------------------------------------------- -----------------------) 2 Precisamos converter graus latitude e longitude em radianos, e precisamos saber o comprimento em km de um radiano no Terra, que é 6378.388. A função: set logbintrustfunctioncreatorsTRUE Média móvel Dada uma tabela de datas e valores diários, recupera sua média móvel de 5 dias: DROP TABLE if EXISTS t TABELA CREATE T (dt DATE, qty INT) INSERT INTO t VALUES (2007-1-1, 5), (2007-1-2,6), (2007-1-3,7), (2007-1-4,8), (2007-1-5,9), (2007-1-6, 10), (2007-1-7,11), (2007-1-8,12), (2007-1-9,13) SELECT a. dt, a. qty, Round ((SELECT SUM (b. qty ) / COUNT (b. qty) FROM t AS b Múltiplas somas em uma junção Você tem uma tabela de festas que contém informações sobre nomes de povos etc. e uma tabela de contratos em que cada linha define um contrato, identificando um cliente como clientpartyID e um contratante Como contractorpartyID, cada um deles uma chave estrangeira referenciando parties. partyID. Você quer uma lista de partes que mostram quantos contratos eles participaram como cliente, e quantos theyve participado como contratante. SEGUIR p. partyID, p. name, ( SELECT COUNT () FROM contractorclient c1 WHERE c1.clientpartyID p. partyID) AS ClienteDeals, (SELECT COUNT () FROM contractorclient c2 WHERE c2.contractorpartyID p. partyID) AS ContractorDeals FROM partes p ORDER BY partyID Percentiles Na Sakila tabela filme. Recuperar um ranking de percentil descendente de comprimentos de filme: SELECT a. filmid. ROUND (100.0 (SELECT COUNT () FROM filme AS b ONDE b. length lt a. length) / total. cnt, 1) AS percentil FROM filme a CROSS JOIN (SELECT COUNT) AS cnt FROM filme) AS total ORDER BY percentil DESC