mysql - 我对同一个表有两个查询,我加入了不同的分组
问题描述
我对同一个表有两个查询,这些查询具有我加入的不同分组。该代码有效,但似乎很慢。我确信,由于查询的相似性质,可以优化此代码。
SELECT A.BatchID,
A.Lot_ID,
A.SN,
A.StepNum,
A.StepName,
A.ProcedureName,
A.HMI_user,
A.RecipeName,
A.batchIdSN,
A.t_stamp,
A.Batch_Time AS 'Batch Time',
B.BatchTimeTotal,
B.t_stamp_start
FROM (SELECT g1.BatchID,
g1.Lot_ID,
g1.SN,
g1.StepNum,
g1.StepName,
g1.ProcedureName,
g1.HMI_user,
g1.RecipeName,
CONCAT(g1.BatchID,g1.SN) AS 'batchIdSN',
CONVERT_TZ(g1.t_stamp, 'UTC', $timeZone$) AS 't_stamp',
SUM(TIME_TO_SEC(TIMEDIFF(g2.t_stamp,g1.t_stamp))/60) AS 'Batch_Time'
FROM `SN_all_Batch_Data` g1
Left JOIN `SN_all_Batch_Data` g2 ON g2.id = g1.id + 1 and g2.SN = g1.SN
WHERE g1.BatchID > 0
GROUP BY g1.BatchID, g1.StepNum) A
LEFT JOIN (SELECT q.BatchID,
q.BatchTimeTotal,
MIN(q.t_stamp) AS 't_stamp_start'
FROM(SELECT g1.BatchID,
CONVERT_TZ(g1.t_stamp, 'UTC', $timeZone$) AS 't_stamp',
SUM(TIME_TO_SEC(TIMEDIFF(g2.t_stamp,g1.t_stamp))/60) AS 'BatchTimeTotal'
FROM `SN_all_Batch_Data` g1
Left JOIN `SN_all_Batch_Data` g2 ON g2.id = g1.id + 1 and g2.SN = g1.SN
WHERE g1.BatchID > 0
GROUP BY g1.BatchID) q
GROUP BY q.BatchID) B
ON A.BatchID = B.BatchID
任何优化指导将不胜感激。
解决方案
假设您提供的查询在您的系统中有效。也许中间表可以省去重复的工作。您可以先创建一个这样的表。
CREATE TABLE temp1 AS
SELECT g1.BatchID,
g1.Lot_ID,
g1.SN,
g1.StepNum,
g1.StepName,
g1.ProcedureName,
g1.HMI_user,
g1.RecipeName,
CONCAT(g1.BatchID,g1.SN) AS 'batchIdSN',
CONVERT_TZ(g1.t_stamp, 'UTC', $timeZone$) AS 't_stamp',
SUM(TIME_TO_SEC(TIMEDIFF(g2.t_stamp,g1.t_stamp))/60) AS 'BatchTimeTotal'
FROM `SN_all_Batch_Data` g1
Left JOIN `SN_all_Batch_Data` g2 ON g2.id = g1.id + 1 and g2.SN = g1.SN
WHERE g1.BatchID > 0;
然后以下步骤将是
SELECT A.*,
B.BatchTimeTotal,
B.t_stamp_start
FROM (SELECT g1.BatchID,
g1.Lot_ID,
g1.SN,
g1.StepNum,
g1.StepName,
g1.ProcedureName,
g1.HMI_user,
g1.RecipeName,
g1.batchIdSN,
g1.t_stamp,
g1.BatchTimeTotal AS 'Batch Time'
FROM temp1 g1
GROUP BY g1.BatchID, g1.StepNum) A
LEFT JOIN (SELECT q.BatchID,
q.BatchTimeTotal,
MIN(q.t_stamp) AS 't_stamp_start'
FROM temp1 q
GROUP BY q.BatchID) B
ON A.BatchID = B.BatchID
推荐阅读
- machine-learning - MeanShift 估计器的无分数方法 - scikit-learn
- javascript - Video.js 黑屏仅在高度超过 225 像素时播放音频
- enums - Elixir 移除嵌套元素
- git - 我可以帮助解决一个小写的 Git 幻像目录吗?
- java - 使用 java final 关键字时出错,无法修改 final ArrayList 的副本
- python - 如何比较持有 numpy.ndarray 的数据类的相等性(bool(a==b)引发 ValueError)?
- javascript - ES5 和 ES6 的导入区别
- c# - 如何禁用 Webbrowser 控件上的弹出窗口并将 Webbrowser 导航到新 url?
- java - java中的文件锁定
- php - 复制 vs Curl 将外部文件保存在我的服务器上