将一组行状态合并为单个汇总状态

Question

我有一个看起来像这样的视图：

package_id  package_line  status_a        status_b
1           1             NOT_STARTED     FAILED
1           2             STARTED         STARTED
1           3             FAILED          NOT_STARTED
2           1             COMPLETE        STARTED
2           2             COMPLETE        NOT_STARTED

依此类推，几千行。 Status_a 和 status_b 是单独的不相关状态（实际上它们是在系统 A 上执行的包还是在系统 B 上执行的包）。

我需要按package_id汇总的摘要，以汇总到

   package_id status_a  status_b
   1          FAILED    FAILED
   2          COMPLETE  NOT_STARTED

组合包装线的规则是

 1. If any package line has failed, the package has failed.
 2. If all package lines are complete, the package is complete.
 3. If all package lines are NOT_STARTED, the package has not started.
 4. Otherwise, the package is started (i.e. a combination of started, not_started, and complete would summarize as STARTED).

所以我正在尝试提出一个查询来创建这个摘要。 我很想发布一个代码示例，但我真的很不知所措。 大概我想GROUP_BY package_id，但是我可以将什么用作聚合函数来应用列出的规则？

如果有帮助，我确实可以控制如何在包裹行级别报告状态，因此我可以报告数字状态（尽管我喜欢让它为理智而人类可读）。

Answer 1

试试这个（status_b也一样）：

SELECT package_id,
       CASE WHEN FAILED_COUNT > 0 THEN 'FAILED'
            WHEN COMPLETE_COUNT = ALL_COUNT THEN 'COMPLETE'
            WHEN NOT_STARTED_COUNT = ALL_COUNT THEN 'NOT_STARTED'
            ELSE 'STARTED'
       END status_a
FROM (            
SELECT package_id,
     COUNT(status_a) ALL_COUNT,
     SUM(CASE WHEN status_a = 'FAILED' THEN 1 ELSE 0 END) FAILED_COUNT,
     SUM(CASE WHEN status_a = 'COMPLETE' THEN 1 ELSE 0 END) COMPLETE_COUNT,
     SUM(CASE WHEN status_a = 'NOT_STARTED' THEN 1 ELSE 0 END) NOT_STARTED_COUNT,
     SUM(CASE WHEN status_a = 'STARTED' THEN 1 ELSE 0 END) STARTED_COUNT
 FROM table
GROUP BY package_id
)