Início / dba / Perguntas / 203511
Accepted
Parker
Asked: 2018-04-11 06:30:00 +0800 CST

Vários COUNTs na mesma coluna

  • 772

Eu tenho uma tabela (PostgreSQL 9.6) contendo mais de 2,6 milhões de linhas com carimbo de data/hora associadas a identificadores de conta e, para qualquer identificador, quero contar o número total de ocorrências , bem como apenas o número de ocorrências hoje em uma única consulta.

Para referência, esta é a mesma tabela descrita nesta pergunta , mas eu a simplifiquei aqui para focar nesse problema específico:

CREATE TABLE account_test
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE log_test
(
  account integer NOT NULL REFERENCES account_test(id),
  event_time timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX account_test_idx ON log_test USING btree (account,event_time);

INSERT INTO account_test VALUES (1);
INSERT INTO account_test VALUES (2);

INSERT INTO log_test VALUES (1,'2018-01-01');
INSERT INTO log_test VALUES (1,'2018-01-02');
INSERT INTO log_test VALUES (1,'2018-01-03');
INSERT INTO log_test VALUES (1,now());
INSERT INTO log_test VALUES (1,now());

INSERT INTO log_test VALUES (2,'2018-01-01');
INSERT INTO log_test VALUES (2,'2018-01-02');
INSERT INTO log_test VALUES (2,now());

Esta é minha tentativa inicial, que está produzindo o mesmo número para as contagens diárias e totais devido ao GROUP BY:

    SELECT a.id,COUNT(d) AS daily,COUNT(t) AS total FROM account_test a 
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id;

 id | daily | total
----+-------+-------
  1 |    10 |    10
(1 row)

Os resultados que procuro são:

 id | daily | total
----+-------+-------
  1 |     2 |     5
(1 row)

Especificamente, o resultado desta consulta feia:

SELECT qd.id,qd.daily,qt.total FROM
(
    SELECT a.id,COUNT(d) AS daily FROM account_test a 
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
     WHERE a.id=1 GROUP BY a.id
) qd,
(
    SELECT a.id,COUNT(t) AS total FROM account_test a 
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id
) qt;

Eu percebo que isso pode ser uma questão de softball, mas neste caso meus instintos SQL estão falhando comigo, e eu suspeito que pode haver algum truque inteligente que eliminaria o extra JOIN.

postgresql count

2 respostas

  1. Best Answer

    Acredito que usar uma expressão SUM+ funcionaria, porque permite que você faça uma 'contagem seletiva'. Ou você pode usar o mais recente :CASECASEFILTER

    SELECT a.id,
  count(*) FILTER (WHERE d.event_time > now() - interval '1 day') AS daily,      
  count(*) AS total
FROM account_test a 
JOIN log_test d ON a.id=d.account
GROUP BY a.id;

    Com o conjunto de dados da sua pergunta, isso fornece os seguintes resultados:

     id | daily | total
----+-------+-------
  2 |    1  |    3
  1 |    2  |    5
    • 5

  2. Esta abordagem é sem usar FILTER

    SELECT 
  a.id,
  sum(if(d.event_time > now() - interval '1 day',1,0)) AS daily,      
  count(*) AS total
FROM account_test a 
JOIN log_test d ON a.id=d.account
GROUP BY a.id;
    • 2

relate perguntas