Alternative to Cross Apply in SQlite-CodePudding

I have a SQL query written for SQL Server but now I want to use it in SQLite.

Query

SELECT w.item_id,
       w.TotalStock,
       LastPartialStock.tranDate,
       LastPartialStock.StockToUse,
       LastPartialStock.RunningTotal,
       w.TotalStock - LastPartialStock.RunningTotal   LastPartialStock.StockToUse AS UseThisStock
FROM cteStockSum AS w CROSS APPLY (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

But SQLite does not support CROSS APPLY, so I have to rewrite it. One possible solution is to use subqueries for each column but that would result in not being able to use those column values as variables in the last column of the table i.e 'UseThisStock'.
Maybe I can use CTE but I am not able to figure this out.

I tried this

SELECT w.item_id,
       w.TotalStock,
       LastPartialStock.tranDate,
       LastPartialStock.StockToUse,
       LastPartialStock.RunningTotal,
       w.TotalStock - LastPartialStock.RunningTotal   LastPartialStock.StockToUse AS UseThisStock
FROM cteStockSum AS w , (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

But the columns of cteStockSum are not recognized nor using any joins.

And the performance for this is critical as there would be 1 million rows and because this query is part of a complex query written with CTEs.

CodePudding user response：

Assuming that your query returns 1 row for each item_id you could use an INNER join between the 2 ctes and aggregation:

SELECT s.item_id,
       s.TotalStock,
       MAX(r.date) tranDate,
       r.ThisStock StockToUse,
       r.RollingStock RunningTotal,
       s.TotalStock - r.RollingStock   r.ThisStock UseThisStock
FROM cteStockSum s INNER JOIN cteReverseInSum r
ON r.item_id = s.item_id AND r.RollingStock >= s.TotalStock
GROUP BY s.item_id;

The above query utilizes SQLite's feature of bare columns to return the row of cteReverseInSum with the max date.

CodePudding user response：

Move the subquery to your select clause in order to get the cteReverseInSum's ROWID (or something uniqely identifying a row within). Then use this ID in order to join the cteReverseInSum row.

SELECT
  ww.item_id,
  ww.TotalStock,
  LastPartialStock.tranDate,
  LastPartialStock.StockToUse,
  LastPartialStock.RunningTotal,
  ww.TotalStock - LastPartialStock.RunningTotal   LastPartialStock.StockToUse AS UseThisStock
FROM
  SELECT 
    w.*,
    (
      SELECT ROWID
      FROM cteReverseInSum AS z
      WHERE z.item_id = w.item_id
      AND z.RollingStock >= w.TotalStock
      ORDER BY z.date DESC
      LIMIT 1
    ) as z_rowid
  FROM cteStockSum AS w
) ww
JOIN cteReverseInSum AS LastPartialStock ON LastPartialStock.ROWID = ww.z_rowid;