Início / dba / Perguntas / 6045
Accepted
James Lee Vann
Asked: 2011-09-22 18:21:24 +0800 CST

Como posso melhorar minha instrução SQL com resultados semanais com semana começando na quinta-feira ou em qualquer outro dia da semana?

  • 772

Sou totalmente novato e não consegui encontrar uma boa maneira de fazer isso em nenhum lugar. Eu tenho uma tabela de banco de dados que contém estatísticas registradas em vários momentos da semana. A semana de relatórios começa na quinta-feira. A tabela contém uma coluna de carimbo de data (data) que armazena quando os dados foram registrados.

Preciso extrair os dados de uma determinada semana (as semanas de relatórios começam na quinta-feira). Eu escrevi a seguinte consulta:

   SELECT * 
FROM `table` 
WHERE 1 = CASE 
  WHEN WEEKDAY(NOW()) = 0 THEN DATEDIFF(NOW(),`date`) BETWEEN -2 AND 4
  WHEN WEEKDAY(NOW()) = 1 THEN DATEDIFF(NOW(),`date`) BETWEEN -1 AND 5
  WHEN WEEKDAY(NOW()) = 2 THEN DATEDIFF(NOW(),`date`) BETWEEN -0 AND 6
  WHEN WEEKDAY(NOW()) = 3 THEN DATEDIFF(NOW(),`date`) BETWEEN -6 AND 0
  WHEN WEEKDAY(NOW()) = 4 THEN DATEDIFF(NOW(),`date`) BETWEEN -5 AND 1
  WHEN WEEKDAY(NOW()) = 5 THEN DATEDIFF(NOW(),`date`) BETWEEN -4 AND 2
  WHEN WEEKDAY(NOW()) = 6 THEN DATEDIFF(NOW(),`date`) BETWEEN -3 AND 3
END

Isso parece funcionar no teste inicial. Mas não tenho certeza se é a melhor maneira de fazer isso. Não sei muito sobre o desempenho do MySQL, mas haverá mais de cem mil registros para filtrar. Esta consulta será muito lenta devido ao número de condições verificadas?

A função NOW () é usada ao extrair o relatório mais atual - no entanto, em alguns casos, precisarei fazer relatórios para outras semanas - portanto, substituiria outra data no local.

Além disso, fazer dessa maneira requer reescrever a consulta se a semana do relatório mudar - digamos que o dia inicial mude para quarta-feira.

Não posso usar a função WEEK() porque você só pode começar uma semana no domingo ou na segunda-feira com ela.

Todas as idéias para melhorar esta consulta são muito apreciadas!

Outras notas: Atualmente usando MariaDB 5.3.

mysql mariadb

3 respostas

  1. Best Answer

    Aqui está uma consulta que escrevi para fornecer a quinta-feira mais recente e a quarta-feira final

    SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

    Aqui está um exemplo para hoje, 2011-09-21

    mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

    Apenas substitua as chamadas de função NOW () por qualquer data e hora que desejar e você terá a semana começando na quinta-feira o tempo todo para a data e hora que escolher.

    Aqui está outro exemplo usando a data específica '2011-01-01'

    mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

    Sua consulta de tablereferência hoje seria algo como isto:

    SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

    De uma chance !!!

    ATUALIZAÇÃO 2011-09-22 16:27 EDT

    Esta foi minha consulta proposta para marcar quinta-feira.

    SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

    Que tal nas outras semanas???

    • (SELECT SUBSTR('6012345',wkndx,1)faz a semana começando seg terminando dom
    • (SELECT SUBSTR('5601234',wkndx,1)faz a semana começando Ter terminando Seg
    • (SELECT SUBSTR('4560123',wkndx,1)faz a semana começando na quarta terminando na terça
    • (SELECT SUBSTR('3456012',wkndx,1)faz a semana começando qui terminando qua
    • (SELECT SUBSTR('2345601',wkndx,1)faz a semana começando sexta terminando qui
    • (SELECT SUBSTR('1234560',wkndx,1)faz a semana começando sab terminando sex
    • (SELECT SUBSTR('0123456',wkndx,1)faz a semana começando Dom terminando Sáb
    • 5

  2. Deixe-me reafirmar o que você está pedindo para ter certeza de que estou acertando:

    Você deseja extrair todos os registros dos últimos 7 dias em uma data especificada?

    Pode parecer com:

    select * from table where `date` between $date - interval 7 day and $date

    É importante observar que $date não é uma sintaxe literal do mysql, mas apenas um exemplo de espaço reservado para a data de início que você deseja. Se for para relatórios, imagino que a consulta será gerada a partir de algum script? Se for verdade, pode ser mais simples nesse idioma e passar o valor literal como parte da consulta construída.

    Sou fã de manter as consultas o mais simples possível, então deixaria assim. Deixarei espaço para que outras pessoas contribuam com respostas para realizar o que você deseja em uma única consulta SQL-fu.

    Editar: depois de reler sua postagem, parece que você provavelmente está usando o tipo de data. Nesse caso, o seguinte bloco em itálico pode ser redundante. Estou deixando para o caso de ser útil para outras pessoas (e desde que dediquei um tempo para escrevê-lo :-)

    Você disse que está usando um "datestamp"? Isso não é tecnicamente um tipo de dados Mysql. É uma data/hora, carimbo de data/hora ou data (hora e ano também existem, mas pelo seu contexto, sinto que não são aplicáveis)? Pergunto porque você pode querer torná-la apenas uma coluna de data em vez das outras. Se esta é a escolha certa para você, realmente depende dos detalhes de como a coluna está sendo usada e da tabela que está sendo consultada. Se o único objetivo é apenas extrair registros em um intervalo de datas, independentemente da hora, a data é definitivamente o caminho a percorrer. Por um lado, são necessários apenas 3 bytes em vez de 4 ou 8. Posso elaborar outros motivos pelos quais você deseja usar data se corresponder aos seus requisitos de uso (ou seja, você não se importa com a parte do tempo). Você pode encontrar detalhes sobre os diferentes tipos em http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

    Se e somente se você estiver usando data especificamente, considere o seguinte:

    Execute a consulta com o prefixo "explain". Detalhes sobre como interpretar a saída e o que é melhor podem ser encontrados em http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

    Depois de explicar isso, altere a consulta para ser

    select * from table where date in ("N", "N+1"..."N+7")

    onde você enumera todas as datas individuais nas quais está interessado. Já encontrei situações em que está claro que o MySQL não é inteligente o suficiente para usar com eficácia uma consulta de intervalo (ou seja, entre x e y) em vez de enumerar valores específicos para pequenos conjuntos.

    Seja qual for o caso de uso, você deve garantir que a coluna seja indexada se estiver fazendo consultas regulares de relatórios com base em seus valores.

    • 1

  3. O senhor @Rolando obviamente responde à pergunta, mas vou propor outra decisão que permitirá manipular e personalizar calendários entre fusos horários e o grupo mais importante por semanas que são definidos em diferentes fusos horários

    então, vamos supor que seu mySQL esteja rodando em um servidor UTC configurado e você queira ter um calendário customizado com 7 horas de antecedência e, portanto, suas semanas devem começar no sábado às 7 da manhã

    CREATE TABLE `wh_blur_calendar` (
  `date` timestamp NOT NULL ,
  `y` smallint(6) DEFAULT NULL,
  `q` tinyint(4) DEFAULT NULL,
  `m` tinyint(4) DEFAULT NULL,
  `d` tinyint(4) DEFAULT NULL,
  `w` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `wh_ints` (
  `i` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into wh_ints values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

    agora uma junção cartesiana popular que deve preencher sua tabela:

    INSERT INTO wh_blur_calendar (date)
SELECT DATE('2010-01-01 00:00:00 ') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM wh_ints a JOIN wh_ints b JOIN wh_ints c JOIN wh_ints d JOIN wh_ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) < 10245
ORDER BY 1;

    Vamos atualizar os horários:

    update  db_wh_repo.wh_blur_calendar set date = date_add(date, interval 7 hour);

    e, finalmente, organize a semana do seu calendário de maneira personalizada

    UPDATE wh_blur_calendar
SET 
    y = YEAR(date),
    q = quarter(date),
    m = MONTH(date),
    d = dayofmonth(date),
    w = week(date_add((date), interval 1 day));

    Acredite em mim, passo algumas horas pensando nessa decisão, mas ela oferece muita liberdade caso você queira agrupar seus resultados com base em um fuso horário personalizado e definições semanais.

    • 1

relate perguntas