详细理解以下代码，我稍后与你沟通
-- 删除已存在的视图
DROP VIEW IF EXISTS bj_database.view_work_order_list_filter;
-- 创建或替换视图
CREATE OR REPLACE VIEW bj_database.view_work_order_list_filter AS
WITH
-- 1. 生成日期维度：从前天到未来第7天（共10天）
date_dimension AS (
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS target_date, 1 AS day_order
UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY), 2
UNION ALL SELECT CURDATE(), 3
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY), 4
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL 2 DAY), 5
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY), 6
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL 4 DAY), 7
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY), 8
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL 6 DAY), 9
UNION ALL SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY), 10
),
-- 2. 定义班次维度（白班：8:00-20:00，夜班：20:00-次日8:00）
shift_dimension AS (
-- 白班：当天 08:00 - 20:00
SELECT target_date, '白班' AS shift_type,
TIMESTAMP(target_date, '08:00:00') AS shift_start,
TIMESTAMP(target_date, '20:00:00') AS shift_end
FROM date_dimension
UNION ALL
-- 夜班：当天 20:00 - 次日 08:00
SELECT target_date, '夜班' AS shift_type,
TIMESTAMP(target_date, '20:00:00') AS shift_start,
TIMESTAMP(DATE_ADD(target_date, INTERVAL 1 DAY), '08:00:00') AS shift_end
FROM date_dimension
),
-- 3. 获取每个工单在"今天"的最新状态（含：已完工、已挂起）
latest_daily AS (
SELECT
w.*,
ROW_NUMBER() OVER (PARTITION BY w.workOrderNo ORDER BY w.updateTime DESC) AS rn
FROM bj_database.work_order_daily w
WHERE DATE(w.updateTime) = CURDATE()
AND w.workOrderStatus IN ('已提交', '已开工', '拟制中', '已完工', '已挂起')
),
current_status AS (
SELECT *
FROM latest_daily
WHERE rn = 1
),
-- 4. 获取前两天各班次的实际产出和目标产出（按班次切分）
actual_by_shift AS (
SELECT
w.workOrderNo,
s.target_date,
s.shift_type,
MAX(w.outputQty) AS actual_output,
ROUND(
COALESCE(
(CAST(MAX(w.taskQty) AS DECIMAL(10,2)) - CAST(MAX(w.outputQty) AS DECIMAL(10,2))) *
GREATEST(
TIMESTAMPDIFF(SECOND,
GREATEST(w.scheduleStartDate, s.shift_start),
LEAST(w.scheduleEndDate, s.shift_end)
), 0
) / NULLIF(TIMESTAMPDIFF(SECOND, w.scheduleStartDate, w.scheduleEndDate), 0),
0
), 2
) AS target_output
FROM bj_database.work_order_daily w
JOIN shift_dimension s
ON DATE(w.updateTime) = s.target_date
AND (
(s.shift_type = '白班' AND HOUR(w.updateTime) >= 8 AND HOUR(w.updateTime) < 20) OR
(s.shift_type = '夜班' AND (HOUR(w.updateTime) >= 20 OR HOUR(w.updateTime) < 8 AND DATE(w.updateTime) = DATE(s.shift_end)))
)
WHERE DATE(w.updateTime) BETWEEN DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND CURDATE()
AND w.workOrderStatus IN ('已提交', '已开工', '拟制中', '已完工', '已挂起')
GROUP BY w.workOrderNo, s.target_date, s.shift_type
),
-- 5. 计算工单静态参数（责任人、线体、剩余数量等）
work_order_params AS (
SELECT
c.workOrderNo,
c.prodplanId AS `批次`, -- 映射为“批次”
COALESCE(m.line_name, c.lineCode) AS `线体`, -- 映射为“线体”
c.itemNo AS `单板代码`, -- 映射为“单板代码”
c.itemName AS `单板名称`, -- 映射为“单板名称”
CAST(c.taskQty AS DECIMAL(10,2)) AS `计划总数`,
CAST(c.outputQty AS DECIMAL(10,2)) AS `当前产出`,
(CAST(c.taskQty AS DECIMAL(10,2)) - CAST(c.outputQty AS DECIMAL(10,2))) AS `剩余数量`,
MIN(c.scheduleStartDate) AS scheduleStartDate,
MAX(c.scheduleEndDate) AS scheduleEndDate,
GREATEST(TIMESTAMPDIFF(SECOND, MIN(c.scheduleStartDate), MAX(c.scheduleEndDate)), 1) AS total_duration_seconds,
COALESCE(
pc.计调员,
CASE
WHEN c.factoryName = '河源' AND c.craftSection IS NOT NULL AND LOCATE('背板', c.craftSection) > 0 THEN '徐捷如'
WHEN c.factoryName = '河源' AND c.itemName REGEXP '^PPC|^PA|^PD|^PN|^ZXSDR BS8800 C100 PSUB0' THEN '何东'
WHEN c.factoryName = '河源' AND c.externalType IN ('ES', 'MSR(R10)', 'CLS', 'IC', 'MSTP') THEN '钟慧珊'
WHEN c.factoryName = '河源' AND c.externalType = '5G-WS' THEN '无线'
WHEN c.factoryName = '河源' AND c.externalType IN (
'5G_CMxU', '5G_COLT', '5G_CONT', 'A10', 'CHS', 'CPE', 'DSL', 'EODN', 'J10', 'ONT', 'POL',
'PON_MxU', 'PON_OLT', 'PONG', 'POND', 'OTN', 'ITPS', 'MSAP', 'CLOUD', 'CT', 'ESI', 'IAD',
'iCDN', 'uSmartView', 'VCS', 'VCT'
) THEN '肖应仲'
WHEN c.factoryName = '河源' AND c.externalType IN ('C-SPN', 'IPRAN-A', 'SPN', 'D-SPN', 'IPRAN', 'IPTN') THEN '徐捷如'
WHEN c.factoryName = '河源' AND c.externalType IN ('CDCG', 'DC_P', 'DPS', 'POWER', 'PS_S', 'SSE', 'TSE', '3GPLAT', 'DC_S', 'SE') THEN '祁思佳'
WHEN c.factoryName = '河源' AND c.externalType = 'RRS' THEN '何东'
WHEN c.factoryName = '深圳' AND c.externalType IN ('ITPS', 'RSS') THEN '江启一'
WHEN c.factoryName = '深圳' AND c.externalType IN ('OTN', 'WDM') THEN '杨港会'
WHEN c.factoryName = '深圳' AND c.externalType = 'CP' THEN '周旭升'
ELSE '未分配'
END
) AS `责任人`,
c.factoryName AS `工厂`,
c.craftSection AS `主工序`,
l.`线体` IS NOT NULL AS is_valid_line
FROM current_status c
LEFT JOIN bj_database.line_mapping m ON c.lineCode = m.line_id
LEFT JOIN bj_database.part_category pc
ON c.factoryName = pc.工厂
AND LEFT(c.itemNo, 12) = LEFT(pc.单板代码, 12)
LEFT JOIN bj_database.lineteamleader l
ON COALESCE(m.line_name, c.lineCode) = l.`线体`
WHERE c.scheduleStartDate IS NOT NULL
AND c.scheduleEndDate IS NOT NULL
GROUP BY
c.workOrderNo,
c.prodplanId,
COALESCE(m.line_name, c.lineCode),
c.itemNo,
c.itemName,
c.taskQty,
c.outputQty,
c.factoryName,
c.craftSection,
l.`线体`,
pc.计调员,
c.externalType,
c.scheduleStartDate -- 已添加到 GROUP BY
),
-- 6. 生成每个工单在每个班次的计划产出（按工时比例分配）
planned_by_shift AS (
SELECT
wp.workOrderNo,
s.target_date,
s.shift_type,
ROUND(
wp.`剩余数量` *
GREATEST(
TIMESTAMPDIFF(SECOND,
GREATEST(wp.scheduleStartDate, s.shift_start),
LEAST(wp.scheduleEndDate, s.shift_end)
), 0
) / wp.total_duration_seconds,
2
) AS planned_output
FROM work_order_params wp
JOIN shift_dimension s
ON s.target_date BETWEEN DATE(wp.scheduleStartDate) AND DATE(wp.scheduleEndDate)
WHERE wp.is_valid_line
AND s.target_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
),
-- 7. 合并实际产出（历史）和计划产出（未来）
combined_by_shift AS (
-- 历史实际产出（前2天）
SELECT
workOrderNo, target_date, shift_type,
actual_output, target_output, 0 AS planned_output, 'actual' AS data_type
FROM actual_by_shift
WHERE target_date >= DATE_SUB(CURDATE(), INTERVAL 2 DAY)
UNION ALL
-- 未来计划产出
SELECT
workOrderNo, target_date, shift_type,
0 AS actual_output, 0 AS target_output, planned_output, 'planned' AS data_type
FROM planned_by_shift
),
-- 8. 最终输出：将班次数据横向展开为列（行转列）
final_output AS (
SELECT
wp.`批次`,
wp.`线体`,
wp.`单板代码`,
wp.`单板名称`,
wp.`计划总数`,
wp.`当前产出`,
wp.`责任人`,
wp.`工厂`,
wp.`主工序`,
-- 前天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '白班' THEN cs.actual_output END), 0) AS `前天白班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '白班' THEN cs.target_output END), 0) AS `前天白班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '白班' THEN cs.planned_output END), 0) AS `前天白班计划产出`,
-- 前天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '夜班' THEN cs.actual_output END), 0) AS `前天夜班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '夜班' THEN cs.target_output END), 0) AS `前天夜班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '夜班' THEN cs.planned_output END), 0) AS `前天夜班计划产出`,
-- 昨天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '白班' THEN cs.actual_output END), 0) AS `昨天白班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '白班' THEN cs.target_output END), 0) AS `昨天白班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '白班' THEN cs.planned_output END), 0) AS `昨天白班计划产出`,
-- 昨天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '夜班' THEN cs.actual_output END), 0) AS `昨天夜班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '夜班' THEN cs.target_output END), 0) AS `昨天夜班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '夜班' THEN cs.planned_output END), 0) AS `昨天夜班计划产出`,
-- 今天 白班
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '白班' THEN cs.actual_output END), 0) AS `今天白班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '白班' THEN cs.target_output END), 0) AS `今天白班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '白班' THEN cs.planned_output END), 0) AS `今天白班计划产出`,
-- 今天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '夜班' THEN cs.actual_output END), 0) AS `今天夜班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '夜班' THEN cs.target_output END), 0) AS `今天夜班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '夜班' THEN cs.planned_output END), 0) AS `今天夜班计划产出`
FROM work_order_params wp
LEFT JOIN combined_by_shift cs ON wp.workOrderNo = cs.workOrderNo
GROUP BY
wp.`批次`,
wp.`线体`,
wp.`单板代码`,
wp.`单板名称`,
wp.`计划总数`,
wp.`当前产出`,
wp.`责任人`,
wp.`工厂`,
wp.`主工序`
)
SELECT * FROM final_output;
本文介绍了一个简单的Python类定义示例，通过定义一个Room类来表示房间，并计算其面积。该类接受房间名称、所有者及尺寸作为参数，并封装了一个用于计算面积的方法。
扫一扫
882
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
