从子查询中进行选择

Question

从子查询中进行选择

4

我将尝试编写一个查询，其中包括两个子查询。当单独运行查询时，这些查询是有效的，但当我将它们全部放在一起时，我没有得到预期的结果集。我将尝试给出一个最小化的示例。

主查询：

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个结果。以下是我的最新尝试：

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子句来获得正确的计数，但此时重跑是错误的。

我已经尝试了三天都找不到适合我的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)，该列对应于底部=Min(bottom)、底部=Max(bottom)、最接近Avg(bottom)的底部和日期时间=Max(date_time)的行。

从这个查询中是否可能实现这一点？

- Larry Martell

你是否发现重复出现的次数比你预期的要高得多？大约高出215倍。如果是这样，那么这是否意味着你的连接操作导致了笛卡尔积？ - Yoztastic

是的，但我该如何防止这种情况发生？ - Larry Martell

好问题，我本来想建议在晶片ID上建立一个谓词，但是我看到你已经这样做了。 - Yoztastic

去掉最后的group by和投影可能会提示需要什么谓词来抑制笛卡尔积。我猜你已经尝试过了。 - Yoztastic

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

如果您使用正确的联接语法，将会对您非常有帮助。与 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