改进大型时间序列数据集的逐组查询

Question

我有一个大表，里面有一整年的时间序列数据，每天有24个值(每小时一个(，用于多个客户。

价值

客户ID	日期
001	2020-01-01 00:00:00	xx
001	2020-01-01 00:01:00	xx
001	2020-01-01 00:02:00	xx
002	2020-01-01 00:00:00	xx
002	2020-01-01 00:01:00	xx
002	2020-01-01 00:02:00	xx

不需要额外的年、月和日列

您可以设置表达式的索引：

CREATE TABLE "data" (
"index" INTEGER NOT NULL,
"customer_id" INTEGER NOT NULL,
"date" TEXT NOT NULL, -- there is no DATETIME data type in SQLite
"value" FLOAT NOT NULL,
PRIMARY KEY("index")
);
CREATE INDEX "idx_data_ym" ON "data"(strftime('%Y-%m', date));
CREATE INDEX "idx_data_ymd" ON "data"(date(date)); -- equivalent of strftime('%Y-%m-%d', date)

对于这些查询：

SELECT STRFTIME('%Y-%m', date) AS year_month, 
SUM(value) AS total
FROM data 
GROUP BY year_month;
SELECT date(date) AS year_month_day, 
SUM(value) AS total
FROM data 
GROUP BY year_month_day; 

将使用适当的索引

请参阅演示

索引是加速操作的最佳方法。