在 SQLite 中重组数据 [英] Reorganising Data in SQLite

问题描述

作为 Python 和 SQLite 的初学者，我想知道是否有更优雅的解决方案来解决我的任务.

AS a beginner on Python and SQLite I am wandering if there are more elegant solutions for the tasks I have.

我当前的 SQLite 数据库包含几个时间序列:

My current SQLite database contains several time series as:

CREATE TABLE Y1 (time REAL, value REAL);
CREATE TABLE Y2 (time REAL, value REAL);
CREATE TABLE Y3 (time REAL, value REAL);
...

并非所有变量的时间值都相同，但可能存在重复项.

The time values are not identical for all variables, however there might be dublicates.

但是，由于变量列表在增加，我想将数据传输到类似的结构中.表 'VARLIST' 应收集所有变量.

However, since the list of the variables is increasing, I want to transfer the data to a sturcture like. The Table 'VARLIST' should collect all variables.

CREATE TABLE VARLIST (VarID INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
INSERT INTO VARLIST VALUES(1, 'Y1');
INSERT INTO VARLIST VALUES(2, 'Y2');

...

数据点本身应传输到表，TIMESTAMPS 和 DATAPOINTS.时间戳在一个单独的表格中，因为我想将附加信息与这个邮票相关联(在这个简化的例子中没有显示).

The datapoints itself shall be transfered to the Tables, TIMESTAMPS and DATAPOINTS. The timestamps are in an sperate tabele since I want to associate additional information to this stamps (not shown in this siplified example).

CREATE TABLE TIMESTAMPS (timeID INTEGER PRIMARY KEY, time REAL);
CREATE TABLE DATAPOINTS (DataID INTEGER PRIMARY KEY, timeID INTEGER, VarID INTEGER, value REAL);

我的问题是:如何最好地传输表 Y1.. Y6 中的数据 TIMESTAMPS 和 数据点?当然，我可以将所有数据读出到 Python 中，然后让 Python 进行重组.无论如何，我想知道这是否也可以通过仅使用 SQLite 命令来实现.

My question is: How can I best transfer the data from the tables Y1.. Y6 in the Tables TIMESTAMPS and DATAPOINTS? Of course I can read out all the data to Python and let Python do the regrouping. Anyhow I want to know if this would be possible also by using only SQLite commands.

推荐答案

时间戳可以简单地复制:

The timestamps can simply be copied:

INSERT INTO TimeStamps(time)
SELECT time FROM Y1 UNION
SELECT time FROM Y2 UNION
SELECT time FROM Y3 UNION
SELECT time FROM Y4 UNION
SELECT time FROM Y5 UNION
SELECT time FROM Y6;

数据点需要查找(使用相关子查询)以获取相应的时间戳 ID:

The datapoints require a lookup (using a correlated subquery) for the corresponding timestamp ID:

INSERT INTO DataPoints(timeID, VarID, value)
SELECT (SELECT timeID
        FROM TimeStamps
        WHERE time = all_Y.time),
       VarID,
       value
FROM (SELECT time, 1 AS VarID, value FROM Y1 UNION ALL
      SELECT time, 2,          value FROM Y2 UNION ALL
      SELECT time, 3,          value FROM Y3 UNION ALL
      SELECT time, 4,          value FROM Y4 UNION ALL
      SELECT time, 5,          value FROM Y5 UNION ALL
      SELECT time, 6,          value FROM Y6) AS all_Y;

这篇关于在 SQLite 中重组数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！