mysql - 从子查询中选择

Question

我正在尝试编写一个包含 2 个子查询的查询。这些查询在单独运行时有效，但是当我将它们放在一起时，我没有得到所需的结果集。我将尝试举一个最小的例子。

主要查询:

mysql> select target_name_id,  ep, count(*), count(distinct wafer_id) 
       from data_cst 
       where target_name_id = 155609 
       and data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59'
       GROUP BY target_name_id, data_cst.ep;

+----------------+------+----------+--------------------------+
| target_name_id | ep   | count(*) | count(distinct wafer_id) |
+----------------+------+----------+--------------------------+
|         155609 | Line |     4799 |                      215 |
+----------------+------+----------+--------------------------+
1 row in set (0.05 sec)

第一个子查询:

 mysql> SELECT target_name_id,ep, wafer_id, AVG(bottom) as averages,
        FROM data_cst            
        WHERE target_name_id = 155609
        AND data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59'
        GROUP BY target_name_id, ep, wafer_id;
+----------------+------+----------+------------+
| target_name_id | ep   | wafer_id | averages   |
+----------------+------+----------+------------+
|         155609 | Line |   401739 | 47.6236667 |
|         155609 | Line |   403041 | 47.3739167 |
|         155609 | Line |   408339 | 47.4901667 |
|         155609 | Line |   409683 | 48.3066250 |
|         155609 | Line |   409690 | 47.2402500 |
|         155609 | Line |   410249 | 47.3346667 |
|         155609 | Line |   410633 | 48.7373333 |
|         155609 | Line |   414000 | 48.1274167 |
              .
              .
              .
215 rows in set (0.07 sec)

第二个子查询:

mysql> SELECT target_name_id, ep, data_file_id, lot_id, wafer_id, 
              date_time,
              COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns
       FROM data_cst         
       WHERE target_name_id = 155609  
       AND data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59' 
       GROUP BY target_name_id, data_cst.ep, wafer_id         
       HAVING COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id) > 1;



   +----------------+------+--------------+--------+----------+---------------------+--------+
    | target_name_id | ep   | data_file_id | lot_id | wafer_id | date_time           | reruns |
    +----------------+------+--------------+--------+----------+---------------------+--------+
    |         155609 | Line |          774 | 120804 |   403041 | 2012-07-06 03:51:50 |      1 |
    |         155609 | Line |         6502 | 123109 |   409683 | 2012-07-16 05:10:04 |      1 |
    |         155609 | Line |          749 | 120804 |   409690 | 2012-07-06 04:08:01 |      1 |
    |         155609 | Line |      3319148 | 123484 |   410633 | 2012-07-07 09:12:20 |      5 |
    |         155609 | Line |         8264 | 134609 |   414098 | 2012-07-03 11:34:12 |      5 |
    |         155609 | Line |      3279867 | 124752 |   414245 | 2012-06-26 00:51:31 |      1
                .
                .
                .
93 rows in set (0.06 sec)

现在，当我将它们放在一起时，我需要主查询的计数、第二个的平均值以及第三个重新运行列的总和。我已经搞砸了 3 天，我无法提出正确的连接来获得我想要的结果。我已经能够得出正确的总和、计数或平均值，但不是全部 3 个。这是我最近的尝试:

mysql> select data_cst.target_name_id, data_cst.ep, count(*) as count, 
       count(distinct data_cst.wafer_id) as wafers, 
       avg(averages) as average, sum(reruns) as rerun 
       from data_cst, 
          (SELECT target_name_id,ep, wafer_id, AVG(bottom) as averages            
           FROM data_cst                        
           WHERE target_name_id = 155609             
           AND data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59'             
           GROUP BY target_name_id, ep, wafer_id) q1, 
          (SELECT target_name_id, ep, data_file_id, lot_id, wafer_id,
                  date_time, 
                  COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns
           FROM data_cst                     
           WHERE target_name_id = 155609              
           AND data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59'             
           GROUP BY target_name_id, data_cst.ep, wafer_id                     
           HAVING COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id) > 1) r 
       where data_cst.target_name_id = 155609 
       AND data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59' 
       and  data_cst.wafer_id = q1.wafer_id 
       GROUP BY target_name_id, data_cst.ep;
+----------------+------+--------+--------+----------------+--------+
| target_name_id | ep   | count  | wafers | average        | rerun  |
+----------------+------+--------+--------+----------------+--------+
|         155609 | Line | 446307 |    215 | 48.12877962148 | 724649 |
+----------------+------+--------+--------+----------------+--------+
1 row in set (23.56 sec)

有了这个最外层的 where 子句，晶圆计数和平均值是正确的，但计数和重新运行是错误的。我可以使用不同的 where 子句并使重新运行正确，但计数和晶圆都是错误的。我还可以使用另一个不同的 where 子句并得到正确的计数，但是重新运行是错误的。

我已经搞砸了 3 天，我就是找不到适合我的 where 子句。

这是对我的问题的更新:

我按照 Gordon Linoff 的建议修改了我的查询，此后客户添加了很多新要求，我已经能够将这些要求合并到查询中。但现在他们添加了一些我不太清楚如何处理它。

我的查询现在看起来像这样:

SELECT data_target.name as Target,
       q1.ep as EP,
       COUNT(*) as Wafers,
       Lots,
       SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer',
       MAX(LastRun) as "Last Run",
       SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as Rerun,
       COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as Runs,
       avgbottom as "Avg Bottom",
       3*stdbottom as "3 Sig",
       maxbottom as Max,
       minbottom as Min,
       SUM(numonep) as Count,
       SUM(numonep) - SUM(numbottoms) as NAs,
       100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as "% Success",
       3*stdbottom/avgbottom as "3Sig/Avg",
       AVG(avgbottom) as 'Wafer Avg',
       AVG(Wafer3Sigma) as 'Wafer 3 Sigma',
       AVG(Ranges) as 'Avg Range',
       3*STD(Ranges) as '3Sig of Ranges',
       MAX(Ranges) as 'Max Range',
       MIN(Ranges) as 'Min Range',
       (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer'
   FROM (SELECT target_name_id,
                ep,
                wafer_id,
                COUNT(bottom) as numbottoms,
                AVG(bottom) as avgbottom, 
                STD(bottom) as stdbottom,
                MAX(bottom) as maxbottom,
                MIN(bottom) as minbottom,
                MAX(date_time) as "LastRun",
                COUNT(*) as numonep,
                COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, 
                COUNT(DISTINCT(lot_id)) as Lots,
                3*STD(bottom) as Wafer3Sigma,
                MAX(bottom) - MIN(bottom) as Ranges
         FROM data_cst
         WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121)  
         AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' 
         GROUP BY target_name_id, ep, wafer_id  
         HAVING count(*) < 999) q1, 
   data_target  
   WHERE data_target.id = target_name_id  
   GROUP BY q1.target_name_id, q1.ep;

这非常有效。但是现在他们要我得到一个特定的列(image_measurer_id)，返回的每一行对应于具有 bottom = Min(bottom)，bottom = Max(bottom)，bottom 最接近 Avg(bottom)和date_time = Max(date_time) 行的底部。

这甚至可以通过这个查询实现吗？

Answer 1

如果您使用正确的连接语法，将会对您有很大帮助。加入 data_cst 似乎是多余的。如果您的两个子查询具有相同的目标和晶片，那么简单的连接或左外部连接应该可以工作:

select q1.target_name_id, q1.ep, count(*) as count, 
   count(distinct q1.wafer_id) as wafers, 
   avg(averages) as average, sum(reruns) as rerun 
   from (SELECT target_name_id,ep, wafer_id, AVG(bottom) as averages            
       FROM data_cst                        
       WHERE target_name_id = 155609             
       AND data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59'             
       GROUP BY target_name_id, ep, wafer_id) q1 left outer join
      (SELECT target_name_id, ep, data_file_id, lot_id, wafer_id,
              date_time, 
              COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns
       FROM data_cst                     
       WHERE target_name_id = 155609              
       AND data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59'             
       GROUP BY target_name_id, data_cst.ep, wafer_id                     
       HAVING COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id) > 1
      ) r 
      on r.wafer_id = q1.wafer_id and
         r.target_name_id = q1.target_name_id and
         r.ep = q1.ep
   GROUP BY q1.target_name_id, q1.ep;

但是，您的查询非常相似，所以我认为您可以简化逻辑:

select q1.target_name_id, q1.ep, sum(numonep) as count, 
       count(*) as wafers, 
       avg(averages) as average,
       sum(case when reruns > 0 then reruns else 0 end) as rerun 
from (SELECT target_name_id, ep, wafer_id, AVG(bottom) as averages,
             count(*) as numonep,
             COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns           
      FROM data_cst                        
      WHERE target_name_id = 155609 and             
            data_cst.date_time BETWEEN '2012-06-23 00:00:00' AND '2012-08-23 23:59:59'             
      GROUP BY target_name_id, ep, wafer_id
     ) q1 
group by q1.target_name_id, q1.ep