我是新手，试图找到总结数据的最佳方法。甲骨文 19c。
我有一个 WORKORDER 表，其中包含工作订单 (ISTASK=0) 和任务 (ISTASK=1)。
- 任务是工单的子级。
- 工作订单和任务按 WOGROUP 分组。
成本分为四列：
actlabcost（实际人工成本）actmatcost（实际材料成本）acttoolcost（实际工具成本）actservcost（实际服务成本）
成本列不可为空。所以我们不需要担心将空值转换为零以避免对空值进行数学运算。
Select 'WO1361' as WONUM, 'WO1361' as WOGROUP, 0 as ISTASK, 0 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167457977' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1362' as WONUM, 'WO1362' as WOGROUP, 0 as ISTASK, 0 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167458280' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1363' as WONUM, 'WO1363' as WOGROUP, 0 as ISTASK, 270.14 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167483430' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1367' as WONUM, 'WO1363' as WOGROUP, 1 as ISTASK, 540.27 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167482806' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1370' as WONUM, 'WO1363' as WOGROUP, 1 as ISTASK, 202.6 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167483431' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1364' as WONUM, 'WO1364' as WOGROUP, 0 as ISTASK, 88.86 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167459454' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1366' as WONUM, 'WO1364' as WOGROUP, 1 as ISTASK, 33.77 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167458946' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1365' as WONUM, 'WO1365' as WOGROUP, 0 as ISTASK, 67.53 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167459331' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1368' as WONUM, 'WO1368' as WOGROUP, 0 as ISTASK, 236.37 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167461627' as OTHER_WO_COLUMNS FROM DUAL
WONUM WOGROUP ISTASK ACTLABCOST ACTMATCOST ACTTOOLCOST ACTSERVCOST OTHER_WO_COLUMNS
------ ------- ---------- ---------- ---------- ----------- ----------- ----------------
WO1361 WO1361 0 0 0 0 0 167457977
WO1362 WO1362 0 0 0 0 0 167458280
WO1363 WO1363 0 270.14 0 0 0 167483430
WO1367 WO1363 1 540.27 0 0 0 167482806
WO1370 WO1363 1 202.6 0 0 0 167483431
WO1364 WO1364 0 88.86 0 0 0 167459454
WO1366 WO1364 1 33.77 0 0 0 167458946
WO1365 WO1365 0 67.53 0 0 0 167459331
WO1368 WO1368 0 236.37 0 0 0 167461627
Notice rows 3-5 are in WOGROUP #WO1363. And rows 6-7 are in WOGROUP #WO1364.
问题：
我想按 WOGROUP 汇总工单成本（包括任务成本），但我不想在结果集中显示任务行。换句话说，我想将任务成本汇总到他们的父工单。
对于工单行，我还想包括未分组的其他列（即 OTHER_WO_COLUMNS）。
我找到了几种方法。
选项 #1：（GROUP BY、JOIN 和 SUM）
该查询在子查询中执行 GROUP BY (SUM) 以获取总工单成本。然后它选择工作订单（不包括任务）并加入子查询以引入总成本。
--The suffix "_ti" stands for "tasks included".
select
a.wonum,
a.istask,
b.actlabcost_ti,
b.actmatcost_ti,
b.actservcost_ti,
b.acttoolcost_ti,
b.acttotalcost_ti,
other_wo_columns
from
cte a
left join
(
select
wogroup as wonum,
sum(actlabcost) as actlabcost_ti,
sum(actmatcost) as actmatcost_ti,
sum(actservcost) as actservcost_ti,
sum(acttoolcost) as acttoolcost_ti,
sum(actlabcost + actmatcost + actservcost + acttoolcost) as acttotalcost_ti
from
cte
group by
wogroup
) b
on a.wonum = b.wonum
where
istask = 0
WONUM ISTASK ACTLABCOST_TI ACTMATCOST_TI ACTSERVCOST_TI ACTTOOLCOST_TI ACTTOTALCOST_TI OTHER_WO_COLUMNS
------ ---------- ------------- ------------- -------------- -------------- --------------- ----------------
WO1361 0 0 0 0 0 0 167457977
WO1362 0 0 0 0 0 0 167458280
WO1363 0 1013.01 0 0 0 1013.01 167483430
WO1364 0 122.63 0 0 0 122.63 167459454
WO1365 0 67.53 0 0 0 67.53 167459331
WO1368 0 236.37 0 0 0 236.37 167461627
我在一个完整的生产表（WORKORDER 表有 4,500 行）上运行了查询并得到了这个解释计划：
Plan hash value: 1879239811
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9841 | 586K| 622 (2)| 00:00:01 |
| 1 | SORT GROUP BY | | 9841 | 586K| 622 (2)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 9841 | 586K| 620 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| WORKORDER | 4609 | 184K| 310 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| WORKORDER | 9841 | 192K| 310 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."WONUM"="WOGROUP"(+))
3 - filter("A"."ISTASK"=0)
估计费用为622。
选项 #2：（SUM 分析函数）
我找到了一种使用 SUM分析函数的方法。我将 SUM 分析函数查询包装在隐藏任务行的外部查询中。
--The suffix "_ti" stands for "tasks included".
select
wonum,
istask,
actlabcost_ti,
actmatcost_ti,
acttoolcost_ti,
actservcost_ti,
acttotalcost_ti,
other_wo_columns
from
(
select
wogroup as wonum,
istask,
sum(actlabcost ) over (partition by wogroup) as actlabcost_ti,
sum(actmatcost ) over (partition by wogroup) as actmatcost_ti,
sum(acttoolcost) over (partition by wogroup) as acttoolcost_ti,
sum(actservcost) over (partition by wogroup) as actservcost_ti,
sum(actlabcost + actmatcost + acttoolcost + actservcost) over (partition by wogroup) as acttotalcost_ti,
other_wo_columns
from
cte
)
where
istask=0
WONUM ISTASK ACTLABCOST_TI ACTMATCOST_TI ACTTOOLCOST_TI ACTSERVCOST_TI ACTTOTALCOST_TI OTHER_WO_COLUMNS
------ ---------- ------------- ------------- -------------- -------------- --------------- ----------------
WO1361 0 0 0 0 0 0 167457977
WO1362 0 0 0 0 0 0 167458280
WO1363 0 1013.01 0 0 0 1013.01 167483430
WO1364 0 122.63 0 0 0 122.63 167459454
WO1365 0 67.53 0 0 0 67.53 167459331
WO1368 0 236.37 0 0 0 236.37 167461627
我也在生产中运行了这个查询，并得到了这个解释计划：
Plan hash value: 2003557620
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9841 | 1749K| 312 (2)| 00:00:01 |
|* 1 | VIEW | | 9841 | 1749K| 312 (2)| 00:00:01 |
| 2 | WINDOW SORT | | 9841 | 394K| 312 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| WORKORDER | 9841 | 394K| 310 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ISTASK"=0)
估计成本为312，大约是第一次查询的一半。
我认为它更快，因为它只进行一次全表扫描（另一个查询进行了两次全扫描）。
问题：
总结这些数据的最佳/最快方法是什么？
如前所述，我注意到选项#2 比#1 快。但老实说，#2 对我来说似乎有点倒退。我在 Stack Overflow 上对这种方法提出了一些非常严厉的批评，所以我猜这不是构建查询的好方法。
我的明确偏好是选项#2。恕我直言，这很直接。您对每个分区进行聚合，然后只保留您感兴趣的行。由于没有框架子句 (
BETWEEN) 的窗口函数的计算得到了很好的优化，并不是您做了很多事然后就扔掉了。尽管如此，还是有一些关于选项 #1 的反馈：
你为什么采取
left join（不是内部连接？）other_wo_columns您可以使用选择性聚合来代替自连接：MAX(CASE WHEN istask = 0 THEN other_wo_column END)如果每组只有一行
istask = 0，它将只返回该行的值。我使用max它是因为它适用于所有数据类型——比如min——但不是sum.当然，如果你有很多列，代码会变得有点混乱。
这很可能是最快的方法，但与#2 相比可能差距不大。
有关此技术的更多信息：
https://modern-sql.com/feature/filter
https://modern-sql.com/use-case/pivot
最后，关于 COST
不要使用COST作为衡量不同查询方法的指标。COST 值用于确定在具有相同数据的相同数据库中的相同查询的各种可能性中的最佳可能执行计划。COST 值的计算针对此特定目的进行了高度优化——即，出于性能原因，跳过了服务于此特定目的所需的所有内容。因此，使用不同的东西很有可能给出错误的结果。
它只是不用于比较不同的查询。
跟进@Markus Winand 的回答：
我接受了这些建议并制作了新版本的 Query #1：
查询 1b：（选择性聚合）
我知道 Markus 建议我们不应该比较查询之间的成本。但无论如何我都会这样做，因为我觉得它很有帮助。
我在一个完整的生产表（WORKORDER 表有 4,500 行）上运行了查询并得到了这个解释计划：
估计成本为312，与第二个查询相同。所以查询#1b 和查询#2 是并列的。
查询 #1b 很快，因为它只进行一次全表扫描。与执行两次全表扫描的查询 1(a) 不同。