将事件型表转时间序列：SQL/Django ORM实现方案问询

一、SQLite 原生SQL实现 #

• 步骤1：获取每个账户每日最后一次策略变更
  从事件表中筛选每个账户每天的最后一次变更记录，用窗口函数ROW_NUMBER()按账户和日期分组，按变更时间降序排序，取每组第一条：

  WITH daily_last_changes AS (
      SELECT
          account_id,
          DATE(change_time) AS change_date,
          strategy_id,
          ROW_NUMBER() OVER (
              PARTITION BY account_id, DATE(change_time)
              ORDER BY change_time DESC
          ) AS rn
      FROM account_strategy_changes
  )
  SELECT account_id, change_date, strategy_id
  FROM daily_last_changes
  WHERE rn = 1
  

• 步骤2：生成目标日期范围的序列
  SQLite没有内置日期序列生成函数，用递归CTE生成所需日期区间（替换'2023-01-01'和'2023-12-31'为实际起止日期）：

  WITH date_series AS (
      SELECT '2023-01-01' AS target_date
      UNION ALL
      SELECT DATE(target_date, '+1 day')
      FROM date_series
      WHERE target_date < '2023-12-31'
  )
  

• 步骤3：关联账户、日期序列并填充缺失值
  结合前两个CTE，先获取所有账户与日期序列的笛卡尔积，左连接每日最后变更记录，最后用LAST_VALUE()窗口函数向前填充缺失的策略值：

  WITH daily_last_changes AS (
      SELECT
          account_id,
          DATE(change_time) AS change_date,
          strategy_id,
          ROW_NUMBER() OVER (
              PARTITION BY account_id, DATE(change_time)
              ORDER BY change_time DESC
          ) AS rn
      FROM account_strategy_changes
  ),
  date_series AS (
      SELECT '2023-01-01' AS target_date
      UNION ALL
      SELECT DATE(target_date, '+1 day')
      FROM date_series
      WHERE target_date < '2023-12-31'
  ),
  all_account_dates AS (
      SELECT a.id AS account_id, ds.target_date
      FROM accounts a
      CROSS JOIN date_series ds
  )
  SELECT
      account_id,
      target_date,
      LAST_VALUE(strategy_id IGNORE NULLS) OVER (
          PARTITION BY account_id
          ORDER BY target_date
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS current_strategy
  FROM all_account_dates
  LEFT JOIN daily_last_changes dlc
      ON all_account_dates.account_id = dlc.account_id
      AND all_account_dates.target_date = dlc.change_date
  ORDER BY account_id, target_date;
  

  注意：替换accounts为你的账户表实际名称；若无需包含从未有过变更的账户，可调整all_account_dates逻辑，仅保留有变更记录的账户。

二、Django ORM 实现 #

• 步骤1：获取每个账户每日最后一次变更
  使用Django的Window和RowNumber函数筛选每日最后一次变更：

  from django.db.models import Window, F
  from django.db.models.functions import RowNumber, TruncDate
  
  daily_last_changes = AccountStrategyChange.objects.annotate(
      change_date=TruncDate('change_time')
  ).annotate(
      rn=Window(
          expression=RowNumber(),
          partition_by=[F('account_id'), F('change_date')],
          order_by=F('change_time').desc()
      )
  ).filter(rn=1).values('account_id', 'change_date', 'strategy_id')
  

• 步骤2：生成日期序列
  Django ORM无直接生成日期序列的方法，用Python生成目标日期范围：

  from datetime import date, timedelta
  
  start_date = date(2023, 1, 1)
  end_date = date(2023, 12, 31)
  date_range = [start_date + timedelta(days=i) for i in range((end_date - start_date).days + 1)]
  

• 步骤3：关联并填充缺失值
  由于Django ORM对LAST_VALUE的IGNORE NULLS支持有限，可直接用RawSQL执行完整SQL逻辑（替换表名为你的Django模型对应数据库表名）：

  from django.db.models import RawSQL
  
  result = Account.objects.raw("""
  WITH daily_last_changes AS (
      SELECT
          account_id,
          DATE(change_time) AS change_date,
          strategy_id,
          ROW_NUMBER() OVER (
              PARTITION BY account_id, DATE(change_time)
              ORDER BY change_time DESC
          ) AS rn
      FROM account_strategy_changes
  ),
  date_series AS (
      SELECT '2023-01-01' AS target_date
      UNION ALL
      SELECT DATE(target_date, '+1 day')
      FROM date_series
      WHERE target_date < '2023-12-31'
  ),
  all_account_dates AS (
      SELECT a.id AS account_id, ds.target_date
      FROM accounts a
      CROSS JOIN date_series ds
  )
  SELECT
      account_id,
      target_date,
      LAST_VALUE(strategy_id IGNORE NULLS) OVER (
          PARTITION BY account_id
          ORDER BY target_date
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS current_strategy
  FROM all_account_dates
  LEFT JOIN daily_last_changes dlc
      ON all_account_dates.account_id = dlc.account_id
      AND all_account_dates.target_date = dlc.change_date
  ORDER BY account_id, target_date
  """)
  

内容的提问来源于stack exchange，提问作者Durand