用DuckDB 单个SQL或Python过程语句实现员工自动分配住房问题 - l1t1/note GitHub Wiki

有人在itpub上提问

create table t_officer3(姓名 varchar(10),部门 varchar(10), 性别 varchar(10));

insert into t_officer3 values ('user1','部门1','男'),('user2','部门1','女'),('user3','部门2','男'),('user4','部门2','男'),('user5','部门2','男'),('user6','部门3','女'),('user7','部门3','男'),('user8','部门3','男'),('user9','部门4','女'),('user10','部门4','女'),('user11','部门5','男'),('user12','部门6','女'),('user13','部门6','男'),('user14','部门7','女'),('user15','部门7','男'),('user16','部门8','男'),('user17','部门8','女'),('user18','部门8','女'),('user19','部门8','女'),('user20','部门8','女'),('user21','部门8','女'),('user22','部门9','男'),('user23','部门10','女'),('user24','部门10','女'),('user25','部门10','女'),('user26','部门11','男'),('user27','部门11','女'),('user28','部门11','男'),('user29','部门11','男'),('user30','部门11','女'),('user31','部门11','男'),('user32','部门11','男'),('user33','部门11','女'),('user34','部门11','女'),('user35','部门12','女'),('user36','部门12','女'),('user37','部门12','男'),('user38','部门12','男'),('user39','部门12','男'),('user40','部门12','男'),('user41','部门12','男'),('user42','部门13','男'),('user43','部门13','女'),('user44','部门13','女'),('user45','部门13','男'),('user46','部门13','男'),('user47','部门13','男'),('user48','部门13','女'),('user49','部门13','男'),('user50','部门13','男'),('user51','部门13','男'),('user52','部门13','男'),('user53','部门13','男'),('user54','部门13','男'),('user55','部门14','男'),('user56','部门14','男'),('user57','部门15','男'),('user58','部门16','男'),('user59','部门17','女'),('user60','部门18','女'),('user61','部门19','女'),('user62','部门19','男'),('user63','部门20','女'),('user64','部门21','女'),('user65','部门21','女'),('user66','部门21','女');

需要将这些人随机分配到酒店入住，每间房住2人；

但必须满足如下要求，才能分配到一起：

  • 性别不同，不能住一起

  • 性别相同，相同部门名称的人不能住一间房，除非特殊情况，某个部门的男性或女性查过公司其他部门男性或女性之和，才可以住一间房

说是试了好多提示词给deepseek和kimi，都不能完成这个sql或plsql。

newkid提出了一个思路：

首先把同性别的归到一起，男性分完再用同样方法分配女性。
按照部门总人数（某一性别人数）排序，形成一个有序队列，从第一个部门和最后一个部门各取一人搭配。
这一步操作之后人数会发生变化，人数最多的部门可能会变。然后重复上述步骤直到结束。
这个没办法用SQL, PLSQL应该可以。

Oracle数据库的递归限制不能用子查询，确实不好实现，不过duckdb可以。

以下是我的实现, 如果要两个性别的结果，就把两个相同的SQL分别计算男女，然后用union all拼起来。

1.sql版本1，房间号通过第二次递归生成，这个程序的效率很差，30个人要算半秒。order by cpt, 部门是为了保证部门人数相同时首尾尽量不是一个部门。

with recursive t as
(select 1 lv ,姓名,部门,cpt,cnt,row_number()over(order by cpt, 部门) rn from
     (select 姓名,部门,count(*)over(partition by 部门)cpt ,count(*)over()cnt from  t_officer3 where 性别<>'男' )a
union all
select lv+1,姓名,部门,cpt,cnt,row_number()over(order by cpt) rn from
      (select lv,姓名,部门,count(*)over(partition by 部门)cpt ,count(*)over()cnt from  t where rn<>1 and rn<>cnt)
)
,r as(select (select max(lv) from t) rlv, 姓名 入住姓名,部门 入住部门 from t where lv=rlv
union
   select rlv -1, a.* from (select 姓名,部门 from t,r where lv=rlv-1
     except
   select 姓名,部门 from t,r where lv=rlv  )a,r
)
select * from r where 入住姓名 is not null order by rlv;

2.sql版本2, 房间号通过 count(*) 技巧生成，只要0.02秒。

with recursive t as
(select 1 lv ,姓名,部门,cpt,cnt,row_number()over(order by cpt, 部门) rn from
     (select 姓名,部门,count(*)over(partition by 部门)cpt ,count(*)over()cnt from  t_officer3 where 性别<>'男' )a
union all
select lv+1,姓名,部门,cpt,cnt,row_number()over(order by cpt) rn from
      (select lv,姓名,部门,count(*)over(partition by 部门)cpt ,count(*)over()cnt from  t where rn<>1 and rn<>cnt)
)
select 姓名,部门, count(*)room from t group by 姓名,部门 order by room;

3.sql版本3, 房间号在递归步骤生成，最后取最后生成的版本，和count(*)一样快。这个思路是Qwen 3想到的，但他实现错了。

with recursive t as
(select 1 lv ,1 room ,姓名,部门,cpt,cnt,row_number()over(order by cpt, 部门) rn from
     (select 姓名,部门,count(*)over(partition by 部门)cpt ,count(*)over()cnt from  t_officer3 where 性别<>'男' )a
union all
select lv+1,room+1,姓名,部门,cpt,cnt,row_number()over(order by cpt) rn from
      (select lv,room,姓名,部门,count(*)over(partition by 部门)cpt ,count(*)over()cnt from  t where rn<>1 and rn<>cnt)
)select 姓名,部门, max(room) room from t group by 姓名,部门 order by room;

4.python版本, 将员工按部门分组存储到列表，然后用python过滤、排序、切片，最简明。

def assign_room(a):
        #print(a)
        b=a
        rn=1
        while True:
                b=list(filter(lambda x:len(x)>0,b))
                if len(b)==0:break
                b=sorted(b,key=lambda x:len(x))
                print(rn, b[0][0], b[-1][-1])
                rn+=1
                b[0]=b[0][1:]
                b[-1]=b[-1][:-1]

import duckdb
con = duckdb.connect("room.db")
x=con.sql(" select array_agg(部门||':'||姓名) a from  t_officer3 where 性别<>'男' group by 部门").fetchall()
a=[i[0] for i in x]
assign_room(a)
⚠️ **GitHub.com Fallback** ⚠️