0 问题描述
- 制程参数是在产品生产过程中影响产品质量的各种因素，如温度、压力、时间、原材料成分等。要根据这些制程参数来计算良品率，需要考虑制程参数与产品是否合格之间的关系。通常，需要先确定每个制程参数的合格范围，然后统计在合格制程参数下生产出的良品数量与总生产数量的比率。
1 数据准备
- 生产数据表（production_data）包含
production_id（生产批次 ID）、product_id（产品 ID）、production_date（生产日期）、quantity_produced（生产数量）、quantity_defective（次品数量）、process_temperature（生产温度）、process_pressure（生产压力）等制程参数字段。
CREATE TABLE production_data ( production_id INT PRIMARY KEY, product_id INT, production_date DATE, quantity_produced INT, quantity_defective INT, process_temperature DECIMAL(5,2), process_pressure DECIMAL(5,2) ); INSERT INTO production_data VALUES (1, 1, '2024-01-01', 1000, 50, 100.00, 2.00), (2, 1, '2024-02-01', 1200, 80, 105.00, 2.20), (3, 2, '2024-01-15', 800, 40, 98.00, 1.80), (4, 2, '2024-02-15', 900, 60, 102.00, 2.10);
- 制程参数标准表（process_standards）包含
product_id（产品 ID）、parameter_name（参数名称，如 'temperature'、'pressure'）、lower_limit（参数下限）、upper_limit（参数上限）。
CREATE TABLE process_standards ( product_id INT, parameter_name VARCHAR(20), lower_limit DECIMAL(5,2), upper_limit DECIMAL(5,2) ); INSERT INTO process_standards VALUES (1, 'temperature', 95.00, 105.00), (1, 'pressure', 1.80, 2.20), (2, 'temperature', 90.00, 102.00), (2, 'pressure', 1.60, 2.00);
2 问题分析
步骤1：确定每个生产批次的制程参数是否在合格范围内
- 通过将生产数据表和制程参数标准表进行连接，使用
CASE WHEN语句判断每个生产批次的制程参数是否在合格范围内。
SELECT pd.production_id, pd.product_id, pd.production_date, pd.quantity_produced, pd.quantity_defective, -- 判断温度是否在合格范围内 CASE WHEN ps_t.lower_limit <= pd.process_temperature AND pd.process_temperature <= ps_t.upper_limit THEN 1 ELSE 0 END AS temperature_in_range, -- 判断压力是否在合格范围内 CASE WHEN ps_p.lower_limit <= pd.process_pressure AND pd.process_pressure <= ps_p.upper_limit THEN 1 ELSE 0 END AS pressure_in_range FROM production_data pd -- 连接温度标准表 JOIN process_standards ps_t ON pd.product_id = ps_t.product_id AND ps_t.parameter_name = 'temperature' -- 连接压力标准表 JOIN process_standards ps_p ON pd.producp.parameter_name = 'pressure';t_id = ps_p.product_id AND ps_
在这个结果集中，我们可以清晰看到每个生产批次对应的产品 ID、生产日期、生产数量、次品数量以及温度和压力这两个制程参数是否在各自的合格范围内（1表示在合格范围内，0表示不在合格范围内）。这一步的中间结果为后续计算良品率提供了基础数据，明确了哪些批次的哪些制程参数是符合标准的。
步骤2：基于中间结果一计算临时良品数量
基于上一步的中间结果，进一步计算在考虑当前制程参数合格情况时，每个生产批次对应的临时良品数量（即如果制程参数都合格，该批次的良品数量；若有参数不合格，则临时良品数量计为 0）。-- 步骤二：基于制程参数合格情况计算临时良品数量并输出中间结果SELECT production_id, product_id, production_date, quantity_produced, quantity_defective, temperature_in_range, pressure_in_range, -- 计算临时良品数量（考虑制程参数合格情况） CASE WHEN temperature_in_range = 1 AND pressure_in_range = 1 THEN quantity_produced - quantity_defective ELSE 0 END AS temp_good_quantityFROM ( SELECT pd.production_id, pd.product_id, pd.production_date, pd.quantity_produced, pd.quantity_defective, -- 判断温度是否在合格范围内 CASE WHEN ps_t.lower_limit <= pd.process_temperature AND pd.process_temperature <= ps_t.upper_limit THEN 1 ELSE 0 END AS temperature_in_range, -- 判断压力是否在合格范围内 CASE WHEN ps_p.lower_limit <= pd.process_pressure AND pd.process_pressure <= ps_p.upper_limit THEN 1 ELSE 0 END AS pressure_in_range FROM production_data pd -- 关联温度标准数据 JOIN process_standards ps_t ON pd.product_id = ps_t.product_id AND ps_t.parameter_name = 'temperature' -- 关联压力标准数据 JOIN process_standards ps_p ON pd.product_id = ps_p.product_id AND ps_p.parameter_name = 'pressure') subquery; GROUP BY product_id, production_date;
这里新增了temp_good_quantity字段，表示在考虑当前制程参数（温度和压力）合格与否的情况下，每个生产批次对应的临时良品数量。如果温度和压力都在合格范围内，该字段的值就是该批次实际的良品数量（生产数量减去次品数量）；若有一个或多个制程参数不在合格范围内，则该字段值为 0。这个中间结果更接近最终计算良品率所需的数据形式，为下一步汇总计算良品率做准备。
步骤3：计算良品率（最终结果）
-- 步骤三：计算良品率并输出最终结果SELECT product_id, production_date, CONCAT(ROUND(((SUM(temp_good_quantity)) / SUM(quantity_produced)) * 100, 2), '%') AS yield_rateFROM ( SELECT production_id, product_id, production_date, quantity_produced, quantity_defective, temperature_in_range, pressure_in_range, -- 计算临时良品数量（考虑制程参数合格情况） CASE WHEN temperature_in_range = 1 AND pressure_in_range = 1 THEN quantity_produced - quantity_defective ELSE 0 END AS temp_good_quantity FROM ( SELECT pd.production_id, pd.product_id, pd.production_date, pd.quantity_produced, pd.quantity_defective, -- 判断温度是否在合格范围内 CASE WHEN ps_t.lower_limit <= pd.process_temperature AND pd.process_temperature <= ps_t.upper_limit THEN 1 ELSE 0 END AS temperature_in_range, -- 判断压力是否在合格范围内 CASE WHEN ps_p.lower_limit <= pd.process_pressure AND pd.process_pressure <= ps_p.upper_limit THEN 1 ELSE 0 END AS pressure_in_range FROM production_data pd -- 关联温度标准数据 JOIN process_standards ps_t ON pd.product_id = ps_t.product_id AND ps_t.parameter_name = 'temperature' -- 关联压力标准数据 JOIN process_standards ps_p ON pd.product_id = ps_p.product_id AND ps_p.parameter_name = 'pressure' ) subquery) final_subqueryGROUP BY product_id, production_date;
最终结果展示
最终结果呈现了每个产品在不同生产日期下的良品率情况，通过展示中间结果，我们可以更清晰地理解整个从原始生产数据和制程参数标准，逐步计算到最终良品率的完整过程，方便排查数据和计算逻辑上可能出现的问题，同时也有助于根据业务需求对计算过程进行灵活调整（比如增加更多制程参数的判断等）。
3 小结
如果有更多的制程参数，需要按照类似的方式添加更多的连接和判断条件来确定每个参数是否在合格范围内，并且在计算良品率时也需要考虑所有参数都合格的情况。同时，这种方法可以根据实际业务需求进行调整，例如，如果某些制程参数的权重不同，可以在计算良品率时引入权重因素。
问题拓展：如果制程参数是动态的，如何合理的设计表模型？
参数标准表（Parameter Standards Table）
- 目的存储每个产品对于不同制程参数的合格标准，这是一个动态变化的部分，因为产品的制程标准可能会根据质量要求、工艺改进等因素而改变。
- 结构示例
- 用途说明通过
product_id和parameter_id的组合，可以灵活地为每个产品定义不同制程参数的合格标准。当制程标准发生变化时，只需更新这个表中的相应记录。例如，如果产品 1 的温度合格范围从95 - 105调整为90 - 100，就可以在这个表中更新对应的记录。这种设计方便了对不同产品和不同制程参数标准的管理，同时可以通过关联parameter_id与参数定义表，获取完整的参数信息。
生产数据表（Production Data Table）
- 目的记录实际生产过程中的详细数据，包括生产批次信息、产品信息、生产数量、次品数量以及实际的制程参数值。
- 结构示例（包含部分字段）
- 用途说明
- 生产数据表存储了实际生产中的动态数据，其中制程参数的值可以根据生产过程中的实际测量而记录。在设计时，可以根据实际的制程参数数量预留足够的字段（如
parameter_value_1、parameter_value_2等），并通过程序逻辑或者数据库存储过程来确保这些值的正确插入和更新。 - 与产品表和参数定义表的关联可以方便地获取产品的其他信息以及制程参数的详细定义，为后续计算良品率等操作提供完整的数据支持。
产品表（Product Table）（可选但推荐）
- 目的存储产品的基本信息，如产品名称、产品型号等。这有助于更好地组织和管理生产数据，特别是当涉及多种产品的生产时。
- 结构示例
- 用途说明
- 通过与生产数据表的关联，可以在查询和分析生产数据时获取产品的详细名称和型号等信息，使数据更具可读性和可分析性。例如，在计算良品率并展示结果时，可以同时显示产品名称和型号，方便用户理解数据。
关联关系和数据完整性约束
- 关联关系
- 生产数据表通过
product_id与产品表关联，获取产品的基本信息。 - 生产数据表中的制程参数值字段（如
parameter_value_1等）通过参数定义表中的parameter_id间接关联，以确定每个参数值对应的参数名称、类型和单位等信息。 - 参数标准表通过
product_id与生产数据表关联，通过parameter_id与参数定义表关联，从而建立起产品、制程参数标准和实际生产数据之间的完整关系。
- 数据完整性约束
- 在参数标准表中，
product_id和parameter_id的组合应该是唯一的，以确保每个产品对于每个制程参数只有一组合格标准。 - 在生产数据表中，
production_id应该是唯一的，用于唯一标识每个生产批次。同时，可以设置外键约束，确保product_id在产品表中存在，制程参数值字段与参数定义表中的数据类型和其他约束相匹配。
这种表模型设计可以灵活地适应制程参数的动态变化，方便对生产数据和制程标准进行管理、查询和分析，无论是计算良品率还是进行其他质量控制相关的操作都能够提供良好的数据支持。
阅读原文：原文链接
该文章在 2024/12/30 15:10:58 编辑过
400 186 1886
