如何用SQL将患者均等分配至所在PIN码区域的对应医生？

现有数据表 #

表1：members（患者表） #

• ID：患者唯一标识
• PIN：患者所在区域的PIN码

建表及插入数据SQL：

create table members (id varchar(255), pin varchar (255));
insert into members (id,pin)
values ('A','11'),('B','11'),('C','11'),('D','11'),('E','11'),('F','11'),('G','11'),('H','11'),('I','11'),('J','11'),('K','11'),
('L','99'),('M','99'),('N','99');

表2：doctors（医生表） #

• PIN：医生所在区域的PIN码
• NPI：医生唯一标识

建表及插入数据SQL：

create table doctors (pin varchar(255), npi varchar (255));
insert into doctors(pin,npi)
values ('11','123'),('11','456'),('11','789'),('99','987'),('99','654');

需求说明 #

按PIN码将同区域患者均等分配给该区域医生，例如PIN'11'下有11名患者、3名医生，分配结果应为4、4、3名患者。

SQL解决方案 #

通过窗口函数计算分组序号，关联医生表实现均等分配：

WITH member_ranked AS (
    SELECT 
        id,
        pin,
        ROW_NUMBER() OVER (PARTITION BY pin ORDER BY id) AS rn
    FROM members
),
doctor_count AS (
    SELECT 
        pin,
        COUNT(npi) AS doc_num
    FROM doctors
    GROUP BY pin
),
doctor_ranked AS (
    SELECT 
        pin,
        npi,
        ROW_NUMBER() OVER (PARTITION BY pin ORDER BY npi) AS doc_rn
    FROM doctors
)
SELECT 
    mr.id AS patient_id,
    mr.pin,
    dr.npi AS doctor_npi
FROM member_ranked mr
JOIN doctor_count dc ON mr.pin = dc.pin
JOIN doctor_ranked dr 
    ON mr.pin = dr.pin
    ON (mr.rn - 1) % dc.doc_num + 1 = dr.doc_rn
ORDER BY mr.pin, dr.npi, mr.id;

逻辑说明 #

1. member_ranked：给每个PIN下的患者按ID排序，生成连续序号rn。
2. doctor_count：统计每个PIN对应的医生总数doc_num。
3. doctor_ranked：给每个PIN下的医生按NPI排序，生成医生序号doc_rn。
4. 通过(rn-1) % doc_num +1计算患者匹配的医生序号，让患者均匀分配到各医生——余数为0的患者分配给最后一名医生，其余按余数对应医生序号，保证分组数量尽可能平均。

结果示例 #

• PIN'11'区域：
  • 医生123：患者A、D、G、J（4名）
  • 医生456：患者B、E、H、K（4名）
  • 医生789：患者C、F、I（3名）
• PIN'99'区域：
  • 医生987：患者L、N（2名）
  • 医生654：患者M（1名）

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