我有一個帶有更改事件表的 SQLite 資料庫（使用 Django 作為 ORM）（Account分配了一個新的Strategy）。我想將其轉換為時間序列，以便每天都有Strategy以下Account內容。
我的桌子：
預期輸出：
如圖所示，每天可以有超過 1 次更改。在這種情況下，我選擇當天的最后一次更改，因為所需的時間序列輸出每天必須只有一個值。
我的問題類似于這個問題，但在 SQL 中，而不是 BigQuery（但我不確定我是否理解unnest他們提出的部分）。我在 Pandas 中有一個使用reindexand的作業解決方案fillna，但我確信 SQL 中有一個優雅而簡單的解決方案（使用 Django ORM 可能更好）。
uj5u.com熱心網友回復：
您可以使用RECURSIVE 公用表運算式生成第一個和最后一個之間的所有日期，然后將此生成的表與您的資料連接起來，以獲得每天所需的值：
WITH RECURSIVE daterange(d) AS (
SELECT date(min(created_at)) from events
UNION ALL
SELECT date(d,'1 day') FROM daterange WHERE d<(select max(created_at) from events)
)
SELECT d, account_id, strategy_id
FROM daterange JOIN events
WHERE created_at = (select max(e.created_at) from events e where e.account_id=events.account_id and date(e.created_at) <= d)
GROUP BY account_id, d
ORDER BY account_id, d
date()函式用于將日期時間值轉換為簡單日期，因此您可以使用它按日期對資料進行分組。
date(d, '1 day')將 1 日歷日的修飾符應用于 d。
這是您的資料的示例：
CREATE TABLE events (
created_at,
account_id,
strategy_id
);
insert into events
VALUES ('2022-10-07 12:53:53', 4801323843, 7),
('2022-10-07 08:10:07', 4801323843, 5),
('2022-10-07 15:00:45', 4801323843, 8),
('2022-10-10 13:01:16', 4801323843, 6);
WITH RECURSIVE daterange(d) AS (
SELECT date(min(created_at)) from events
UNION ALL
SELECT date(d,'1 day') FROM daterange WHERE d<(select max(created_at) from events)
)
SELECT d, account_id, strategy_id
FROM daterange JOIN events
WHERE created_at = (select max(e.created_at) from events e where e.account_id=events.account_id and date(e.created_at) <= d)
GROUP BY account_id, d
ORDER BY account_id, d
| d | 帳戶ID | strategy_id |
|---|---|---|
| 2022-10-07 | 4801323843 | 8 |
| 2022-10-08 | 4801323843 | 8 |
| 2022-10-09 | 4801323843 | 8 |
| 2022-10-10 | 4801323843 | 6 |
| 2022-10-11 | 4801323843 | 6 |
小提琴
多行查詢可能會很慢。在這種情況下，在 created_at 列上創建一個索引：
CREATE INDEX events_created_idx ON events(created_at);
uj5u.com熱心網友回復：
我的最終版本是@Andrea B. 提出的版本，性能略有提高，僅合并我們在聯接中需要的行，因此丟棄了該where子句。我也將其轉換null為date('now')
這是我使用的最終版本：
with recursive daterange(day) as
(
select min(date(created_at)) from events
union all select date(day, '1 day') from daterange
where day < date('now')
),
events as (
select account_id, strategy_id, created_at as start_date,
case lead(created_at) over(partition by account_id order by created_at) is null
when True then datetime('now')
else lead(created_at) over(partition by account_id order by created_at)
end as end_date
from events
)
select * from daterange
join events on events.start_date<daterange.day and daterange.day<events.end_date
order by events.account_id
希望這可以幫助 ！
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/514003.html
上一篇：如何在SQLite中添加一個帶有Case的列作為默認值？
下一篇：插入年齡<0時觸發更新年齡