Início / dba / Perguntas / 206972
Accepted
Chloe
Asked: 2018-05-17 14:54:08 +0800 CST

É possível combinar essas três consultas de junção em uma?

  • 772

Eu gostaria de combinar as contagens das estatísticas por diferentes hit_types em uma consulta. Isso é possível?

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| aaa |            1 |
| cnn |           16 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 1 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            1 |
+-----+--------------+

MariaDB [db]> select allurls.id, count(s1.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 2 where s1.hit_date >= '2018-01-15'  group by allurls.id;
+-----+--------------+
| id  | count(s1.id) |
+-----+--------------+
| cnn |            4 |
+-----+--------------+

Tentei combinar os dois primeiros, mas os números estão todos confusos e eliminou o primeiro resultado 'aaa'.

MariaDB [db]> select allurls.id, count(s1.id), count(s2.id) from allurls inner join stats s1 on allurls.id = s1.allurl_id and s1.hit_type = 0 inner join stats s2 on allurls.id = s2.allurl_id and s2.hit_type = 1 where s1.hit_date >= '2018-01-15' and s2.hit_date >= '2018-01-15' group by allurls.id;
+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| cnn |           16 |           16 |
+-----+--------------+--------------+

eu esperava ver

+-----+--------------+--------------+
| id  | count(s1.id) | count(s2.id) |
+-----+--------------+--------------+
| aaa |            1 |            0 |
| cnn |           16 |            1 |
+-----+--------------+--------------+

Em última análise, também quero incluir count(distinct(s4.source_id)).

Aqui está um violino: https://www.db-fiddle.com/f/tGP5SbC2AdGgeEwWTAgobf/0

join count

3 respostas

  1. Best Answer

    Não tenho certeza se há algo que sinto falta aqui, mas você pode contar condicionalmente como:

    select allurls.id
    , count(case when s1.hit_type = 0 then 1 end) 
    , count(case when s1.hit_type = 1 then 1 end) 
    , count(case when s1.hit_type = 2 then 1 end) 
    , count(distinct s1.source_id)
from allurls 
join stats s1 
    on allurls.id = s1.allurl_id 
where s1.hit_date >= '2018-01-15' 
  and s1.hit_type between 0 and 2 
group by allurls.id;

 id     c1  c2  c3  c4
aaa     1   0   0   1
cnn     16  1   4   3

    Algumas coisas a serem observadas:

    Mudei hit_typeo predicado para where, não faz diferença lógica, mas prefiro ter apenas o relacionamento entre tabelas na cláusula join se possível.

    distinct não é uma função, count(distinct(s1.source_id))é confuso. Melhor usocount(distinct s1.source_id)

    • 2

  2. Isso pode ajudar (pelo menos parcialmente):

    SELECT allurls.id, COUNT(s1.id)
FROM allurls 
INNER JOIN stats s1 ON allurls.id = s1.allurl_id 
AND (s1.hit_type = 0 OR s1.hit_type = 1 OR s1.hit_type = 2) 
WHERE s1.hit_date >= '2018-01-15'  
GROUP by allurls.id;

    Resultado: 

     id  COUNT(s1.id)
aaa             1
cnn            21

    O db-fiddle está disponível aqui e os números aparecem corretos na medida em que somam as contagens nas 3 primeiras consultas (individual - não OR). Estou dando +1 a esta pergunta por fornecer um violino - como recomendado aqui - se todos os pôsteres fizessem o mesmo (suspiro ...)

    Eu não vi nenhum s2, nem um s4, no violino - talvez ele precise ser expandido?

    • 0

  3. OK, encontrei uma maneira com sub-consultas (elas corrigem tudo), mas não tenho certeza se essa é a mais eficiente.

    MariaDB [db]> SELECT allurls.id,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 0 AND hit_date > '2018-01-15') AS c1,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 1 AND hit_date > '2018-01-15') AS c2,
  (SELECT count(*) FROM stats WHERE allurl_id = allurls.id AND hit_type = 2 AND hit_date > '2018-01-15') AS c3,
  (SELECT count(DISTINCT( source_id )) FROM stats WHERE allurl_id = allurls.id AND hit_date > '2018-01-15') AS c4
FROM   allurls
HAVING c1 > 0; 

+-----+------+------+------+------+
| id  | c1   | c2   | c3   | c4   |
+-----+------+------+------+------+
| aaa |    1 |    0 |    0 |    1 |
| cnn |   16 |    1 |    4 |    3 |
+-----+------+------+------+------+
    • 0

relate perguntas