postgreSQL查询以计数Distinct和Group By(带联接)

Question

在有序过程中，有许多位置的成员处于不同的步骤。成员可以在一个或多个位置处于进度不同的多个进程中。

位置和成员之间的联接表可能看起来像这样，称为步骤。

| id | member_id | step_no | location_id | process_id |
-------------------------------------------------------
| 1  | 1         | 2       | 10          | 57
| 2  | 1         | 5       | 10          | 58
| 3  | 2         | 5       | 11          | 37
| 4  | 2         | 1       | 10          | 57

我一直不知道如何按位置获得成员的唯一计数，因为成员在这个过程中走得最远。

location_id | 1 | 2 | 3 | 4 | 5 |
---------------------------------
 10         | 1 | 0 | 0 | 0 | 1 |
 11         | 0 | 0 | 0 | 0 | 1 |

到目前为止，我有一个问题：

SELECT count(DISTINCT m.id), l.id, l.name
      FROM members m
      INNER JOIN steps s ON m.id = s.member_id
      INNER JOIN locations l ON s.location_id = l.id
      WHERE step_no = 5
      GROUP BY l.id
      ORDER BY l.name

当然，这只返回step_no=5，如果我写了其中五个查询，那么成员可能会在不同的步骤中被计数两次。

Answer 1

select 
location_id
,sum(case when max_step = 1 then 1 else 0 end) as step_one_cnt
,sum(case when max_step = 2 then 1 else 0 end) as step_two_cnt
,sum(case when max_step = 3 then 1 else 0 end) as step_three_cnt
,sum(case when max_step = 4 then 1 else 0 end) as step_four_cnt
,sum(case when max_step = 5 then 1 else 0 end) as step_five_cnt
FROM
(select
s.location_id, 
s.member_id, 
max(s.step_no) as max_step
FROM steps S
group by 1,2
) as base
group by 1

对其进行细分，基本查询会给出以下结果：

member_id |  location_id | max_step_no
-------------------------------------------------------
| 1         | 10       | 5 
| 2         | 10       | 1
| 2         | 11       | 5

这个子查询（基础）上的聚合查询基本上是将结果转换为您希望看到的形式。这种方法的唯一限制是步骤数是在高级中静态定义的。

Answer 2

1-首先在临时表中选择以下内容：

select   location_id , step_no , count(member_id) as count 
into     stepsPrime
from     (select member_id, location_id, max(step_no) as step_no
          from steps 
          group by member_id, location_id ) definedTab 
group by location_id, step_no 
order by location_id;

2-使用以下查询来调整以上结果：

select distinct stp.location_id,
       stp1.count as step1, 
       stp2.count as step2, 
       stp3.count as step3, 
       stp4.count as step4, 
       stp5.count as step5 
from stepsPrime stp 
left join stepsPrime stp1 on stp.location_id = stp1.location_id and stp1.step_no = 1 
left join stepsPrime stp2 on stp.location_id = stp2.location_id and stp2.step_no = 2 
left join stepsPrime stp3 on stp.location_id = stp3.location_id and stp3.step_no = 3 
left join stepsPrime stp4 on stp.location_id = stp4.location_id and stp4.step_no = 4 
left join stepsPrime stp5 on stp.location_id = stp5.location_id and stp5.step_no = 5;