当只有一个查询结果出现时，如何保存多个查询结果？

Question

我正在计算20只果蝇的平均速度和最大速度。我有一个名为CSV_DAM_ACTIVITY的数据库，其中有20列，每个fly对应一列。每一行都代表了fly X在一分钟内所做的移动。一旦我在DB Browser for SQLite中打开数据库，我就会进入"running SQL“窗口。通过输入：select max(ROI_1), avg(ROI_1) as moyenne from CSV_DAM_ACTIVITY;，我可以轻松地获得一个fly的最大速度和平均速度，但当我尝试获得所有fly的结果时，我只看到最后一个结果:fly 20的最大速度和平均速度。

我首先尝试将数据保存到表中：

    create table z (a int not null,b int not null);
    insert into t values ( max(ROI_1)from CSV_DAM_ACTIVITY , avg(ROI_1) ) from  CSV_DAM_ACTIVITY);
    select * from t ;

但我得到了以下错误：

   Result: near "from": syntax error
   At line 1:
   insert into t values( max(ROI_1)from

我也试过了：

   create table droso ( maxi1 int not null , moyenne1 int not null ,   maxi2 int not null ,   moyenne2 int not null ,   maxi3 int not null ,   moyenne3 int not null ,    maxi4 int not null ,   moyenne4 int not null , maxi5 int not null ,   moyenne5 int not null ,  maxi6 int not null ,   moyenne6 int not null ,  maxi7 int not null ,   moyenne7 int not null ,   maxi8 int not null ,   moyenne8 int not null , maxi9 int not null ,   moyenne9 int not null ,  maxi10 int not null ,   moyenne10 int not null , maxi11 int not null ,   moyenne11 int not null ,   maxi12 int not null ,   moyenne12 int not null ,   maxi13 int not null ,   moyenne13 int not null ,  maxi14 int not null ,   moyenne14 int not null , maxi15 int not null ,   moyenne15 int not null ,  maxi16 int not null ,   moyenne16 int not null ,  maxi17 int not null ,   moyenne17 int not null ,  maxi18 int not null ,   moyenne18 int not null ,  maxi19 int not null ,   moyenne19 int not null ,  maxi20 int not null ,   moyenne20 int not null) ;
   insert into droso select max(ROI_1),avg(ROI_1),  max(ROI_2),  avg(ROI_2),  max(ROI_3),  avg(ROI_3),   max(ROI_4),  avg(ROI_4),max(ROI_5),  avg(ROI_5) , max(ROI_6),  avg(ROI_6) , max(ROI_7),  avg(ROI_7),  max(ROI_8),  avg(ROI_8) ,max(ROI_9),  avg(ROI_9), max(ROI_10),  avg(ROI_10) ,max(ROI_11),  avg(ROI_11),  max(ROI_12),  avg(ROI_12),  max(ROI_13),  avg(ROI_13), max(ROI_14),  avg(ROI_14),max(ROI_15),  avg(ROI_15), max(ROI_16),  avg(ROI_16), max(ROI_17),  avg(ROI_17), max(ROI_18),  avg(ROI_18), max(ROI_19),  avg(ROI_19), max(ROI_20),  avg(ROI_20),from CSV_DAM_ACTIVITY ;
   select * from droso ;

但是得到了相同的错误

然后我试着通过录音直接显示所有的结果：

 select max(ROI_1), avg(ROI_1) as moyenne from CSV_DAM_ACTIVITY; 
   select max(ROI_2), avg(ROI_2) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_3), avg(ROI_3) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_4), avg(ROI_4) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_5),  avg(ROI_5) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_6), avg(ROI_6) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_7), avg(ROI_7) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_8), avg(ROI_8) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_9), avg(ROI_9) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_10), avg(ROI_10) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_11), avg(ROI_11) as moyenne from CSV_DAM_ACTIVITY; 
   select max(ROI_12), avg(ROI_12) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_13), avg(ROI_13) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_14), avg(ROI_14) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_15), avg(ROI_15) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_16), avg(ROI_16) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_17), avg(ROI_17) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_18), avg(ROI_18) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_19), avg(ROI_19) as moyenne from CSV_DAM_ACTIVITY;
   select max(ROI_20), avg(ROI_20) as moyenne from CSV_DAM_ACTIVITY;`

它只显示了fly 20的结果

我试着把这一切都归结为一条指令：

   select max(ROI_1),avg(ROI_1),  max(ROI_2),  avg(ROI_2),  max(ROI_3),  avg (ROI_3),   max(ROI_4),  avg(ROI_4),max(ROI_5),  avg(ROI_5) , max(ROI_6),  avg(ROI_6) , max(ROI_7),  avg(ROI_7),  max(ROI_8),  avg(ROI_8) ,max(ROI_9),  avg(ROI_9), max(ROI_10),  avg(ROI_10) ,max(ROI_11),  avg(ROI_11),  max(ROI_12),  avg(ROI_12),  max(ROI_13),  avg(ROI_13), max(ROI_14),  avg(ROI_14),max(ROI_15),  avg(ROI_15), max(ROI_16),  avg(ROI_16), max(ROI_17),  avg(ROI_17), max(ROI_18),  avg(ROI_18), max(ROI_19),  avg(ROI_19), max(ROI_20),  avg(ROI_20),  from CSV_DAM_ACTIVITY ;

它显示了以下错误：result: near "from":syntax error

Answer 1

您得到的错误是语法错误。

第一条声明：

create table z (a int not null,b int not null);
insert into t values ( max(ROI_1)from CSV_DAM_ACTIVITY , avg(ROI_1) ) from  CSV_DAM_ACTIVITY);
select * from t ;

应该写成：

create table z (a int not null,b int not null);
insert into z(a, b) 
select max(ROI_1), avg(ROI_1) from CSV_DAM_ACTIVITY;

你可以在这里找到更多：SQLite Insert

这将使用值max(ROI_1)和avg(ROI_1)插入到新表z中。

在第二个和最后一个语句中，删除from前的最后一个逗号。

Answer 2

因此您有一个类似于以下内容的表：

CREATE TABLE CSV_DAM_ACTIVITY(ROI_1, ROI_2, ..., ROI_20);

一种更好的表设计是在一行中保存每个苍蝇在给定时间戳的速度：

CREATE TABLE fly_data(fly_id INTEGER, minute INTEGER, speed NUMERIC,
                      PRIMARY KEY(fly_id, minute)) WITHOUT ROWID;

然后，您可以使用以下命令获取每个飞行的最大速度和平均速度的列表：

SELECT fly_id, max(speed), avg(speed) FROM fly_data GROUP BY fly_id ORDER BY fly_id;

将CSV数据转换为这种结构的最简单方法可能是一个快速的ETL perl (或python或任何您喜欢的脚本语言)脚本，它将CSV文件的1行转换为20个插入。

INSERT INTO fly_data(fly_id, minute, speed) VALUES(1, 1, $row_1_col_1_value);
INSERT INTO fly_data(fly_id, minute, speed) VALUES(2, 1, $row_1_col_2_value);
...
INSERT INTO fly_data(fly_id, minute, speed) VALUES(1, 20, $row_20_col_1_value);
-- etc.

或者直接在sqlite中使用像这样的一组插入

INSERT INTO fly_data(fly_id, minute, speed)
  SELECT 1, rowid, roi_1 FROM CSV_DAM_ACTIVITY;
INSERT INTO fly_data(fly_id, minute, speed)
  SELECT 2, rowid, roi_2 FROM CSV_DAM_ACTIVITY;
-- and so on

只要CSV_DAM_ACTIVITY的CSV值表示来自rowid文件的行(以及时间戳)的插入顺序的相当安全的假设是真的。

(WITHOUT ROWID描述)