主页 / dba / 问题 / 203511
Accepted
Parker
Asked: 2018-04-11 06:30:00 +0800 CST

同一列上的多个 COUNT

  • 772

我有一个表（PostgreSQL 9.6），其中包含 260 万多个与帐户标识符关联的带时间戳的行，对于任何给定的标识符，我想在单个查询中计算出现的总数以及今天的出现次数。

作为参考，这是这个问题中描述的同一张表，但我在这里对其进行了简化以关注这个特定问题：

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

这是我最初的尝试，由于以下原因，每日计数和总计数都会产生相同的数字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)

我正在寻找的结果是：

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

具体来说，这个丑陋的查询的结果：

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;

我意识到这可能是一个垒球问题，但在这种情况下，我的 SQL 直觉让我失望了，我怀疑可能有一些巧妙的技巧可以消除额外的JOIN.

postgresql count

2 个回答

  1. Best Answer

    我相信使用SUM+CASE表达式会起作用，因为它CASE使您能够进行“选择性计数”。或者您可以使用较新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;

    使用您问题中的数据集，这给出了以下结果：

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

  2. 这种方法不使用 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

相关问题