如何修改我的SQL语句，使每个Project都有(x)法官，并且每个法官在他们的人群中平均参与？

Question

问题

如何修改我的SQL语句，使每个项目有4个唯一的评委，每个评审员平均判断分配给他们的部门和类别的项目数量相同？

要求

人口项目需要由人口法官(x)次数审查。X=4

• 每个项目按部门和类别分组。
• 每名法官按司和职类分组。
• 每个项目必须由(x)独特的法官进行审查。
• 每位法官不应对同一项目进行两次审查。
• 应指派每一名法官与其分区和职类中的其他法官一样，审查同等数目的项目。

样本来源判断表

create table judges(judge_id, division, category) as (
    select 68, 'Elementary', 'ANY' from dual union all
    select 19, 'Elementary', 'ANY' from dual union all
    select 22, 'Elementary', 'ANY' from dual union all
    select 32, 'Elementary', 'ANY' from dual union all
    select 33, 'Elementary', 'ANY' from dual union all
    select 37, 'Elementary', 'ANY' from dual union all
    select 38, 'Elementary', 'ANY' from dual union all
    select 40, 'Elementary', 'ANY' from dual union all
    select 47, 'Elementary', 'ANY' from dual union all
    select 62, 'Elementary', 'ANY' from dual union all
    select 63, 'Elementary', 'ANY' from dual union all
    select 13, 'Elementary', 'ANY' from dual union all
    select 36, 'Elementary', 'ANY' from dual union all
    select 55, 'Elementary', 'ANY' from dual union all
    select 59, 'Elementary', 'ANY' from dual union all
    select 60, 'Elementary', 'ANY' from dual union all
    select 9, 'Junior', 'EC' from dual union all
    select 10, 'Junior', 'EC' from dual union all
    select 48, 'Junior', 'EC' from dual union all
    select 11, 'Junior', 'LS' from dual union all
    select 21, 'Junior', 'LS' from dual union all
    select 23, 'Junior', 'LS' from dual union all
    select 26, 'Junior', 'LS' from dual union all
    select 31, 'Junior', 'LS' from dual union all
    select 80, 'Junior', 'LS' from dual union all
    select 14, 'Junior', 'PE' from dual union all
    select 15, 'Junior', 'PE' from dual union all
    select 24, 'Junior', 'PE' from dual union all
    select 29, 'Junior', 'PE' from dual union all
    select 30, 'Junior', 'PE' from dual union all
    select 43, 'Junior', 'PE' from dual union all
    select 53, 'Junior', 'PE' from dual union all
    select 56, 'Junior', 'PE' from dual union all
    select 57, 'Junior', 'PE' from dual union all
    select 58, 'Junior', 'PE' from dual union all
    select 65, 'Junior', 'PE' from dual union all
    select 67, 'Junior', 'PE' from dual union all
    select 69, 'Junior', 'PE' from dual union all
    select 8, 'Senior', 'ANY' from dual union all
    select 18, 'Senior', 'ANY' from dual union all
    select 20, 'Senior', 'ANY' from dual union all
    select 35, 'Senior', 'ANY' from dual union all
    select 66, 'Senior', 'ANY' from dual union all
    select 39, 'Senior', 'ANY' from dual union all
    select 70, 'Senior', 'ANY' from dual union all
    select 71, 'Senior', 'ANY' from dual union all
    select 74, 'Senior', 'ANY' from dual union all
    select 6, 'Senior', 'ANY' from dual union all
    select 44, 'Senior', 'ANY' from dual union all
    select 45, 'Senior', 'ANY' from dual union all
    select 49, 'Senior', 'ANY' from dual union all
    select 61, 'Senior', 'ANY' from dual union all
    select 64, 'Senior', 'ANY' from dual union all
    select 72, 'Senior', 'ANY' from dual union all
    select 75, 'Senior', 'ANY' from dual union all
    select 79, 'Senior', 'ANY' from dual union all
    select 82, 'Senior', 'ANY' from dual
);

样本源项目表

create table projects(project_id, division, category) as (
     select 39, 'Elementary', 'ANY' from dual union all
    select 40, 'Elementary', 'ANY' from dual union all
    select 47, 'Elementary', 'ANY' from dual union all
    select 48, 'Elementary', 'ANY' from dual union all
    select 56, 'Elementary', 'ANY' from dual union all
    select 57, 'Elementary', 'ANY' from dual union all
    select 58, 'Elementary', 'ANY' from dual union all
    select 60, 'Elementary', 'ANY' from dual union all
    select 65, 'Elementary', 'ANY' from dual union all
    select 66, 'Elementary', 'ANY' from dual union all
    select 93, 'Elementary', 'ANY' from dual union all
    select 94, 'Elementary', 'ANY' from dual union all
    select 97, 'Elementary', 'ANY' from dual union all
    select 104, 'Elementary', 'ANY' from dual union all
    select 105, 'Elementary', 'ANY' from dual union all
    select 107, 'Elementary', 'ANY' from dual union all
    select 110, 'Elementary', 'ANY' from dual union all
    select 112, 'Elementary', 'ANY' from dual union all
    select 114, 'Elementary', 'ANY' from dual union all
    select 117, 'Elementary', 'ANY' from dual union all
    select 120, 'Elementary', 'ANY' from dual union all
    select 121, 'Elementary', 'ANY' from dual union all
    select 123, 'Elementary', 'ANY' from dual union all
    select 124, 'Elementary', 'ANY' from dual union all
    select 126, 'Elementary', 'ANY' from dual union all
    select 127, 'Elementary', 'ANY' from dual union all
    select 128, 'Elementary', 'ANY' from dual union all
    select 133, 'Elementary', 'ANY' from dual union all
    select 134, 'Elementary', 'ANY' from dual union all
    select 136, 'Elementary', 'ANY' from dual union all
    select 140, 'Elementary', 'ANY' from dual union all
    select 142, 'Elementary', 'ANY' from dual union all
    select 152, 'Elementary', 'ANY' from dual union all
    select 153, 'Elementary', 'ANY' from dual union all
    select 162, 'Elementary', 'ANY' from dual union all
    select 167, 'Elementary', 'ANY' from dual union all
    select 173, 'Elementary', 'ANY' from dual union all
    select 174, 'Elementary', 'ANY' from dual union all
    select 188, 'Elementary', 'ANY' from dual union all
    select 27, 'Junior', 'EC' from dual union all
    select 67, 'Junior', 'EC' from dual union all
    select 82, 'Junior', 'EC' from dual union all
    select 83, 'Junior', 'EC' from dual union all
    select 99, 'Junior', 'EC' from dual union all
    select 113, 'Junior', 'EC' from dual union all
    select 115, 'Junior', 'EC' from dual union all
    select 149, 'Junior', 'EC' from dual union all
    select 164, 'Junior', 'EC' from dual union all
    select 169, 'Junior', 'EC' from dual union all
    select 185, 'Junior', 'EC' from dual union all
    select 189, 'Junior', 'EC' from dual union all
    select 18, 'Junior', 'LS' from dual union all
    select 19, 'Junior', 'LS' from dual union all
    select 20, 'Junior', 'LS' from dual union all
    select 21, 'Junior', 'LS' from dual union all
    select 23, 'Junior', 'LS' from dual union all
    select 24, 'Junior', 'LS' from dual union all
    select 26, 'Junior', 'LS' from dual union all
    select 28, 'Junior', 'LS' from dual union all
    select 29, 'Junior', 'LS' from dual union all
    select 30, 'Junior', 'LS' from dual union all
    select 78, 'Junior', 'LS' from dual union all
    select 80, 'Junior', 'LS' from dual union all
    select 90, 'Junior', 'LS' from dual union all
    select 91, 'Junior', 'LS' from dual union all
    select 106, 'Junior', 'LS' from dual union all
    select 144, 'Junior', 'LS' from dual union all
    select 150, 'Junior', 'LS' from dual union all
    select 151, 'Junior', 'LS' from dual union all
    select 156, 'Junior', 'LS' from dual union all
    select 157, 'Junior', 'LS' from dual union all
    select 159, 'Junior', 'LS' from dual union all
    select 161, 'Junior', 'LS' from dual union all
    select 165, 'Junior', 'LS' from dual union all
    select 166, 'Junior', 'LS' from dual union all
    select 168, 'Junior', 'LS' from dual union all
    select 178, 'Junior', 'LS' from dual union all
    select 180, 'Junior', 'LS' from dual union all
    select 187, 'Junior', 'LS' from dual union all
    select 22, 'Junior', 'PE' from dual union all
    select 59, 'Junior', 'PE' from dual union all
    select 61, 'Junior', 'PE' from dual union all
    select 74, 'Junior', 'PE' from dual union all
    select 81, 'Junior', 'PE' from dual union all
    select 95, 'Junior', 'PE' from dual union all
    select 96, 'Junior', 'PE' from dual union all
    select 116, 'Junior', 'PE' from dual union all
    select 145, 'Junior', 'PE' from dual union all
    select 148, 'Junior', 'PE' from dual union all
    select 158, 'Junior', 'PE' from dual union all
    select 160, 'Junior', 'PE' from dual union all
    select 172, 'Junior', 'PE' from dual union all
    select 176, 'Junior', 'PE' from dual union all
    select 181, 'Junior', 'PE' from dual union all
    select 186, 'Junior', 'PE' from dual union all
    select 14, 'Senior', 'ANY' from dual union all
    select 15, 'Senior', 'ANY' from dual union all
    select 42, 'Senior', 'ANY' from dual union all
    select 43, 'Senior', 'ANY' from dual union all
    select 44, 'Senior', 'ANY' from dual union all
    select 45, 'Senior', 'ANY' from dual union all
    select 46, 'Senior', 'ANY' from dual union all
    select 49, 'Senior', 'ANY' from dual union all
    select 51, 'Senior', 'ANY' from dual union all
    select 52, 'Senior', 'ANY' from dual union all
    select 53, 'Senior', 'ANY' from dual union all
    select 54, 'Senior', 'ANY' from dual union all
    select 75, 'Senior', 'ANY' from dual union all
    select 76, 'Senior', 'ANY' from dual union all
    select 77, 'Senior', 'ANY' from dual union all
    select 92, 'Senior', 'ANY' from dual union all
    select 108, 'Senior', 'ANY' from dual union all
    select 118, 'Senior', 'ANY' from dual union all
    select 119, 'Senior', 'ANY' from dual union all
    select 125, 'Senior', 'ANY' from dual union all
    select 131, 'Senior', 'ANY' from dual union all
    select 132, 'Senior', 'ANY' from dual union all
    select 135, 'Senior', 'ANY' from dual union all
    select 137, 'Senior', 'ANY' from dual union all
    select 138, 'Senior', 'ANY' from dual union all
    select 139, 'Senior', 'ANY' from dual union all
    select 141, 'Senior', 'ANY' from dual union all
    select 143, 'Senior', 'ANY' from dual union all
    select 146, 'Senior', 'ANY' from dual union all
    select 154, 'Senior', 'ANY' from dual union all
    select 171, 'Senior', 'ANY' from dual
);

Oracle SQL代码

select 
     project_id
    ,division
    ,category
    ,judge_id
    ,best_match
from(
    select 
         project.project_id
        ,project.division
        ,project.category
        ,judge.judge_id
        ,row_number() over(
            partition by project.project_id 
            order by dbms_random.value
        ) as best_match
    from frsf_fair.project_division_category project
    left join (
        select 
            judge_id
            ,division
            ,category
            ,row_number() over(
                partition by division 
                order by dbms_random.value
            ) as r
        from frsf_fair.judge_division_category
        ) judge on project.division = judge.division 
            and project.category = judge.category   
) assignment
where best_match <= 4

数据输出示例

PROJECT_ID  DIVISION    CATEGORY    JUDGE_ID    BEST_MATCH
14  Senior  ANY 72  1
14  Senior  ANY 74  2
14  Senior  ANY 20  3
14  Senior  ANY 44  4

15  Senior  ANY 79  1
15  Senior  ANY 39  2
15  Senior  ANY 75  3
15  Senior  ANY 20  4

18  Junior  LS  23  1
18  Junior  LS  31  2
18  Junior  LS  26  3
18  Junior  LS  21  4

问题

计数(*)/projects的数量应该在法官池的分区和类别中平均分配。相反，一些法官被指派到20个项目，而另一些则被指派到极少数项目。我不知道如何修改我的查询，所以每个评审员必须在项目分区和类别分组中引用相同的次数。我要确保平等参与。

JUDGE_ID    COUNT(*)
64  8
72  16
23  20
57  4
14  4
6   4
58  9

Answer 1

我想出了一个利用Oracle数据库中的过程的解决方案。

我的方法是循环遍历每个项目id。在我的循环中，它会循环1到我所需要的分配数，在本例中是4。

1. 我们需要从评委名单中抽取一个随机的法官身份，计算他们已经按升序分配了多少任务。我想先用最低数量的作业。
2. 如果碰到+1，他们的赋值就会计算。
3. 我们更新最终列表表中的值。
4. 我们循环，直到按项目分配的数量，然后评估下一个项目。 创建或替换过程random_judge_project_list为 assign_count数:= 0；next_count数:= 1；赋值编号:= 4；jid号；sql_stmt varchar2(4 000)；位置varchar2(20)； 开始 立即执行‘截断表判断_项目_列表’；立即执行'update judge_assignment_count set assignment_count = 0'；/*We首先需要循环每个项目*/在(选择project_id，除法，类别作为source_category，案件划分时，‘初级’，然后分类任何‘结束为类别从项目的顺序，asc )循环/*While循环，然后我们需要分配法官随机*/为分配1 .赋值循环选择judge_id到jid from(选择*来自judge_assignment_count其中的除法= project.division和judge_id not in (从judge_project_list选择judge_id project_id = project.project_id)顺序由assignment_count asc，( dbms_random.random asc )其中行数=1；从assignment_count = jid和rownum = 1；next_count := assign_count +1；-dbms_output.put_line( sql_stmt )中选择judge_assignment_count中的judge_id _output.put_line(Sql_stmt)；插入到judge_project_list (project_id，judge_id，除法，类别)值(project.project_id，jid，project.division，project.category)中；立即执行'update judge_assignment_count set assignment_count =‘end judge_assignment_count set assignment_count’，其中judge_id =‘{##**$}}’\_\、\ 结束；

Answer 2

我能做的最好的就是这个查询：

with 
  t as (select judge_id jid, project_id pid, category cat, division div, 
               dense_rank() over (order by project_id) rp,
               dense_rank() over (order by judge_id) rj,
               count(1) over (partition by project_id) cnt
          from judges join projects using (division, category)),
  c(pid, jid, cat, div, rn, rp) as (
    select pid, jid, cat, div, 1, rp from t where mod(rp, cnt) = mod(rj, cnt)
    union all
    select c.pid, t.jid, c.cat, c.div, rn + 1, c.rp
      from c join t on c.rp = t.rp and c.jid <> t.jid 
                   and mod(t.rp, t.cnt) = mod(t.rj + c.rn, t.cnt) 
      where rn < 3)
select * from c 

它能做什么？在第一次基本联接添加行号和计数(rp，rj，cnt)。然后，mod()使用这些列连接锚查询where mod(rp, cnt) = mod(rj, cnt)中的行。然后是递归部分，它只为每个项目增加了所需的评审数。也许有人不需要递归就能做这件事，但我不能。通过将随机的id分配给判断器，并在查询中使用它们而不是实际的id，您可以在某种程度上随机化输出。用所需的评审数替换3。

这个[医]小提琴显示了5个项目，5个评委，每个项目3次评审。法官不重复，分配平等。

编辑：这是更正的基本查询：

with t as ( 
    select category, division, project_id, judge_id,
           dense_rank() over (partition by category, division 
                              order by project_id) rp,
           dense_rank() over (partition by category, division, project_id 
                              order by judge_id) rj,
           count(1) over (partition by project_id) cnt
      from projects join judges using (category, division) ) 
select * from t
  join (select level lvl from dual connect by level <= 3) 
    on mod(rj, cnt) =  mod((rp -1) * 3 + lvl, cnt) 

我检查了项目的结果(由project_id组成)，每个项目都有3个不同的评审员。我检查了法官(按类别、部门、judge_id)的结果，他们班上的每一位法官都有相同数量的案件(如果没有足够的项目进行同等分配，则相差一个)。

逻辑与第一个查询中的逻辑相同。正如我说的，一开始你可以随机选择法官：

select row_number() over (order by dbms_random.value) rnd_jdg_id, judges.* from judges

并利用这个伪id作为主查询的输入，得到完全随机的赋值。