Teradata FIFO脚本

Question

有一个旧的堆栈post (First-in-first-out (FIFO) inventory costing)，其中包含基于集合的速度:先进先出库存SQL问题：(https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/)。

我一直在尝试将其从SQL Server调整为Teradata SQL，但我发现：

(a) Teradata只能处理一条CTE with语句

(b)您不能使用交叉应用

(c)不能使用提示索引？

我的问题是：

在Teradata中有没有替代方法(除了易失性表)来绕过上面的(a)？

Terdata "Cross Join“是否与SQL Server中的Cross Apply相同？

有没有人把这个脚本改编成Teradata？

Answer 1

你的帖子已经发布了几个月了，但这篇文章可能对其他人也很有用。

当我想起几年前我把它移植到Teradata上时，Teradata的开发者交换上也有类似的问题。快速搜索其他解决方案后，我找到了这篇文章。

事实证明，这要简单得多，因为Teradata支持行无界前置(微软在SS2012中添加了这一点)：

关于你的问题：

答: CTE可以被派生表取代，它只是一种语法变体。

b: CROSS/OUTER APPLY是SQL Server专有语法，有时可以用OUTER JOIN代替，在这种情况下，它只是进行累积求和的一种复杂方法。

c:当优化器没有做好计划时，索引提示应该是最后的手段

SELECT
   ArticleId
  ,SUM(ItemCnt) AS CurrentItems -- same as TotalStock
  ,SUM(ItemCnt * CurrentPrice) AS CurrentValue
FROM
 (
   SELECT
      ArticleId

     -- how many items will be used from this transaction, maybe less than all for the oldest row
     ,CASE WHEN RollingStock + Items > TotalStock THEN TotalStock - RollingStock ELSE Items END AS ItemCnt

     -- find the latest IN-price for RET rows
     ,MAX(Price)
      OVER (PARTITION BY ArticleID, PriceGroup
            ORDER BY TranDate) AS CurrentPrice
   FROM
    (
      SELECT
         ArticleId ,TranDate ,Price ,Items --,TranCode

        -- dummy column to get the current price in the next step, new group starts with every 'IN'
        ,SUM(CASE WHEN TranCode = 'IN' THEN 1 ELSE 0 END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate
               ROWS UNBOUNDED PRECEDING) AS PriceGroup

        -- Aggregating all in/out movements -> number of items left in stock after all transactions
        ,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items ELSE -Items END)
         OVER (PARTITION BY ArticleID) AS TotalStock

        -- reverse sum of all inbound IN/RET movements
        ,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
         OVER (PARTITION BY ArticleID)
        -SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate
               ROWS UNBOUNDED PRECEDING) AS RollingStock
/*
        -- same as above, simpler syntax, but different ORDER BY results in extra STATS step in explain
        ,COALESCE(SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate DESC
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS RollingStock
*/
/*      -- cumulative sum, not needed to get the result
        ,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items ELSE -Items END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate
               ROWS UNBOUNDED PRECEDING) AS CurrentItems
*/
      FROM Stock
      -- only keep the row needed to calculate the value
      -- plus all IN rows to find the current price for RET rows in the next step
      -- to exclude items out of stock: add "AND (TotalStock > 0)"
      QUALIFY ((TranCode = 'IN') OR (RollingStock <= TotalStock AND TranCode = 'RET'))AND (TotalStock > 0)
    ) AS dt
   -- remove older IN rows
   QUALIFY ItemCnt >= 0
 ) AS dt
GROUP BY 1
ORDER BY 1

它基于与此处描述的制胜解决方案相同的逻辑：https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

这将运行得相当快，并且您不必创建SQL Server所需的任何索引:-)

移植到其他DBMSes的备注：

它是普通的标准SQL，只有限定符是特定于Teradata的。QUALIFY与过滤OLAP函数结果的HAVING for GROUP BY相同。通过将条件移动到外层的WHERE中，可以很容易地替换它。

Answer 2

您可以使用派生表而不是CTE