Início / dba / Perguntas / 102814
Accepted
LefterisL
Asked: 2015-05-30 09:03:54 +0800 CST

MySQL JOIN duas tabelas e obtenha o resultado mais recente

  • 772

Quero juntar duas tabelas e obter o resultado mais recente de cada uma dessas duas em uma única tabela. Estou meio preocupado com a velocidade também, já que as mesas estão crescendo meio rápido. Perto de 60-70k registros por dia. Mais tarde, irei para o particionamento, mas isso é outro problema. Agora eu tenho uma tabela principal com as devicesinformações.

+--------+-----------+---------+
|     id |    Name   | type    |
+--------+-----------+---------+
|      1 | Oh        | A       |
|      2 | This      | A       |
|      3 | Is        | B       |
|      4 | Hard      | A       |
+--------+-----------+---------+

De acordo com o tipo, eles têm alguns dados em tabelas diferentes. O tipo A é

+--------+-----------+------------------+---------+---------+
|     id | device_id |   stats_time     | status  |  noise  |
+--------+-----------+------------------+---------+---------+
|      1 | 1         | 2012-10-23 07:50 | foo     |   10    |
|      2 | 1         | 2012-10-23 16:59 | bar     |   12    |
|      3 | 2         | 2012-10-23 15:11 | bar     |   0     |
|      4 | 4         | 2012-10-23 23:23 | foo     |   25    |
+--------+-----------+------------------+---------+---------+

Tipo B é

+--------+-----------+------------------+---------+---------+
|     id | device_id |   stats_time     | status  |  signal |
+--------+-----------+------------------+---------+---------+
|      1 | 3         | 2012-10-23 04:50 | foo     |  1000   |
|      2 | 3         | 2012-10-23 05:59 | bar     |  450    |
|      3 | 3         | 2012-10-23 09:11 | bar     |  980    |
|      4 | 3         | 2012-10-23 10:23 | foo     |   0     |
+--------+-----------+------------------+---------+---------+

Estou quebrando a cabeça por uma consulta para acabar com algo assim

+--------+-----------+------------------+---------+---------+---------+
|     id | device_id |   stats_time     | status  |  signal |   noise |
+--------+-----------+------------------+---------+---------+---------+
|      1 | 1         | 2012-10-23 16:59 | bar     |  12     |         |
|      2 | 2         | 2012-10-23 15:11 | bar     |  0      |         |
|      3 | 3         | 2012-10-23 10:23 | foo     |         |    0    |
|      4 | 4         | 2012-10-23 23:23 | foo     |  25     |         |
+--------+-----------+------------------+---------+---------+---------+

Usar a consulta abaixo não é bom, pois recebo duas colunas destats_time

SELECT devices.id AS id, A.stats_time , B.stats_time
FROM devices 
LEFT JOIN A ON devices.id = A.device_id 
LEFT JOIN B ON devices.id = B.device_id 
GROUP BY devices.id

Antes de acabar usando tabelas diferentes para os tipos de dispositivo, usei para obter os resultados com o seguinte, mas acabei ficando muito lento

SELECT *
FROM (
    SELECT *
    FROM A
    ORDER BY stats_time DESC, id ASC
) AS d
RIGHT JOIN devices ON A.device_id = devices.id
GROUP BY devices.id
mysql performance

2 respostas

  1. Afirmei nos comentários acima que este parece ser um caso de relacionamento supertipo-subtipo, mas, como alterar a estrutura do banco de dados pode ser uma decisão que está fora do escopo desta questão, vou me concentrar em oferecer uma solução para sua situação atual.

    Então, depois de algumas edições, decidi incluir algumas DDLdeclarações e uma breve descrição de minhas suposições sobre a estrutura do seu banco de dados, esperando que, dessa forma, minha resposta e suas consultas envolvidas sejam mais facilmente compreendidas.

    Compreensão da situação e hipotéticaDDL

    Conforme eu percebo o cenário, type_ae type_bpodem ser dois tipos diferentes de readingou measurementque você está coletando para cada umdevice.

    Dessa forma, embora eu não tenha certeza do significado das colunas type_a.type_a_ide type_b.type_b_id, presumo que sejam algum tipo de ou sequential_numberem cada tabela respectiva. Da mesma forma, as colunas e são os pontos exatos no tempo em que a publica algum tipo de arquivo .row_numberrecord_identifiertype_a.stats_timetype_b.stats_timedevicereading

    Também assumi que type_a.device_idsão type_b.device_idFOREIGN KEYS que fazem referência à devicetabela que, por sua vez, tem a coluna device.device_idsendo usada como uma espécie de sequential_numberou row_numberou record_identifierque você definiu como PRIMARY KEY.

    Tendo descrito minha compreensão do estado de coisas, observe que não estou sugerindo que esta seja a estrutura ideal (já que, naturalmente, não estou familiarizado com o cenário real, pode faltar normalização, integridade etc.), estou apenas fazendo algumas suposições com base nas amostras de dados e consultas que você apresentou, a fim de fornecer uma possível solução para sua situação específica. Então vamos as especulações DDL:

    CREATE TABLE device
(
    device_id INT      NOT NULL AUTO_INCREMENT,
    name      CHAR(30) NOT NULL,
    type      CHAR(1)  NOT NULL,
    PRIMARY KEY (device_id),
    UNIQUE INDEX uix_name (name) 
);

CREATE TABLE type_a
(
    type_a_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL ,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    noise      INT      NOT NULL,
   PRIMARY KEY (type_a_id),
   CONSTRAINT FOREIGN KEY fk_type_a_device (device_id) 
   REFERENCES device(device_id),
   UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

CREATE TABLE type_b
(
    type_b_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    `signal`   INT      NOT NULL,
    PRIMARY KEY (type_b_id),
    CONSTRAINT FOREIGN KEY fk_type_b_device (device_id) 
    REFERENCES device(device_id),
    UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

-- Some ‘device’ INSERTS...

INSERT INTO device (name, type) VALUES ('First device', 'A');
INSERT INTO device (name, type) VALUES ('Second device', 'A');

-- ... And then, some ‘type_a’ and ‘type_b’ INSERTS 
-- in order to have some sample data for retrieving.

INSERT INTO type_a (device_id, stats_time, `status`, noise) 
VALUES (1, STR_TO_DATE('06/01/2015 08:10:01 AM', '%c/%e/%Y %r'), 'Foo', 123);

INSERT INTO type_a (device_id, stats_time, `status`, noise) 
VALUES (1, STR_TO_DATE('04/04/2015 03:07:34 PM', '%c/%e/%Y %r'), 'Bar', 456);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`) 
VALUES (2, STR_TO_DATE('03/04/2015 02:08:15 PM', '%c/%e/%Y %r'), 'Boo', 789);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`) 
VALUES (2, STR_TO_DATE('05/07/2015 04:03:12 PM', '%c/%e/%Y %r'), 'Far', 852);

    proposta inicial

    E então, aqui está minha primeira consulta enviada que, mantendo a ideia original, foi reformatada e adaptada à DDLestrutura que foi adicionada posteriormente:

    SELECT DE.device_id,
       COALESCE(TA.type_a_id, TB.type_b_id)   AS type_id,
       DE.name,
       DE.type,    
       COALESCE(TA.stats_time, TB.stats_time) AS stats_time,
       COALESCE(TA.status, TB.status)         AS `status`,
       COALESCE(TA.noise, 0)                  AS noise,
       COALESCE(TB.signal, 0)                 AS `signal`
  FROM device            DE
  LEFT OUTER JOIN type_a TA
    ON TA.device_id    = DE.device_id
  LEFT OUTER JOIN type_b TB
    ON TB.device_id    = DE.device_id
ORDER BY stats_time DESC;

    Como você pode ver, estou usando a função COALESCE() que somente se a coluna type_a.stats_timecontiver um NULLvalor, então 'imprime' o valor da coluna type_b.stats_timee funciona de maneira semelhante para as colunas type_a.statuse .type_b.status

    Proposta subsequente

    Primeiro método

    Agora, depois de reler sua pergunta e depois de algumas interações de comentários, entendo que o que você realmente deseja é obter um único conjunto de resultados contendo apenas duas linhas - uma linha incluindo colunas correspondentes ao type_a.stats_timevalor mais recente e uma linha contendo colunas relacionadas ao type_b.stats_timevalor mais recente —. Portanto sugiro a seguinte consulta que também recupera dados da DDLproposta acima:

    (SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,      
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id  = DE.device_id
  WHERE TA.stats_time = (SELECT MAX(stats_time) 
                           FROM type_a))

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,      
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id  = DE.device_id
  WHERE TB.stats_time = (SELECT MAX(stats_time) 
                           FROM type_b))

ORDER BY stats_time DESC;

    Observe o uso do operador UNION , que serve para combinar em um único conjunto de resultados a última linha de type_a(com base no stats_timevalor da coluna mais recente, obtido por meio da função MAX() em uma subconsulta na cláusula WHERE) com o último row in type_b(da mesma forma, com base no stats_timevalor da coluna mais recente, também obtido por meio da função MAX() em uma subconsulta na cláusula WHERE).

    Segundo método

    Você também pode tentar com esta consulta alternativa, que classifica cada um dos conjuntos de resultados combinados com base na stats_timecoluna em cada instrução SELECT respectiva usando as cláusulas ORDER BY e LIMIT .

    (SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,      
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id = DE.device_id
  ORDER BY TA.stats_time DESC LIMIT 1)

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,      
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id = DE.device_id
  ORDER BY TB.stats_time DESC LIMIT 1)

ORDER BY stats_time DESC;

    Depois de comparar o desempenho de todos os métodos sugeridos, será fácil definir qual é o melhor para suas necessidades. Além disso, se um deles resolver o seu problema, você poderá configurá-lo como um VIEW, assim a recuperação futura de dados será mais fácil de obter.

    Sobre o aspecto de desempenho de velocidade de suas consultas, você pode começar a refinar tais questões prestando atenção especial à definição de índices nas colunas apropriadas, por exemplo, type_a.stats_timee type_b.stats_time.

    • 3
  2. Best Answer

    Eu vejo isso como 2 etapas:

    1. Crie tabelas apenas com o sinal (ou ruído) mais recente para cada dispositivo
    2. JOINou UNIONas duas tabelas.

    O passo 1 é uma variante de groupwise max :

    SELECT  device_id, stats_time, status, noise -- The desired columns
    FROM  ( SELECT  @prev := '' ) init
    JOIN  ( SELECT
                device_id != @prev AS first, -- `device_id` is the 'GROUP BY'
                @prev := device_id,          -- the 'GROUP BY'
                device_id, stats_time, status, noise -- Also the desired columns
            FROM  TableA -- The table
            ORDER BY  device_id  DESC, -- The 'GROUP BY'
                      stats_time DESC  -- to get latest
      ) x
    WHERE  first;

    Isso pode ser benéfico para o desempenho:

    INDEX(device_id, stats_time)

    Idem para TableBe signal. Execute-os manualmente para ver se os acertei.

    Seu exemplo não mostra um caso em que ambos signale noiseexistem para o mesmo device_id. Vou assumir que é realmente o caso, portanto UNION:

    Passo 2:

    SELECT device_id, stats_time, status, signal, noise
    FROM
    ( SELECT device_id, stats_time, status, signal, '' AS noise
        ... (the rest of the signal query)
    )
    UNION ALL
    ( SELECT device_id, stats_time, status, '' AS signal, noise
        ... (the rest of the noise query)
    );
    • 0

relate perguntas