在我的sqlite数据库中,我动态生成了几个(~150(表,每个表代表一个由时间戳和浮点值组成的数据集。这些表格可以通过以下语句描述或生成:
create table "MY_FIRST_TABLE"
(
Date TIMESTAMP,
Value REAL
);
create index "ix_MY_FIRST_TABLE_Date"
on "MY_FIRST_TABLE" (Date);
现在,我想创建一个查询,为每个表检索具有有效值(不等于None(的第一个和最后一个日期。
我已经发现,我可以用以下语句查询数据库中所有表的列表:
SELECT
name
FROM
sqlite_schema
WHERE
type ='table' AND
name NOT LIKE 'sqlite_%';
此外,我有两个命令来查询第一个和最后一个具有有效值(不等于None(的日期:
SELECT Date
FROM "MY_FIRST_TABLE"
WHERE Value is not null
ORDER BY Date ASC
LIMIT 1;
SELECT Date
FROM "MY_FIRST_TABLE"
WHERE Value is not null
ORDER BY Date DESC
LIMIT 1;
现在我的问题是,我如何将这三个单独的查询合并到一个语句中,最终得到一个表,该表包含每个表的名称及其有效值的第一个和最后一个日期(不等于None(,结果如下:
表 | 第一个 | 最后一个|
---|---|---|
FIRST_TABLE_NAME | 2011年1月1日 | 2021年1月12日|
SECOND_TABLE名称 | 2011年1月5日 | 2021年2月5日|
THIRD_TABLE名称 | 2011年6月1日 | 2021年11月8日
如果使用聚合,则可以合并最后两个查询:
SELECT 'MY_FIRST_TABLE' TableName,
MIN(Date) First,
MAX(Date) Last
FROM "MY_FIRST_TABLE"
WHERE Value IS NOT NULL;
然后使用编程语言构造sql语句(使用UNION ALL
(,并在从第一个查询中获得的表上循环
或者,使用SQLite,您可以使用获得sql语句
WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table')
SELECT GROUP_CONCAT(
'SELECT ''' || name ||
''' TableName, MIN(Date) First, MAX(Date) Last FROM "' || name || '" ' ||
'WHERE Value IS NOT NULL',
' UNION ALL '
) sql
FROM tables;
这返回一个字符串,如:
SELECT 'MY_FIRST_TABLE' TableName, MIN(Date) First, MAX(Date) Last FROM "MY_FIRST_TABLE" WHERE Value IS NOT NULL
UNION ALL
SELECT 'MY_SECOND_TABLE' TableName, MIN(Date) First, MAX(Date) Last FROM "MY_SECOND_TABLE" WHERE Value IS NOT NULL
UNION ALL
SELECT 'MY_THIRD_TABLE' TableName, MIN(Date) First, MAX(Date) Last FROM "MY_THIRD_TABLE" WHERE Value IS NOT NULL