Django/SQLite : Преобразование таблицы "событие-мужчина" во временную таблицу

У меня есть база данных SQLite (с Django в качестве ORM) с таблицей зарегистрированных событий изменений (Account присваивается новое Strategy).
Я хотел бы преобразовать ее во временную таблицу, чтобы иметь возможность иметь на каждый день Strategy, который Account был следующим.

Вот предварительный просмотр моей таблицы :

the base table : event-wise data

и вот ожидаемый результат :

the desired table : timeserie data

Для простоты я показал только один счет в качестве примера, но их несколько, поэтому операция group by на account_id может быть началом.
Кроме того, может быть более 1 изменения в день. В этом случае мы выбираем последнее изменение за день, так как желаемый вывод timeserie должен иметь только одно значение в день.

Мой вопрос полностью аналогичен этому, но в SQL, а не в BigQuery (и для полной прозрачности я не совсем уверен, что правильно понял, что происходит в предложенной ими части unnest).

У меня есть рабочее решение в pandas, но оно очень уродливое (с reindex и fillna), и я уверен, что есть элегантное и простое решение в SQL (или, возможно, даже лучше с Django ORM).

Вы можете использовать RECURSIVE Common Table Expression для генерации всех дат между первой и последней, а затем соединить эту сгенерированную таблицу с вашими данными, чтобы получить необходимое значение для каждого дня:

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() используется для преобразования значения datetime в простую дату, поэтому вы можете использовать ее для группировки данных по дате. date(d, '1 day') применяет к d модификатор +1 календарный день.

Вот пример с вашими данными:

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 account_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

fiddle

Запрос может быть медленным при большом количестве строк. В этом случае создайте индекс на столбце created_at:

CREATE INDEX events_created_idx ON events(created_at);

Моя окончательная версия - это версия, предложенная @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 < (select max(created_at) from events)
  ),
  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

Надеюсь, это поможет!

Вернуться на верх