SQL进阶技巧：如何分析工厂制程顺序问题？

0 问题描述

1 数据准备

2 问题分析

方法一：按照条件进行标记，并进行条件判断

方法2：通过符合条件的工单与原表关联，并利用having子句过滤符合条件的工单

方法三：使用窗口函数求解

方法4:优雅解法:挖掘数据特征【逆向思维：补集思想】

3 小结

目标导向的清晰思维

抽象与归纳的思考角度

巧用聚合特性挖掘深度关系

0 问题描述

在工厂中，制程是以一定工作顺序来描述的，它有一系列的工作步骤，工作顺序是以字段step_nbr来描述，该字段或者是完成或者是等待前面一个或多个步骤完成。

CREATE TABLE Projects (workorder_id VARCHAR(5) NOT NULL,step_nbr INT NOT NULL,step_status CHAR(1) NOT NULL CHECK (step_status IN ('C', 'W')),PRIMARY KEY (workorder_id, step_nbr)
);

问题需求：查询step_nbr为 0 且step_status为 'C' 的工单（workorder_id），但要求该工单的其他行的step_status都是 'W'。例如，在给定的示例数据中，查询结果应该只返回 'AA100'。

1 数据准备

CREATE TABLE Projects
(workorder_id STRING,step_nbr     INT,step_status  STRING
)
;
INSERT INTO Projects (workorder_id, step_nbr, step_status)
VALUES ('AA100', 0, 'C'),('AA100', 1, 'W'),('AA100', 2, 'W'),('AA200', 0, 'W'),('AA200', 1, 'W'),('AA300', 0, 'C'),('AA300', 1, 'C');

2 问题分析

方法一：按照条件进行标记，并进行条件判断

SELECT t.workorder_id
FROM (SELECT workorder_id,MAX(CASE WHEN step_nbr = 0 AND step_status = 'C' THEN 1 ELSE 0 END) AS cond1,MAX(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1 ELSE 0 END) AS cond2,MAX(CASE WHEN step_nbr <> 0 AND step_status = 'C' THEN 1 ELSE 0 END) AS cond3FROM ProjectsGROUP BY workorder_id) AS t
WHERE t.cond1 = 1AND t.cond2 = 1AND t.cond3 = 0;

中间子查询结果如下：

最终结果如下：

方法2：通过符合条件的工单与原表关联，并利用having子句过滤符合条件的工单

-- 筛选符合条件的工单
WITH TempProjects AS (SELECT workorder_idFROM ProjectsWHERE step_nbr = 0 AND step_status = 'C'
)
-- 通过符合条件的工单与原表关联，并利用having子句过滤符合条件的工单
SELECT t.workorder_id
FROM TempProjects t
JOIN Projects p ON t.workorder_id = p.workorder_id
GROUP BY t.workorder_id
HAVING SUM(CASE WHEN p.step_nbr <> 0 AND p.step_status != 'W' THEN 1 ELSE 0 END) = 0;

方法三：使用窗口函数求解

SELECT workorder_id
FROM (SELECT workorder_id,step_nbr,step_status,-- 使用窗口函数统计同一workorder_id下step_status为'W'且step_nbr大于0的记录数量SUM(CASE WHEN step_nbr > 0 AND step_status = 'W' THEN 1 ELSE 0 END) OVER (PARTITION BY workorder_id) AS count_w_status,-- 使用窗口函数统计同一workorder_id下step_nbr大于0的记录数量COUNT(*) OVER (PARTITION BY workorder_id) - 1 AS total_other_statusFROM Projects
) subquery
WHERE step_nbr = 0 AND step_status = 'C' AND count_w_status = total_other_status;

解释：

在内部子查询中：
- 通过SUM(CASE WHEN step_nbr > 0 AND step_status = 'W' THEN 1 ELSE 0 END) OVER (PARTITION BY workorder_id)这个窗口函数表达式，基于workorder_id进行分区，统计每个workorder_id分组内step_nbr大于 0 且step_status为'W'的记录数量，将其命名为count_w_status。
- 同时使用COUNT(*) OVER (PARTITION BY workorder_id) - 1来统计每个workorder_id分组内除了当前正在处理的（也就是step_nbr为 0 这条记录之外）的其他记录数量，将其命名为total_other_status。这里减 1 是因为要排除掉step_nbr为 0 的那条记录，只考虑其他分支记录的数量。
在外部查询中，筛选出step_nbr等于 0 且step_status等于'C'，并且count_w_status等于total_other_status的workorder_id，也就是满足题目条件的工作顺序对应的workorder_id。

中间结果如下：

SELECT workorder_id,step_nbr,step_status,-- 使用窗口函数统计同一workorder_id下step_status为'W'且step_nbr大于0的记录数量SUM(CASE WHEN step_nbr > 0 AND step_status = 'W' THEN 1 ELSE 0 END) OVER (PARTITION BY workorder_id) AS count_w_status,-- 使用窗口函数统计同一workorder_id下step_nbr大于0的记录数量COUNT(*) OVER (PARTITION BY workorder_id) - 1 AS total_other_statusFROM Projects

或者条件也可以写成如下形式：计算符合条件的记录数

SELECT workorder_id,step_nbr,step_status,-- 使用窗口函数统计同一workorder_id下符合条件记录数量SUM(CASEWHEN step_nbr <> 0 AND step_status = 'W' THEN 1WHEN step_nbr = 0 AND step_status = 'C' THEN 1ELSE 0 END) OVER (PARTITION BY workorder_id) AS count_w_status,-- 使用窗口函数统计同一workorder_id下记录数量COUNT(*) OVER (PARTITION BY workorder_id)            AS total_cnt
FROM Projects

将上述窗口条件改成having子句

select workorder_id
FROM Projects
group by workorder_id
having SUM(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1WHEN step_nbr =  0 AND step_status = 'C' THEN 1ELSE 0 END) = count(step_nbr)

方法4:优雅解法:挖掘数据特征【逆向思维：补集思想】

select workorder_id
from Projects
where step_status = 'C'
GROUP BY workorder_id
HAVING SUM(step_nbr) = '0'

解释：

等于C是符合条件的，当在C的状态下sum(step_status) != 0 说明该工单一定有C状态下的其他值，是不符合条件的。sum(step_status) = 0 说明在状态为C，只有step_nbr =0的值，由于step_status要么为C要么为W，sum(step_status) = 0，排除了C的其他可能，则step_status只能为W或 step_status='C',step_nbr=0的记录

3 小结

从处理问题思维方式及SQL优雅程度来看，本文方法4最为优雅，具体体现在如下几个方面：

目标导向的清晰思维

该方法有着明确的目标导向，整体围绕着找出符合特定条件的 workorder_id 来构建查询逻辑。先是锁定了 step_status 为 'C' 的记录，这体现出从众多数据中精准定位到与目标状态相关数据的思维，明确了想要关注的数据范围，没有在无关的数据上浪费精力，直奔核心问题而去。然后通过分组和进一步基于聚合结果的筛选，逐步细化条件，直到精准地圈定出那些所有 step_status 为 'C' 且对应 step_nbr 总和为 0 的工作顺序对应的 workorder_id，整个过程逻辑连贯，思维路径清晰，每一步操作都紧密服务于最终的查询目标。

抽象与归纳的思考角度

在对数据的处理上，运用了抽象与归纳的思考角度。通过 GROUP BY workorder_id 将具体的每条记录按照工作顺序进行了抽象归纳，把分散的数据按照业务上有意义的单元（不同的 workorder_id）整合起来，从关注单个记录转变为关注每个工作顺序整体的特征。这种从具体到抽象、从个体到集合的思考方式，符合分析批量数据时的常见思路，有助于发现隐藏在数据中的规律和关系，为后续通过聚合函数进行特征提取以及条件判断提供了便利，使得复杂的数据情况能够以一种有条理的方式被梳理清楚。

巧用聚合特性挖掘深度关系

思考角度的巧妙之处还体现在利用聚合函数的特性来挖掘数据间的深度关系。并非只是简单地查询数据或者进行一些常规的条件筛选，而是想到了使用 SUM(step_nbr) 来汇总每个工作顺序分组内的步骤编号，进而通过与 0 的比较来判断这个分组是否满足更深层次的业务要求。这种思考方式跳出了表面的、单一维度的数据查看，深入到了基于分组聚合后的数据关系层面，以一种简洁却有力的方式揭示了不同记录之间在数值总和上的关联，体现出对 SQL 功能的深刻理解以及运用 SQL 去挖掘数据内在联系的高超思维技巧，用相对简洁的语句表达出了较为复杂且有深度的业务逻辑。

总的来说，这种方法所展现出的思维方式和思考问题的角度，简洁高效且逻辑严谨，能够以一种优雅的方式处理数据、解决问题，对于理解如何运用 SQL 来实现复杂业务场景下的数据查询与分析有着很好的示范作用。