Início / coding / Perguntas / 79048302
Accepted
John Beasley
Asked: 2024-10-03 04:05:06 +0800 CST

Obter contagem e data de uma tabela e contagem e data de outra tabela em uma consulta

  • 772

Tenho uma consulta que obtém uma contagem e uma data de uma tabela. Parece com isso:

SELECT 
 date(`date`) as 'Date'
 , sb.`send_id`
 , count(`id`) as 'SUBS' 
FROM `table1`
WHERE `date` BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date(`date`), `send_id`;

Isso me dá resultados parecidos com estes:

|    DATE    |   SEND_ID    |   SUBS    |
-----------------------------------------
| 2024-09-01 |  1111111     |    1900   | 
| 2024-09-01 |  2222222     |    1835   | 
| 2024-09-02 |  1111111     |    264    | 
| 2024-09-02 |  2222222     |    26     | 
-----------------------------------------

Tenho uma segunda consulta que se parece com esta:

SELECT 
  date(`o_date`)
  , `o_send_id`
  , count(`id`) AS 'UNI'
FROM `table2`
WHERE `o_date` BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date(`o_date`), `o_send_id`;

Isso me dá resultados parecidos com estes:

|    O_DATE    |   O_SEND_ID    |   UNITS  |
--------------------------------------------
| 2024-09-01   |    1111111     |    107   | 
| 2024-09-01   |    2222222     |    360   | 
| 2024-09-02   |    1111111     |    223   | 
| 2024-09-02   |    2222222     |    209   | 
--------------------------------------------

O que estou tentando fazer é JUNTAR essas tabelas para exibir SUB e UNIDADES.

Então os resultados seriam mais ou menos assim:

|    DATE    |   SEND_ID    |   SUBS    |   UNITS |
---------------------------------------------------
| 2024-09-01 |  1111111     |    1900   |   107   |
| 2024-09-01 |  2222222     |    1835   |   360   |
| 2024-09-02 |  1111111     |    264    |   223   |
| 2024-09-02 |  2222222     |    26     |   209   |
---------------------------------------------------

Aqui está minha tentativa de JUNTAR as tabelas em uma única data:

SELECT 
  date(tb1.`date`) as 'Date'
  , tb1.`send_id`
  , count(tb1.`id`) as 'SUBS' 
  , count(tb2.`id`) as 'UNITS'
FROM `table1` tb1
JOIN `table2` tb2 ON tb2.`o_send_id` = tb1.`send_id` AND tb2.`o_date` = tb1.`date`
WHERE (tb1.`date` BETWEEN '2024-09-01%' AND '2024-09-30%')
GROUP BY date(tb1.`date`), tb1.`send_id`;

Ele roda um pouco, mas eventualmente trava. Estou supondo que seja porque há muitos registros (na casa dos milhões). Recebo um erro que diz:

Error in processing request
Error code: 500
Error text: Internal Error (rejected)
It seems that the connection to server has been lost.
Please check your network connectivity and server status.

Então tentei executar a mesma consulta, mas desta vez usei apenas 1 data e 1 send_id, assim:

SELECT 
  date(tb1.`date`) as 'Date'
  , tb1.`send_id`
  , count(tb1.`id`) as 'SUBS' 
  , count(tb2.`id`) as 'UNITS'
FROM `table1` tb1
JOIN `table2` tb2 ON tb2.`o_send_id` = tb1.`send_id` AND tb2.`o_date` = tb1.`date`
WHERE (tb1.`date` LIKE '2024-09-30%') AND tb1.`send_id` = '1111111'
GROUP BY date(tb1.`date`), tb1.`send_id`;

Esta consulta é executada, mas obtenho um resultado parecido com este:

|    DATE    |   SEND_ID    |   SUBS     |   UNITS   |
------------------------------------------------------
| 2024-09-30 |  1111111     |    35960   |   35960   |
------------------------------------------------------

Enquanto a consulta foi executada com sucesso, não tenho certeza do que aconteceu com SUBS e UNITS, pois eles chegaram com o mesmo resultado 36960. Os totais devem ser diferentes, e de jeito nenhum devem ser tão altos.

Não tenho certeza do que fiz de errado.

Em uma pergunta anterior que fiz, alguém sugeriu que eu postasse a descrição da tabela.

Aqui está a descrição da tabela 1:

| Field   |   Type        | Null | Key  | Default |         Extra       |
-------------------------------------------------------------------------
| id      | int(11)       |  NO  |  PRI |  NULL   |  AUTO_INCREMENT     |  
| send_id | varchar(255)  |  YES |      |  NULL   |                     |   
| date    | datetime      |  YES |      |  NULL   |                     |
-------------------------------------------------------------------------

E aqui está a descrição da tabela2:

| Field     |   Type        | Null | Key  | Default |         Extra       |
---------------------------------------------------------------------------
| id        | int(11)       |  NO  |  PRI |  NULL   |  AUTO_INCREMENT     |  
| o_send_id | varchar(255)  |  YES |      |  NULL   |                     |    
| o_date    | datetime      |  YES |      |  NULL   |                     |
---------------------------------------------------------------------------

Nada de especial nas descrições de cada mesa.

Dito isso, ajude-me a descobrir como posso fazer com que a contagem de SUBS e UNIT seja exibida na mesma consulta.

Os índices da tabela 1 estão abaixo.

insira a descrição da imagem aqui

E aqui estão os índices da tabela2.

insira a descrição da imagem aqui

Estou usando apenas o ID.

mysql

2 respostas

  1. Best Answer

    Você pode juntar suas 2 consultas como

    select coalesce(first.Date,second.Date) as `date`
  ,coalesce(first.`send_id`,second.`send_id`)
  ,coalesce(first.SUBS,0) Subs
  ,coalesce(first.UNI,0) Uni
from
(  SELECT 
   date(`date`) as 'Date'
   , sb.`send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
 )first
left join (
  SELECT date(`o_date`)
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)second on first.date=second.date and first.send_id=second.send_id

    Se dateou o_send_idexistir em uma saída de subconsulta e não existir em outra, você poderá perder linhas.

    Atualização 1:

    Caso mais confiável

    eu ia data id_de_envio
    1 2024-09-01 100
    2 2024-09-01 101
    3 2024-09-02 101
    eu ia o_date o_send_id
    1 2024-09-01 100
    2 2024-09-01 101
    3 2024-09-01 101
    4 2024-09-11 104 
    select `date`
  ,send_id
  ,sum(Subs) subs
  ,sum(UNI)  Uni
from
(  SELECT date(`date`) as `date`, `send_id` , count(`id`) as 'SUBS' ,0 UNI
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
  union all
  SELECT date(`o_date`) `date` , `o_send_id` ,0 SUBS ,count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)u
group by `date`,send_id
    data id_de_envio Subs Uni
    2024-09-01 100 1 1
    2024-09-01 101 1 2
    2024-09-02 101 1 0
    2024-09-11 104 0 1

    Atualização 2:

    No primeiro exemplo, perdemos date=2024-09-11' e send_id=104

    select coalesce(first.Date,second.Date) as `date`
  ,coalesce(first.`send_id`,second.`o_send_id`) send_id
  ,coalesce(first.SUBS,0) Subs
  ,coalesce(second.UNI,0) Uni
  ,first.*, second.*
from
(  SELECT 
   date(`date`) as 'Date'
   , `send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
 )first
left join (
  SELECT date(`o_date`) Date
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)second on first.date=second.date and first.send_id=second.o_send_id
    data id_de_envio Subs Uni Data id_de_envio SUBS Data o_send_id UNI
    2024-09-01 100 1 1 2024-09-01 100 1 2024-09-01 100 1
    2024-09-01 101 1 2 2024-09-01 101 1 2024-09-01 101 2
    2024-09-02 101 1 0 2024-09-02 101 1 nulo nulo nulo

    violino

    • 1

  2. Um índice composto em (send_id, date)ou talvez ((date(date)), send_id)provavelmente melhoraria o desempenho de todas as suas consultas.

    Em vez de juntar as tabelas, você pode juntar suas consultas agrupadas.

    Nota: Você não precisa adicionar um % a uma consulta entre datas, o MySQL provavelmente está ignorando isso. Faça o cast explicitamente date como recomendado na documentação .

    with table1_count as (
  SELECT 
   date(`date`) as 'Date'
   , sb.`send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`date`), `send_id`
),
table2_count as (
  SELECT 
    date(`o_date`) as 'Date'
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`o_date`), `o_send_id`
)
select 
  t1c.date,
  t1c.send_id,
  t1c.subs,
  t2c.uni
from table1_count t1c
left join table2_count t2c on 
  t1c.date = t2c.date and 
  t1c.send_id = t2c.o_send_id
order by t1c.date, t1c.subs

    A desvantagem dessa abordagem é que se algum dia estiver faltando na tabela1, mas estiver na tabela2, ele não aparecerá na consulta.

    Como você está no MySQL 5.7, que não tem with, elas podem ser reescritas usando subconsultas.

    select 
  t1c.date,
  t1c.send_id,
  t1c.subs,
  t2c.uni
from (
  SELECT 
    date(`date`) as 'Date'
    , sb.`send_id`
    , count(`id`) as 'SUBS' 
    FROM `table1`
    WHERE `date` BETWEEN date('2024-09-01') AND date('2024-09-30')
    GROUP BY date(`date`), `send_id`
) t1c
left join (
  SELECT 
    date(`o_date`) as 'Date'
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`o_date`), `o_send_id`
) t2c on 
  t1c.date = t2c.date and 
  t1c.send_id = t2c.o_send_id
order by t1c.date, t1c.subs
    • 0

relate perguntas