SQLite从不完整交易时序计算每日股票持仓的SQL实现方案

你的代码有4个核心问题，直接导致结果不符合预期：

• 关联条件缺失维度匹配：左连接仅判断tr.Date <= dt.Date，没有关联Depot、Ticker字段，每个日期会匹配到所有历史交易记录，本质是产生了笛卡尔积，和你观察到的「接近交叉连接」现象完全一致。
• 未处理买卖方向：Buy or Sell字段区分买卖，卖出时Shares应该取负值参与累计计算，直接求和会把卖出股数算成持仓增加，数值结果完全错误。
• 未补全持仓的日期延续性：非交易日没有交易记录时，没有逻辑继承上一交易日的持仓值，直接关联会出现大量空值或者重复行。
• 语法疏漏：左连接的子查询没有设置别名tr，执行时会直接报别名不存在的错误。

以下代码适配你使用的SQLite语法，覆盖从2022-02-01到指定结束日期的每日持仓计算：

WITH RECURSIVE dates(trade_date) AS (
  -- 生成连续日期列表，如果只需要到2022-02-10可以把DATE()改成'2022-02-10'
  VALUES('2022-02-01')
  UNION ALL
  SELECT date(trade_date, '+1 day')
  FROM dates
  WHERE trade_date < DATE()
),
-- 预处理交易数据：卖出转负数，按天聚合同账户同股票的交易净额
trade_agg AS (
  SELECT
    Date,
    Depot,
    Ticker,
    SUM(
      CASE WHEN "Buy or Sell" = 'Buy' THEN Shares
           WHEN "Buy or Sell" = 'Sell' THEN -Shares
           ELSE 0 END
    ) AS daily_net_shares
  FROM TRANSACTION
  GROUP BY Date, Depot, Ticker
),
-- 计算每个账户每只股票在交易日的累计持仓
trade_cum AS (
  SELECT
    Date,
    Depot,
    Ticker,
    SUM(daily_net_shares) OVER (
      PARTITION BY Depot, Ticker
      ORDER BY Date
      ROWS UNBOUNDED PRECEDING
    ) AS total_shares
  FROM trade_agg
),
-- 生成所有日期+账户+股票的全量维度组合
full_dim AS (
  SELECT
    d.trade_date,
    t.Depot,
    t.Ticker
  FROM dates d
  CROSS JOIN (SELECT DISTINCT Depot, Ticker FROM TRANSACTION) t
)
-- 关联累计持仓，填充非交易日的持仓值
SELECT
  f.trade_date AS Date,
  f.Depot,
  f.Ticker,
  -- 无历史交易时持仓返回0，需要null可去掉COALESCE
  COALESCE(
    LAST_VALUE(c.total_shares) IGNORE NULLS OVER (
      PARTITION BY f.Depot, f.Ticker
      ORDER BY f.trade_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ),
  0) AS [Share Count]
FROM full_dim f
LEFT JOIN trade_cum c
  ON f.trade_date = c.Date
  AND f.Depot = c.Depot
  AND f.Ticker = c.Ticker
ORDER BY f.trade_date, f.Depot, f.Ticker

• 先把卖出股数转为负值，避免持仓计算方向错误，同时聚合同一天同账户同股票的多笔交易，避免重复计算。
• 先构建全量的「日期+账户+股票」维度组合，从根源避免笛卡尔积问题。
• 关联交易数据时同时匹配日期、账户、股票三个字段，仅在交易日匹配到对应的累计持仓。
• 用LAST_VALUE() IGNORE NULLS窗口函数自动填充非交易日的持仓，自动继承最近一次交易后的持仓数量，不需要额外写子查询遍历找最近交易日期。
• 如果你的SQLite版本不支持IGNORE NULLS语法，可以替换为相关子查询的写法，匹配每个日期之前最近的交易日持仓即可兼容低版本。

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