时间戳

首页 > 解决方案 > SQLite 组日期时间按 168 小时（7 天）返回 NULL

sql - SQLite 组日期时间按 168 小时（7 天）返回 NULL

问题描述

我的 SQLite 数据库中有一个Orders表。我想要做的是每 168 小时（7 天）按数据分组，每 168 小时计数Orders一次。

我所做的是在内存中创建一个“日历表”，并将我的Orders表加入到该日历集。

当我按 12、24、48 甚至 120 小时（5 天）分组时，这很好用。但是由于某种原因，当我按 168 小时（7 天）分组时它不起作用。我得到NULL了值，而不是count()真正应该返回的值。

以下 sql 代码是按每 120 小时（5 天）分组的示例。

CREATE TABLE IF NOT EXISTS Orders (
    Id    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    Key   TEXT,
    Timestamp TEXT NOT NULL
);

INSERT INTO Orders (Key, Timestamp) VALUES ('k1', '2019-10-01 10:00:23');
INSERT INTO Orders (Key, Timestamp) VALUES ('k2', '2019-10-01 15:45:19');
INSERT INTO Orders (Key, Timestamp) VALUES ('k3', '2019-10-02 17:05:19');
INSERT INTO Orders (Key, Timestamp) VALUES ('k4', '2019-10-03 20:12:19');
INSERT INTO Orders (Key, Timestamp) VALUES ('k5', '2019-10-04 08:49:19');
INSERT INTO Orders (Key, Timestamp) VALUES ('k6', '2019-10-05 11:24:19');
INSERT INTO Orders (Key, Timestamp) VALUES ('k7', '2019-10-07 11:24:19');

WITH RECURSIVE dates(date1) AS (
    VALUES('2019-10-01 00:00:00')
    UNION ALL
    SELECT datetime(date1, '+120 hours')
    FROM dates
    WHERE date1 <= '2019-10-29 00:00:00'
)
SELECT date1 as __ddd, d2.* FROM dates AS d1

LEFT JOIN (
    SELECT count(Key) AS OrderKey, 
    datetime((strftime('%s', timestamp) / 432000) * 432000, 'unixepoch') as __interval 
    FROM `Orders` 
    WHERE `Timestamp` >= '2019-09-29T00:00:00.000' 
    GROUP BY __interval  LIMIT 10 
) d2 ON d1.date1 = d2.__interval

重要提示： 如果您想更新此代码以在 168 小时（7 天）内对其进行测试，那么您应该执行以下操作：

有人知道为什么当我将 sql 代码更改为 168 小时时它会停止正常工作吗？

标签： sqlsqlitegroup-by

解决方案

您的问题是，当您更改为 7 天间隔时，datesCTE 中的值与Orders表生成的间隔不一致。您可以通过使datesCTE 在类似对齐的日期开始来解决此问题：

WITH RECURSIVE dates(date1) AS (
    SELECT datetime((strftime('%s', '2019-10-01 00:00:00') / 604800) * 604800, 'unixepoch')
    UNION ALL
    SELECT datetime(date1, '+168 hours')
    FROM dates
    WHERE date1 <= '2019-10-29 00:00:00'
)

输出：

__ddd               OrderKey    __interval
2019-09-26 00:00:00 3           2019-09-26 00:00:00
2019-10-03 00:00:00 4           2019-10-03 00:00:00
2019-10-10 00:00:00 null        null
2019-10-17 00:00:00 null        null
2019-10-24 00:00:00 null        null
2019-10-31 00:00:00 null        null

dbfiddle 上的演示

推荐阅读