PostgreSQL自引用关联查询：合作伙伴新咨询次数去重统计问题

需求说明 #

统计存在'New'类型咨询的伙伴关系数量，核心规则：若同一伙伴关系中的两位客户均有'New'咨询，该伙伴关系仅计数一次。

数据库表结构 #

CREATE TABLE clients (
  id INT NOT NULL,
  name VARCHAR NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE relations (
  id INT NOT NULL,
  from_client_id INT NOT NULL,
  to_client_id INT NOT NULL,
  relation_type VARCHAR NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE consultations (
  id INT NOT NULL,
  client_id INT NOT NULL,
  type_of VARCHAR NOT NULL,
  PRIMARY KEY(id)
);

测试数据 #

INSERT INTO clients (id, name)
VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Fiddle Blaab'), 
(4, 'Agnes Blaab'),
(5, 'Kid Blaab');

INSERT INTO relations (id, from_client_id, to_client_id, relation_type)
VALUES
(1, 1, 2, 'partner'),
(2, 3, 4, 'partner'),
(3, 3, 5, 'child'),
(4, 4, 5, 'child');

INSERT INTO consultations (id, client_id, type_of)
VALUES
(1, 1, 'New'),
(2, 2, 'New'),
(3, 2, 'Follow Up'),
(4, 3, 'New'),
(5, 4, 'Follow Up');

当前问题 #

现有查询返回结果为3，但期望结果是2：

SELECT 
count(*)
FROM consultations
INNER JOIN clients ON consultations.client_id = clients.id 
  AND "consultations"."type_of" = 'New'
INNER JOIN relations ON (relations.from_client_id = consultations.client_id OR relations.to_client_id = consultations.client_id)
  AND relations.relation_type = 'partner'

问题原因：John Doe和Jane Doe属于同一伙伴关系，且两人均有'New'咨询，当前查询将该关系分别计数两次，加上Fiddle Blaab与Agnes Blaab的关系，总计3次，不符合仅计数一次的要求。

解决方案 #

通过对伙伴关系ID去重，确保每个符合条件的伙伴关系只被计数一次：

WITH new_consult_clients AS (
    -- 先筛选出所有有'New'咨询的客户
    SELECT DISTINCT client_id 
    FROM consultations 
    WHERE type_of = 'New'
)
-- 统计关联到New咨询客户的伙伴关系，按关系ID去重计数
SELECT COUNT(DISTINCT r.id) AS partner_relation_count
FROM relations r
JOIN new_consult_clients nc 
  ON nc.client_id = r.from_client_id OR nc.client_id = r.to_client_id
WHERE r.relation_type = 'partner';

逻辑说明 #

1. 用CTE new_consult_clients 获取所有有'New'咨询的客户ID，去重避免同一客户多次咨询的干扰；
2. 关联伙伴关系表，筛选出与这些客户相关的伙伴关系；
3. 通过COUNT(DISTINCT r.id)确保每个伙伴关系仅被计数一次，无论关系中有几个客户有'New'咨询。

执行该查询后，返回结果为2，符合需求。

内容的提问来源于stack exchange，提问作者Cris