需要在Oracle中跨多层嵌套SQL查询查找平均值

Question

这是一个有点混乱，可能有一些更好的方法，但我们只是需要一些信息，一些报告，我们正在进行的工作。

因此，我们有许多项目；每个项目都有一组任务，每个任务都有一个与其关联的文档类型ID。项目可以属于一个或多个工作组。

我们希望分析至少有一个文档类型为x的任务的项目，然后查看它有多少工作组。我可以这样做：

   select distinct T.PROJECTID,
      (select COUNT(*) from TPM_PROJECTWORKGROUPS where PROJECTID=T.PROJECTID) as NumWorkgroups
   from TPM_TASK T
   where T.DOCUMENTTYPEID=17

现在，我们希望看到这些项目中工作组的平均数量。所以我可以：

select AVG(NumWorkgroups) FROM (
   select distinct T.PROJECTID,
      (select COUNT(*) from TPM_PROJECTWORKGROUPS where PROJECTID=T.PROJECTID) as NumWorkgroups
   from TPM_TASK T
   where T.DOCUMENTTYPEID=17
)

但是，我们希望在所有文档类型中运行相同的查询(大约有200个文档类型)。如果不复制和粘贴查询200次，我就无法找到这样的方法。我试过：

select DOCUMENTTYPEID, 
    (select AVG(NumWorkgroups) FROM (
       select distinct T.PROJECTID,
          (select COUNT(*) from TPM_PROJECTWORKGROUPS where PROJECTID=T.PROJECTID) as NumWorkgroups
       from TPM_TASK T
       where T.DOCUMENTTYPEID=DT.DOCUMENTTYPEID
    ))
from TPM_DOCUMENTTYPE DT

但是，我得到了错误：

ORA-00904: "TPM_DOCUMENTTYPE"."DOCUMENTTYPEID": invalid identifier

我相信，因为DT超出了嵌套查询中的多个级别。有更好的方法来执行这个查询吗？

贾斯汀的更新：

下面是一个示例模式：

create table Test_Projects (
  id   number primary key
)

create table Test_Tasks (
  id   number primary key,
  project number,
  doctype number
)

create table Test_Workgroups (
  id   number primary key,
  workgroup number,
  project number
)

通过一些样本数据：

insert into Test_Projects VALUES (1) --Create projects 1 and 2
insert into Test_Projects VALUES (2)
insert into Test_Tasks VALUES (1, 1, 5) --Project 1 has two tasks, doc types 5 and 6
insert into Test_Tasks VALUES (2, 1, 6)
insert into Test_Tasks VALUES (3, 2, 6) --Project 2 has one task, doc type 6
insert into Test_Workgroups VALUES (1, 1, 1) --Project 1 belongs to workgroups 1 and 2
insert into Test_Workgroups VALUES (2, 2, 1)
insert into Test_Workgroups VALUES (3, 2, 2) --Project 2 belongs to workgroup 2

我们需要知道具有x类型任务的项目所属的工作组的平均数量。

例如，doc类型5只有两个工作组的项目1，因此平均为2个。Doc 6有2个项目(1和2 )--1有2个工作组，2个有一个工作组--因此平均为1.5。

我们需要列出所有文档类型和每个工作组的平均数量。

我希望这个查询返回：

DOCTYPE     AverageWorkgroups
-------     -----------------
5           2
6           1.5

Answer 1

谢谢你提供的样本数据。这样就更清楚了。

我相信这可以实现您想要的结果(我还计算了输出中的项目数和工作组数，因为这使我的测试更容易)

SQL> ed
Wrote file afiedt.buf

  1  select t.doctype,
  2         count(distinct p.id) numProjects,
  3         count(*) numWorkgroups,
  4         count(*)/ count( distinct p.id) avgNumWorkgroups
  5    from test_projects p,
  6         test_tasks    t,
  7         test_workgroups w
  8   where p.id = t.project
  9     and p.id = w.project
 10*  group by t.doctype
SQL> /

   DOCTYPE NUMPROJECTS NUMWORKGROUPS AVGNUMWORKGROUPS
---------- ----------- ------------- ----------------
         6           2             3              1.5
         5           1             2                2