将相同的表连接两次以用于不同列中的计数

I have 2 tables

我有2张桌子

A
+----+-------+
| Id | User  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
+----+-------+

B
+----+--------+------+
| Id | UserId | Type |
+----+--------+------+
|  1 |      1 | A    |
|  2 |      1 | B    |
|  3 |      1 | C    |
|  4 |      2 | A    |
|  5 |      2 | B    |
|  6 |      2 | C    |
|  7 |      3 | A    |
|  8 |      3 | C    |
+----+--------+------+

UserId is FK from table A.Id

I'm trying to get count of each type and type permutations as below with single SQL query. (e.g count A^B means that number of users who has type A and B)

我试图通过单个SQL查询来计算每种类型和类型排列,如下所示。 (例如,计数A ^ B表示具有类型A和B的用户数)

+---------+---------+---------+-----------+-----------+-----------+-------------+
| Count A | Count B | Count C | Count A^B | Count A^C | Count B^C | Count A^B^C |
+---------+---------+---------+-----------+-----------+-----------+-------------+
|       3 |       2 |       3 |         2 |         3 |         2 |           2 |
+---------+---------+---------+-----------+-----------+-----------+-------------+

Or separate query for each permutation count.

或者对每个排列计数单独查询。

I tried below query to get count for type A and B separately and it didn't work.

我尝试在下面的查询分别计算类型A和B,但它不起作用。

SELECT count(b1.type) AS count_a, count(b2.type) AS count_b FROM A 
JOIN B on A.id = B.user_id
WHERE b1.type = 'A' or b2.type = 'B' 
GROUP BY A.id;

+---------+---------+
| Count A | Count B |
+---------+---------+
|       3 |       2 |
+---------+---------+

2 个解决方案

#1

You can write:

你可以写:

select count(case when "Types" @> array['A'] then 1 end) as "COUNT A",
       count(case when "Types" @> array['B'] then 1 end) as "COUNT B",
       count(case when "Types" @> array['C'] then 1 end) as "COUNT C",
       count(case when "Types" @> array['A','B'] then 1 end) as "COUNT A^B",
       count(case when "Types" @> array['A','C'] then 1 end) as "COUNT A^C",
       count(case when "Types" @> array['B','C'] then 1 end) as "COUNT B^C",
       count(case when "Types" @> array['A','B','C'] then 1 end) as "COUNT A^B^C"
  from ( select array_agg("Type"::text) as "Types"
           from "B"
          group by "UserId"
       ) t
;

The idea is that first we use a subquery that produces, for each user, an array containing his/her types; the outer query then just counts the arrays that contain each set of types.

我们的想法是首先使用子查询为每个用户生成一个包含他/她类型的数组;然后外部查询只计算包含每组类型的数组。

You can see it in action at http://sqlfiddle.com/#!15/cbb45/1. (I've also included there a modified version of the subquery, to help you see how it works.)

您可以在http://sqlfiddle.com/#!15/cbb45/1上看到它的实际效果。 (我还在其中包含了子查询的修改版本,以帮助您了解它是如何工作的。)

Some relevant PostreSQL documentation:

一些相关的PostreSQL文档:

http://www.postgresql.org/docs/9.1/static/functions-aggregate.html (explains array_agg)

http://www.postgresql.org/docs/9.1/static/functions-aggregate.html(解释array_agg)

http://www.postgresql.org/docs/9.1/static/functions-array.html (explains @>)

http://www.postgresql.org/docs/9.1/static/functions-array.html(解释@>)

#2

Maybe I am interpreting this wrong, but I think you can do a pretty simple case statement in your select statement, but rather than count do a SUM:

也许我正在解释这个错误,但我认为你可以在你的select语句中做一个非常简单的case语句,而不是计算一个SUM:

SELECT SUM(CASE b.Types WHEN 'A' THEN 1 ELSE 0) as COUNT_A,
       SUM(CASE b.Types WHEN 'B' THEN 1 ELSE 0) as COUNT_B
FROM  A 
JOIN B
ON A.id = B.user_id
WHERE b1.type = 'A' or b2.type = 'B'

#1