复杂数据的SQLite查询

Question

我做实验，在多个烧瓶中培养酵母菌株，并采集样本，从中获取不同化学物质的数据。你知道吗

我已经建立了一个SQLite表，其中包含字段StrainId、FlaskNum、DateTime、HarvestIndex、Species、NumLabeledCarbons和MDV。你知道吗

我很感兴趣的是，在最新的实验中，从每一个烧瓶中采集的所有代谢物的最早样品的MDV数据，对应于特定的菌株。你知道吗

我已经使用sqlite3包编写了一个Python脚本，使用一系列for循环来检索我需要的数据，它可以工作，但是我怀疑可能有一种更以SQL为中心的方法来获取和分组单个数据。你知道吗

下面是获取代码的基本情况：

import sqlite3 as lite

con = lite.connect('fluxData.db')
with con:
    cur = con.cursor()
    for strainId in cur.execute("SELECT DISTINCT StrainId FROM LCMS WHERE StrainId IN ('U','S','UG','SG');").fetchall():
        for dateVal in cur.execute("SELECT max(date(DateTime)) FROM LCMS WHERE StrainId=?",[strainId[0]]).fetchall():
            for flaskNum in cur.execute("SELECT DISTINCT FlaskNum FROM LCMS WHERE StrainId=? AND date(DateTime)=?",[strainId[0],dateVal[0]]).fetchall():
                for harvestIndex in cur.execute("SELECT min(HarvestIndex) FROM LCMS").fetchall():
                    for species in cur.execute("SELECT DISTINCT Species FROM LCMS WHERE StrainId=? AND date(DateTime)=? AND FlaskNum=? AND HarvestIndex=?",[strainId[0],dateVal[0],flaskNum[0],harvestIndex[0]]).fetchall():
                        print '\n'
                        for row in cur.execute("SELECT NumLabeledCarbons,MDV FROM LCMS WHERE StrainId=? AND date(DateTime)=? AND FlaskNum=? AND HarvestIndex=? AND Species=? ORDER BY NumLabeledCarbons",[strainId[0],dateVal[0],flaskNum[0],harvestIndex[0],species[0]]).fetchall():
                            print row

在我获得数据后，我将数据分组，然后将它们平均在同一菌株的每组中。你知道吗

我想知道这是不是最好的办法？有没有一种只使用SQL语句获取所有分组数据的方法？你知道吗

Answer 1

显然，大多数查询的目的只是将结果按组排序。这可以简单地按顺序完成。你知道吗

可以通过correlated subquery获得每个菌株的最新日期：

SELECT NumLabeledCarbons, MDV
FROM LCMS
WHERE StrainId IN ('U','S','UG','SG')
  AND date(DateTime) = (SELECT max(date(DateTime))
                        FROM LCMS AS L2
                        WHERE L2.StrainId = LCMS.StrainId)
  AND HarvestIndex = (SELECT min(HarvestIndex)
                      FROM LCMS)
ORDER BY StrainId, FlaskNum, Species;

但是，这将为表中的每一行重新执行子查询。 在一个单独的子查询中计算四个菌株的最新日期，并将其连接回原始表，可能更有效：

SELECT NumLabeledCarbons, MDV
FROM LCMS
JOIN (SELECT StrainId,
             max(DateTime) AS MaxDate
      FROM LCMS
      WHERE StrainId IN ('U','S','UG','SG')
      GROUP BY StrainId) AS MD
ON LCMS.StrainId       = MD.StrainId AND
   date(LCMS.DateTime) = MD.MaxDate
WHERE HarvestIndex = (SELECT min(HarvestIndex)
                      FROM LCMS)
ORDER BY StrainId, FlaskNum, Species;

Answer 2

方法A是冻糕的答案，我对它做了一些修改以使其发挥作用：

SELECT StrainId, Max(date(DateTime)) As LatestExperimentDate, FlaskNum, Min(HarvestIndex) As EarliestHarvest,Species,NumLabeledCarbons,MDV
FROM LCMS
WHERE StrainId IN ('U','S','UG','SG') AND HarvestIndex = (SELECT min(HarvestIndex) FROM LCMS)
GROUP BY StrainId, FlaskNum, Species, NumLabeledCarbons, MDV
HAVING date(DateTime) = Max(date(DateTime))
ORDER BY StrainId, date(DateTime), FlaskNum, HarvestIndex,Species,NumLabeledCarbons;    

方法B是CL，我只是稍微修改了一下，因为列名称不明确：

SELECT LCMS.StrainId,date(DateTime),FlaskNum,HarvestIndex,Species,NumLabeledCarbons,MDV
FROM LCMS
JOIN (  SELECT StrainId,max(date(DateTime)) AS MaxDate
        FROM LCMS
        WHERE StrainId IN ('U','S','UG','SG')
        GROUP BY StrainId) AS MD
ON LCMS.StrainId=MD.StrainId AND date(LCMS.DateTime)=MD.MaxDate
WHERE HarvestIndex = (  SELECT min(HarvestIndex)
                        FROM LCMS)
ORDER BY LCMS.StrainId,date(DateTime),FlaskNum,HarvestIndex,Species,NumLabeledCarbons;

方法C是我上面的for循环方法。你知道吗

我实现并计时了每个方法，以获取包含我想要的所有数据的中间字典，并验证它们是否都相同。你知道吗

此外，在100次迭代中以秒为单位计时，发现方法A、B和C的平均值分别为0.05831611、0.05774071和0.23839145秒，各自的标准偏差分别为0.0071795、0.00439557和0.01499475。你知道吗

for-loop方法比CL和Parfait提出的方法花费了大约4倍的时间。你知道吗

从技术上讲，我希望有一种方法可以将sqlite3返回的数据划分为NumLabeledCarbons，每个样本/物种组合的MDV数据，以及那些按物种分组的数据，而不是匹配查询的每一行的元组的有序列表。尽管有人告诉我这不是SQL的重点。你知道吗

然而，这比我提出的解决方案要好得多，我在试图理解你的答案时学到了很多。你知道吗

感谢您抽出时间回答我的第一个堆栈溢出问题！你知道吗

Answer 3

考虑以下SQL聚合查询：

import sqlite3 as lite
con = lite.connect('fluxData.db')

sql = "SELECT StrainId, FlaskNum, Species, NumLabeledCarbons, MDV, \
       Max(date(DateTime)) As LatestExperimentDate, Min(HarvestIndex) As EarliestHarvest \
       FROM LCMS \
       WHERE StrainId IN ('U','S','UG','SG') \
       GROUP BY StrainId, FlaskNum, Species, NumLabeledCarbons, MDV \
       HAVING DateTime = Max(date(DateTime)) AND HarvestIndex = Min(HarvestIndex) \
       ORDER BY  NumLabeledCarbons;"

with con:
   cur = con.cursor()
   cur.execute(sql)   

   for data in cur.fetchall():
      ...

然后可以将Avg()、Sum()和Count()添加到SELECT行中的任何数值列中，以便按组进行计算。你知道吗