Postgres COUNT 具有 INNER JOIN 的列值数 [英] Postgres COUNT number of column values with INNER JOIN

问题描述

我正在 Postgres 9.3 中创建报告.这是我的 SQL Fiddle.
基本上我有两个表，responses 和questions，结构是:

I am creating a report in Postgres 9.3. This is my SQL Fiddle.
Basically I have two tables, responses and questions, the structure is:

responses
->id
->question_id
->response

questions
->id
->question
->costperlead

对于列 response 只能有 3 个值，Yes/No/Possbily，我的报告应该有列:

for the column response there can only be 3 values, Yes/No/Possbily, and my report should have the columns:

  question_id
, # of Yes Responses
, # of No Responses
, # of Possbily Responses
, Revenue

那么:

# of Yes Responses - count of all Yes values in the response column
# of No Responses - count of all No values in the response column
# of Possbily Responses - count of all 'Possbily' values in the response column

收入是 costperlead *(是响应的数量 + 可能响应的数量).

Revenue is the costperlead * (Number of Yes Responses + Number of Possibly Responses).

我不知道如何构造查询，我是新来的，而且我来自 MySQL，所以 postgres 的一些事情是不同的.在我的 SQL Fiddle 示例中，大多数响应都是 Yes 和 Null，最终没关系，会有可能和否.

I don't know how to construct the query, I'm new plus I came from MySQL so some things are different for postgres. In my SQL Fiddle sample most responses are Yes and Null, it's ok eventually, there will be Possibly and No.

到目前为止我只有:

SELECT a.question_id
FROM responses a
INNER JOIN questions b ON a.question_id = b.id
WHERE a.created_at = '2015-07-17'
GROUP BY a.question_id;

推荐答案

由于唯一的谓词过滤表 responses 中的行，因此最有效的是先聚合响应，然后加入问题:

Since the only predicate filters rows from table responses, it would be most efficient to aggregate responses first, then join to questions:

SELECT *, q.costperlead * (r.ct_yes + r.ct_maybe) AS revenue
FROM  (
   SELECT question_id
        , count(*) FILTER (WHERE response = 'Yes')      AS ct_yes
        , count(*) FILTER (WHERE response = 'No')       AS ct_no
        , count(*) FILTER (WHERE response = 'Possibly') AS ct_maybe
   FROM   responses
   WHERE  created_at = '2015-07-17'
   GROUP  BY 1
   ) r
JOIN   questions q ON q.id = r.question_id;

db<>fiddle 这里

这使用聚合 FILTER 子句(在 Postgres 9.4 或更高版本中).见:

This uses the aggregate FILTER clause (in Postgres 9.4 or later). See:

• 具有附加(不同)过滤器的聚合列

旁白:考虑将 response 实现为 boolean 类型，true/false/null.

Aside: consider implementing response as boolean type with true/false/null.

对于 Postgres 9.3:

For Postgres 9.3:

SELECT *, q.costperlead * (r.ct_yes + r.ct_maybe) AS revenue
FROM  (
   SELECT question_id
        , count(response = 'Yes' OR NULL)      AS ct_yes
        , count(response = 'No' OR NULL)       AS ct_no
        , count(response = 'Possibly' OR NULL) AS ct_maybe
   FROM   responses
   WHERE  created_at = '2015-07-17'
   GROUP  BY 1
   ) r
JOIN   questions q ON q.id = r.question_id;

_{旧 sqlfiddle}

综合比较技术:

• 对于绝对性能，SUM 是更快还是 COUNT?

这篇关于Postgres COUNT 具有 INNER JOIN 的列值数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！