Oracle-如何检查多个记录中的值，以及是否有一个值匹配-将其用于更大的数据集

Question

这是我的数据：

Step   random_task     Status         date
1        do_this      Completed       1-Nov-2020
1        do-that      Cancelled       2-Nov-2020
2        do_this     Not Assigned     1-Nov-2020
2        do_that      Cancelled       2-Nov-2020
2        and_that     Cancelled       2-Nov-2020
3        do_this      Cancelled       2-Nov-2020
3        do_that      Cancelled       2-Nov-2020
3        also_that    Cancelled       2-Nov-2020
4        do_that      In Progress or Scheduled or whatever   1-Nov-2020
4        and_that     Completed       2-Nov-2020

按步骤分组：

Completed=如果步骤中的所有任务都具有APS_Status_c="Completed"。

Completed=如果至少有1个任务是"已完成"；已完成"；并且所有剩余任务＝"0"；取消"；。

未开始=如果至少有一个任务是"未分配"，并且所有剩余任务="；取消"；。

未启动=如果步骤中的所有任务都具有APS_Status_c="未分配"。

Cancelled=如果步骤中的所有任务都具有APS_Status_c="Cancelled"。

进行中=如果至少有一个任务<gt"已完成"；或"；取消"；或"；未分配"；。

输出：

Step       Status            date 
1          Completed         2-Nov-2020 (if status = completed, then max(date) of that step
2          Not Started      (null)
3          Cancelled        (null)
4          In Progress      (null)

我尝试过将赋值添加到100、-100&0，求和，然后提取mod(sum，100(以确定正确的值。但我无法指定该步骤的最长(日期(。

Answer 1

您可以使用条件聚合作为：

select step,
(case when min(status) = max(status) and min(status) in ('Completed', 'Cancelled')
then min(status)
when sum(case when status in ('Completed', 'Cancelled') then 1 else 0 end) = count(*)
then 'Completed'
when sum(case when status in ('Not Assigned', 'Cancelled') then 1 else 0 end) = count(*)
then 'Not Started'
then ''
else 'In Progress'
end) as status,
(case when min(status) = max(status) and min(status) in 'Completed'
then max(date)
end)
from t
group by step;

这些稍微简化了您的逻辑。特别地；已完成"；以及"；取消"；在逻辑中首先处理——如果所有状态都相同。然后一个步骤是"；未启动"；如果所有状态都是"0"；未分配"；或"；取消"；。

Answer 2

您可以使用GROUP BY和条件聚合，如下所示：

SELECT STEP, STATUS, CASE WHEN STATUS = 'Completed' THEN DATE END AS DATE FROM
(SELECT STEP,
DATE,
CASE WHEN COMPLETED = TOTAL THEN 'Completed' 
WHEN (COMPLETED >= 1 AND CANCELLED = TOTAL - COMPLETED) 
OR (NOTASSIGNED >= 1 AND CANCELLED = TOTAL - NOTASSIGNED) THEN 'Cancelled'
WHEN NOTASSIGNED = TOTAL THEN 'Not Assigned'
WHEN CANCELLED = TOTAL THEN 'Cancelled'
END AS STATUS
FROM
(SELECT STEP,
COUNT(CASE WHEN STATUS = 'Completed' THEN 1 END) AS COMPLETED,
COUNT(CASE WHEN STATUS = 'Cancelled' THEN 1 END) AS CANCELLED,
COUNT(CASE WHEN STATUS = 'Not Assigned' THEN 1 END) AS NOTASSIGNED,
COUNT(CASE WHEN STATUS = 'In Progress' THEN 1 END) AS INPROGRESS,
COUNT(1) AS TOTAL,
MAX(DATE) AS DATE
FROM YOUR_TABLE
GROUP BY STEP))

我不确定进展中的情况，所以请你自己处理。