PostgreSQL合伙人New类型咨询去重统计：按年龄段计数

需求概述 #

从clients、relations、consultations三张表统计**'New'类型的合伙人咨询**，按客户咨询时的年龄段计数。核心规则：若一对合伙人均存在'New'咨询，仅统计一次，避免重复计数。

数据库表结构 #

CREATE TABLE clients (
  id INT NOT NULL,
  name VARCHAR NOT NULL,
  birthday DATE 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,
  "date" timestamp without time zone,
  PRIMARY KEY(id)
);

测试数据 #

INSERT INTO clients (id, name, birthday)
VALUES
(1, 'John Doe', '1979-05-01'),
(2, 'Jane Doe', '1980-06-14'),
(3, 'Fiddle Blaab', '1992-03-08'), 
(4, 'Agnes Blaab', '1993-04-08'),
(5, 'Kid Blaab', '2002-04-08');

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, "date")
VALUES
(1, 1, 'New', '2022-03-01 12:00'),
(2, 2, 'New', '2022-03-01 12:01'),
(3, 2, 'Follow Up', '2022-04-01 14:39'),
(4, 3, 'New', '2022-08-01 12:59'),
(5, 4, 'Follow Up', '2022-09-12 14:00');

当前问题 #

原查询将John Doe与Jane Doe这对合伙人的两次'New'咨询重复计数，导致41-60年龄段的统计结果为2，不符合仅统计一次的要求。

解决方案 #

通过生成唯一的合伙人组标识实现去重，最终SQL如下：

WITH new_consultations AS (
    SELECT 
        con.client_id,
        cl.birthday,
        con.date AS consult_date,
        -- 生成唯一合伙人组：用最小/最大ID避免重复组
        LEAST(r.from_client_id, r.to_client_id) AS partner_group_min,
        GREATEST(r.from_client_id, r.to_client_id) AS partner_group_max
    FROM consultations con
    JOIN clients cl ON con.client_id = cl.id
    JOIN relations r ON (r.from_client_id = con.client_id OR r.to_client_id = con.client_id)
    WHERE con.type_of = 'New'
      AND con.date BETWEEN '2022-01-01 00:00' AND '2022-12-31 23:59'
      AND r.relation_type = 'partner'
),
unique_partner_groups AS (
    -- 按合伙人组去重，每个组仅保留一条记录
    SELECT DISTINCT ON (partner_group_min, partner_group_max)
        partner_group_min,
        partner_group_max,
        consult_date,
        birthday
    FROM new_consultations
)
SELECT 
    CASE 
        WHEN date_part('year', AGE(consult_date, birthday)) BETWEEN 0 AND 17 THEN '0 - 18'
        WHEN date_part('year', AGE(consult_date, birthday)) BETWEEN 18 AND 25 THEN '18-25'
        WHEN date_part('year', AGE(consult_date, birthday)) BETWEEN 26 AND 40 THEN '26-40'
        WHEN date_part('year', AGE(consult_date, birthday)) BETWEEN 41 AND 60 THEN '41-60'
        WHEN date_part('year', AGE(consult_date, birthday)) BETWEEN 60 AND 200 THEN '60+'
    END AS "Age",
    COUNT(*) AS "Count"
FROM unique_partner_groups
GROUP BY "Age"
ORDER BY "Age";

逻辑说明 #

1. new_consultations CTE：筛选所有'New'咨询，关联客户生日与合伙人关系，通过LEAST/GREATEST将合伙人ID按顺序组合，生成唯一的组标识（避免因关联顺序导致的重复组）。
2. unique_partner_groups CTE：使用DISTINCT ON对合伙人组去重，每个组仅保留一条咨询记录（可根据需求调整保留规则，比如取最早的咨询）。
3. 最终统计：基于去重后的组计算年龄段并计数，得到符合要求的结果。

执行结果 #

Age   Count
26-40 1
41-60 1

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