不包括自配对的组内的自联接将为每个组生成一个包含所有配对的表。将这个概念作为计算所有组中每个医生的不同“伴侣”的基础。
为了真正的独特性，请确保您使用的是 doctorId 对每个人都不同。试图阻止基于名字的“自我配对”是自找麻烦(设想一个虚构的群体，有杜威医生，杜威，杜威，杜威和杜威——是的，麻烦）

data have;
input doctor $ group $;
datalines;
A P1
E P1
C P2
B P2
E P2
A P3
D P3
E P3
E P5
A P5
;
run;
proc sql;
  * demonstrate the combinatoric effect of who (P) paired with whom (Q) within group;
  * do not submit against the big data;
  create table works_with_each as
  select 
    P.doctor as P
  , Q.doctor as Q
  , P.group 
  from have as P
  join have as Q
    on P.group = Q.group
     & P.doctor ^= Q.doctor
  order by 
   P.doctor, Q.doctor, P.group
  ; 
  * count the distinct pairing, regardless of group;
  create table works_with_counts as
  select 
    P.doctor as P
  , count(distinct Q.doctor) as unique_work_with_count
  from have as P
  join have as Q
    on P.group = Q.group
     & P.doctor ^= Q.doctor
  group by 
   P.doctor
  order by 
   P.doctor
  ;

每个

独特的其他成对（工作）计数

您可能需要用您的语言（尤其是 COUNT(DISTINCT var) )

SELECT docA , COUNT(DISTINCT docB) FROM 
    (SELECT A.doctor as docA, B.doctor as docB FROM mytable A JOIN mytable B
    ON A.group_practices = B.group_practices WHERE A.doctor > B.doctor)
GROUP BY docA

然后可以将此表与前面显示的表联接起来 on doctor = docA 这个 docA>docB 防止： A in relation with A ,
或者像这样的复制品 A in relation in B 以及 B in relation with A

您可以自行加入并聚合：

select t.doctor, count(distinct t1.doctor) no_coworkers
from mytable t
inner join mytable t1 on t1.doctor <> t.doctor and t1.group_practices = t.group_practices
group by t.doctor

在数据库中，可以使用窗口函数：

select t.*, count(*) over (partition by practice) as cnt
from t;

procsql不支持这个。相反，您可以在sas中使用一个称为“重新融合”的奇怪功能：

select t.*, count(*) as num_in_practice
from t
group by practice;

如果您对数据库使用本机格式，那么请使用window函数！