如何以有效的方式使用min()和max()? [英] How to use min() and max() in an efficient way?

问题描述

我有一个SQL查询，其中检查值是否在表的最大值和最小值之间.我现在实现了以下步骤:

I have an sql query where I check if a value is between a max and a min value of a table. I've now implement this as follows:

SELECT spectrum_id, feature_table_id
FROM 'spectrum', 'feature' 
WHERE `spectrum`.msrun_msrun_id = 1
AND `feature`.msrun_msrun_id = 1
AND (SELECT min(rt) FROM `convexhull` WHERE `convexhull`.feature_feature_table_id =  `feature`.feature_table_id) <= scan_start_time 
AND scan_start_time <= (SELECT max(rt) FROM `convexhull` WHERE 'convexhull'.feature_feature_table_id = 'feature'.feature_table_id)
AND (SELECT min(mz) FROM `convexhull` WHERE `convexhull`.feature_feature_table_id = `feature`.feature_table_id) <= base_peak_mz 
AND base_peak_mz <= (SELECT max(mz) FROM `convexhull` WHERE `convexhull`.feature_feature_table_id = `feature`feature_table_id)

运行非常缓慢，因为每次运行此查询时，我都会从凸包中选择4次，因此我尝试使用内部联接来对其进行改进:

This is running very slowly, because I'm selecting from convexhull 4 times every time I run this query, so I tried to improve it using an inner join:

SELECT spectrum_id, feature_table_id 
FROM 'spectrum', 'feature'
INNER JOIN `convexhull` ON `convexhull`.feature_feature_table_id = `feature`.feature_table_id
WHERE `spectrum`.msrun_msrun_id = ? "+ 
AND `feature`.msrun_msrun_id = ? "+
AND min(`convexhull`.rt) <= scan_start_time "+
AND scan_start_time <= max(`convexhull`.rt) "+
AND min(`convexhull`.mz) <= base_peak_mz "+
AND base_peak_mz <= max(`convexhull`.mz)", spectrumFeature_InputValues)

但是，min()和max()语句只能在select语句之后使用.如何使第一个查询更高效，从而不必进行4个查询就可以获得最小和最大rt和mz?

However, the min() and max() statements can only be used after a select statement. How can I make the first query more efficient, so that I can get the min and max rt and mz without having to do 4 queries?

推荐答案

再花几分钟，然后再次查看，发现所有数据都来自该表，因此类似的事情应该起作用

had a few more mins and looked again and realised all the data comes from that one table so something like this should work

SELECT 
    spectrum_id
    ,feature_table_id
FROM 
    spectrum AS s
    INNER JOIN feature AS f
        on f.msrun_msrun_id = s.msrun_msrun_id
    INNER JOIN (select 
         feature_feature_table_id
         ,min(rt) AS rtMin
        ,max(rt) AS rtMax
        ,min(mz) AS mzMin
        ,max(mz) as mzMax
     FROM 
        convexhull
     GROUP BY 
         feature_feature_table_id
     ) AS t
     ON t.feature_feature_table_id = f.feature_table_id
WHERE
    s.msrun_msrun_id = 1
    AND s.scan_start_time >= t.rtMin
    AND s.scan_start_time <= t.rtMax
    AND base_peak_mz >= t.mxMin
    AND base_peak_mz <= t.mzMax

我认为您想从凸包表中进行选择，并按feature_feature_table_id进行分组，以获取该分组中的最小和最大rt.

I think you want to select from the convexhull table and group by feature_feature_table_id getting the min and max rt within that grouping.

然后可以将选择的内容括在方括号中，为它命名(以t表示)并加入该名称.

you can then wrap that select in brackets give it a name (as t) and join to it.

希望这足以使您上路..如果未在此处创建示例架构: http://sqlfiddle.com /

Hope this is enought to get you on the road.. if not create a sample schema here: http://sqlfiddle.com/

并输入您的查询，我可以对其进行修改.

and put in your query and i can modify it.

作为旁注，我想您希望将这些表连接到特定字段，而不是使用where子句进行比较:

as a side note, I think you wan to join these tables on a particular field rather than select from both with a where clause compare:

SELECT spectrum_id, feature_table_id
FROM 'spectrum', 'feature' 
WHERE `spectrum`.msrun_msrun_id = 1
AND `feature`.msrun_msrun_id = 1

和:

SELECT 
    spectrum_id
    ,feature_table_id
FROM 
    spectrum AS s
    INNER JOIN feature AS f
        on f.msrun_msrun_id = s.msrun_msrun_id
WHERE
    s.msrun_msrun_id = 1

如果我有什么问题，请告诉我.

If i have got something wrong there let me know.

这篇关于如何以有效的方式使用min()和max()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！