Postgres使用INNER JOIN的COUNT列值 [英] Postgres COUNT number of column values with INNER JOIN

问题描述

我在Postgres 9.3中创建了一个报表。这是我的 SQL Fiddle 。

基本上我有两个表，响应和问题，结构是：

 回应
  - > id 
  - > question_id 
  - >回应
 
 question 
  - > id 
  - > question 
  - > costperlead 
  

为响应只能有3个值，是/否/ Possbily ，
我的报告应该有以下列：

  question_id 
，＃of是响应
， 
，＃of Possbily回复
，收入
  

＃of是响应 - 计数全部是响应列中的值
无响应 - 计数全部没有值响应列
返回响应列中的所有Possbily值的计数


收入是 costperlead *（回复数量+可能回复数量）。

知道如何构造查询，我是新的加上我来自MySQL，所以一些事情是不同的postgres。在我的SQL Fiddle示例中，大部分的回答是Yes和Null，最终确定会有可能和没有。

到目前为止，我只有：

  SELECT a.question_id 
从响应a 
 INNER JOIN问题b ON a.question_id = b.id 
 WHERE a.created_at ='2015-07-17'
 GROUP BY a.question_id; 
  

解决方案

由于唯一的谓词过滤响应，高效地聚合响应，然后加入问题：

  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 ='没有'）AS ct_no 
，count（*）FILTER（WHERE response ='Possibly'）AS ct_maybe 
 FROM responses 
 WHERE created_at ='2015-07-17'
 BY 1 
）r 
 JOIN问题q ON q.id = r.question_id; 
  

这使用Postgres 9.4

的新的聚合Filter子句：

• 我如何简化这个游戏统计查询？

code> response 为 boolean type with true / false / null 。

对于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问题q ON q.id = r.question_id; 
  

这里是技术的比较，在聚合之前 FILTER 子句已存在：

• 对于绝对性能，SUM是否更快或COUNT？

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

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

Then:

# 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

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

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.

So far I have only:

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;

解决方案

Since the only predicate filters 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;

This uses the new aggregate Filter clause of Postgres 9.4:

• How can I simplify this game statistics query?

BTW, I would consider implementing response as boolean type with true/false/null.

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;

SQL Fiddle (building on yours).

Here is a comparison of techniques, before the aggregate FILTER clause existed:

• For absolute performance, is SUM faster or COUNT?

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