1

I have a table (PostgreSQL 9.6) containing 2.6 million+ timestamped rows associated with account identifiers, and for any given identifier, I want to count the total number of occurrences as well as just the number of occurrences today in a single query.

For reference, this is the same table described in this question, but I have simplified it here to focus on this specific issue:

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());

This is my initial attempt, which is producing the same number for both the daily and total counts due to the 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)

The results I am looking for are:

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

Specifically, the result of this ugly query:

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;

I realize this may be a softball question, but in this case my SQL instincts are failing me, and I suspect there might be some clever trick that would eliminate the extra JOIN.

3

I believe using a SUM + CASE expression would work, because the CASE enables you to do a 'selective count'. Or you can use the newer FILTER:

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;

With the dataset from your question, this gives the following results:

 id | daily | total
----+-------+-------
  2 |    1  |    3
  1 |    2  |    5
  • 2
    Alternatively, use the filter clause: postgresql.org/docs/current/static/… – a_horse_with_no_name Apr 10 '18 at 14:47
  • @a_horse_with_no_name Thank you, I just learned something new. Interestingly, over the full table both versions of the query (CASE vs. FILTER) have near identical execution times (1262ms vs 1238ms). – vallismortis Apr 10 '18 at 14:57
  • 2
    @vallismortis: the filter clause is not a performance optimization, it's more for readability – a_horse_with_no_name Apr 10 '18 at 14:58
  • @a_horse_with_no_name interesting! Never seen that before, probably because I'm working with SQL Server most of the time. – Glorfindel Apr 10 '18 at 14:59
2

This approach is without using 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
    Yeah, just like my first revision using CASE. Which I would've preferred over the current one as it's a cross-platform solution, but as we're using interval we're already in PostgreSQL territory. – Glorfindel Apr 10 '18 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.