SQLite中替代CROSS APPLY的方案实现及性能优化问询

原SQL Server查询通过CROSS APPLY为每个item_id匹配cteReverseInSum中符合RollingStock >= TotalStock条件的最新记录，由于SQLite不支持CROSS APPLY，可以通过以下两种高效方案重写，同时适配百万级数据的性能需求：

方案1：使用LATERAL JOIN（推荐，SQLite 3.33.0+） #

SQLite 3.33.0及以上版本支持LATERAL JOIN，功能和CROSS APPLY完全等价，能直接替换且性能最优：

SELECT w.item_id,
       w.TotalStock,
       LastPartialStock.tranDate,
       LastPartialStock.StockToUse,
       LastPartialStock.RunningTotal,
       w.TotalStock - LastPartialStock.RunningTotal + LastPartialStock.StockToUse AS UseThisStock
FROM cteStockSum AS w
-- 若需保留无匹配记录的行，改用LEFT JOIN LATERAL
INNER JOIN LATERAL (
    SELECT z.date AS tranDate,
           z.ThisStock AS StockToUse,
           z.RollingStock AS RunningTotal
    FROM cteReverseInSum AS z
    WHERE z.item_id = w.item_id
      AND z.RollingStock >= w.TotalStock
    ORDER BY z.date DESC
    LIMIT 1
) AS LastPartialStock ON 1=1

方案2：窗口函数预处理（兼容旧版SQLite） #

如果使用的SQLite版本低于3.33.0，可通过ROW_NUMBER()窗口函数提前为符合条件的记录排序，再关联查询：

WITH RankedStock AS (
    SELECT z.item_id,
           z.date AS tranDate,
           z.ThisStock AS StockToUse,
           z.RollingStock AS RunningTotal,
           -- 按item_id分组，日期降序排号，取最新的一条
           ROW_NUMBER() OVER (PARTITION BY z.item_id ORDER BY z.date DESC) AS rn
    FROM cteReverseInSum AS z
    -- 提前过滤掉和cteStockSum无匹配的记录，减少计算量
    WHERE EXISTS (
        SELECT 1 FROM cteStockSum AS w 
        WHERE w.item_id = z.item_id 
          AND z.RollingStock >= w.TotalStock
    )
)
SELECT w.item_id,
       w.TotalStock,
       r.tranDate,
       r.StockToUse,
       r.RunningTotal,
       w.TotalStock - r.RunningTotal + r.StockToUse AS UseThisStock
FROM cteStockSum AS w
JOIN RankedStock AS r 
    ON w.item_id = r.item_id 
    AND r.rn = 1
    AND r.RunningTotal >= w.TotalStock

性能优化关键（针对百万级数据） #

• 创建复合索引：为cteReverseInSum创建覆盖索引，让WHERE过滤和ORDER排序直接使用索引，避免全表扫描：

  CREATE INDEX idx_reverse_item_date_rolling ON cteReverseInSum(item_id, date DESC, RollingStock, ThisStock);
  

• 优化CTE逻辑：确保cteStockSum和cteReverseInSum本身的计算逻辑已优化，比如避免冗余字段、提前过滤无效数据。
• 索引item_id：如果cteStockSum的item_id未建立索引，建议创建索引加速关联过程。

说明原写法的问题 #

你尝试的逗号连接属于笛卡尔积查询，SQLite中FROM子句里的普通子查询无法引用外部表（cteStockSum AS w）的列，因此会出现w.item_id无法识别的错误，必须使用LATERAL或窗口函数方案解决关联问题。

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