如何將生成的日期時間與SQLite資料庫中的資料連接起來？-有解無憂

我有一個表格，其中包含來自這樣創建的傳感器的資料：

CREATE TABLE IF NOT EXISTS "aqi" (
  "time" datetime,
  "pm25" real,
  "pm10" real
);

當傳感器運行時，它每秒將資料發送到服務器（它寫入資料庫）。但是當傳感器沒有運行時，資料庫中的資料會出現這樣的“空白”（我已將time列重寫為可讀格式和 timezone GMT 01，將原始資料留在括號中）：

時間	下午 25 點	下午10點
...	...	...
`2021-12-28 18:44`( `1640713462`)	`9.19`	`9.27`
`2021-12-28 18:45`( `1640713522`)	`9.65`	`9.69`
`2021-12-28 18:46`( `1640713582`)	`9.68`	`9.76`
`2021-12-29 10:17`( `1640769421`)	`7.42`	`7.42`
`2021-12-29 10:18`( `1640769481`)	`7.94`	`7.98`
`2021-12-29 10:19`( `1640769541`)	`7.42`	`7.43`
...	...	...

我想創建一個查詢，從過去24小時內選擇的資料，輸出pm25并pm10作為NULL如果沒有在表中的當前時間資料。所以上面的表格看起來像這樣：

時間	下午 25 點	下午10點
...	...	...
`2021-12-28 18:44`( `1640713462`)	`9.19`	`9.27`
`2021-12-28 18:45`( `1640713522`)	`9.65`	`9.69`
`2021-12-28 18:46`( `1640713582`)	`9.68`	`9.76`
`2021-12-28 18:47`( `1640713642`)	`NULL`	`NULL`
`2021-12-28 18:48`( `1640713702`)	`NULL`	`NULL`
`2021-12-28 18:49`( `1640713762`)	`NULL`	`NULL`
...	...	...
`2021-12-29 10:14`( `1640769262`)	`NULL`	`NULL`
`2021-12-29 10:15`( `1640769322`)	`NULL`	`NULL`
`2021-12-29 10:16`( `1640769382`)	`NULL`	`NULL`
`2021-12-29 10:17`( `1640769421`)	`7.42`	`7.42`
`2021-12-29 10:18`( `1640769481`)	`7.94`	`7.98`
`2021-12-29 10:19`( `1640769541`)	`7.42`	`7.43`
...	...	...

_{^{我不介意秒數是否會因為時間的產生而不同......}}

I tried generating time for the last 24 hours using code from https://stackoverflow.com/a/32987070 and that works, as I wanted:

WITH RECURSIVE dates(generated_time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(generated_time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT strftime('%Y-%m-%d %H:%M', datetime(generated_time)) AS time
FROM dates;

But I don't know how to add (JOIN) data from the sensor (columns pm25, pm10) to query above... I tried something, but it outputs 0 rows:

WITH RECURSIVE dates(generated_time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(generated_time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT
    strftime('%Y-%m-%d %H:%M', datetime(generated_time)) AS generated_time,
    pm25, 
    pm10
FROM
    dates
    INNER JOIN aqi ON generated_time = strftime('%Y-%m-%d %H:%M', datetime(aqi.time));

Probably it's something really obvious, that I'm missing, but I have no idea :/

EDIT:
As @DrummerMann pointed out, it works with LEFT JOIN, but it takes around one whole minute to execute the query (in the database is around 14 000 values):

WITH RECURSIVE dates(time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT
    dates.time,
    aqi.pm25, 
    aqi.pm10
FROM
    dates
    LEFT JOIN aqi ON strftime('%Y-%m-%d %H:%M', datetime(dates.time)) = strftime('%Y-%m-%d %H:%M', datetime(aqi.time, 'unixepoch', 'localtime'))
    ORDER BY dates.time;

Is there any better way to do that?

uj5u.com熱心網友回復：

試試這個版本的 cte，它使用整數 unix 時間戳，其中秒數被剝離，并且連接的 ON 子句中沒有函式：

WITH RECURSIVE dates(generated_time) AS (
  SELECT strftime('%s', 'now', '-1 minute', 'localtime') / 60 * 60
  UNION ALL
  SELECT generated_time - 60
  FROM dates
  LIMIT 1440
)
SELECT strftime('%Y-%m-%d %H:%M', d.generated_time, 'unixepoch', 'localtime') AS generated_time,
       a.pm25, 
       a.pm10
FROM dates d LEFT JOIN aqi a
ON d.generated_time = a.time / 60 * 60;

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/405695.html

標籤：

上一篇：SQL布爾運算式

下一篇：SQL列資料型別無限制